COUNT and search results

[myraleen]

hi everyone.

I have searched and searched all over the place, and every time if find COUNT information it's different then the next advice i find. So hopefully someone can help me here. 🙂

I have a search engine, which works fine, and is very complex. It searches the database, and a number of tables, and comes up with the results according to which ever the user has selected as search criteria.

If a user searches for the division "A" I am getting seven results back, and this is working great. The results appear in a table with general information in a row about each row found in the database.

Now, i've added another column to the search results to be displayed in the table. And this is also a new section.

So... A user searches for say the division A in the database. The results would appear with all of the division A subjects in the database, along with their names, dates, genders, and status. I have added another column and to make this simple we'll say it is their assets.

I want it to look at the asset table, count how many assets are for each division code and report the number of that subjects assets in the asset column.

So essentially it would look like this:

Div || Name || Sex || Assets
A01 || Joe || M || 2
A03 || Kim || F || 4
A09 || Tim || M | | 0

So far I have this code for that specific column display:

$asset_query = SELECT COUNT (*) as asset_num FROM assets WHERE div='$div' GROUP BY div";

$results = mysql_query ($asset_query);
     $asset_num = mysql_num_rows($result);

echo '<td>$asset_num.'</td>';

And i'm stuck because it keeps displaying a 3 instead of the number of assets each one has.

[LordShryku]

I think instead of count(), you want to sum()...

[myraleen]

I tried replacing COUNT with SUM and it's doing the same thing

[LordShryku]

You're echoing the num_rows, not the results.

[myraleen]

If i just do it as the result, without the number of rows, then i get nothing at all.

[LordShryku]

Okay, then you're code is probably wrong.
Using this as a basis

Div || Name || Sex || Assets
A01 || Joe || M || 2
A03 || Kim || F || 4
A09 || Tim || M || 0

You say you're selecting div "A", but div "A" doesn't exist. They're broken down with numbers, so using

$asset_query = "SELECT   div, count(*) AS asset_num 
                FROM     assets 
                GROUP BY div";
$results = mysql_query($asset_query);
while($row=mysql_fetch_array($results)) {
   echo $row['div'].": ".$row['asset_num']."<br />";
}

This should produce
A01: 2
A03: 4
A09: 0

But I'm thinking what you want it
A: 6
right?
On that notion, this should product that

$div = "A";
$asset_query = "SELECT count(*) AS asset_num 
                FROM   assets 
                WHERE  div LIKE '".$div."%'";
$results = mysql_query($asset_query);
while($row=mysql_fetch_array($results)) {
   echo $div.": ".$row['asset_num']."<br />";
}

[myraleen]

YAY
it works
thanks!!!

I used the top one. 🙂