storing the selected value of a drop-down list

Question

Venom Rush 21 Posting Whiz

18 Years Ago

What I'd like to do is store the "selected" value of the drop down box in a DB. At the moment the selected value for the drop down list is pulled from the DB. Now what I want to happen is if I don't want to change that specific option from the drop-down list then the variable gets put back into the DB. At the moment if I don't select anything then my the value that is put back into the DB is 0.

My code is as follows:

The drop-down list

<?php	
// open connection to MySQL server
$connection = mysql_connect('localhost', 'username', 'password')
or die ('Unable to connect!');
			
//select database
mysql_select_db('database') or die ('Unable to select database!');
						
//create and execute query
$query = "SELECT day(farmFullStartDate) FROM rates";
$result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
									
//create selection list
echo "<select name='startDay' class='eventDay' id='startDay'>\n";
while($row = mysql_fetch_row($result))
  {
    echo "<option selected='selected'>$row[0]</option>";
  }
echo  "<option>01</option>";
echo  "<option>02</option>";
echo  "<option>03</option>";
echo  "<option>04</option>";
echo  "<option>05</option>";
echo  "<option>06</option>";
echo  "<option>07</option>";
echo  "<option>08</option>";
echo  "<option>09</option>";
echo  "<option>10</option>";
echo  "<option>11</option>";
echo  "<option>12</option>";
echo  "<option>13</option>";
echo  "<option>14</option>";
echo  "<option>15</option>";
echo  "<option>16</option>";
echo  "<option>17</option>";
echo  "<option>18</option>";
echo  "<option>19</option>";
echo  "<option>20</option>";
echo  "<option>21</option>";
echo  "<option>22</option>";
echo  "<option>23</option>";
echo  "<option>24</option>";
echo  "<option>25</option>";
echo  "<option>26</option>";
echo  "<option>27</option>";
echo  "<option>28</option>";
echo  "<option>29</option>";
echo  "<option>30</option>";
echo  "<option>31</option>";
echo "</select>";
?>

And this is the php that updates the DB

// open connection to MySQL server
$connection = mysql_connect('localhost', 'username', 'password')
or die ('Unable to connect!');
		
//select database
mysql_select_db('database') or die ('Unable to select database!');
	
//define variables
$startDay = $_POST['startDay'];
$startMonth = $_POST['startMonth'];
$startYear = $_POST['startYear'];
$startDate = $startYear."".$startMonth."".$startDay;
$endDay = $_POST['endDay'];
$endMonth = $_POST['endMonth'];
$endYear = $_POST['endYear'];
$endDate = $endYear."".$endMonth."".$endDay;
$luxury = $_POST['luxury'];
$orchid = $_POST['orchid'];
	
//create query
$query = "UPDATE rates SET farmFullStartDate = '$startDate', farmFullEndDate = '$endDate', farmFullLuxury = '$luxury', farmFullOrchid = '$orchid'";
//$query = "INSERT INTO rates (farmFullStartDate, farmFullEndDate, farmFullLuxury, farmFullOrchid) VALUES ('$startDate', '$endDate', '$luxury', '$orchid')";
	
//execute query
$result = mysql_query($query)
or die ("Error in query: $query. " . mysql_error());
	
// close connection
mysql_close($connection);

Any help would be greatly appreciated.

P.S. If I haven't explained myself well enough then here's an example situation:

I select 05 from startDay and hit submit. The page refreshes and 5 is displayed in the startDay drop-down list. Now I don't want to change startDay but I want to change other fields in the form so I do so and hit submit. What I have changed gets sent to the DB and 0 gets sent to the DB for startDay. What should be happening is it should be sending 5 to the DB.

database-design mysql php

4 Contributors
7 Replies
1K Views
1 Year Discussion Span
Latest Post 17 Years Ago Latest Post by Venom Rush

Dani AI

Generated 10 Months Ago

A concise diagnosis and safe fix for the problem reported by (with useful pointers from and ):

The root causes are usually one or more of these: the <option> elements were emitted without explicit values (browsers will fall back to the option text, which can be surprising), the form-processing code reads the wrong request method (GET vs POST), a conditional used assignment instead of comparison, or the handler always overwrites the DB value instead of falling back to the stored value when no new input was submitted. See the HTML <option> docs for the value/selected behavior: MDN: option element. (developer.mozilla.org)

Practical, safe pattern to follow:

When rendering the select, give every <option> an explicit value and mark selected only for the matching value (either the posted value if present, otherwise the DB value).
When processing the form, fetch the current DB value first, then use the posted value only if it exists and is non-empty; otherwise keep the DB value. Format dates for MySQL DATE columns as YYYY-MM-DD.
Use parameterized queries (PDO or mysqli) instead of string concatenation.

Example (PDO-style, not present earlier in the thread):

