Running a query in a loop?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
DusterG20
Forum Newbie
Posts: 5
Joined: Wed Feb 03, 2010 9:06 am

Running a query in a loop?

Post by DusterG20 »

Hello Everyone,

I recreationally use php and mysql to create web databases. I have a problem that has been an issue
for me for several years now. I have tried and tried to figure this out but with no formal knowledge
of Mysql and PHP just practical knowledge I am missing something.

Here is the problem.

In this instance I have built an inventory tracking databse. I have 2 tables. The first is called

modem_model which has 2 colums, model and key. This table is a list of the availible models for dynamic
use on the web app.

The next table is

modem_inv the is the actual inventory table for the modems. This table has 3 colums serial, model, and
user.

What i am trying to do is automatically run a query when the page loads on the modem_inv table
where model = all of the availible models listed in the modem_model table and then count the
rows so that I can get a count on the modems assigned to a user broken down by model.

Here is the best that I can come up with which does not actually work.

Code: Select all

 
Code:
 
sql ="SELECT * FROM `modem_model`";
 
$result = @mysql_query($sql,$connection) or die(mysql_error()); 
 
 
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$model=$row[model];
 
$sql2 = "SELECT * FROM `modem_inv` WHERE `model` = '$model'";
$result2 = @mysql_query($sql2,$connection) or die(mysql_error()); 
$num = mysql_num_rows($result2);
 
$count .="<tr><td>$model Count: $num</td><tr>";
}
 
echo"<table>
$count
</table>";

Result:

1099061: 3
1099053: 3


As it should, it gives me all of the model #'s which there are only 2 and they are listed, but it gives
an incorrect count for the second on which i realize the way it is written is will, but I have struggled
with this for a while and I would greatly appreciate any help you could give me.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Running a query in a loop?

Post by VladSun »

You can do this in a single query - using a COUNT, LEFT JOIN and GROUP BY.
Take a look at the manuals.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Running a query in a loop?

Post by VladSun »

Also, never use SELCT * and mysql_num_rows() just to find the rows count. Use SELECT COUNT(*)
or even better SELECT COUNT(id)
There are 10 types of people in this world, those who understand binary and those who don't
DusterG20
Forum Newbie
Posts: 5
Joined: Wed Feb 03, 2010 9:06 am

Re: Running a query in a loop?

Post by DusterG20 »

Thank you very much, I have been reading about left joins, and I am having trouble understanding some of the things I am reading on how to implement it in this situation. I will continue to read I know i am asking alot, but any examples would be so greatly appretiated.

Thanks
DusterG20
Forum Newbie
Posts: 5
Joined: Wed Feb 03, 2010 9:06 am

Re: Running a query in a loop?

Post by DusterG20 »

Thank you so much for the information, I did not figure out left joins yet, but I did figure out the solution to this problem, and thanks to you I learned something new today! :D

Code: Select all

$sql5 = "SELECT `model`, COUNT(model) FROM `modem_inv` WHERE `user` = '$oper' GROUP BY `model`"; 
     
$result5 = mysql_query($sql5) or die(mysql_error());
 
 
while($row = mysql_fetch_array($result5)){
   $c=$row['COUNT(model)'];
     $count.="<tr><td>$row[model]: $c </td></tr>";
}
 
Result:

1099053: 2
1099061: 3

Thanks again and I am going to research left joins. The manual is a little cryptic for me because it does not speak in the particular example I am writing about.

Thanks again
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Running a query in a loop?

Post by VladSun »

Glad to hear you've made a progress.
The LEFT JOIN between modem_model and modem_inv together with your current query will ensure that even modems that don't have any records in modem_inv will be listed (with zero items).
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply