Forum Moderators: coopster
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 :)
so lets break it down.
First you want to update your database, when the user selects something from a select box, without submitting the form... I would think that AJAX would be the most logical (and if not only) approach to do this.
so,ONBLUR -> Call a javascript function, that triggers an AJAX database update with the selected ID, after your database has been updated, you can remove the selected element from the drop down list in one of a few ways.
I would think that the best way would be to remove the element using Javascript and the DOM.
You could also completly replace the select box with a new one from ajax, and re-build it every time excluding the options allready selected.
Whats the downside? What if your user accidentely clicks on a selection they didint mean to click on? You might also want to update a table, that shows all of the currently selected items, and possibly even have a button to push a selected item back into the list.
[edited by: eelixduppy at 10:19 pm (utc) on July 17, 2008]
Its my first time posting a big example here, so i apologize if its not formatted correctly
<script type="text/javascript">
function delRow(src){
//Get The Row To Delete via ID
var UpdateDisplayRow =document.getElementById(src);
//Delete The Row
document.getElementById("updateDisplayTable").deleteRow(UpdateDisplayRow.rowIndex);
}
function removeOptions(selectbox,id){
var i;
//Cycle Through all the select options
for(i=selectbox.options.length-1;i>=0;i--){
//If its the one were looking for
if(selectbox.options[i].value==id){
//Delete It
selectbox.remove(i);
}
}
}
function addRowToTable(selectbox)
{
//Set The Update Display Table
var tbl = document.getElementById('updateDisplayTable');
// Set The Select Box
var selectElement = document.getElementById("selectOptions");
// Get The Text Sting
var selectElementText = selectElement.options[selectElement.selectedIndex].text;
// Get The value
var selectElementID = selectElement.value;
// Create a Text Node for display
var descCellText = document.createTextNode(selectElementText);
// If its not the 'Choose an option' element
if(selectElementID!='DONTUSE'){
// Get The Table Length
var lastRow = tbl.rows.length;
var id=selectElementID;
//Create A New Row after the previous
var row = tbl.insertRow(lastRow);
// Assign an id for the row (for the delete function)
row.id='crow'+id;
// Create A Cell in the Row
var descCell = row.insertCell(0);
// Center The Contents
descCell.style.textAlign='center';
// Ad The Text
descCell.appendChild(descCellText);
//Create A Span that triggers the delete row event
var spanOpen = document.createElement('span');
// Give in an onclick event that calls the function to delete the row, and passes the ID
spanOpen.onclick = function (){ delRow('crow'+id); };
//A Nice Little Cancel Image
spanOpen.innerHTML = '<img src="images/icons/cancel.png">';
//Append The Span to the cell
descCell.appendChild(spanOpen);
// Remove The Option from the select Box
removeOptions(selectElement,selectElementID);
//Call ajax to update the database
ajaxFunction('updateDatabase',selectElementID,selectElementText)
}
}
function ajaxFunction(actionType,selectID,selectValue){
var ajaxRequest; // The variable that makes Ajax possible!
try{
// Opera 8.0+, Firefox, Safari
ajaxRequest = new XMLHttpRequest();
} catch (e){
// Internet Explorer Browsers
try{
ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
} catch (e) {
try{
ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
} catch (e){
// Something went wrong
alert("Your browser broke!");
return false;
}
}
}
// Create a function that will receive data sent from the server
ajaxRequest.onreadystatechange = function(){
if(ajaxRequest.readyState == 4){
// The text returned from ajaxPage.php will be in ajaxRequest.responseText
alert(ajaxRequest.responseText);
}
}
if(actionType=="updateDatabase"){
//Create the query string to send to the ajax page
var queryString = "?carID="+selectID+"&carName="+selectValue;
//Send It!
ajaxRequest.open("GET", "ajaxPage.php" + queryString, true);
ajaxRequest.send(null);
}
}
</script>
<select name="selectOptions" id="selectOptions" onchange='addRowToTable(this)'>
<option value="DONTUSE" selected="selected">Chose An Option</option>
<option value="k1">Audi</option>
<option value="k2">Mazda</option>
<option value="k3">Toyota</option>
</select>
<table id='updateDisplayTable'>
<tr>
<td>Your Selected Elements</td>
</tr>
</table>
Enjoy
id .¦ccar_brand .¦ddate_check .
-----------------------------
1 .Audi .NULL
2 .Mazda .NULL
3 .Toyota .NULL
-----------------------------
Here is 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>
Here is the function for above code: when a user select 'Audi' from the list, it will record the submission date and return to the form. The drop down list will then populate available car brand that has not yet selected for following user.
