Run another sql query inside foreach loop

Question

I have a foreach loop from the first sql query, then inside that foreach loop, i would like to run another sqli query, but i got this error: Call to a member function prepare() on a non-object

Here is my query:

$sqli = "SELECT DISTINCT approveby FROM ads LIMIT 0,20";
$resulti = $conn->prepare($sqli);
$resulti->execute();
foreach ($resulti as $rowi){
$sqlii = "SELECT * FROM ads WHERE approveby=:approveby ORDER BY approvedate ASC";
    $resultii = $conn->prepare($sqlii);
    $resultii->bindParam(':approveby', $rowi['approveby']);
    $resultii->execute();
    $num_rowsii=$resultii->rowCount();
    echo "This person: ". $rowi['approveby']."has approved $num_rowsii advertisements";
}

The reason for above code is:

There are different people who can approve advertisement from table ads. There will be one person who approve many advertisements. So the first query, i would like to list the people who have just approved advertisement.

And the second query, I would like to calculate total number of approvement for each person.

SO please tell me how to fix the error above? And also is there anyway to do this more effectively?

Many thanks

There's rarely a good reason for feeding the results of one query into another query in this way - and no evidence of a good reason here. — symcbean
– symcbean, Commented Mar 23, 2015 at 13:42

Girish · Accepted Answer · 2015-03-23 13:50:55Z

1

You can achieve result in single query, user GROUP BY instead of DISTINCT result and then count result will show total advertisements of each group set, try this query

$sqli = "SELECT *, count(*) AS `total_advertisements` FROM ads 
    GROUP BY approveby
    ORDER BY approvedate ASC
    LIMIT 0,20";
$resulti = $conn->prepare($sqli);
$resulti->execute();
foreach ($resulti as $rowi){
  echo "This person: ". $rowi['approveby']."has approved {$rowi['total_advertisements']} advertisements";
}

edited Mar 23, 2015 at 13:50

answered Mar 23, 2015 at 13:38

Girish

12.1k3 gold badges37 silver badges54 bronze badges

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

2 Comments

Giang Nguyen Over a year ago

So

foreach ($resulti as $rowi){     echo "This person: ". $rowi['approveby']."has approved $total_advertisements  advertisements"; }

is that correct?

Girish Over a year ago

yes, you can use as echo "This person: ". $rowi['approveby']."has approved {$rowi['total_advertisements']} advertisements";

ratsbane · Accepted Answer · 2015-03-23 13:45:30Z

0

That code is not going to perform well at all. Instead, combine the two queries like this:

$sql = "SELECT approveby, count(*) FROM ads GROUP BY approveby"
$result = $conn->prepare($sql);
$result->execute();

answered Mar 23, 2015 at 13:45

ratsbane

8906 silver badges7 bronze badges

Collectives™ on Stack Overflow

Run another sql query inside foreach loop

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related