MySQL Query in foreach loop not returning results when fed an array with more than 1 item

Question

As the title states, I can't seem to get results for any of the queries if I have more than 1 item in the array.

Uploaded code here: http://www.text-upload.com/read.php?id=44739&c=8286678

{    
$keywords_array = array("foo","bar","rawr");    
echo "<ul>";    
foreach($keywords_array as $keyword) 
{    
 // Clean keywords
 $keyword = strtolower($keyword);

 // Check to see if keyword is a "name"

 $keyword_check_name_result = mysql_query("
     select * FROM `some_table` 
              WHERE name = '$keyword'") or die (mysql_error());

 $keyword_check_name_total = mysql_num_rows($keyword_check_name_result);

 // check
 echo "keyword_check_name_total for $keyword: $keyword_check_name_total<br />
 select * FROM `some_table` WHERE name = '$keyword'";

 if($keyword_check_name_total > 0) 
 {
    echo "<li><a href=\"$link/$keyword.html\">$keyword</a></li>";
 }
}

echo "</ul>";

}

If $keywords_array = array("foo"); only, then $keyword_check_name_total = 1. But if I have $keywords_array = array("foo","bar","rawr");, then the $keyword_check_name_total = 0 each loop.

why don't you try using SELECT count() FROM some_table WHERE name = '$keyword'" instead of using mysql_num_rows()? I'm not sure this is the root of the problem, but anyway it's a good practice to use count() when you need to get the total count of rows. — itsmeee
– itsmeee, Commented Jan 29, 2011 at 17:42
Why do you post your code on text-upload.com ? It is not just text, it is code. Besides, it makes one link to click if one wants to help you, and many will feel lazy. — greg0ire
– greg0ire, Commented Jan 29, 2011 at 17:45
There are entries in the database for 2 out of 3 of the values, so the count should return 2. — Jay
– Jay, Commented Jan 29, 2011 at 18:07
@greg0ire sorry about that, first time here and tried to enter the code in the form, but it kept rendering everything funky. — Jay
– Jay, Commented Jan 29, 2011 at 18:08

Prisoner · Accepted Answer · 2011-01-29 18:40:21Z

1

The code you've posted is a bit wasteful in terms of resources, you should try something like the following:

<?php

$array = array("foo","bar","test");
$query = mysql_query("SELECT COUNT(name) as countVal, name FROM test WHERE name IN('".strtolower(implode("','",$array))."') GROUP BY name");
$total = 0;
while($row = mysql_fetch_assoc($query)){
        echo "name = ".$row['name'].", count = ".$row['countVal']."<br />";
        $total += $row['countVal'];
}
echo "total = ".$total;

EDIT:

Something like this:

<?php

    $array = array("foo","bar","test");
    $query = mysql_query("SELECT name, link FROM test WHERE name IN('".strtolower(implode("','",$array))."')");
    $total = 0;
    while($row = mysql_fetch_assoc($query)){
        echo "<a href=\"".$row['link']."/".$row['name']."\">".$row['name']."</a>";
    }

edited Jan 29, 2011 at 18:40

answered Jan 29, 2011 at 18:07

Prisoner

27.7k11 gold badges78 silver badges104 bronze badges

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

2 Comments

Jay Over a year ago

Prisoner: Actually, I need a specific link (uniquely identified by the row found in the 'name' table) for each "> 1" result. Is there a way to do that?

Jay Over a year ago

I tried the edited script, but it still doesn't change the fact that I'm getting 0 results for some reason (and I double checked the mysql query in phpMyAdmin, and it works perfectly there)

horatio · Accepted Answer · 2011-07-08 19:44:11Z

0

I personally recall that num_rows was unreliable, but I don't recall why.

The mysql reference page ( http://dev.mysql.com/doc/refman/5.0/en/mysql-num-rows.html ) offers one possible reason you receive a 0 count:

If you use mysql_use_result(), mysql_num_rows() does not return the correct value until all the rows in the result set have been retrieved.

answered Jul 8, 2011 at 19:44

horatio

1,4368 silver badges7 bronze badges

Collectives™ on Stack Overflow

MySQL Query in foreach loop not returning results when fed an array with more than 1 item

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related