Storing information froma drop down list into a mysql database

mattypoirier

im having a bit of trouble with some forms im using. What i have so far is a form which allows the user to select a month, day, year, hour, minutes through a series of drop down lists. this is for a band to post shows. i have the forms all set up and when everything is selected and submitted it works fine but nothing gets saved into the database. im not sure if i am using the wrong mysql data types or what but the info is not getting saved into the database. what mysql data type do i need to use for drop down lists with text like january, february etc and drop down lists with numbers like for days 01 02 03 and so on. im stuck and getting aggravated haha. if anyone can help me out i would appreciate it, any help. thanks.

Drakla

Either use the mysql date or datetime types, or an INT and use unix timetamps, which are native to PHP (see http://uk2.php.net/manual/en/ref.datetime.php). Creating timestamps is simple with functions like [man]strtotime[/man], and convert back with [man]date[/man]. Mysql has DATE_FORMAT() and FROM_UNIXTIME() to do the same things before they're served up to you.

mattypoirier

what im trying to do is have a list of dates the user can select for upcoming shows. the date could be like 3 months in advance and what not. so i had the months and days along with the time of day availabal to be selected in the drop down lists. i cant get them to store in the database. do i need to use values or something?

kburger

Not really enough information here to be able to help you much yet. I'm assuming you're trying to store the dates and times into a MySQL datetime field? Show us your table structure and the query that you're trying to use to insert data. Also -- use die() statements and use mysql_error() at least until you get it working. Check out the examples in the manual.

mattypoirier

actually all i am trying to do is get the information that is selected from a drop down list to the database. basically just whatever month, day, year, time, a person selects from the drop down list will be saved into the database. what exactly does the data type in mysql need to be for a drop down list? im not sure.

kburger

It doesn't matter what format it's in in your dropdown -- as long as it's in this format when you insert it into a datetime field in a MySQL database:

'YYYY-MM-DD HH:MM:SS'

mattypoirier

this is the format i have it listed in the drop down list, there are six drop down lists.

one is for month, January, February, March, April etc. thats in one drop down list

i have another for day, 01, 02, 03, 04 etc. thats in another drop down list

another is year, 2005, 2006. another drop down list

then i have time, hours, minutes, and AM PM in 3 other drop down lists.

i want it to show up on the page whatever is selected for example:

January 01, 2005
12:00 am

do i need to save them as a date/time type or can it be something else? if i cant how do i set this up in my database?

kburger

As I mentioned previously, it makes no difference what you have in your dropdowns as long as you get it into the format you need before inserting it into the database. So you can have your month dropdown read January, February, ... but you're going to need '01', '02', ... for the month value for your query. Example:

<select name="month">
<option value="-01-">January</option>
<option value="-02-">February</option>
...
</select>
<select name="day">
<option value="01">1</option>
<option value="02">2</option>
...
</select>
<select name="year">
<option value="2005">2005</option>
<option value="2006">2006</option>
...
</select>
<select name="hour">
<option value="00:">12 am</option>
<option value="01:">1 am</option>
...
</select>
<select name="min">
<option value="00:00">:00</option>
<option value="15:00">:15</option>
...
</select>

Then in your form handler you'll need to take those values and create your 'datetime' value. Something like this:

$datetime = $_POST['year'] . $_POST['month'] . $_POST['day'] . 
" " . $_POST['hour'] . $_POST['min'];

Your query would then look something like this:

INSERT INTO tablename ... showtimecolumn='$datetime'...

mattypoirier

wow i feel so stupid the reason it wasent saving my info in the database was i had a set of () at the end of one of the strings in my INSERT command. boy do i feel dumb, thanks for the help.

Weedpacket

kburger wrote:

$datetime = $_POST['year'] . $_POST['month'] . $_POST['day'] . 
" " . $_POST['hour'] . $_POST['min'];

Your query would then look something like this:

INSERT INTO tablename ... showtimecolumn='$datetime'...

WARNING! SQL Injection vulnerability!

kburger

Weedpacket wrote:
WARNING! SQL Injection vulnerability!

Thanks for pointing that out, Weedpacket. I'm used to an environment where magic_quotes are enabled. His may not be.

jmshillito

I am a newbie, but inherited some code I want to use (here is an extract)
This drop down list shows all the students in my table; a teacher selects a student by choosing the student name on the drop down list (there is no submit button as the teacher later chooses in a similar way from a merit category table ) Eventually there is a "SAVE THIS MERIT" button, which moves the user on to the SAVE-MERITS php page.

<select

size="1"
name="fstudentid"
onchange='document.frmstudent.submit()'>

<?php
require 'mer_connect.php';

$csql = "select * from mer_students order by studentname";
$result = mysql_query($csql);
$nrows = mysql_num_rows($result);
if ($nrows == -1) {echo 'Unexpected error in the query:<br/>'; die($csql);};
$cdummy = (!isset($p_fstudentid) ? "selected=\'selected\'" : "");
$row = mysql_fetch_array($result);

echo('<option value="0"'.$cdummy.'>(select a student)</option>');

while ($row = mysql_fetch_array($result))
{
if ($p_fstudentid == $row['id'])
{
echo('<option value = '.$row['id'].' selected=\'selected\' >'.$row['studentname'].'</option>');
$cdummy = substr($row['group'],2,1);
}
else
{
echo('<option value="'.$row['id'].'" >'.$row['studentname'].'</option>');
}
}

?>
</select>
<br/><br/>

////////////////////////////////////////
When all the data is selected via drop down, I move onto a new php page to save the data into a new (merits) table; however I'm not sure how to INSERT the variables from the above code into the merits table. What variables will be carried over, and can I simply INSERT INTO 'merits' ('Studentid', 'Studentname') VALUES (..??what goes here?)

Many thanks in advance

John

Would it help to post all the code?

kburger

Looks like you need some basic understanding of HTML forms. Here's the basic syntax for a 'select' dropdown:

<select name="fruit">
<option value="apple">Apple</option>
<option value="bananna">Bananna</option>
</select>

Assuming the method of your form was "GET", in your form handler the value would be found in $GET['fruit']. So if 'Bananna' was selected in your form, $GET['fruit'] would have a value of "bananna".

That should get you started.