Trying To Get Table Data To Display Properly

foetoid

Hi all, I'm obviously new to PHP. Have done a bit of ASP VBScript in the past but php is seeming very foreign to me.

What I'm trying to do is display a dynamic table that contains statistical information. About the various assets and the number of them contained within the database table.

My MySQL DB contains a table called Assets. I'm trying to display data from a single column called AssetType.

I would like to display the AssetType name such as "Laptop" once with the total number of laptops next to it.

I can manually do:

echo "Laptops : <b>$number</b>

I attain the result I want, however If someone were to add a new AssetType such as "Desktop" to the table I don't want to have to manually go back and add:

echo "Desktops : <b>$number</b>

I don't know how to build the code to fill in that asset type dynamically. Basically what I'm picturing in my head would be something like this:

echo "$AssetType : <b>$number</b>

I have no idea how to do this. I've been Googling for a few hours and I'm beginning to think I don't know how to phrase what I'm searching for.

I have the following as my SQL query

$query  = mysql_query("SELECT AssetType FROM Assets WHERE Retired IS NULL");

$number=mysql_num_rows($query);

I think there needs to be something in between $query and $number but I'm clueless. Any help at all and I will bow to you as lord of PHP.

sharad_impinge

Look there you need to use 2 queries, first you need to fetch all asset types, and after that you need to fetch number of that each Asset type occurring in your table.
try the following

$query_ast = mysql_query("Select distinct AssetType From Assets where Retired IS NULL");
$row_ast = mysql_fetch_array($query_ast); // This will be an array of Asset tpes in your Asset Table
//Now use a foreach loop on this array
foreach$row_ast as $asset)
{
$query = mysql_query("SELECT id FROM Assets WHERE AssetType=$asset"");
$number=mysql_num_rows($query);

echo $asset." : ".$number;

}

foetoid

Well it looks like you're on the right track. The output is a little bit odd though.

I had to make a couple of tweaks to your code to get it to give me any output and perhaps I've messed something up. I've bolded the items I've changed.

$query_ast = mysql_query("Select distinct AssetType From Assets where Retired IS NULL");
$row_ast = mysql_fetch_array($query_ast); // This will be an array of Asset tpes in your Asset Table
//Now use a foreach loop on this array
foreach [B]($row_ast as $asset)[/B]
{
$query = mysql_query("SELECT ID FROM Assets WHERE AssetType=[B]'$asset'"[/B]);
$number=mysql_num_rows($query);

echo $asset." : ".$number;

}

The output is now giving me "ThinClient : 39ThinClient : 39" and that is all. There are 8 different AssetTypes currently in the table. I'm not sure why it's duplicating itself on ThinClient only. The count is accurate, however it's not moving forward.

I tried throwing an ORDER BY in the last SQL statement and tried both ascending and descending with no change in the result. It's always trying to write out the ThinClient first for some reason. One would think it would try and choose Aircard first.

I'll ponder this some more, but if you or anyone else has any other ideas, please let me know.

sharad_impinge

ok, Have you checked properly those all 8 asset type don't have Retired attribute NULL. Please confirm this

sharad_impinge

$query_ast = mysql_query("Select distinct AssetType From Assets where Retired IS NULL");
$row_ast = mysql_fetch_array($query_ast);

well it should work

//try this
echo "<pre>";
print_r($row_ast);

and check whats the result you get in $row_ast array and also try the following syntax in query.

$query_ast = mysql_query("Select DISTINCT AssetType From Assets where Retired=''");