Searching a mySQL DB with PHP

Anon

I need help with the following im trying to create a searchable database. i have the database setup and running and i have created my web interface what i need to do now is be able to send a query to the database and have it return any records that have what the user entered as there search criteria here is an example

if the user enters somthing like: win98 installation

i want the database to send me back all the records that contain either the words win98 or installation or both if applicable.

I have no clue how to format the query string to get such a responce i have what the user enters stored as the
variable: $sbox

Thank you for you help

Robert

aborg

Here is what I use..

$hostname = "IP or localhost";
$username = "username to login";
$password = "duh";
$dbName = "Database Name";
$usertable = "Name of table to search";

MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to database");
@mysql_select_db( "$dbName") or die( "Unable to select database");

$query = mysql_query("SELECT * FROM $usertable WHERE $sbox LIKE '%$search%' ORDER BY date DESC");

Very simple... but it works for me...i don't think it will take into account multiple word search strings but you can always search through the string for a space and break the string apart into multiple strings and run multiple searchs or something.. but it works on my web page just fine.

Aaron

Anon

Mabye im just really lame or mabye it cause i started workin with PHP 4 days ago... but here is the code i have and i get 2 errors whenever i try to run it

username and password were removed just cause

<?php
if ($submit)
{
$hostname = "localhost";
$username = "";
$password = "";
$dbName = "myDB";
$usertable = "coffee_inventory";

MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to database");
@mysql_select_db("$dbName") or die("Unable to select database");

$sql_result = mysql_query("SELECT * FROM $usertable WHERE $sbox LIKE '%$search%' ORDER BY date DESC");
echo "<CENTER><P><BR><P><BR><TABLE BORDER=1>";
echo "<TR><TH>Coffee Name</TH><TH>Roast Type</TH><TH>Quantity</TH>";

while ($row = mysql_fetch_array($sql_result)) {
$coffee_name = $row["COFFEE_NAME"];
$roast_type = $row["ROAST_TYPE"];
$quantity = $row["QUANTITY"];
echo "<TR><TD>$coffee_name</TD><TD>$roast_type</TD><TD>$quantity</TD></TR>";
}

echo "</TABLE></CENTER>";
mysql_free_result($sql_result);
mysql_close($connection);

}
?>

Any ideas on how to get this to work heh

Thanks

aborg

Well... lets start by telling me what the errors are...

Oh.. one thing.. do you have a "date" column? If not, that sort by part will cause an error.. replace "date" with whatever item you want your data sorted by....

Aaron

Anon

Try this one :

$connection = MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to database");
@mysql_select_db("$dbName", $connection) or die("Unable to select database");

You did close your connection whith the proper link identifier :

mysql_close($connection);

but you forgot to give the second argument when selecting the database.
Hope it helps.

Cheng

Anon

Hey Aaron thanks for all your help

Here is my test2.php File this is all the code im using and after the code i have pasted the errors that i get when i run it

---( Test2.php )---

<head><title>Digital Playground - PHP Database Gateway</title></head>
<body bgcolor=#000000 text=#FFFFFF>
<img src=/images/title.jpg>
<br><hr><p>
<center>
<table>
<tr><td width=800>

<?php
if (!$submit)
{
echo "<center><form method=get action=test2.php>
<INPUT TYPE=TEXT NAME=sbox MAXLENGTH=100 SIZE=40 VALUE= >
<INPUT TYPE=submit NAME=submit VALUE= search >
</form>
<p><font face=arial,helvetica size=-1>
Enter <B>one or more words</B> to search for. If you want to specify more than one word,
use a space as a separator. Example : <I>death dismemberment</I> for all topics dealing
with both death and dismemberment.</font></center>";
}
?>

<?php
if ($submit)
{
$xcount = 0;
$connection = mysql_connect("localhost","user","pass");
if (!$connection) { echo "Couldn't make a connection!"; exit; }
$db = mysql_select_db("myDB", $connection);
if (!$db) { echo "Couldn't select database!"; exit; }

$sql = "SELECT * FROM COFFEE_INVENTORY WHERE ROAST_TYPE LIKE $sbox";
$sql_result = mysql_query($sql,$connection);

echo "<CENTER><TABLE BORDER=1 width=640>";
echo "<TR><TH>Coffee Name</TH><TH>Roast Type</TH><TH>Quantity</TH><TH>Links</TH>";

while ($row = mysql_fetch_array($sql_result))
{
	$coffee_name = $row["COFFEE_NAME"];
	$roast_type = $row["ROAST_TYPE"];
	$quantity = $row["QUANTITY"];
	$link = $row["LINK"];
	echo "<TR><TD>$coffee_name</TD><TD>$roast_type</TD><TD>$quantity</TD><TD><CENTER>$link</CENTER></TD></TR>";
	$xcount = $xcount + 1;
}

echo "<P><b>$xcount Results found for:</b> $sbox";
echo "</TABLE></CENTER>";
echo "<p><center><a href=javascript:history.go(-1)>Return to Main Search Page</a></center>";

mysql_free_result($sql_result);
mysql_close($connection);

}
?>

</td></tr>
</table>
</center>
</body>

---( END Test2.php )---

Here are the errors:

Warning: Supplied argument is not a valid MySQL result resource in c:/program files/apache group/apache/htdocs/test2.php on line 40

Warning: Supplied argument is not a valid MySQL result resource in c:/program files/apache group/apache/htdocs/test2.php on line 54

Here is what my database looks like:
DB: myDB
Table: Coffee_Inventory

Fields:
Coffee_Name, Roast_type, Quantity, Link

Data:
coffee1 dark 36 www.coffee1.com
coffee2 medium 31 www.coffee2.com
coffee3 dark 56 www.coffee3.com
coffee4 light 12 www.coffee4.com
coffee5 dark 26 www.coffee5.com
coffee6 medium 41 www.coffee6.com
coffee7 light 23 www.coffee7.com

And as far as Setting things as Primary or Indexed or things like that i have no clue what they are so i dident use them

I hope this will help you help me.

Thanks for all your help

Robert

Anon

could an1 send me the working script?

thnx