Page 1 of 1

MQSQL Tables and Dropdown lists

Posted: Tue Oct 07, 2008 10:44 am
by Masonan
The code below draws information from an sql table. It's very simple. A main table is generated at the bottom of the page, that includes all of the information from the sql table. Above the main table is a set of dropdown lists that draw information from individual columns.

There are two things that I would like to have happen, but don't know how to do.

1) Set the dropdown lists so that they don't repeat same information... like days of the week for instance.

2) Have the ability to select information from the drop down lists to narrow down the information displayed in the main table.

Can anyone help me out with this? Thank you for any help in advance.

Code: Select all

<form action="">
Where would you like to take the class?<br>
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("username_redcross", $con);
 
$query="SELECT location FROM take_a_class";
 
$result = mysql_query($query);
echo "<select name=location value=''>Select Location</option>";
 
while($nt=mysql_fetch_array($result)){
echo "<option value=$nt[location]>$nt[location]</option>";
}
echo "</select>";
?>
<br><br>
 
 
On which date would you like to take the class?<br>
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("username_redcross", $con);
 
$query="SELECT date FROM take_a_class";
 
$result = mysql_query($query);
echo "<select name=date value=''>Select Date</option>";
 
while($nt=mysql_fetch_array($result)){
echo "<option value=$nt[date]>$nt[date]</option>";
}
echo "</select>";
?>
<br><br>
 
 
Which day of the week is best for you?<br>
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("username_redcross", $con);
 
$query="SELECT day FROM take_a_class";
 
$result = mysql_query($query);
echo "<select name=day value=''>Select Day</option>";
 
while($nt=mysql_fetch_array($result)){
echo "<option value=$nt[day]>$nt[day]</option>";
}
echo "</select>";
?>
</select>
<br><br>
 
 
Which time of day is best for you?<br>
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("username_redcross", $con);
 
$query="SELECT time FROM take_a_class";
 
$result = mysql_query($query);
echo "<select name=time value=''>Select Time</option>";
 
while($nt=mysql_fetch_array($result)){
echo "<option value=$nt[time]>$nt[time]</option>";
}
echo "</select>";
?>
<br><br>
</form>
 
 
 
 
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("username_redcross", $con);
 
$result = mysql_query("SELECT * FROM take_a_class");
 
echo "<table border='1'>
<tr>
<th>Class Type</th>
<th>Location</th>
<th>Date</th>
<th>Day</th>
<th>Time</th>
<th>Phone</th>
</tr>";while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['class_type'] . "</td>";
  echo "<td>" . $row['location'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";
  echo "<td>" . $row['day'] . "</td>";
  echo "<td>" . $row['time'] . "</td>";
  echo "<td>" . $row['phone'] . "</td>";
  echo "</tr>";
  }
echo "</table>";mysql_close($con);
?>

Re: MQSQL Tables and Dropdown lists

Posted: Tue Oct 07, 2008 11:47 am
by Masonan
And by help... I'm not asking for an instaneous solution. A tutorial, code snippet, etc... I'm still googling, but can't find anything pertaining to this.

Re: MQSQL Tables and Dropdown lists

Posted: Tue Oct 07, 2008 11:51 am
by RobertGonzalez
Where is the data coming from? I would suspect that a list of days of the week would be nothing more than a seven member array of weekdays or something like that so there would never be repeating there.

In terms of filtering your data result, take what was entered in the form and use that as criteria in a where clause in the query. That would certainly reduce the number of rows returned.

Re: MQSQL Tables and Dropdown lists

Posted: Tue Oct 07, 2008 12:25 pm
by Masonan
http://wildlamb.com/red-cross/index2.php?page=tac

The information in the table is called from an mysql database. As you can see, the dropdown lists have multiple similar values. It's a bit redundant.

Re: MQSQL Tables and Dropdown lists

Posted: Tue Oct 07, 2008 1:44 pm
by RobertGonzalez
I think you are going about this the wrong way. You are pulling data from a source where data is stored not keyed on what you want. You could use DISTINCT in your query, or a GROUP BY in your query. Of course you could always not use the days that are in the database and opt instead of a single list of days from a seven member array.

Of course if you already have the data in an array, you could always use array_unique().