help with displaying mysql data based on list box

[Evil8]

Okay the client wants the user to be able to display all "events" in a city listed in a list box.

I have the list box working:

<form name="form1" method="post" action="">
 <?php 
	mysql_connect($host,$username,$password);
	@mysql_select_db($database) or die("Unable to select database");
		
	$query = "SELECT venue.venueid, venue.city FROM venue GROUP BY city";
 	$result = mysql_query($query);
	
	$options="";

	while ($row = mysql_fetch_array($result)) {
	
		$id = $row["venueid"];
		$city = $row["city"];
		$options.="<OPTION VALUE=\"$id\">".$city.'</OPTION>';
	}
?>
        <div align="left">Find a meeting in my city: 
          <select name=city>
            <option value = 0>Choose 
            <?=$options?>
          </select>
        </div>
 </form>

Now I need help to figure out how to display all the data based on user choice. I'm sure I have to do something with that form action but I don't know what. I have this code, but it doesn't work yet.

<?php 

	$username="mediqw5_return";
	$host="localhost";
	$password="ghost";
	$database="mediqw5_meetingdb";

	mysql_connect($host,$username,$password);
	@mysql_select_db($database) or die("Unable to select database");
		
	$query = "SELECT venue.venueid, venue.city FROM venue GROUP BY city";
 	$result = mysql_query($query);
	
	$options="";

	while ($row = mysql_fetch_array($result)) {
	
		$id = $row["venueid"];
		$citylist = $row["city"];
		$options.="<OPTION VALUE=\"$id\">".$citylist.'</OPTION>';
	}
?>
      <div align="left">Find a meeting in my city: 
        <select name=citylist>
          <option value = 0>Choose 
          <?=$options?>
        </select>
      </div></td>
    <td colspan="3">&nbsp;</td>
  </tr>
  <tr align="center" valign="middle"> 
    <td height="2" colspan="6"> </td>
  </tr>
</table>

<?php

     $query = "SELECT ,event.eventdate
     ,event.eventtime
     , venue.name
     , venue.address
     , venue.city
     , venue.state
     , venue.zip
     , companyplan.company
     , companyplan.plantype
  FROM event
INNER
  JOIN venue
    ON 
INNER
  JOIN companyplan
    ON companyplan.planid = event.eventplanID
WHERE eventdate >= CURRENT_DATE & venue.name = citylist
ORDER
    BY event.eventdate
      ,event.eventtime
      ,venue.name";
 
	$result=mysql_query($query);
	$plan = $city = $state = ''; 
	$first = true;   
?>

<style type="text/css">
    body,div {padding:0; margin:0; line-height:1; font-family:Times New Roman, Times, serif; font-size:70%;}
    #container{margin:0 auto; width:80%; background-color:#f1f1f1;overflow-y:hidden;}
    .citystateplancolumn{width: 20%; min-width: 250px; margin-right:40px; float:left;}
    .name{color:#000000; font-weight: bold; font-size:14}    
    .city{color:#990000; font-weight: bold; font-size:14px;}    
    .plan{color:#000000; font-size:14px}
    .date{color:#000000; font-size:14px}
    .address{margin-bottom: 14px; color:#000000; font-size:14px}
</style>
  
<div class="event">

<?php
  $colfill=0;

if(!$result){
             echo "There's an error with the mysql query: " . $query . "The Error is: " . "mysql_error()";
             die(); 
             }
   elseif(mysql_num_rows($result)==0){
                                      echo "Sorry there are no meetings scheduled. Please check back soon.";
                                      die();
                                      }

   while ($row = mysql_fetch_array($result)): 
?>
<?php if ($plan !== $row['plantype'] || $state !==$row['state'] || $city !== $row['city']):
          $plan = $row['state'];    
          $city = $row['city'];    
          $plan = $row['plantype'];    
      if (!$first) echo '</div>'; 
      else $first = false;
        if($colfill==0){
?>

<div class="citystateplancolumn">

  <?php } ?>
  
  
  <div class="citystateplan">
    <div class="plan"><?php echo $row['company']; ?> <?php echo $plan; ?></div>
    <div class="name"><?php echo $row['name']; ?></div>
    <div class="date"><?php echo date("F j, Y ", strtotime($row['eventdate'])). " - " . date("g:i a ", strtotime($row['eventtime'])); ?></div>
    <div class="address"><?php echo $row['address'];  ?></div>

    <?php endif; ?>
   </div>

  <?php 
    $colfill++;
    if($colfill==2){
    $colfill=0;
    echo "</div>";
    }
    endwhile;
  ?>

Any help is appreciated. Currently I'm getting second sql select query which I knew I'd get, because I'm not passing the list box select variable correctly.

 

[jpadie]

you will need to create this as two stages. one that displays just the city. when the city changes the form needs to be submitted to the server and new content sent back down. do this via a submit/search button or use javascript to fire the form on the onchange event.

alternatively, in the ajax world, you could use js to fire just the city ID to the server which would return some json, xml or html which your javascript would pick up, process and render on the screen.

there are tens of thousands of tutorials on this on the web. alternatively, for javascript and ajax help head on over to the relevant forums on this site.

 

[Evil8]

Thanks jpadi, I have it working now using the submit button and two pages. I'm going to research a javascript solution that would only use the one page, but for now this is good.

Thank you for the help!