<select list> and MYSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mckinnon81
Forum Newbie
Posts: 12
Joined: Mon Mar 15, 2004 8:02 pm
Location: Sydney
Contact:

<select list> and MYSQL

Post by mckinnon81 »

Hey guys;

I am trying to create a drop down list that is relative to a mysql field.

Database Table:

Code: Select all

mysql> select * from events;
+----+------+-------------+------------------+
| id | type | description | date             |
+----+------+-------------+------------------+
|  1 | 1    | Easter      | 9/3/04 - 12/3/04 |
+----+------+-------------+------------------+
1 row in set (0.00 sec)
so when type = 1 it would choose social event if type = 2 it would choose professional. I can get the base of the dropdown list now if just putting in the code around it to do what i want

Code: Select all

<SELECT NAME="type">
   <OPTION value="">Choose Event Type
   <OPTION value="1">Social Event
   <OPTION value="">Professional
</SELECT>
Can any one help?

Thanks;
Moe
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

You trying to create the list from the database, or simply select the item as stated in the database?

Create list from database
---------------------------

Code: Select all

echo "<select name="list">";
echo "<option value="0">None";
$result = mysql_query("SELECT values, id FROM table");
while($row = mysql_fetch_assoc($result)){
   echo "<option value="$row['id']">$row['value']";
}
echo "</select>";
To select item from database result
-----------------------------------------

Code: Select all

$result = mysql_query("SELECT blah FROM table WHERE value=1");
$value = mysql_fetch_assoc($result);
then when your outputting the form just do a check:

Code: Select all

if($option==$value['blah']){
   echo "SELECTED";
}
User avatar
mckinnon81
Forum Newbie
Posts: 12
Joined: Mon Mar 15, 2004 8:02 pm
Location: Sydney
Contact:

Post by mckinnon81 »

Well its basically for editing the entry via forms so when edit is clicked next to that entry it will fill in all the fields i can get the others just not the drop down list to work

Heres the entrie edit code:

Code: Select all

<?php

$db = mysql_connect("localhost", "root");
mysql_select_db("makdap",$db);
$result = mysql_query("SELECT * FROM events",$db);
$myrow = mysql_fetch_array($result);
   
If($_GET&#1111;'edit'] == "yes")&#123;
$result = mysql_query("SELECT * FROM events where id=".$_GET&#1111;'id'],$db);
?>

<form method="post" action="<?php echo $_SERVER&#1111;'PHP_SELF']; ?>">
<input type=hidden name='edit' value="<? echo $myrow&#1111;"id"] ?>">
<table width="100%" border="0" >
<tr>
<td>

<div align="right">

<SELECT NAME="type">
<OPTION value="">Choose Event Type
<OPTION value="1">Social Event
<OPTION value="">Professional
</SELECT>

</div>

</form>
</td>
</tr>

<tr>
<td>
Description:
</td>
<td>
<input type="Text" name="description" value="<? echo $myrow&#1111;"description"] ?>">
</td>
</tr>

<tr>
<td>
Date:
</td>
<td>
<input type="Text" name="date" value="<? echo $myrow&#1111;"date"] ?>">
</td>
</tr>
</table>

<table width="100%" border="0" >
<tr>
<td>
<input type="Submit" name="submit" value="Submit">
<form method="post" action="<?php echo $_SERVER&#1111;'PHP_SELF']; ?>">
</td>

<td>
&nbsp;
</td>

<td>
<input type=hidden name='cancel' value="<? echo $myrow&#1111;"id"] ?>">
<input type="submit" name="cancel" value="Cancel">
</td>
</form>
</table>

<table border="0" bgcolor="#336699" width="100%">
<tr>
<td width="*" align="center"><b><font color="white">Type</td>
<td width="*" align="center"><b><font color="white">Description</td>
<td width="*" align="center"><b><font color="white">Date</td>
<td width="*" align="center"><b><font color="white">Action</td>
</tr>

<? while($myrow = mysql_fetch_array($result)) &#123;
  echo "<tr>";
  printf ("<td bgcolor=#CDDBEB><font color=#006699>%s</td>",$myrow&#1111;"type"]);
  printf ("<td bgcolor=#CDDBEB><font color=#006699>%s</td>",$myrow&#1111;"description"]);
  printf ("<td bgcolor=#CDDBEB><font color=#006699>%s</td>",$myrow&#1111;"date"]);
  printf ("<td bgcolor=#CDDBEB><font color=#006699>&#1111;<a href="%s?id=%s&delete=yes">Delete</a>]",$PHP_SELF, $myrow&#1111;"id"]);
  printf ("&#1111;<a href="%s?id=%s&edit=yes">Edit</a>]</td>",$PHP_SELF, $myrow&#1111;"id"]);
  echo "</tr>"; &#125;
				
?>
</table>
User avatar
mckinnon81
Forum Newbie
Posts: 12
Joined: Mon Mar 15, 2004 8:02 pm
Location: Sydney
Contact:

Post by mckinnon81 »

Wohoo!!! I got it!!

Code: Select all

<SELECT NAME="type">
<? If ($myrow&#1111;'type'] == '1') &#123; ?>
   <OPTION selected value="<? $myrow&#1111;'type']; ?>">social
<? &#125;else&#123; ?>
   <OPTION selected value="<? $myrow&#1111;'type']; ?>">professional
<? &#125; ?>
</SELECT>
Thanks
Post Reply