CODEWITHSUNDEEP
phpmysqlloopsforeach
Jay
Jay

Reputation: 1104

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

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.

Upvotes: 0

Views: 1971

Answers (2)

horatio
horatio

Reputation: 1436

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.

Upvotes: 0

Prisoner
Prisoner

Reputation: 27618

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>";
    }

Upvotes: 1

Related Questions