PDA

View Full Version : PHP Intermediate Question: Modify Dropdown menu listing


benghee
July 17th, 2008, 07:26 AM
Hi all,

i need to create a dropdown box which whenever user select a value in it, MYSQL will update the selected value and that selected value will no longer available inside the dropdown list. So the following user can select another available value without overwrite or duplicate the previous one in database. The default value in MYSQL database fieldname is NULL.

For example, The dropdown list contains "Audi","Mazda", and "Toyota". If first user select "Audi" and the database will stored the selection. Then second user will only see "Mazda" and "Toyota" available in the list as database already stored "Audi" in it.

I hope someone can help me out here. Many thanks :)
agnus
July 18th, 2008, 05:27 AM
How would I do that:

Make a table for options in MySQL (let's say it will have columns id_opt, option_value, selected). The selected column would be bool (true/false).
When a user selectes one of these options and saves it, it would update in a different table his option and in this table I would set selected to true. This way when i want to generate options for another user I simply do:

SELECT * FROM my_options_table WHERE selected = 0

I think that's all. Hope it answers your question.
benghee
July 18th, 2008, 06:48 AM
Thanks agnus for your reply. I forget to add missing criteria in my question. It should be when a user select a car brand, say, "Audi", the database will record the brand and current date. Then a following user can only select either "Mazda" or "Toyota". The date default value is null. I solved it by giving this condition: If date_check value is null, then display all car brand in dropdown list. Here is the table:

car_brand | date_check |
-------------------------
Audi | null |
Mazda | null |
Toyota | null |
-------------------------

And here is my code, update.php


<?php
if ( isset($_POST['Submit']) ){
$link = mysql_connect("localhost", "user", "pwd") or die("Connection cannot establish");
$query = "UPDATE auto_table SET
car = '".$_POST['Car']."',
date_check = NOW() LIMIT 1";
mysql_query($query,$link) or die("Query Failed");
echo '<script>alert("Update Successfully !");opener.location.reload();</script>';
}
$link = mysql_connect("localhost", "user", "pwd") or die("Connection cannot establish");
$query = "SELECT * FROM auto_table WHERE date_check IS NULL";
$result = mysql_query($query,$link) or die("Query Failed");
while($row = mysql_fetch_array($result)){
$ID = $row['id'];
$auto[$ID]['ID'] = $ID;
$auto[$ID]['car'] = $row['car'];
}

?>
<body topmargin="20" leftmargin="20">
<table width="367" border="1" cellpadding="0" cellspacing="1" bordercolor="#333333" align="center">
<form method="post">
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="1">
<tr>
<td width="91"><strong>Car</strong></td>
<td width="266">:
<select name='Car'>
<?php foreach($auto as $data){ ?>
<option value='<?php echo $data['car']; ?>'><?php echo $data['car']; ?></option>
<?php } ?>
</select>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" name="Submit" value="Update">
</td>
</tr>
</table>
</td>
</tr>
</form>
</table>
</body>


Just run the whole code and you will have a big picture of what is my question about. I hope my solution will be a future reference for those who has similiar problem. Thank you :)
agnus
July 18th, 2008, 09:02 AM
It looks a little more complicated than it should. Maybe you would consider trying this:



<?php
mysql_connect('localhost','user','password');
mysql_select_db('database');

if($_POST['car'])
{
mysql_query("UPDATE my_table SET
selected = 1
WHERE id_car = '".((int)$_POST['car'])."'");
}

$cars_options_query = mysql_query("SELECT id_car, brand_name
FROM my_table
WHERE selected = 0
ORDER BY id_car ASC");
$options = array();
while ( $r = mysql_fetch_array( $cars_options_query ) )
{
$options[] = $r;
}
?>

<form method="post" action="">
<select name="car">
<?php
foreach ($options as $car)
{
?>
<option value="<?=$car['id_car']?>"><?=$car['brand_name']?></option>
<?php
}
?>
</select>
</form>


And you option's table SQL is this:

CREATE TABLE `car_brand_options` (
`id_car` int(11) NOT NULL auto_increment,
`brand_name` varchar(256) collate latin1_general_ci default NULL,
`selected` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id_car`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Tell me if this helps you.
benghee
July 21st, 2008, 04:15 AM
Thanks, agnus. My codes are wrong in some way but when i apply your method, everything is working fine :D Thank you once again and i'm jumping to next level regarding to this question.

Here is my complete codes: update.php


<?php
if ( isset($_POST['Submit']) ){
$link = mysql_connect("localhost", "user", "pwd") or die("Connection cannot establish");
$query = "UPDATE auto_table SET WHERE id = '".((int)$_POST['Car'])."' LIMIT 1";
mysql_query($query,$link) or die("Query Failed");
echo '<script>alert("Update Successfully !");opener.location.reload();</script>';
}
$link = mysql_connect("localhost", "user", "pwd") or die("Connection cannot establish");
$query = "SELECT id,car_brand FROM auto_table WHERE date_check IS NULL ORDER BY id ASC";
$result = mysql_query($query,$link) or die("Query Failed");
$options = array();
while($row = mysql_fetch_array($result)){
$options[] = $row;
}

?>
<body topmargin="20" leftmargin="20">
<table width="367" border="1" cellpadding="0" cellspacing="1" bordercolor="#333333" align="center">
<form method="post">
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="1">
<tr>
<td width="91"><strong>Car</strong></td>
<td width="266">:
<select name='Car'>
<?php foreach($options as $data){ ?>
<option value='<?php echo $data['id']; ?>'><?php echo $data['car_brand']; ?></option>
<?php } ?>
</select>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" name="Submit" value="Update">
</td>
</tr>
</table>
</td>
</tr>
</form>
</table>
</body>
agnus
July 21st, 2008, 07:49 AM
Glad to be of help. Still, I have a question: Why do you create two mysql connections when one is more than enogh? I mean inside the "if($_POST[])" you create a mysql connection and and after that check you create another one. You should have one connection above the if() and that's all you need. You don't need to create a mysql connection for every query but only once.
benghee
July 21st, 2008, 08:10 AM
Sorry that I'm a newbie in php world as i dun aware of duplicating of same mysql connections. And now i'm trying to implement stored procedure in it, so i hope it will make as neat as possible. Can you guide me to correct it, if you could. Thank you so much.