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.