Storing data in a MySQL database using MySQL & PHP

Question

I'm new to PHP and MySQL and I'm trying to store a users entered data from the following fields $skill, $experience, $years which a user can also add additional fields of $skill, $experience, $years if needed so in instead of 1 of each field there might be multiples of each field.

I was wondering how can I store the fields in my MySQL database using PHP and MySQL? I have the following script but I know its wrong. can some one help me fix the script listed below?

Here is the PHP and MySQL code.

$skill = serialize($_POST['skill']);
$experience = serialize($_POST['experience']);
$years = serialize($_POST['years']);


for (($s = 0; $s < count($skill); $s++) && ($x = 0; $x < count($experience); $x++) && ($g = 0; $g < count($years); $g++)){
    $mysqli = mysqli_connect("localhost", "root", "", "sitename");
    $query1 = "INSERT INTO learned_skills (skill, experience, years) VALUES ('" . $skill[$s] . "', '" . $experience[$x] . "', '" . $years[$g] . "')";

    if (!mysqli_query($mysqli, $query1)) {
            print mysqli_error($mysqli);
            return;
    }

}

Here is my MySQL table.

CREATE TABLE learned_skills (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
skill TEXT NOT NULL,
experience TEXT NOT NULL,
years INT NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE u_skills (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
skill_id INT UNSIGNED NOT NULL, 
users_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

webbiedave · Accepted Answer · 2010-04-22 22:47:01Z

2

You would create two tables that have a 1 to many relationship:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(40) collate latin1_general_ci NOT NULL,
  `password` varchar(255) collate latin1_general_ci NOT NULL
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`)
);



CREATE TABLE learned_skills (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
skill TEXT NOT NULL,
experience TEXT NOT NULL,
years INT NOT NULL,
PRIMARY KEY (id)
);

This way a user can have any number of listed skills. If skill is really just a small text string (like "PHP" or "MySQL") then you should use a VARCHAR type instead of TEXT. If that's the case, once you get going you'll see that it would be better to create a list of skills that the user can choose from and just have a skill_id rather than a text field. This helps with something called normalization (a way to prevent duplicate data).

Good luck in your learning.

answered Apr 22, 2010 at 22:47

webbiedave

49k8 gold badges92 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

comma Over a year ago

so I should just combine the two tables u_skills and learned_skills is this best?

webbiedave Over a year ago

If a user can have multiple skills, then that is a 1 (user) to many (skills) relationship. That's what my suggested DDL implements.

comma Over a year ago

the user will only enter the skill everything else is in a drop down list.

comma Over a year ago

I only left the skill as a text field because it would be impossible to list every skill as a drop down list.

webbiedave Over a year ago

But you'll only be storing one skill per row. I don't think you need to allow a skill that's more than 50 characters. So a VARCHAR (which is up to 255 characters) should be more than enough.

|

Tilo Mitra · Accepted Answer · 2010-04-22 22:48:36Z

0

If there are multiples, you will have to either create a column for each one or put them into the same field with some sort of a delimiter (skill1, skill2, skill3). Then, upon retrieval you can split the array using that delimiter.

answered Apr 22, 2010 at 22:48

Tilo Mitra

2,7831 gold badge27 silver badges20 bronze badges

Comments

brian_d · Accepted Answer · 2010-04-22 23:51:26Z

0

you can use multiple tables with column lookups as suggested.

there are also times when you might want to use a serialized array like you started to do. however, after you serialized it, it is a string, so trying to access values by index does not make sense. you have

 $skill = serialize($_POST['skill']);
...    

    $query1 = "INSERT INTO learned_skills (skill, experience, years) 
VALUES ('" $skill[$s]

which would not work.

also you might want to do something like

$counter = min(count($skill), count($experience), count($years));
for($i = 0; $i < $counter; ++$i){

instead of

for (($s = 0; $s < count($skill); $s++) && ($x = 0; $x < count($experience); $x++) && ($g = 0; $g < count($years); $g++)){

answered Apr 22, 2010 at 23:51

brian_d

11.4k5 gold badges51 silver badges73 bronze badges

Collectives™ on Stack Overflow

Storing data in a MySQL database using MySQL & PHP

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related