[SOLVED] SQL Like Help Needed

[marcus]

I am just making a search query to search for items in the database. I have two options, whether user wants to search for EXACT results or results containing what they search. When I chose the 'containing' option (which includes the LIKE in the query) comes up with the 'Error not found' I have made.

 

$act = $_GET['act'];
protect($act);
echo "<center>\n";
if(!isset($act)){
echo "<table border=0 cellspacing=3 cellpadding=3 width=400>\n";
echo "<tr><td colspan=2 class=bar align=center><font class=barfont>Market - Search</font></td></tr>\n";
echo "<tr><td><form name=search method=post action=\"search.php?act=search\">\n";
echo "Item Name:</td><td valign=top><input type=text name=item maxlength=255></td></tr>\n";
echo "<tr><td>Search As:</td><td valign=top><select name=look><option value=1>Containing</option><option value=2>Exact</option></select></td></tr>\n";
echo "<tr><td colspan=2 align=right><input type=submit value=\"Search\"></td></tr>\n";
echo "</form></table>\n";
}

if($act == search){
$item = $_POST['item'];
protect($item);
$look = $_POST['look'];
protect($look);

if($look == 1){
$sql = "SELECT * FROM `user_items` WHERE `name` LIKE '$item' AND `place` = 'Market' AND `price` > 0 ORDER BY `price` ASC LIMIT 25";
}

if($look == 2){
$sql = "SELECT * FROm `user_items` WHERE `name` ='$item' AND `place` = 'Market' AND `price` > 0 ORDER BY `price` ASC LIMIT 25";
}

if($look != 1 && $look !=2){
$sql = "SELECT * FROM `user_items` WHERE `name` LIKE '$item' AND `place` = 'Market' AND `price` > 0 ORDER BY `price` ASC LIMIT 25";
}

$res = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($res) == 0 && $look == 1){
echo "<table border=0 cellspacing=3 cellpadding=3 width=400>\n";
echo "<tr><td class=bar align=center><font class=barfont>Error!</font></td></tr>\n";
echo "<tr><td align=center>We did not find any results containing <b>$item</b>.</td></tr>\n";
echo "</table>\n";
exit;
}

if(mysql_num_rows($res) == 0 && $look == 2){
echo "<table border=0 cellspacing=3 cellpadding=3 width=400>\n";
echo "<tr><td class=bar align=center><font class=barfont>Error!</font></td></tr>\n";
echo "<tr><td align=center>We did not find any results for <b>$item</b>.</td></tr>\n";
echo "</table>\n";
exit;
}

echo "<table border=0 cellspacing=3 cellpadding=3 width=400>\n";
echo "<tr><td colspan=4 class=bar align=center><font class=barfont>Market - Search Results</font></td></tr>\n";
echo "<tr><td>Owner</td><td>Item Name</td><td>Price</td><td>Quick Buy</td></tr>\n";
while($row = mysql_fetch_assoc($res)){
$row2 = mysql_fetch_assoc(mysql_query("SELECT * FROM `users` WHERE `uid` =$row[uid]"));
echo "<tr><td>$row2[username]</td><td>$row[name]</td><td>$row[price]</td><td><a href=\"buy.php?itemid=$row[id]&ownerid=$row[uid]\">Buy</a></td></tr>\n";
}
echo "<tr><td colspan=4 align=right><a href=\"search.php\">New Search</a></td></tr>\n";
echo "</table>\n";
}
echo "</center>\n";

[per1os]

Have you tested the query with the results in phpMyAdmin to make sure the query isn't bad?

 

--FrosT

[marcus]

Query is fine.

[Barand]

If you want a "Contains" type query you need

 

... WHERE `name` LIKE '%$item%'

 

where "%" is a wildcard  meaning "None, one or many characters", so, basically,

 

where name is anything followed by $item followed by anything

[marcus]

Works, thanks