[SOLVED] record count on jump to menu

[yukafluck]

I have a piece of code that generates a jump to menu, using a group by query.  What I would like to do is be able to have it shows the number of records for each options.

 

right now the option for 2009 would show "2009", I would like it to show: "2009 (3)".

 

Here is the code that I have:

<form name="Year" id="Year">

<select name="YearMenu" onchange="MM_jumpMenu('parent',this,0)">

  <option selected="selected">Choose</option>

   

        <?php

  include_once "_functions/db_info.php";

  //connect to database

$dbserver = mysql_connect($hostname, $dbuser, $dbpswd);

$connection = $dbserver or die ('I cannot connect to the server');

mysql_select_db ($dbname) or die ('I cannot connect to the database');

  $sqlGetVehicleYear="SELECT *  FROM vehicles GROUP BY vehicle_year DESC; ";

  $sqlGetVehicleYearRes = mysql_query($sqlGetVehicleYear,$connection) or die ("could not connect to  sqlGetVehicleYear ");

While ($row = mysql_fetch_array($sqlGetVehicleYearRes))

{

$vehicle_year=$row["vehicle_year"]; 

  ?>

             

            <option value="vehicles_list.php?makenarrow=%&yearnarrow=<?php echo $vehicle_year ?>"><?php echo $vehicle_year ?></option>

 

      <?php }?>

  </select>

    </form>

[code=php:0]

 

Any help would be greatly appreciated

 

[Maq]

You can use COUNT() in your query to achieve this.

[yukafluck]

not to sound like a complete numbnut, but how?

 

[Maq]

Click on the link I provided, there are plenty of examples there are similar to yours.  If you can't figure it out, post back with what you tried and why it doesn't work.

[yukafluck]

ok, all you need to do is point me in the direction to have it show with the echo statement

[Maq]

Change your query to:

 

SELECT COUNT(*) as num, *  FROM vehicles GROUP BY vehicle_year DESC

 

And to display the number for each one change your menu output to:

 

 ( )

[yukafluck]

tried that, and it just stops

 

no errors or anything

[yukafluck]

and just has the first option listed

 

[Maq]

Then do this:

 

SELECT COUNT(*) num, vehicle_year  FROM vehicles GROUP BY vehicle_year DESC

[yukafluck]

That's the ticket, you are awesome  I've been trying to figure it out for two days