Insert multiple (fk) data in one of the two phpMyAdmin table

Question

**Hi. These are my structures :

I got 2 tables in phpMyAdmin :

Class ( classid(pk), className )
Students ( studentid(pk), classid(fk), studentName ) 1 form.php and 1 form_post.php

I've done this in form.php**

include_once("connection.php");


    $className= $_POST["className"];
    $studentName1= $_POST["studentName1"];
    $studentName2= $_POST["studentName2"];
    $studentName3= $_POST["studentName30"];


    $sql = "insert into class(className) values ('$className')";


    mysql_query($sql);

    $lastid=mysql_insert_id();


    $sql2= "INSERT INTO students (studentid, classid, studentName)
            VALUES (' ', $lastid, '$studentName')";

    mysql_query($sql2);

form_post.php :

            <tr>
              <td width="140"><span class="order">Class Name: </span></td>
              <td><span class="order">
                <input name="className" type="text" id="className" size="35" />
              </span></td>
            </tr>
            <tr>
              <td width="132"><span class="order">Student's Name: </span></td>
              <td><span class="order">
                <input type="text" name="studentName1" id="studentName1" />
              </span></td>
            </tr>
           <tr>
              <td width="132"><span class="order">Student's Name: </span></td>
              <td><span class="order">
                <input type="text" name="studentName2" id="studentName2" />
              </span></td>
            </tr>
            <tr>
              <td width="132"><span class="order">Student's Name: </span></td>
              <td><span class="order">
                <input type="text" name="studentName3" id="studentName3" />
              </span></td>
            </tr>

It's a form for registering class and students in it. In phMyAdmin student table, there's a field/column for studentid(pk), a field/column for classid(fk), a field/column for studentName while this code which I got from my friend got 3 students name field. How to insert the 3 students name into the phpMyAdmin student table's studentName column with the same class foreign key? The red colored words are the confusing one. I'm still a beginner and I tried all the night to get this work. But, I'm still stuck. Thank you :)

score 1 · Accepted Answer · edited May 23 '17 at 12:12

You can use insert multiple rows depending on if you're inserting the same data into the same tables

Always good to escape your values

$className= mysql_real_escape_string($_POST["className"]));
$studentName1= mysql_real_escape_string($_POST["studentName1"]);
$studentName2= mysql_real_escape_string($_POST["studentName2"]);
$studentName3= mysql_real_escape_string($_POST["studentName3"]);

$sql2= "INSERT INTO students (studentid, classid, studentName)
        VALUES (' ', $lastid, '$studentName1'),
               (' ', $lastid, '$studentName2'),
               (' ', $lastid, '$studentName3')";

mysql_query($sql2);

Recommendations:

1.Learn to prevent from MySQL Injections: Good Link

2.Mysql extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. More reading: PHP Manual

Second Solution:

if(!empty($studentName1)) {
  $sql2= "INSERT INTO students (studentid, classid, studentName)
            VALUES (' ', $lastid, '$studentName1')";

  mysql_query($sql2);
}

if(!empty($studentName2)) {
  $sql2= "INSERT INTO students (studentid, classid, studentName)
            VALUES (' ', $lastid, '$studentName2')";
  mysql_query($sql2);
}

if(!empty($studentName3)) {
  $sql2= "INSERT INTO students (studentid, classid, studentName)
            VALUES (' ', $lastid, '$studentName3')";
  mysql_query($sql2);
}

I wonder, what if the $studentname2 is empty? . It will insert the value 'no' in the table right? How do I get rid of this? Thanks :D — user1822825, Nov 14 '12 at 14:42
Then you need to write three individual insert queries and check if value exist, then query otherwise no using `if` statement — GBD, Nov 14 '12 at 14:45
Thanks for the answer. After exploring the if statement. I changed the code to be like this : if (is_null($_POST["studentName2"]) || $_POST["studentName2"]=="") { $sql2= "INSERT INTO students (studentid, classid, studentName) VALUES ('NULL', 'NULL', 'NULL')"; ; } else { $sql2= "INSERT INTO students (studentid, classid, studentName) VALUES (' ', $lastid, '$studentName2')"; } Is it right ? Thanks :D — user1822825, Nov 14 '12 at 15:10
Thank you very much :D. Didn't know that you edited this post and I accidentally commented in this post. Thank you again :D — user1822825, Nov 14 '12 at 15:12

score 0 · Answer 2 · answered Nov 14 '12 at 06:06

0

$sql2= "INSERT INTO students (studentid, classid, studentName)
        VALUES (null, $lastid, '$studentName')";

answered Nov 14 '12 at 06:06

keyboardSmasher

2,661
18
20

score 0 · Answer 3 · answered Nov 14 '12 at 06:07

0

$sql2= "INSERT INTO students (studentid, classid, studentName)
        VALUES ('', $lastid, '$studentName')";

answered Nov 14 '12 at 06:07

shapeshifter

2,967
2
25
39

Oops, I tried to edit my post and I think I attempted to edit yours by accident. sigh – keyboardSmasher Nov 14 '12 at 06:15

Insert multiple (fk) data in one of the two phpMyAdmin table

3 Answers3