phpmysql search db

[fauzey]

Hi can someone help me please. I'm stuck in here. I want to make a search form using dropdown month & year. but keeps getting error 'Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in D:\xampp\htdocs\testdb\search.php on line 32'

Here is my php code:

<form method="post" action="search.php?go" id="searchform">
<label>
<select name="calendar" id="calendar">
<option>------</option>
<option value="Jan">01</option>
<option value="Feb">02</option>
<option value="Mar">03</option>
</select>
</label>
<select name="year" id="year">
<option>------</option>
<option value="2010">2010</option>
<option value="2011">2011</option>
<option value="2012">2012</option>
</select>
<input type="submit" name="submit" value="Search">
</form>
<?php
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
if(preg_match("/^[ a-zA-Z0-9]+/", $_POST['calendar'], $_POST['year'] )){
$month=$_POST['calendar'];
$year=$_POST['year'];

$db=mysql_connect ("localhost", "root", "root") or die ('I cannot connect to the database because: ' . mysql_error());
$mydb=mysql_select_db("contoh");

$sql = "SELECT *, DATE_FORMAT(date, '%c') AS month, DATE_FORMAT(date, '%Y') AS year FROM data WHERE month LIKE '%$month%' AND year LIKE '%$year%'";

$result=mysql_query($sql);

while($row=mysql_fetch_array($result)){

echo "<ul>\n";

echo "{$row['date']} <br>";
echo "{$row['normal_date']} <br>";
echo "{$row['manual']} <br>";
echo "</ul>";
}
}
else{
echo "<p>Please enter a search query</p>";
}
}
}
?>

[enigma1]

Hi fauzey welcome to the forums.

Your query fails, I don't know the columns of the table to see where the error occurs. Put some error handling see what it says. Or it maybe something from the form input fields.

$result = mysql_query($sql) or die($sql . '<br />' . mysql_errno() . '<br />' . mysql_error());

[fauzey]

Hi anigma1 thanks for replying my post. I put the error handling and it says

SELECT DATE_FORMAT(date, '%c') AS month, DATE_FORMAT(date, '%Y') AS year FROM data WHERE month LIKE '%09%' AND year LIKE '%Array%'
1054
Unknown column 'month' in 'where clause'

my table look like this. I want to convert the date field as month & year.

id | date | shift | manual | tng | stag

actually what I'm going to do is make a search form for user to search record by date.

like this:
[daniweb.com...]

[enigma1]

Yes the month is not a db column and also the calendar appears to be an array at input presumably because of the pregmatch which isn't correct.

Try few things:

Change the lists so they are

<form method="post" action="search.php?go" id="searchform">
<label>
<select name="calendar" id="calendar">
<option>------</option>
<option value="1">01</option>
<option value="2">02</option>
<option value="3">03</option>
</select>
</label>
<select name="year" id="year">
<option>------</option>
<option value="2010">2010</option>
<option value="2011">2011</option>
<option value="2012">2012</option>
</select>
<input type="submit" name="submit" value="Search">
</form>

Then change the php so it is:

f(isset($_POST['submit'])){

$sql_time = date ("Y-m-", gmmktime(0,0,0,(int)$_POST['calendar'],1,(int)$_POST['year']));

$sql = "SELECT * FROM data WHERE date LIKE '$sql_time%'";

$result=mysql_query($sql);
// .........rest of code
}

See if it helps and make sure the date column is of type DATE in the database.