0

I have a checkbox asking the user to put his/her favorite courses. I want to store all the selected courses in one column separated by delimiters in MySQL database

This is the way I insert it into mysql database. How should I be doing this?

$sql="INSERT INTO Students(FirstName, LastName,gender,Major,Favorite_courses,GPA,Date_of_reg)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[sex]','$_POST[major]','$_POST[favorite]','$_POST[GPA]',SYSDATE())";

$_POST[favorite] is the field in question.

Jason
  • 15,017
  • 23
  • 85
  • 116
sabsab
  • 1,073
  • 3
  • 16
  • 30
  • 2
    You should read up on sql injection and database normalization. This is definitely not the way to do it. – jeroen Jun 04 '13 at 20:38
  • 1
    Oh, hello there [Bobby](http://xkcd.com/327/). I'd like you to meet my good friend [Prevention](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php). – George Cummins Jun 04 '13 at 20:39
  • We [just told you yesterday](http://stackoverflow.com/questions/16904818/storing-the-date-variable-into-mysql-database-using-php) that you need to deal with your sql injection problems before proceeding. – nhinkle Jun 04 '13 at 21:48

3 Answers3

3
$sql="INSERT INTO Students(FirstName, LastName,gender,Major,Favorite_courses,GPA,Date_of_reg) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[sex]','$_POST[major]','implode(\',\',$_POST[\'favorite\'])','$_POST[GPA]',SYSDATE())";
AlienWebguy
  • 76,997
  • 17
  • 122
  • 145
  • 1
    Not a well-formed answer. Some details should be added. A single line of code doesn't explain so much. – Pavel Strakhov Jun 04 '13 at 20:39
  • Literally this comment took more effort than the answer. There is so much more wrong with the question than we have time or space for here so I'm just providing the fix as-is. No details should be added. – AlienWebguy Jun 04 '13 at 20:41
  • 1
    Considering the form of the question I think your answer in this form would be rather confusing than helpful. – Pavel Strakhov Jun 04 '13 at 20:46
  • 1
    can you please right the whole code because this is not clear enough for me – sabsab Jun 04 '13 at 20:47
  • 1
    No, Ahmed, SO is not a can-i-get-a-slave-so-I-can-sit-drinking-my-wine site... Slavery ended a few centuries ago in the western world and I, for one (and I'm sure the others would agree), see no sense in reviving it in 2013. – hd1 Jun 04 '13 at 21:38
  • Sorry but the code is not working there is syntax error message .. thank you for the lyrics hd1 – sabsab Jun 04 '13 at 22:36
2

If you REALLY want to put all favorite courses into a single field, you can make this field a string, and store the PHP array in it by serializing it. You can use the serialize() function, or JSON, json_encode(), or any other similar method. To retrieve the data, just use unserialize(), or json_decode(), and you will have your array back in PHP.

But keep in mind that using a SELECT statement to filter who likes course foo or course bar, will be quite dificult. The best approach is to follow jeroen's suggestion, normalizing your data.

Marcovecchio
  • 1,322
  • 9
  • 19
  • please I want a direct answer the serialize function looks complicated for me – sabsab Jun 04 '13 at 20:57
  • It's not. These functions can turn an array into a string and back to an array. In 5 minutes you can learn how it works, and it's your only hope to do it in a more or less clean way. – Marcovecchio Jun 04 '13 at 21:01
  • I will do my best what about this quastion can you answer it please [link](http://stackoverflow.com/questions/16927341/creating-and-coding-save-and-submit-buttons-in-php) – sabsab Jun 04 '13 at 21:08
  • If it's just an array of integers, a comma separated list is a much better option. mysql has functions which allow queries against comma separated lists; serialized data is useless until you parse it in php. – Sam Dufel Jun 04 '13 at 21:45
0

Well, for security and normalization reasons you shouldn't do that.

However, you can do it, if you really want to by using the function serialize() of php.

$myFavorites = serialize($_POST[favorite]);
$sql="INSERT INTO Students  (FirstName, LastName, gender, Major, Favorite_courses, GPA, Date_of_reg) VALUES
('$_POST[firstname]' ,'$_POST[lastname]' ,'$_POST[sex]' ,'$_POST[major]', '$myFavorites', '$_POST[GPA]', SYSDATE())";

You'll be able to unserialize it after you got it from your database with

$serializedCourses = myFunctionToGetItFromMyDatabase();
$unserializedCourses = unserialize($serializedCourses);

What I would do would be to create another table in your database for the Courses, and link it to the user via the user's id. For example you could create as much columns as there is type of courses, and that will make you able to get easily the list of people who like Maths (for example) or even Maths and Geometry.

You won't be able to do that with your solution easily.

Jay Zus
  • 573
  • 5
  • 19