Stuck on php search

Any help is appreciated...

[jspeed]

I am attempting to implement a pretty simple search function to my database. I've already got it to work, but heres the problem. I am trying to define what column to search in by a post variable. It seems simple enough, but I can't get it to work for some reason. Any idea's? I am running PHP version 4.4.6 and MySQL version 4.0.27-standard if that helps.

Error message:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'column = 'name' LIKE '%search%'' at line 1

---------------------------------
Here is my form:

<form method="POST" action="search.php">
Search By:<br />
<SELECT NAME='column'>
<OPTION VALUE='number'>Number
<OPTION VALUE='name'>Name
<OPTION VALUE='address'>Address
<OPTION VALUE='zip'>Zip Code
</SELECT>
<input type="text" name="search" size=25 maxlength=25>
<input type="Submit" name="submit" value="SEARCH">
</form><br />

---------------------------------
And here is my search page:

<?php

$search = $_POST['search'];
$column = $_POST['column'];

require 'connect.php';

// select the specific database name we want to access.
if (!mysql_select_db($dbname)) die(mysql_error());

//get the mysql and store them in $result
$result = mysql_query("SELECT * FROM $dbtable WHERE column = '$column' LIKE '%$search%'") or die(mysql_error());

//grab all the content
while($r=mysql_fetch_array($result))
{

$id=$r["id"];
$number=$r["number"];
$name=$r["name"];
$address=$r["address"];
$zip=$r["zip"];

//display the row
echo "<div align='center'>SEARCH RESULTS: <br>Index Number: $id <br>Number: $number <br>Owner Name: $name <br>Address: $address ¦ $zip <br></div>";
}
?>
---------------------------------

Thanks in advance for any help...

[adwatson]

I'd recommend outputting your query first in your html before running it - then copy and paste into your mySQL console or phpmyadmin and see what happens...

Also, be sure you're not opening up a security loophole by letting people send other column names to your php and getting values that you may not want them seeing... So you may want an if statement checking that the column specified is one of the set of columns you want to allow searching one, just to be careful.

[gergoe]

The SQL query is wrong, you either meant

SELECT * FROM $dbtable WHERE (column = '$column') AND (column_value LIKE '%$search%')

or

SELECT * FROM $dbtable WHERE ($column LIKE '%$search%')

[jspeed]

thanks that fixed it.

how are you suppose to figure that kind of thing out on your own? granted i am pretty new to php.

[phnord]

If you can, download the MySQL GUI tools and debug your SQL on your dev box...or use phpMyAdmin in a dev environment as suggested.

Also, you should be handling your inputs better as adwatson said.

[php.net...]

or at min. be using "addslashes" and some kind of data checks.

That version of MySQL is pretty outdated and will most likely weigh you down in doing any advanced queries or even doing functions/stored procs. Sub-queries weren't even available until v. 4.1.1. I don't even think there is development being done for MySQL v4.x anymore.

[gergoe]

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'column = 'name' LIKE '%search%'' at line 1

It says that the sql command you executed is somehow wrong. mySQL is not really clear about syntax errors, but at least it mentions where the problem is. In this case it says something is wrong around "column = 'name' LIKE '%search%'", and by examining the whole query and the part in question, it becomes clear that the left operand from the LIKE operator is missing. In SQL the operators are always like this:

left_operand operator right_operand

You can read more about querying data here:
[dev.mysql.com...]