Hi all,

 

I am having a hair-ripping moment. I have a form where I want some of the information stored in one database and some in another. Currently I can insert data into one table fine and with no problems but as soon as I add the code to add into an additional table, the first table is populated, but the second is not and no errors are displayed to inform me of any problems.

 

I am attempting to populate both tables with separate INSERT statements and I have compared the two for any typos or errors but there are none. I have searched through google, but there is nothing that seems useful or understandable. I would really be grateful if anyone can help. If anyone needs any other information, just let me know.

 

TIA

youll need to post your code

I guess the code would be helpful! lol. When the file is called, it runs through the code and echos out the "Data Inserted!" line which is what's causing me problems as I can't find out what's going wrong.

 

<?php
  // Connect to Database
  mysql_connect("***", "***", "***") or die(mysql_error());
  echo "Connected to MySQL";

  mysql_select_db("djmacdon") or die(mysql_error());
  echo "Connected to Database";

  // Insert Data into the Person Table
  $PersonType = $_POST["PersonType"];
  $Forename = $_POST["Forename"];
  $MiddleName = $_POST["MiddleName"];
  $Surname = $_POST["Surname"];
  $Address1 = $_POST["Address1"];
  $Address2 = $_POST["Address2"];
  $City = $_POST["City"];
  $County = $_POST["County"];
  $PostCode = $_POST["PostCode"];
  $Telephone = $_POST["Telephone"];

  $LAddress1 = $_POST["LAddress1"];
  $LAddress2 = $_POST["LAddress2"];
  $LCity = $_POST["LCity"];
  $LCounty = $_POST["LCounty"];
  $LPostCode = $_POST["LPostCode"];
  $LDescription = $_POST["LDescription"];

  $query2 = "INSERT INTO Person (Person_id, PersonType, Forename, MiddleName, Surname, Address1, Address2, City, County, PostCode, Telephone) VALUES ('NULL', '$PersonType', '$Forename', '$MiddleName', '$Surname', '$Address1', '$Address2', '$City', '$County', '$PostCode', '$Telephone')";
  $result2 = mysql_query($query2) or die(mysql_error);

  // Insert Data into the Location Table
  $query3 = "INSERT INTO Location (Location_id, LocationType, Address1, Address2, City, County, PostCode, Description) VALUES ('NULL', 'NULL', '$LAddress1', $LAddress2', '$LCity', '$LCounty', '$LPostCode', '$LDescription')";
  $result3 = mysql_query($query3) or die(mysql_error);
  echo "Data Inserted!";
?>

I guess the code would be helpful! lol. When the file is called, it runs through the code and echos out the "Data Inserted!" line which is what's causing me problems as I can't find out what's going wrong.

 

<?php
  // Connect to Database
  mysql_connect("***", "***", "***") or die(mysql_error());
  echo "Connected to MySQL";

  mysql_select_db("djmacdon") or die(mysql_error());
  echo "Connected to Database";

  // Insert Data into the Person Table
  $PersonType = $_POST["PersonType"];
  $Forename = $_POST["Forename"];
  $MiddleName = $_POST["MiddleName"];
  $Surname = $_POST["Surname"];
  $Address1 = $_POST["Address1"];
  $Address2 = $_POST["Address2"];
  $City = $_POST["City"];
  $County = $_POST["County"];
  $PostCode = $_POST["PostCode"];
  $Telephone = $_POST["Telephone"];

  $LAddress1 = $_POST["LAddress1"];
  $LAddress2 = $_POST["LAddress2"];
  $LCity = $_POST["LCity"];
  $LCounty = $_POST["LCounty"];
  $LPostCode = $_POST["LPostCode"];
  $LDescription = $_POST["LDescription"];

  $query2 = "INSERT INTO Person (Person_id, PersonType, Forename, MiddleName, Surname, Address1, Address2, City, County, PostCode, Telephone) VALUES ('NULL', '$PersonType', '$Forename', '$MiddleName', '$Surname', '$Address1', '$Address2', '$City', '$County', '$PostCode', '$Telephone')";
  $result2 = mysql_query($query2) or die(mysql_error);

  // Insert Data into the Location Table
  $query3 = "INSERT INTO Location (Location_id, LocationType, Address1, Address2, City, County, PostCode, Description) VALUES ('NULL', 'NULL', '$LAddress1', $LAddress2', '$LCity', '$LCounty', '$LPostCode', '$LDescription')";
  $result3 = mysql_query($query3) or die(mysql_error);
  echo "Data Inserted!";
?>

 

try this for the first query

 

  $result2 = mysql_query($query2);
if(!$result2){
die("Mysqli error found can not insert data<br />".mysql_error());
}

The errors aren't showing because you've used mysql_error with no parentheses following it. It should be mysql_error()

Also, does "Data inserted" actually appear on the page?

Dont quote NULL in single quotes, just NULL on its own will suffice otherwise MySQL may try and interpret it as the string NULL rather than the datatype.

 

Or consider ditching those columns from your insert query if they already have a default value.

Edited March 23, 201115 yr by Jock

Good Point, NULL in quotes will always be treated as a string, not NULL so shouldn't be in quotes. Well spotted Jock!

Thank you for the responses. I have changed the code you suggested and also for the other table too. I have also removed the single quotes from the NULL entries and I am still only getting one table populated. I am not getting any errors and it does display "Data inserted." on the page when it's finished.

Thank you for the responses. I have changed the code you suggested and also for the other table too. I have also removed the single quotes from the NULL entries and I am still only getting one table populated. I am not getting any errors and it does display "Data inserted." on the page when it's finished.

 

Today is your lucky day as I have the same obstacle like this a year ago!

 

The magic word here is TRUE

You can make multiple calls to mysql_connect(), but if the parameters are the same ($host, $user, $pass) you need to pass the value "true" as the forth parameter ($host, $user, $pass, true), otherwise the same connection is reused.

 

I have made a Blog Post: Connecting Multiple MySQL Databases On A Single Page in my personal website for the solution and code demonstration for the SQL statement.

 

There you go

hes using the same connection, just different tables.

I was carried away with the problem and I seems to create another problem here

 

After looking back at your code I saw this syntax error, missing the symbol ' in the $LAddress2 variable:

 

// Insert Data into the Location Table
  $query3 = "INSERT INTO Location (Location_id, LocationType, Address1, Address2, City, County, PostCode, Description) VALUES ('NULL', 'NULL', '$LAddress1', $LAddress2', '$LCity', '$LCounty', '$LPostCode', '$LDescription')";
  $result3 = mysql_query($query3) or die(mysql_error);
  echo "Data Inserted!";

Edited March 25, 201115 yr by Monie

I was carried away with the problem and I seems to create another problem here

 

After looking back at your code I saw this syntax error, missing the symbol ' in the $LAddress2 variable:

 

// Insert Data into the Location Table
  $query3 = "INSERT INTO Location (Location_id, LocationType, Address1, Address2, City, County, PostCode, Description) VALUES ('NULL', 'NULL', '$LAddress1', $LAddress2', '$LCity', '$LCounty', '$LPostCode', '$LDescription')";
  $result3 = mysql_query($query3) or die(mysql_error);
  echo "Data Inserted!";

 

I believe Monie has solved the problem here.

 

I'm assuming that the Location data will be related to the Person data. Would it be a good to insert the person data first, get the id for that result using mysql_insert_id, and then linking the location result to that id?