webmaster1 Posted January 11, 2009 Share Posted January 11, 2009 Hi All, I'm outputting the contents of a mySQL table into a web page using a simple SELECT * statement. I want to be able to filter results by date and/or name so I presume I need a dropdown for each and one submit button. From what I understand I need to do the following: [*]Populate the distinct dates and names in their respective dropdowns [*]Filter whats to be displayed depending on whats selected in the two dropdown menus Can anyone point me in the right direction? When I search online I find help forums with partial snippets of code rather than any decent tutorials. Heres the code I'm using that SELECTS * <?php session_start(); if (!$_SESSION['logged']) { header("location: loginfl.php"); } else { ?> <html> <body> <a href='admin.php'>Return to Administration Home</a> <table border="1" cellspacing="2" cellpadding="2"> <tr> <th><font face="Arial, Helvetica, sans-serif">Date</font></th> <th><font face="Arial, Helvetica, sans-serif">URN</font></th> <th><font face="Arial, Helvetica, sans-serif">Recipient Email</font></th> <th><font face="Arial, Helvetica, sans-serif">Recipient Name</font></th> <th><font face="Arial, Helvetica, sans-serif">Sales Agent Name</font></th> </tr> <? include("dbinfo.php"); mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="SELECT * FROM zevasce ORDER BY date"; $result=mysql_query($query); $num=mysql_num_rows($result); mysql_close(); $i=0; while ($i < $num) { $date=mysql_result($result,$i,"date"); $urn=mysql_result($result,$i,"urn"); $recipientemail=mysql_result($result,$i,"recipientemail"); $recipientname=mysql_result($result,$i,"recipientname"); $salesagentname=mysql_result($result,$i,"salesagentname"); ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><? echo $date; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $urn; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $recipientemail; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $recipientemail; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><? echo $salesagentname; ?></font></td> </tr> <? $i++; } echo "</table>"; } ?> Quote Link to comment Share on other sites More sharing options...
RestlessThoughts Posted January 12, 2009 Share Posted January 12, 2009 I'm not totally sure what you mean by filtering the data by date or name. What are you filtering out of the results? Do you mean search by date or name? Could you possibly give an example of what you're looking for? I.E. do you want users to be able to select a date, then things that happened on that date are shown? Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted January 12, 2009 Author Share Posted January 12, 2009 The provided code displays the contents of the table in its entirety using SELECT * The columns of the table are as follows: Date | URN | Recipient Email | Recipient Name | Sales Agent Name I want all the distinct values of Date to list in the first dropdown and and the distinct values of Sales Agent Name in the second dropdown. Finally depending on which options the user selects from each drop down the results will be listed accordingly on the same page. Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted January 12, 2009 Author Share Posted January 12, 2009 I still can't find anything decent online. Its wrecking my head. Quote Link to comment Share on other sites More sharing options...
RestlessThoughts Posted January 12, 2009 Share Posted January 12, 2009 Oh so you want to populate the table based on a specific date or agent. Okay then, you're on the right track. First create your drop down lists. Then use a IF and ELSEIF statements to search the table for specific information. The query will look something like: $stuff = mysql_query("SELECT * FROM `zevasce` WHERE salesagentname=$salesagentname") or die(mysql_error()); while ($in = mysql_fetch_assoc($stuff)) { TABLE HTML } And it should throw out all the information (should be no need for the $i statements). Do you need help building the dropdown lists or the IF, ELSEIF statements? Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted January 12, 2009 Author Share Posted January 12, 2009 Phew. I thought I had explained it all arse ways. I would definatley apreciate any help you can offer with the dropdown lists and IF, ELSE IF statements. I haven't worked with either before. Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted January 12, 2009 Author Share Posted January 12, 2009 All the examples I find only show how to populate one dropdown. I'm usually okay following the step by steps but I get stuck when it comes to combining different snippets of code. Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted January 12, 2009 Author Share Posted January 12, 2009 Does anyone know of a solid tutorial to populate results into a dropdown menu? Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted January 12, 2009 Author Share Posted January 12, 2009 I've tried working with this script I found through google but it seems incomplete. <?php ... mysql cnx code ... $sql="SELECT id, thing FROM table"; $result=mysql_query($sql); $options=""; while ($row=mysql_fetch_array($result)) { $id=$row["id"]; $thing=$row["thing"]; $options.="<OPTION VALUE=\"$id\">".$thing; } ?> ... html code ... <SELECT NAME=thing> <OPTION VALUE=0>Choose <?=$options?> </SELECT> ... I know this seems like a basic example though I can't actually work out whats going on. Why is there a need for it to call on the id when its only populating one variable? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.