<?php
// $dbStartDay retrieved from the DB before processing
$day = (isset($_POST['startDay']) && $_POST['startDay'] !== '') ? $_POST['startDay'] : $dbStartDay;
$startDay = str_pad((int)$day, 2, '0', STR_PAD_LEFT);
$startDate = sprintf('%04d-%02d-%02d', $year, $month, $startDay);

$stmt = $pdo->prepare(
'UPDATE rates SET farmFullStartDate = ?, farmFullEndDate = ?, farmFullLuxury = ?, farmFullOrchid = ? WHERE id = ?'
);
$stmt->execute([$startDate, $endDate, $luxury, $orchid, $id]);
?>

For the DATE format and best practices when storing dates in MySQL, see the manual. Also prefer PDO/mysqli over the old ext/mysql APIs. (dev.mysql.com)

Security and maintenance notes: ext/mysql was removed from PHP 7; migrate legacy code to mysqli or PDO. Always use prepared/parameterized statements to avoid SQL injection (OWASP guidance). Also include a proper WHERE clause when issuing UPDATE so the entire table is not modified accidentally. (php.net)

All 7 Replies

ezb 0 Newbie Poster

18 Years Ago

The first problem I can see if the options have no values, so there for they can not post anything?

<option value="4">4</option>

Not sure if that will sort it but worth a try

JeniF 0 Junior Poster in Training

18 Years Ago

I have built a dynamic select box for something like this.
Here I populate the select options with information from a table:
(I also created the "sticky" to hold the users input upon error checking

<?php
$result = mysql_query("SELECT * FROM facilities ORDER BY Street1 ASC") or 
die(mysql_error());
$sticky= '';
if (isset($_POST['facility']))
$sticky = ($_POST['facility']);
$pulldown1 = '<select name="facility">';
$pulldown1 .= '<option></option>';
while($row = mysql_fetch_array($result))
{
if($row['FacilitiesID'] == $sticky) {
$pulldown1 .= "<option selected value=\"{$row['FacilitiesID']}\">
								{$row['Street1']}&nbsp;-&nbsp;
{$row['City']}&nbsp;-&nbsp;
{$row['Name']}
</option>\n";
} else {
$pulldown1 .= "<option value=\"{$row['FacilitiesID']}\">
{$row['Street1']}&nbsp;-&nbsp;
{$row['City']}&nbsp;-&nbsp;
{$row['Name']}
</option>\n";
}
}
$pulldown1 .= '</select>';
echo $pulldown1;	
?>

Then all I do is place the value in the insert statement

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Venom Rush 21 Posting Whiz · Answer 1 · 2007-11-13T15:25:37+00:00

The first problem I can see if the options have no values, so there for they can not post anything?
Not sure if that will sort it but worth a try

The values get posted without the value specified. So if I select <option>04</option> then it gets posted to the database.

My problem is that <option selected='selected'>$row[0]</option> isn't being posted to the DB. And adding a value to it doesn't help. 0 still gets processed.

Venom Rush 21 Posting Whiz · Answer 2 · 2007-11-13T15:52:43+00:00

I've come up with an idea. What I could do is use an if statement in the code that posts the variales to the DB. Something along the lines of the following:

if (farmHouse.startDay.value = $row[0]) {
$startDay = $row[0];
}
else {
$startDay = $_POST['startDay'];
}

I know this won't work. I have no idea how to point to the form using php as I'm somewhat new to it.

Could anyone help me with this please.

Venom Rush 21 Posting Whiz · Answer 3 · 2007-11-13T21:01:03+00:00

Thanks for your response JeniF.

Managed to sort my problem out with the following code:

if ($_GET['startDay'] = $row[0]) {
    $day = $_GET['startDay'];
    if ($day < 10) {
        $startDay = "0".$day;
    } else {
    $startDay = $_GET['startDay'];
    }
} else {
$startDay = $_POST['startDay'];
}

mad0 0 Newbie Poster · Answer 4 · 2009-02-23T09:41:49+00:00

i know this thread if quite old, but i just wanted to say thanks for lingering web pages, i almost tore my hair out trying to figure this out and literally just as I was about to call it a day, found quite the easy solution, so thanks JeniF and Venom Rush and Daniweb. i created an account just for this today. THANKS!

Venom Rush 21 Posting Whiz · Answer 5 · 2009-02-23T13:21:01+00:00

i know this thread if quite old, but i just wanted to say thanks for lingering web pages, i almost tore my hair out trying to figure this out and literally just as I was about to call it a day, found quite the easy solution, so thanks JeniF and Venom Rush and Daniweb. i created an account just for this today. THANKS!

Welcome to the community and glad this thread helped someone other than myself ;)

storing the selected value of a drop-down list

Dani AI

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers