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
– dapperwaterbuffalo, Commented Apr 13, 2012 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
– dapperwaterbuffalo, Commented Apr 13, 2012 at 15:11

citizenen · Accepted Answer · 2012-04-13 15:10:41Z

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, 2012 at 15:10

citizenen

7036 silver badges24 bronze badges

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

1 Comment

citizenen Over a year ago

Best way would be to use the WHERE ... IN () syntax without the foreach loop. Link - stackoverflow.com/questions/3867840/…

dalazx · Accepted Answer · 2012-04-13 15:18:07Z

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, 2012 at 15:18

dalazx

1603 bronze badges

Collectives™ on Stack Overflow

MySql query in foreach statement PHP

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related