MySql query in foreach statement PHP

Question

I have this query in a PHP foreach statement:

<?php $i = 2; $j = 0; foreach($listings AS $listing): $i = $i == 2 ? 1 : 2;?>

...

<?php
    $db =& JFactory::getDBO();
    $query = "SELECT COUNT(product) FROM my_table WHERE contentid = 'I want content id here'";
    $db->setQuery($query);
    $count = $db->loadResult();

    echo ($count); //this print the count
?>

...

<?php endforeach;?>

How can I retrieve content id for each "listing" from MySQL database?

$query = "SELECT COUNT(product) FROM my_table WHERE contentid = '**I want content id here**'";

I'm using joomla 2.5 and Jreviews. The listing id is "called" by <?php echo $listing['Listing']['listing_id'];?> into the foreach statement.

just to confirm, you are currently looping through your mysql result set and you want to perform another query for every iteration of that loop? — dapperwaterbuffalo, Apr 13 '12 at 14:54
I don't have time to remind myself to php and mysql but i wouldn't suggest performing another query for every iteration as that is simply overkill, build an array of the values you wish to query, and write a single query at the end to query everything use IN(), if i remember correctly. — dapperwaterbuffalo, Apr 13 '12 at 15:11

score 1 · Accepted Answer · answered Apr 13 '12 at 15:10

1

You can simply add the variable name to your query, assuming $i is the id:

$query = "SELECT COUNT(product) FROM my_table WHERE contentid = $i";

or better

$query = sprintf("SELECT COUNT(product) FROM my_table WHERE contentid = %d", $i);

answered Apr 13 '12 at 15:10

citizenen

703
6
24

1

Best way would be to use the WHERE ... IN () syntax without the foreach loop. Link - http://stackoverflow.com/questions/3867840/php-implode-array-to-generate-mysql-in-criteria – citizenen Apr 13 '12 at 15:11

score 1 · Answer 2 · answered Apr 13 '12 at 15:18

1

I think the best way is to prepare the needed data before outputting it. In that particular case you can use only ONE SQL query with IN statement in the WHERE clause.

answered Apr 13 '12 at 15:18

dalazx

149
3

MySql query in foreach statement PHP

2 Answers2