how to populate a drop down list and save it to your database?

Question

I would like to populate a drop down list of moods and save it to my database with a time stamp. And at the same time connect the moods with the current user. How should I approach this?

In my database, I have a table called "checkin" which includes: ID, Time, FKMoods (foreign key to list of moods) and FKUsers(foreign key to users).

Right now I have a form called checkin in "page.php":

<form id="checkin" action="checkin/checkin.php" method="POST">
<label for="checkinMood">Select your current mood :</label>
<select id="checkinMood" class="SelectMood"></select><br />
<input type="submit" id="checkin-button" value="Update!" />

Your best bet is 1) Create an HTML form with a PHP action - just like you've done, 2) your PHP action (checkin/checkin.php) will 3) read the POST variables and 4) update the database. — paulsm4, Dec 06 '12 at 16:13
I've tried this tutorial: http://www.aleixcortadellas.com/main/2009/02/13/277/ but i would also like a time stamp on that — needhelp, Dec 06 '12 at 16:18
@needhelp: Use MySQL's `NOW()` or PHP's `date()` to get a timestamp. — gen_Eric, Dec 06 '12 at 16:20
This question shows no research effort. You are essentially asking someone to write all the code for you. As desperate as people are for internet points, I don't think anyone will do this for you. Your best bet is to check out http://php.net/manual/en/ and start from there. — Kevin Brydon, Dec 06 '12 at 16:22

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

Steps to accomplish this

Select the moods from a moods table
Loop through the results and put them into a select input
Upon submitting, update the given mood into the user table

edited May 23 '17 at 11:48

Community

1
1

answered Dec 06 '12 at 16:15

twodayslate

2,803
3
27
43

score 0 · Answer 2 · answered Dec 06 '12 at 16:19

Don't save the html option list to the database. It's too hard to edit when you want to change the options. Store a list of moods like this:

CREATE table moods
mood_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
mood VARCHAR(45);

To make the option list, query the mood table and loop through the results creating options as you go:

<option value='{$mood["mood_id"}'>{$mood["mood"]}</option>

To store the user's mood, update the users table with the mood_id.

score 0 · Answer 3 · edited Dec 06 '12 at 16:30

Do a joined sql query to pull the foreign keyed values/rows from other tables first - assuming you are using mysql - using very basic functions everyone knows (without going into whether you are using mysqli or any pdo or lib) :

SELECT * FROM checkin c LEFT JOIN listofmoods lm ON c.FKMoods = lm.FKMoods LEFT JOIN listofusers lu ON c.FKUsers = lu.FKUsers ORDER BY c.ID ASC

(or however you want to sort)

Now you got all the fields you need. then run a mysql fetch to create the list, something like :

$query=mysql_query(-thequery listed above-);

while($fetch=mysql_fetch_assoc($query))
{

   $moodselect.='<option value="'.$fetch['FKMoods'].'">'.$fetch['whateverfieldlabelthemoodnameyouhaveonmoodstable'].'</option>';


}

this will pop you a $moodselect with the necessary options being there. you can then shove this in to the select you have in your example.

you would probably have to put in the user id in some hidden field in your form. or if your user system recognizes the user automatically from session you can just use it when the form is posted to checkin.php.

how to populate a drop down list and save it to your database?

3 Answers3