Page 1 of 2

search mysql database for records

Posted: Wed May 12, 2004 7:52 pm
by ]{ronic
Hey,

Can anyone see why the following script is not displaying the correct results:

Code: Select all

<html>
<head>
<title>Search</title>
</head>
<body>

<?php
$db_server = "#####";
$user = "#####";
$pass = "#####";
$db_private = "#####";
//They are searching...do that for them (we know this from the hidden "go" field in the table)
if ($_POST&#1111;go] == "True") &#123;
//Open database server connection
$db_connect = mysql_connect($db_server, $user, $pass, $db);
if (! $db_connect)&#123;
Echo "Couldn't connect";
exit;
&#125;

//Open database
if (! mysql_select_db($db_private, $db_connect))&#123;
echo "Error selecting database to use " . mysql_error();
exit;
&#125;

//Build the SQL statement based on POST options
//The basic SQL will select all records
$db_query = "SELECT";
$db_query .= "description";
$db_query .= "FROM";
$db_query .= "C_Memory";

//depending on which of the following are filled in (none, some, all)
//the resultset returned will be filtered down
if (strlen(trim($_POST&#1111;description])) > 0) &#123;$db_query .= "AND description LIKE '$_POST&#1111;description]%' ";&#125;
echo $db_query;
exit;

//Run our query
$result1 = mysql_query($db_query, $db_connect);
if (! $result1)&#123;
echo mysql_error();
exit;
&#125;

//loop through all the results and display them
echo "<table cellpadding="2" border="1"><tr><td colspan=5>Search Results</td>\r\n";
while($row = mysql_fetch_array($result1)) &#123;
echo "<tr><td>$row&#1111;description]></td></tr>\r\n";
&#125; // while

echo "</table>\r\n\r\n";
echo "</body></html>";
//exit out
exit;
&#125;
?>
<table width="500">
<tr>
<td width="500">Search Products </td>
</tr>
</table>
<table>
<form name="mysearch" action="search.php" method="post">
<tr>
<td>Product Description</td><td><input type="text" size="20" value="" name="Product Description"></td>
</tr>
<tr>
<td colspan="2" align="center">
 <input type="hidden" name="go" value="True">
 <input type="submit" value="Search">
</td>
</tr>
</table>
</form>
</body>
</html>
When the script is run .. it returns instead of any results:

Code: Select all

SELECTdescriptionFROMC_MemoryWHERE 1
Any know why this is happening .. or maybe knows of another script that I could use to search the mysql database and return the results

Thanks

]{ronic

Posted: Wed May 12, 2004 8:00 pm
by launchcode

Code: Select all

if ($_POST[go] == "True") { 
should be:
if ($_POST['go'] == "True") {
and change this:

Code: Select all

$db_query = "SELECT";
$db_query .= "description";
$db_query .= "FROM";
$db_query .= "C_Memory";
to this:

Code: Select all

$db_query = "SELECT description FROM C_Memory";
finally, change:

Code: Select all

if (strlen(trim($_POST[description])) > 0) {$db_query .= "AND description LIKE '$_POST[description]%' ";}
echo $db_query;
exit;
to this:

Code: Select all

$desc = $_POST['description'];
if (strlen($desc) > 0) {
$db_query .= " WHERE description LIKE '$desc%'";
}
Remove the lines "echo $db_query" and "exit" - they are why nothing is happening!

Where on earth did you find this script? It's quite badly written :) (sorry if you wrote it!)

Posted: Wed May 12, 2004 8:28 pm
by ]{ronic
Hey,
I didn't write the script
Thanks for your help .. I found the script in a post on another forum ..

With the changes you suggested it is working alot better .. Altho it is displaying all the records in the "description" fields not just the ones containg the word used in the search ..

Say I type in samsung .. I only want it to return "description" fields that have the word samsung within that field .. Any idea on how to acheive this?

Even if you have a better script in mind to acheive this ..

I have no idea on search scripts .. and havnt found much around the net on how to create them.

Thanks

]{ronic

Posted: Wed May 12, 2004 8:39 pm
by launchcode
Sure, just change this:

<input type="text" size="20" value="" name="Product Description">

to:

<input type="text" size="20" value="" name="description">

Now it should work when you submit the form, i.e. it'll only bring back records matching what you entered.

Posted: Wed May 12, 2004 8:58 pm
by ]{ronic
Hey

Ausome, that works great .. Altho if I use "samsung" or "winbond Seitec" which are the fist word/s in the description field that works fine .. but if I search "256MB" which is a few words into the field it brings back no results... or search "PC2700" which is the next word after "samsung" .. Any idea on how to make it show results for the other words ie "PC2700"?

Also can u search across multuple tables or just one?


Thanks heaps .. your a legend


]{ronic

Posted: Wed May 12, 2004 9:02 pm
by launchcode
The trick to the search is this part:

$db_query .= " WHERE description LIKE '$desc%'";

Think of the % sign as the equivalent of a * in a directory listing. i.e.

*.gif would list all gif files, right?

So if you want to match a string anywhere in the field, you could do:

$db_query .= " WHERE description LIKE '%$desc%'";

Ideally you should be using a fulltext search on this, but that depends on your version of MySQL - so try the above for now.

Posted: Wed May 12, 2004 9:15 pm
by ]{ronic
Hey,

That works great .. Thanks

Can u search multiple tables ie:

$db_query = "SELECT description, price FROM C_Memory, C_Audio, C_VGA";

As you would by adding the fields?

I guess not I tried it .. is it posible to do so some how?


]{ronic

Posted: Wed May 12, 2004 9:17 pm
by launchcode
Yes, just different syntax:

SELECT table1.*, table2.* FROM table1,table2 WHERE table1.field LIKE '%whatever%' OR table2.field LIKE '%whatever%'

Posted: Wed May 12, 2004 9:26 pm
by ]{ronic
Hmmz so u mean:

Code: Select all

$db_query = "SELECT C_Memory.*, C_Audio.*, C_VGA.*FROM C_Memory,C_Audio,C_VGA WHERE C_Memory.description LIKE '%whatever%' OR C_Audio.description LIKE '%whatever%' OR C_VGA.description LIKE '%whatever%'";
Altho i wanted to add the price field as well which i had as:

Code: Select all

$db_query = "SELECT description, price FROM C_Memory";
If i have like 20 tables .. is this a messy way to do it?

]{ronic

Posted: Wed May 12, 2004 9:39 pm
by launchcode
Yes that is pretty much how you would do it although watch your spacing (you have the C_VGA.*FROM with no space before FROM which would cause an SQL error).

Doing this across 20 tables isn't ideal, but is still perfectly valid SQL. The only other way would be to create a temporary table by dragging in all of the fields from all of the tables you need and then searching that - but I wouldn't recommend it really.

Posted: Wed May 12, 2004 9:53 pm
by ]{ronic
I have:

Code: Select all

//Build the SQL statement based on POST options
//The basic SQL will select all records
db_query = "SELECT C_Memory.*, C_Audio.*, C_VGA.*FROM C_Memory,C_Audio,C_VGA WHERE C_Memory.description LIKE '%whatever%' OR C_Audio.description LIKE '%whatever%' OR C_VGA.description LIKE '%whatever%'";

But it gives me an error on that line .. Also does the below code stay the same:

Code: Select all

//depending on which of the following are filled in (none, some, all)
//the resultset returned will be filtered down
$desc = $_POST&#1111;'description']; 
if (strlen($desc) > 0) &#123; 
$db_query .= " WHERE description LIKE '%$desc%'"; 
&#125;
Also where do i put the other fields so i can display those with the results .. I had:

Code: Select all

$db_query = "SELECT description, price FROM C_Memory";



Thanks

]{ronic

Posted: Thu May 13, 2004 5:21 am
by launchcode
The error is because of the thing I pointed out to you above (no space before FROM) and because you're appending that WHERE description block to the end of it - which is now invalid SQL.

You MUST get in the habit of echoing out the SQL query itself and also the results of mysql_error() - they will actually tell you what is wrong rather than just a random line number.

Your other fields are correct, put them there - but don't forget to extract them from the results, specifically:

<?=$row['description']?> - change that to the value to display

Posted: Thu May 13, 2004 6:33 am
by ]{ronic
Hey,

The thing is I get the error on line 30 when the script is executed .. Line 30 is:

Code: Select all

db_query = "SELECT C_Memory.*, C_VGA.* FROM C_Memory,C_VGA WHERE C_Memory.description LIKE '%whatever%' OR C_VGA.description LIKE '%whatever%'";
I had already taken the space out .. Is it becouse both tables are using the "description" field ?

Posted: Thu May 13, 2004 6:42 am
by launchcode
No, that's absolutely perfectly valid SQL - there is nothing wrong with it (other than in the missing $ from db_query, but I guess you just didn't select that part when pasting?)

Echo out $db_query right before you pass it to mysql_query and post it here, maybe it is being modified elsewhere in the code.

Posted: Thu May 13, 2004 6:54 am
by ]{ronic
If i search "samsung" the result i get is:

Code: Select all

You have an error in your SQL syntax near 'WHERE description LIKE '%samsung%'' at line 1