Help! Trying to create MySQL search form using PHP

[kelseyirene]

I've been struggling with this all afternoon. The code seems correct, no problems are turning up, I'm connecting to the database, but the search isn't yielding results that I know are there. My table is fairly simple, it's basically a list of the books I own... my columns are 'num' 'title' 'author_first' 'author_last' 'isbn' and 'read'. Ideally, I'd like to be able to search for any keyword in any of the columns, but for now I just want to figure out how to do the search form.

 

Here's the code I used for the form:

 

<form method="post" action="search.php">

<div align="center">

<table border="0" cellpadding="0" cellspacing="0">

<tr>

<td bordercolor="#000000">

<p align="center">

<select name="num" size="1">

<option value="title">Title</option>

<option value="telephone">Author Last</option>

<option value="birthday">ISBN</option>

</select> <input type="text" name="search" size="25">  <br>

Search database: <input type="submit"></p>

</td>

</tr>

</table>

</div>

</form>

 

--

 

and this is the form I used for search.php:

 

<center>

<table border="1" cellpadding="5" cellspacing="0" bordercolor="#000000">

<tr>

<td width="60"><b>num</b></td>

<td width="100"><b>title</b></td>

<td width="70"><b>author last</b></td>

<td width="150"><b>isbn</b></td>

</tr>

<tr>

<td>

<?

$con = mysql_connect("localhost","username","password") or die(mysql_error());

 

mysql_select_db("my_db",$con);

 

?>

 

<?

//error message (not found message)begins

$XX = "No Record Found, to search again please close this window";

//query details table begins

$query = mysql_query("SELECT * FROM TitleInfo WHERE $num LIKE '%$search%' LIMIT 0, 50");

while ($row = @mysql_fetch_array($query))

{

$variable1=$row["num"];

$variable2=$row["title"];

$variable3=$row["author_last"];

$variable4=$row["isbn"];

//table layout for results

 

print ("<tr>");

print ("<td>$variable1</td>");

print ("<td>$variable2</td>");

print ("<td>$variable3</td>");

print ("<td>$variable4</td>");

print ("</tr>");

}

//below this is the function for no record!!

if (!$variable1)

{

print ("$XX");

}

//end

?>

</table>

</center>

 

 

</html>

 

--

 

help!! what am I doing wrong?

[kelseyirene]

eek! I'm new to the forums and posted it to the wrong place. I'm reposting in the correct forum... sorry!

[mrMarcus]

i don't see where you're defining/setting $search.

 

EDIT: do you have register_globals() on?  what version of PHP are you running?

 

and, is $num supposed to be just num, without the $?  otherwise, you haven't set $num, either.  and if $num is actually supposed to be a variable, is it a number?  'cause that won't work.

 

answer those for me please, and also add trigger_error() to your query, like so:

 

$query = mysql_query("SELECT * FROM TitleInfo WHERE $num LIKE '%$search%' LIMIT 0, 50") or trigger_error (mysql_error());

[kelseyirene]

Thank you for your response! I'm running PHP version 5.2.11. you're right, I never defined $search....updated that, and also I did mean to use $num without the $, but for now I changed num to title. with the necessary changes, the form is working, but there are still a couple things that aren't. on the results page, everything is showing up but the ISBN, and I know there's an ISBN value assigned to the particular books I searched. I checked both codes and it seems like it should work, but it doesn't.

 

also - with the form I built, there's the drop-down menu to choose from which column value the person wants to search. right now I've specified in the $query .. WHERE title LIKE .. so I know the search won't go through for the author_last or isbn. is there a way to build the code so it can search whichever is designated? or does that get way too complicated?

 

thank you so much for your patience. I'm teaching myself and it's slowgoing but fun!

 

here's the updated code:

 

search.html -

 

<form method="post" action="search.php">

<div align="center">

<table border="0" cellpadding="0" cellspacing="0">

<tr>

<td bordercolor="#000000">

<p align="center">

<select name="num" size="1">

<option value="title">Title</option>

<option value="author_last">Author Last</option>

<option value="isbn">ISBN</option>

</select> <input type="text" name="search" size="25">  <br>

Search database: <input type="submit"></p>

</td>

</tr>

</table>

</div>

</form>

 

search.php -

 

<center>

<table border="1" cellpadding="5" cellspacing="0" bordercolor="#000000">

<tr>

<td width="60"><b>num</b></td>

<td width="100"><b>title</b></td>

<td width="70"><b>author last</b></td>

<td width="150"><b>isbn</b></td>

</tr>

<tr>

<td>

<?

$con = mysql_connect("localhost","username,"password") or die(mysql_error());

 

mysql_select_db("my_db",$con);

 

$search=$_POST['search'];

 

//error message (not found message)begins

$XX = "No Record Found, to search again please close this window";

 

$query = mysql_query("SELECT * FROM TitleInfo WHERE title LIKE '%$search%' LIMIT 0, 50") or trigger_error (mysql_error());;

 

while ($row = @mysql_fetch_array($query))

{

$variable1=$row["num"];

$variable2=$row["title"];

$variable3=$row["author_last"];

$variable4=$row["isbn"];

//table layout for results

 

print ("<tr>");

print ("<td>$variable1</td>");

print ("<td>$variable2</td>");

print ("<td>$variable3</td>");

print ("<td>$variable4</td>");

print ("</tr>");

}

//below this is the function for no record!!

if (!$variable1)

{

print ("$XX");

}

//end

?>

</table>

</center>