[Resolved] search query problem

jadeddog

so what im trying to do is build my first search script, i have a database that i want to search.... there is a form that asks for search criteria, user enters it and then hits search button, this passes the value 'criteria' to this .php file, which then searches the database and displays the results (in this case im only displaying the name field of the database record... i am getting the "Couldn’t execute query" error when i try to search.... here is the code im using, andy help would be greatly appreciated, thx in advance

<html>
<head><title>Page</title></head>
<?php
include("other.inc");
$connection = mysql_connect($host,$user,$password)or die ("couldn’t connect to server");
$db = mysql_select_db($database,$connection)or die ("Couldn’t select database");

$userinput = explode(" ", "{$_POST['criteria']}");

//in case they don't enter any text
if (!$userinput)
{
$userinput = '%';
echo "no search criteria entered.";
}
else
{
for ($i = 0; $i < count($userinput); $i++)
{
$query = "select * from houses and name like '%$userinput[$i]%'
or address like '%$userinput[$i]%'
or city like '%$userinput[$i]%'
or province like '%$userinput[$i]%'
or postalCode '%$userinput[$i]%'
or phoneNum '%$userinput[$i]%'
or altPhoneNum like '%$userinput[$i]%'
or description like %$userinput[$i]%'
or grouping like '%$userinput[$i]%'
or lattitude '%$userinput[$i]%'
or longitude like '%$userinput[$i]%'";
}
}

/ Display results in a table /
$result = mysql_query($query) or die ("Couldn’t execute query.");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
extract($row);
echo "$name<br>";
}
?>

PallaviDalvi

heres what i wud do in this situation. echo the contents of $query. the prob lies here. some how the query string is not getting created correctly.

jadeddog

ok i put a echo"$query" statement after the query itself and this is what i get when i type in "test" in the search field

success
SELECT * FROM houseswhere name like '%test%' or address like '%test%' or city like '%test%' or province like '%test%' or postalCode '%test%' or phoneNum '%test%' or altPhoneNum like '%test%' or description like %test%' or grouping like '%test%' or lattitude '%test%' or longitude like '%test%'Couldn’t execute query.

so its passing the search variable, but im not sure why its not finding any matches, and im still getting the "Couldnt execuate query" error, even though it seems to be getting pushed the search variable

also if i enter two terms in the search field, i get the following

so again, the variables are getting passed to the $userinput array, and the loop is running the correct amount of times, but still getting the "couldnt execute query" error.... im baffled

PallaviDalvi

ok... am not sure if its a typo of this prob carries to ur query string also....
SELECT * FROM houses where name like '%test%' or address like '%test%' or city like '%test%' or province like '%test%' or postalCode like '%test%' or phoneNum like '%test%' or altPhoneNum like '%test%' or description like %test%' or grouping like '%test%' or lattitude like '%test%' or longitude like '%test%

also there was no space between houses and where.

if this is just a typo... the next step i'd take is to copy the created query string and execute it as is it in MYSQL environment. that shud give a proper description of the errors. although i'm sure there must be a way to get the SQL errors in PHP i'm not aware of it.

jadeddog

ok so i made the typo corrections and that seemed to solve most of it, it now will properly search for one word and display the appropriate results.... but it is still not searching for more than one work correctly.... for example there is a city field in the database where one of the cities names is "prince albert".... if i search for either "prince" or "albert" it will find the record properly, but if i search for "prince albert" it gives the error message for couldnt execute query

here is the updated code:

$userinput = explode(" ","{$POST['criteria']}");
$query = "SELECT * FROM houses";
echo "success<br>";
if ("{$POST['criteria']}" == "")
{
echo "no search criteria entered.";
exit;
}
//in case they don't enter any text
else
{
$arraycount = count($userinput);
for ($i = 0; $i < $arraycount; $i++)
{
$query .= " where name like '%$userinput[$i]%'
or address like '%$userinput[$i]%'
or city like '%$userinput[$i]%'
or province like '%$userinput[$i]%'
or postalCode like '%$userinput[$i]%'
or phoneNum like '%$userinput[$i]%'
or altPhoneNum like '%$userinput[$i]%'
or description like '%$userinput[$i]%'
or grouping like '%$userinput[$i]%'
or lattitude like '%userinput[$i]%'
or longitude like '%$userinput[$i]%'";
}
}

echo($query);

/ Display results in a table /
$result = mysql_query($query) or die ("Couldn’t execute query.");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
extract($row);
echo "$name<br>";
}
?>

and here is the results from the echo($query); command:

success
SELECT * FROM houses where name like '%prince%' or address like '%prince%' or city like '%prince%' or province like '%prince%' or postalCode like '%prince%' or phoneNum like '%prince%' or altPhoneNum like '%prince%' or description like '%prince%' or grouping like '%prince%' or lattitude like '%userinput[0]%' or longitude like '%prince%' where name like '%albert%' or address like '%albert%' or city like '%albert%' or province like '%albert%' or postalCode like '%albert%' or phoneNum like '%albert%' or altPhoneNum like '%albert%' or description like '%albert%' or grouping like '%albert%' or lattitude like '%userinput[1]%' or longitude like '%albert%'Couldn’t execute query.

you will notice that the lattitude column is not getting the userinput variable passed to it, but is instead just incrementing the array.... not sure what is happening here, thx for the help so far.... im hoping somebody can further help me, ive been at this for hours so far

PallaviDalvi

this doesnt work simply coz the query is constructed incorrectly. a select query cannot have two where clauses!

SELECT * FROM houses where name like '%prince%' or address like '%prince%' or city like '%prince%' or province like '%prince%' or postalCode like '%prince%' or phoneNum like '%prince%' or altPhoneNum like '%prince%' or description like '%prince%' or grouping like '%prince%' or lattitude like '%userinput[0]%' or longitude like '%prince%' or name like '%albert%' or address like '%albert%' or city like '%albert%' or province like '%albert%' or postalCode like '%albert%' or phoneNum like '%albert%' or altPhoneNum like '%albert%' or description like '%albert%' or grouping like '%albert%' or lattitude like '%userinput[1]%' or longitude like '%albert%

am not very sure as to why the lattitude didnt get the substituited value.

xblue

you missed the $ in this line, so it is not recognized as a variable

or lattitude like '%userinput[$i]%'

one more suggestion: use mysql_error() instead of the static message "Couldn’t execute query." to detect why the query failed.

jadeddog

thank you both for the help, it is much appreciated.... it works fine now..... i didnt know that you could not have two where parameters in a query, but i do now! oh the joys of self teaching, lol.... but thx again