php mysql select like not working

Question

I am trying to query a mysql table using some "likes". But it doesn't work at all.

This is my code:

 $color_base1 = $row[color_base1];
$color_base2 = $row[color_base2];

$result2 = mysql_query("SELECT * FROM item_descr WHERE (color_base1 LIKE 
           '%$color_base1%' OR color_base2 LIKE '%$color_base1%' OR color_base1 
           LIKE '%$color_base2%' OR color_base2 LIKE '%$color_base2%') 
           AND id_item != $itemId");

if (mysql_fetch_array($result2) == 0)
{
     $result2 = mysql_query("SELECT * FROM item_descr WHERE (keywords LIKE 
                '%$keywords%') AND id_item != $itemId LIMIT 3");
}
else 
{
    $row2 = mysql_fetch_array($result2);
    echo "<div class='similarTitle'>YOU MAY ALSO LIKE</div>";
    while ($row2 = mysql_fetch_array($result2))
    {
       echo "<div class='similarItems'>";
       echo "<img class='similarImage' src='images/{$row2[thumb1]}.jpg'>";
       echo "<div class='similarItemsText'>".$row2[name]."</div></div>";
    }
}

Thanks!

You are not doing any error checking after your queries. Please add proper error checking as outlined in the examples in the manual mysql_query() — Pekka
– Pekka, Commented Aug 6, 2012 at 12:07
change mysql_fetch_array() to mysql_num_rows()? you have if(array()==0) — Waygood
– Waygood, Commented Aug 6, 2012 at 12:10
Echo your query and run it directly in database to know what exactly is the error? — Rohit Choudhary
– Rohit Choudhary, Commented Aug 6, 2012 at 12:16
Try and keep to one method of putting variables in strings ".$row2[name]." OR {$row2[thumb1]} I suggest the first as you can then ".htmlentities($row2[name])." or ".mysql_real_escape_string($keywords)." — Waygood
– Waygood, Commented Aug 6, 2012 at 12:18
Obligatory comment: Please don't write new code that uses the mysql_* functions. The extension is outdated and has been superceeded by MySQLi and PDO_mysql. The PHP development team have begun the deprecation process for the stock mysql extension and it will be removed from PHP in a future version (hopefully the not-too-distant future). — DaveRandom
– DaveRandom, Commented Aug 6, 2012 at 12:20

John Woo · Accepted Answer · 2012-08-06 12:13:39Z

1

try changing your queries into these:

Query1:

SELECT  * 
FROM    item_descr 
WHERE   (color_base1 LIKE CONCAT('%' , $color_base1, '%') OR 
         color_base2 LIKE CONCAT('%' , $color_base1, '%') OR 
         color_base1 LIKE CONCAT('%' , $color_base2, '%') OR 
         color_base2 LIKE CONCAT('%' , $color_base2, '%')) AND 
         id_item != $itemId

Query 2:

SELECT  * 
FROM    item_descr 
WHERE   keywords LIKE CONCAT('%' , $keywords, '%') AND 
        id_item != $itemId 
LIMIT 3

answered Aug 6, 2012 at 12:13

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Ilfrich · Accepted Answer · 2012-08-06 12:13:16Z

0

Try escaping your variables inside the query

mysql_query("select ... like '%" . $var . "%' ...");

answered Aug 6, 2012 at 12:13

Peter Ilfrich

3,8263 gold badges34 silver badges35 bronze badges

Comments

Waygood · Accepted Answer · 2012-08-06 13:01:39Z

$color_base1 = $row['color_base1']; // <- index NOT constant
$color_base2 = $row['color_base2'];

$result2 = mysql_query("SELECT * FROM item_descr WHERE (color_base1 LIKE '%$color_base1%' OR color_base2 LIKE '%$color_base1%' OR color_base1 LIKE '%$color_base2%' OR color_base2 LIKE '%$color_base2%') AND id_item != $itemId");

if(mysql_num_rows($result2) == 0) // <- check for NO RESULTS
{
    $result2 = mysql_query("SELECT * FROM item_descr WHERE (keywords LIKE '%$keywords%') AND id_item != $itemId LIMIT 3");
}

$row2 = mysql_fetch_array($result2);
echo "<div class='similarTitle'>YOU MAY ALSO LIKE</div>";
while ($row2 = mysql_fetch_array($result2))
{

Your array referencing is done incorrectly. $row['color_base1'] is not the same as $row[color_base1]
Your code gets an array from the search results (first row) and checks it against 0. I believe you mean to check the number of results returned and if there were none then check for suggestions.
If your second query is executed, it does nothing with the results
When you process your results to produce output, you are looping through the results from row 2 onwards (see point 2)

DEBUG:

if you do $sql="..."; then you can debug by echo-ing $sql and then checking the results manually by putting them into phpmyadmin
do lots of echo "I am at line:".__ LINE __; to check which path your logic is going in.
compare your phpmyadmin results with a var_dump($row); inside your loop to see if your skipping rows.
myql_query($sql) or die(mysql_error()); // will stop if your query is bad

etc.....

Collectives™ on Stack Overflow

php mysql select like not working

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related