Multiple table insert

prent327

Can someone tell me what is wrong with this code? The $IDNumber variable falls in an auto_increment column. However, nothing is being inserted into the 'General' table, and 'Notes' is inserted into the 'IncNotes' table, but it is coupled with a blank value for the IDNumber.

$IDNumber = "";
$Company = $_POST['Company'];
$Description = $_POST['Description'];
$Ticker = $_POST['Ticker']; 
$Ticker2 = $_POST['Ticker2'];
$Sector = $_POST['Sector'];
$Industry = $_POST['Industry'];
$Country = $_POST['Country'];
$Currency = $_POST['Currency'];
$Index1 = $_POST['Index1'];
$Index2 = $_POST['Index2'];
$Competitor = $_POST['Competitor'];
$Competitor2 = $_POST['Competitor2'];
$Competitor3 = $_POST['Competitor3'];
$Competitor4 = $_POST['Competitor4'];
$Competitor5 = $_POST['Competitor5'];
$Competitor6 = $_POST['Competitor6'];
$FinUnits = $_POST['FinUnits'];
$CorporateStatus = $_POST['CorporateStatus'];
$Entry = Notes;
$IDString = $IDNumber.$Entry;

$sql = "INSERT INTO General (Ticker, Ticker2, Company, Country, Sector, Industry, Competitor, Competitor2, Competitor3, 

Competitor4, Competitor5, Competitor6, FinUnits, Currency, Description, Index1, Index2, CorporateStatus) VALUES ('$Ticker', 

'$Ticker2', '$Company', '$Country', '$Sector', '$Industry', '$Competitor', '$Competitor2', '$Competitor3', '$Competitor4', 

'$Competitor5', '$Competitor6', '$FinUnits', '$Currency', '$Description', '$Index1', '$Index2', '$CorporateStatus')";

$sql = "INSERT INTO IncNotes (IDString, IDNumber, Data) VALUES ('$IDString', '$IDNumber', '$Entry')";

$result = mysql_query($sql, $db) or die ('There was a problem with the query: ' . $sql . '<br>' . mysql_error());

mysql_close();

umrguy

Why don't you create two different INSERT queries and run each seperatly? You are overwriting your first $sql variable when you rewrite it in the next line.

prent327

I changed it to the following, and the first Insert works fine, but I don't know how to tell the second Insert query to take the 'IDNumber' established in the first query through the auto_increment and apply it to the second Insert query.

$IDNumber = "";
$Company = $_POST['Company'];
$Description = $_POST['Description'];
$Ticker = $_POST['Ticker']; 
$Ticker2 = $_POST['Ticker2'];
$Sector = $_POST['Sector'];
$Industry = $_POST['Industry'];
$Country = $_POST['Country'];
$Currency = $_POST['Currency'];
$Index1 = $_POST['Index1'];
$Index2 = $_POST['Index2'];
$Competitor = $_POST['Competitor'];
$Competitor2 = $_POST['Competitor2'];
$Competitor3 = $_POST['Competitor3'];
$Competitor4 = $_POST['Competitor4'];
$Competitor5 = $_POST['Competitor5'];
$Competitor6 = $_POST['Competitor6'];
$FinUnits = $_POST['FinUnits'];
$CorporateStatus = $_POST['CorporateStatus'];


$sql = "INSERT INTO General (Ticker, Ticker2, Company, Country, Sector, Industry, Competitor, Competitor2, Competitor3, Competitor4, Competitor5, Competitor6, FinUnits, Currency, Description, Index1, Index2, CorporateStatus) VALUES ('$Ticker', '$Ticker2', '$Company', '$Country', '$Sector', '$Industry', '$Competitor', '$Competitor2', '$Competitor3', '$Competitor4', '$Competitor5', '$Competitor6', '$FinUnits', '$Currency', '$Description', '$Index1', '$Index2', '$CorporateStatus')";

$result = mysql_query($sql, $db) or die ('There was a problem with the query: ' . $sql . '<br>' . mysql_error());

$IDNumber = "";
$Entry = Notes;
$IDString = $IDNumber.$Entry;

$sql = "INSERT INTO IncNotes (IDString, IDNumber, Data) VALUES ('$IDString', '$IDNumber', '$Entry')";

$result = mysql_query($sql, $db) or die ('There was a problem with the query: ' . $sql . '<br>' . mysql_error());

mysql_close();

Weedpacket

You mean you want the ID MySQL used in the first INSERT? Something like that returned by [man]mysql_insert_id[/man]?

prent327

Yeah, I want the last IDNumber, which is an auto_increment ID. And I also then want to create a variable $IDString, which takes that ID and couples it with $Entry.
How would this look?

Weedpacket

See the link.

prent327

I got that working, and now I want to pass the IDNumber on again, but it isn't working, it's just passing along a 0 value. What would you recommend? Here's my code.

$IDNumber = "";
$Company = $_POST['Company'];
$Description = $_POST['Description'];
$Ticker = $_POST['Ticker']; 
$Ticker2 = $_POST['Ticker2'];
$Sector = $_POST['Sector'];
$Industry = $_POST['Industry'];
$Country = $_POST['Country'];
$Currency = $_POST['Currency'];
$Index1 = $_POST['Index1'];
$Index2 = $_POST['Index2'];
$Competitor = $_POST['Competitor'];
$Competitor2 = $_POST['Competitor2'];
$Competitor3 = $_POST['Competitor3'];
$Competitor4 = $_POST['Competitor4'];
$Competitor5 = $_POST['Competitor5'];
$Competitor6 = $_POST['Competitor6'];
$FinUnits = $_POST['FinUnits'];
$CorporateStatus = $_POST['CorporateStatus'];


$sql = "INSERT INTO General (Ticker, Ticker2, Company, Country, Sector, Industry, Competitor, Competitor2, Competitor3, Competitor4, Competitor5, Competitor6, FinUnits, Currency, Description, Index1, Index2, CorporateStatus) VALUES ('$Ticker', '$Ticker2', '$Company', '$Country', '$Sector', '$Industry', '$Competitor', '$Competitor2', '$Competitor3', '$Competitor4', '$Competitor5', '$Competitor6', '$FinUnits', '$Currency', '$Description', '$Index1', '$Index2', '$CorporateStatus')";

$result = mysql_query($sql, $db) or die ('There was a problem with the query: ' . $sql . '<br>' . mysql_error());

$IDNumber2 = mysql_insert_id();
$Entry = Notes;
$IDString = $IDNumber2.$Entry;

$sql = "INSERT INTO IncNotes (IDString, IDNumber, Data) VALUES ('$IDString', '$IDNumber2', '$Entry')";

$result = mysql_query($sql, $db) or die ('There was a problem with the query: ' . $sql . '<br>' . mysql_error());

$IDNumber3 = mysql_insert_id();
$Entry = Equity;
$IDString = $IDNumber3.$Entry;

$sql = "INSERT INTO BalanceSheet (IDString, IDNumber, Data) VALUES ('$IDString', '$IDNumber3', '$Entry')";

$result = mysql_query($sql, $db) or die ('There was a problem with the query: ' . $sql . '<br>' . mysql_error());
mysql_close();