Help::Multiple table insert into 4 tables

[Utech22]

I want to insert data from a html form into 4 mysql table using php.

This code is inserting but duplicating the values about 6 times:

 

$query = ("INSERT INTO name (fname,lname,..) VALUES ("Mary","Blige",..)"); 
$query = ("INSERT INTO address (town,parish,..) VALUES ("Mona","St. Andrew",..)"); 
$query = ("INSERT INTO phone (cell,fax,..) VALUES (8760001234,8769230002,..)"); 
$query = ("INSERT INTO computer (brand,type,..) VALUES ("Dell","Laptop",..)"); 

mysql_query($query)or die(mysql_error()); 

 

This is the select statement I have:

$query_student = mysql_query('SELECT * FROM name, address, phone, computer')or die (mysql_error());
while ($result_stu = mysql_fetch_array($query_student))
{
                  //echo statement to display the info
             }

 

How do I insert the data from the html form into the 4 mysql table?

[premiso]

First off mysql will throw an error cause you are using double quotes. (not to mention php also for a syntax error)  MySQL uses single quotes around data.

 

You would access form elements by $_POST['fieldname']

 

You would have to run mysql_query() on each of those statements, as the way you have it only the last statement would be executed.

[Utech22]

Are you refering to this method:

 

$query  = ('INSERT INTO name (fname,lname,..) VALUES ('Mary','Blige',..);  ')
$query1 = ('INSERT INTO address (town,parish,..) VALUES ('Mona','t. Andrew',..);  ')
$query2 = ('INSERT INTO phone (cell,fax,..) VALUES (8760001234,8769230002,..);  ')
$query3 = ('INSERT INTO computer (brand,type,..) VALUES ('Dell','Laptop',..);  ')
  
mysql_query($query)or die(mysql_error()); 
mysql_query($query1)or die(mysql_error()); 
mysql_query($query2)or die(mysql_error()); 
mysql_query($query3)or die(mysql_error());  

 

Yes! The fields are access form elements by $POST['fieldname']

[teng84]

Are you refering to this method:

 

$query  = ('INSERT INTO name (fname,lname,..) VALUES ('Mary','Blige',..);  ')
$query1 = ('INSERT INTO address (town,parish,..) VALUES ('Mona','t. Andrew',..);  ')
$query2 = ('INSERT INTO phone (cell,fax,..) VALUES (8760001234,8769230002,..);  ')
$query3 = ('INSERT INTO computer (brand,type,..) VALUES ('Dell','Laptop',..);  ')
  
mysql_query($query)or die(mysql_error()); 
mysql_query($query1)or die(mysql_error()); 
mysql_query($query2)or die(mysql_error()); 
mysql_query($query3)or die(mysql_error());  

 

Yes! The fields are access form elements by $POST['fieldname']

 

i guess this solved the prob so mark this solved by the way looking back at your code

the vakue for the $query  will only be the last $query= ('INSERT INTO computer (brand,type,..) VALUES ('Dell','Laptop',..);  ') it will be overwriten over and over agian and take the last $query variable to run on the query

[simcoweb]

I'm just wondering why you have so many tables for data on each person. You should have one table with a 'field' for each item then make just one query to insert it all.

[shen]

your query is correct, to avoid duplicates, after inserting a first table, get a last inserted id, and stored that id in other tables,

 

$query  = ('INSERT INTO name (fname,lname,..) VALUES ('Mary','Blige',..);  ') // here, set custid as autoincrement

$res = mysql_query($query)or die(mysql_error());

$custid = mysql_insert_id ($res);

 

$query1 = ('INSERT INTO address (custid,town,parish,..) VALUES (1,'Mona','t. Andrew',..);  ')

$query2 = ('INSERT INTO phone (custid,cell,fax,..) VALUES (1,8760001234,8769230002,..);  ')

$query3 = ('INSERT INTO computer (custid,brand,type,..) VALUES (1,'Dell','Laptop',..);  ')

 

 

mysql_query($query1)or die(mysql_error());

mysql_query($query2)or die(mysql_error());

mysql_query($query3)or die(mysql_error()); 

[simcoweb]

The query works but i'm still wondering why you'd want to have 4 separate tables. To me this looks like a single record regarding an person, their address, their phone and what computer they have. This should be in one table and have one query.

[shen]

you question is correct, but the user needs to access only the contact nos or their computer brands,  instead of access the large single table, a seperate table for contacts, computer brands, is slightly faster. whenever the very large number of records are stored in a table

[simcoweb]

Ok, but in the tone of that same logic...is it not slower and take more resources to run 4 queries on each insert?

[shen]

insertion is made only one time 4 queries are executed on registration only. but accessing of records (selection) ?

[Utech22]

Thank for the info so far. Let I modify to a more exact example

 

$id = $_POST['id']; //primary key: name table, foriegn key: other tables  

$fname = $_POST['fname'];

$lname = $_POST['lname'];

$town = $_POST['town'];

$parish = $_POST['parish'];

$cell = $_POST['cell'];

$fax = $_POST['fax'];

$brand = $_POST['brand'];

$type = $_POST['type'];



$query  = ("INSERT INTO name (id,fname,lname,..) VALUES ('$id','$fname','$lname',..)") or die(mysql_error());  

$query = ("INSERT INTO address (id,town,parish,..) VALUES ($id','$town','$parish',..)") or die(mysql_error());  

$query = ("INSERT INTO phone (id,cell,fax,..) VALUES ('$id','$cell','$fax',..)") or die(mysql_error());  

$query = ("INSERT INTO computer (id,brand,type,..) VALUES ('$id','$brand','$type',..)") or die(mysql_error());  



mysql_query($query)or die(mysql_error());  

 

This is what I have for the query:

 

$query = mysql_query('SELECT * FROM istudent, iaddress, iprogramme, iemployment')or die (mysql_error());

while ($result = mysql_fetch_array($query))

    {

                      echo $result['fname'];

                      //other echo statements

             }