Sql table relationship from a form

Question

I have a form that gathers information about a person to be used in a contact book. I need to create two tables. One for the majority of the data and another for phone numbers alone because each person can have multiple phone numbers. Obviously I want these table to be relatable but I do not understand how to input the data from the form into both tables so they share a relationship. I also need to be able to join the data into a single phonenumbers column in the main table.

The idea is illustrated by this:

_{(source: gyazo.com)}

Use the tableA_id to populate the phonenumbers fields. How to i input the data from the form to be able to do this?

Updated code

11 //Form Validation
12 $name = $email = $gender = $comment = $website = "";
13
14 if ($_SERVER["REQUEST_METHOD"] == "POST")
15 {
16   $firstname = test_input($_POST["firstname"]);
17   $email = test_input($_POST["email"]);
18   $lastname = test_input($_POST["lastname"]);
19   $street = test_input($_POST["street"]);
20   $city = test_input($_POST["city"]);
21   $state = test_input($_POST["state"]);
22   $country = test_input($_POST["country"]);
23   $workphone = test_input($_POST["workphone"]);
24   $mobilephone = test_input($_POST["mobilephone"]);
25   $homephone = test_input($_POST["homephone"]);
26   $phonearray = array("$workphone","$mobilephone","$homephone");
27
28
29 }
30
31 function test_input($data)
32 {
33   $data = trim($data);
34   $data = stripslashes($data);
35   $data = htmlspecialchars($data);
36   return $data;
37 }
//After validation we input the data into the tables.
84
85 $sql="INSERT INTO nameaddress
86 (FirstName, LastName, Street, City, State, Country,email,photo)
87 VALUES
88     ('$firstname','$lastname','$street','$city','$state','$country','$email','$uploadfile')";
89
90
91
92
93
94 if (!mysqli_query($con,$sql))
95   {
96   die('Error: ' . mysqli_error($con));
97   }
98
99 $lastInsertId=mysqli_insert_id($con);
100 foreach ($phonearray as $a) {
101 $select="INSERT INTO userphones(phonenumbers,nameaddress_id)102
102         VALUES ($a,".$lastInsertId.")";
103 mysqli_query($con,$select);
104 }

score 1 · Accepted Answer · answered Nov 01 '13 at 18:11

1

To insert data you need to do it in loop in php, will look like (pseudo-code):

single insert into tableA (firstname, lastname, address, email)

get last inserted id (mysql_insert_id or mysqli_insert_id)

in loop insert all phone numbers: foreach $phonenumber insert into phones(phonenumber, lastidfrom insert into tableA)

For output you need to use group_concat, so your query will look like this:

select a.*, group_concat(b.phonenumbers)
from tableA as a
inner join
phones as b
on (a.id=b.tableA_id)
group by a.id

NOTE: mysql with strict mode will not allow you to use a.* with only a.id in group by, you will need to do like this:

select *
from tableA as a
inner join
(
    select tableA_id, group_concat(phonenumbers)
    from tableB
    group by tableA_id
) as q
on (a.id=q.tableA_id)
group by a.id

answered Nov 01 '13 at 18:11

Iłya Bursov

23,342
4
33
57

I having trouble getting the loop to work. I am going to post a snippet of my code in the original question. – user2872127 Nov 01 '13 at 20:27
@user2872127 why do you set variable $lastinsert to _strange_ string value and trying to insert it into database? maybe you wanted to execute it and store result? or maybe you should go with mysqli_insert_id - will be easier – Iłya Bursov Nov 01 '13 at 20:45
Alright I changed it and i think the mysqli_insert_id syntax is correct but im not sure. – user2872127 Nov 01 '13 at 20:51
@user2872127 syntax is not correct, you will see it if you handle error from mysqli_query, change it to `$lastInsertId=mysqli_insert_id($con);` before FOREACH and in it `$select="INSERT INTO userphones(mobilephone,nameaddress_id) 102 VALUES ($a,".$lastInsertId.")";` – Iłya Bursov Nov 01 '13 at 20:53
Alright i changed it again. It's not throwing an error but its not inserting anything into my phonenumber table. – user2872127 Nov 01 '13 at 20:59
@user2872127 you're not handling errors from mysqli_query - it is syntax error in query itself for debug always use `mysqli_query($con,$select) or die(mysqli_error($con))`; – Iłya Bursov Nov 01 '13 at 21:01

score 0 · Answer 2 · edited May 23 '17 at 11:45

Add to your first table for the majority of data a primary key (e.g., "id"). The second table for the phonenumber data should have an indexed field for the primary key from the first table.

Then you will want to first INSERT the non-phonenumber data into this table. Then use SELECT LAST_INSERT_ID() to get the primary key of this insert and add that to your insert into the second table. This way all then entries in the phonenumber table will have an indexed field that points back to the user in the first table.

This stackoverflow thread has a good discussion on using LAST_INSERT_ID():

Get the new record primary key ID from mysql insert query?

Sql table relationship from a form

2 Answers2