inserting into 2 tables at same time

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

inserting into 2 tables at same time

Post by John Cartwright »

This is the code I use which inputs the information from a form into 2 different tables when clicked submit

Code: Select all

<?


if ($submitrecord == "SUBMIT") { 
  	if ($league == cal) {
  	$sql = "INSERT INTO records_cal SET opponent='$opponent', date='$date', map='$map', league='$league', outcome='$outcome'";
    $sql = "INSERT INTO records_total SET opponent='$opponent', date='$date', map='$map', league='$league', outcome='$outcome'";
	}else{
	$sql = "INSERT INTO records_scrim SET opponent='$opponent', date='$date', map='$map', league='$league', outcome='$outcome'";
    $sql = "INSERT INTO records_total SET opponent='$opponent', date='$date', map='$map', league='$league', outcome='$outcome'";
	}

?>
The problem is it is only inserting into the second insert statement :S
How do I go about mergin the 2 different $sql insert commands
tsg
Forum Contributor
Posts: 142
Joined: Sun Jan 12, 2003 9:22 pm
Location: SE, Alabama
Contact:

Post by tsg »

First I would add some error checking, second, you probably need to add some quotes around cal
if(@mysql_query($sql)) {

Code: Select all

<?php
if ($submitrecord == "SUBMIT") { 
     if ($league == "cal") { 
     $sql = "INSERT INTO records_cal SET opponent='$opponent', date='$date', map='$map', league='$league', outcome='$outcome'"; 
if(@mysql_query($sql)) {
print "added";
} else { echo("Error adding > " . mysql_error() . " < that error"); }

    $sql = "INSERT INTO records_total SET opponent='$opponent', date='$date', map='$map', league='$league', outcome='$outcome'"; 
if(@mysql_query($sql)) {
print "added";
} else { echo("Error adding > " . mysql_error() . " < that error"); }

   }else{ 
   $sql = "INSERT INTO records_scrim SET opponent='$opponent', date='$date', map='$map', league='$league', outcome='$outcome'"; 
if(@mysql_query($sql)) {
print "added";
} else { echo("Error adding > " . mysql_error() . " < that error"); }

    $sql = "INSERT INTO records_total SET opponent='$opponent', date='$date', map='$map', league='$league', outcome='$outcome'"; 
if(@mysql_query($sql)) {
print "added";
} else { echo("Error adding > " . mysql_error() . " < that error"); }

   } 

?>
[/quote]
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

hrm.. when I do that I get no bad error messages, but information in the records_total is being inputted twice.

Can't I do something like this?

Code: Select all

<?php
  	$sql = "INSERT INTO records_cal,records_total SET opponent='$opponent', date='$date', map='$map', league='$league', outcome='$outcome'" ;

?>
But that gives me syntax error
tsg
Forum Contributor
Posts: 142
Joined: Sun Jan 12, 2003 9:22 pm
Location: SE, Alabama
Contact:

Post by tsg »

I have no idea, but if you try it, I would do something like this:

Code: Select all

<?php
<?
     $sql = "INSERT INTO records_cal,records_total SET records_cal.opponent='$opponent', records_total.opponent='$opponent', etc ..... " ; 


?>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I dun't understand what you mean
tsg
Forum Contributor
Posts: 142
Joined: Sun Jan 12, 2003 9:22 pm
Location: SE, Alabama
Contact:

Post by tsg »

Code: Select all

<? 
     $sql = "INSERT INTO records_cal,records_total SET records_cal.opponent='$opponent', records_total.opponent='$opponent', etc ..... " ; 
?>
indicate each table name with each value.
records_cal.opponent='$opponent' (TABLE NAME).opponent='$opponent'

Like I said, I don't know if it will work, never tried it, but just going by theory.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Okay everything is working fine ( as in no errors )... but even the new sql insert thing but I am still getting duplicate entries !! GRRR

This is what I'm using

Code: Select all

<?php
// Inserting information into the database
if ($submitrecord == "SUBMIT") { 
     if ($league == "cal") { 


	$sql = "INSERT INTO records_cal,records_total SET records_cal.'$opponent', date='$date', map='$map', league='$league', outcome='$outcome', records_total.'$opponent', date='$date', map='$map', league='$league', outcome='$outcome'" ; 
	
	}
// Confirming data has been entered or not
  if (@mysql_query($sql)) { 
    echo("<div align="center">Your Record has been added.</div>"); 
  } else { 
    echo ("<div align="center">Error adding submitted record: " . mysql_error() . "</div>"); 
  } 
}
?>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

up

Sorry let me clarify what I mean by duplicate entries

1 row is created in the cal table ( like its supposed to )
2 rows are created in total table ( only 1 should be created )
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

any ideas :( cant figure this out
tsg
Forum Contributor
Posts: 142
Joined: Sun Jan 12, 2003 9:22 pm
Location: SE, Alabama
Contact:

Post by tsg »

I remember one time I had a problem with a stats script I wrote duplicating. The problem was a misplaced table tag, or not closing the table or something to do with the HTML. Make sure that your tables are closed and HTML on the page correct.
Post Reply