Inserting data into multiple tables in MySQL using PHP

They have: 1 posts

Joined: Sep 2012


I have a form whereby when user clicks "submit" the data filled inside the field will be stored inside 2 tables. all of the fields will be saved inside studentDetails table while only some of it will be saved inside another table, called KPP table.

Below is a fraction of my codes. Am i doing it right? Thanks in advance..

$sql="INSERT INTO $studentDetails VALUES ('$IC','$name','$sex','$address','$phonenum1','$phonenum2','$type','$date','$deposit','$totalFee')";

$sql2="INSERT INTO $kpp(ICnum,licenseType,dateRegistered,totalFee,remainingAmount) VALUES ('$IC','$type','$date','$totalFee','$answer')";

if($result && $result2){
printf("window.alert (\"New record added!\");");
include "AddNewRecord.html";
else {
echo "Error: " . mysql_error()."";

Greg K's picture

He has: 2,145 posts

Joined: Nov 2003

On the basic level, the code looks like it would work, below is what I would have done given the code you presented.

1. If not already done so, variables affected by the web user need to be escaped so that if they enter don't know as an entry, it won't break your query.

2. Guessing that you are not needing the SQL statement or results else where, you can simply recycle the variable named.

3. The way you had it working on the error checking, if the first query failed yet the second one went fine, you would not have seen the error, as mysql_error() gives you the results from the last query executed. As it is good to get into the practice of all data handling being done BEFORE any actual output (makes the output code a lot easier to work with), I put the die() statements right up with the query, so the script will stop at the right spot, not in the middle of code. To be honest, once live they should really never fire. If there is a possible issue of insert errors, say ICnum is a primary key, so you can't have duplicates, you should be doing a query beforehand to check to see if it exists, and either nicely error to the user, or do an update instead)

4. Assuming that $studentDetails and $kpp variables are NOT somehow set user, it is good practice to get used to wrapping all table names/fieldnames with the correct type of (basically) quoting method, the backtick (NOT a single quote, on PC's the key to the left of the 1 key, above TAB). Once you get used to it, it really ends up not being much extra. Also, just in case $kpp is something like "database.table" instead of just "table", note the following code would not work, each item needs wrapped... ex `database`.`table` not `database.table`

// If not already handled somewhere else, each variable MUST have the following applied to it, do NOT use addslashes
$IC        = mysql_real_escape_string($IC);
$name      = mysql_real_escape_string($name);
$sex       = mysql_real_escape_string($sex);
$address   = mysql_real_escape_string($address);
$phonenum1 = mysql_real_escape_string($phonenum1);
$phonenum2 = mysql_real_escape_string($phonenum2);
$type      = mysql_real_escape_string($type);
$date      = mysql_real_escape_string($date);
$deposit   = mysql_real_escape_string($deposit);

$sql = "INSERT INTO `$studentDetails` VALUES ('$IC','$name','$sex','$address','$phonenum1','$phonenum2','$type','$date','$deposit','$totalFee')";
$result = mysql_query($sql,$conn) or die('[ERR:'.__LINE__.']: '.mysql_error());

$sql = "INSERT INTO `$kpp` (`ICnum`,`licenseType`,`dateRegistered`,`totalFee`,`remainingAmount`) VALUES ('$IC','$type','$date','$totalFee','$answer')";
$result = mysql_query($sql,$conn) or die('[ERR:'.__LINE__.']: '.mysql_error());

// At this point it would have DIEd out if there was error...
printf("window.alert (\"New record added!\");");
include "AddNewRecord.html";

They have: 11 posts

Joined: Apr 2013

We can done it by using database connectivity code in php i.e. mysql_connect(); after the clicking on the submit button, the data transfer on multiple tables.

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.