Page 1 of 1

Adding row counts to generated drop down menu list?

Posted: Mon Nov 08, 2004 9:04 am
by rhaynes
Hi, I am a relative newbie. I have created a webpage with php which calls a postgresql database and then populates a drop down menu with the results of the search.
Based on the selection from the menu the "user" is presented with a new page with another
drop down menu whose entries are based on the first selection and so on. This all works nicely.

What I would like to do now is add some bells and whistles. One thing I would like to do
is to indicate next to each entry in these drop down menus how many items in the database satisfy that particular search criteria. I have played around with the select count function but I haven't gotten it to do what I want. I have included a snippet of code below.

What I would like displayed in the drop down menu is

Year1 (#)
Year2 (#)
Year3 (#)

where # refers to the number of entries in the database which satisfy search criteria Year1.
Any help to incorporate this feature into my existing code would be greatly appreciated.

Here is the code so far...

Code: Select all

<?php
        $connection = pg_connect(blah, blah,blah);
        if (!$connection) {
                print("Connection Failed.");
                exit;
        }

        $query = "select distinct year from tradelist where quantity > 0 order by year";

        $myresult = pg_exec($connection,$query);
        $num_rows = pg_numrows($myresult);
        $query2 = "select * from tradelist";
        $myresult2 = pg_exec($connection,$query2);
        $num_rows2 = pg_numrows($myresult2);


        print("<p>There are currently <font color=blue> $num_rows2 </font> entries in the tradelist database<br>");
 if ($num_rows == 0) {
                print("No records exist");
        }
        else {
                print("<FORM METHOD="POST" ACTION="blah...">");
                print("Choose a year from which to search ...<br><br>");
                print("<font color=blue>Available Years</font><br><br>");
                print("<SELECT NAME="year_to_search">");

                while ($row = pg_fetch_array($myresult))
                        {
                                $id = $row["year"];
                                print("<OPTION VALUE = "$id"> $id");
                        }
        }
print("</select>");
print("<INPUT TYPE="SUBMIT" VALUE="VIEW SETS BY YEAR">");
print("<INPUT TYPE="RESET" VALUE="RESET">");
print("</FORM>");
?>
Any help would be greatly appreciated.

R Haynes

Next to each entry

Weirdan | Help us, help you. Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Mon Nov 08, 2004 9:17 am
by Tubbietoeter
Basically you have to loop for each "Year", do a count on the database and then print the results as an option value.

Code: Select all

// fetch all years from database
$year_array= ...

foreach $year in $year_array {
// fetch the count for this year
$count = ...
print("<OPTION VALUE = "$id"> $Year $Count"); 
}
Probably you can combine both steps with a subselect or something so you have one DB query only.

Weirdan | Help us, help you. Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Mon Nov 08, 2004 9:31 am
by Weirdan
Tubbietoeter wrote: Probably you can combine both steps with a subselect or something so you have one DB query only.
Subselect sounds like an overkill for such simple task. Plain old GROUP BY would be enough:

Code: Select all

select 
    year, 
    count(*) as cnt 
from 
    tradelist 
where 
    quantity > 0 
group by 
    year
rhaynes, this query is for MySQL, but IMO it should work for PG as well (with little modifications, perhaps).

weirdan... you got mail...

Posted: Mon Nov 08, 2004 10:40 am
by rhaynes
Hi Weirdan, thanks your response. I have PM'ed you with a followup question.

Thanks,
R Haynes

Posted: Mon Nov 08, 2004 10:55 am
by Weirdan
rhaynes asked me for full script via pm, here it is:

Code: Select all

<?php
        $connection = pg_connect(blah, blah,blah);
        if (!$connection) {
                print('Connection Failed.');
                exit;
        }

        $query = 'select
                          year,
                          count(*) as cnt
                       from
                          tradelist
                       where
                         quantity > 0
                       group by
                         year ';

        $myresult = pg_exec($connection,$query);
        $query2 = 'select count(*) from tradelist'; // count is faster
        list($num_rows2) = pg_fetch_row( pg_exec($connection,$query2) );


        print('<p>There are currently <span style="color:blue">' . $num_rows2 . ' </span> entries in the tradelist database<br />');
        if (!pg_numrows($myresult)) {
                print('No records exist');
        } else {
                print('<FORM METHOD="POST" ACTION="blah...">');
                print('Choose a year from which to search ...<br /><br />');
                print('<span style="color:blue">Available Years</span><br /><br />');
                print('<SELECT NAME="year_to_search">');
                while ( list($year, $cnt)  = pg_fetch_row($myresult) )
                        echo '<OPTION VALUE = "' . $year . '">' . $year . ' (' . $cnt . ') </OPTION>';
                print('</select>');
                print('<INPUT TYPE="SUBMIT" VALUE="VIEW SETS BY YEAR" />');
                print('<INPUT TYPE="RESET" VALUE="RESET" />');
                print('</FORM>');
        }
?>

Many thanks

Posted: Mon Nov 08, 2004 11:00 am
by rhaynes
Hi, many thanks... getting the count numbers I figured out... how to extract that info from the query and incorporate into the drop down menu was stumping me. Many thanks,
R Haynes