General question regarding INSERT INTO - SELECT

PenguinsGM · #1 (**permalink**) 06-05-08, 08:42

Hey everyone,

I'm new on these boards, and fairly new to MySQL. I'm just generally curious if I'm attacking this problem the right way. I'm extracting information from an XML file and putting it into a MySQL database. I would like the areID to be fetched from another table (called Arenas) in the same database "on-the-fly" so to speak. Is there a way to do this? Is there possibly another/better way to do it?

Code:

// Populate Teams
$sql = "INSERT INTO Teams (teaID,
			    teaNAME,
			    teaABBREVIATION,
			    teaMORALE,
			    teaCITY,
			    teaARENA,
			    teaDIVISION) VALUES";

foreach ($xml->Team as $team) {

$sql_addition .= " ('$team->Number',
		    	'$team->Name',
			'$team->Abbre',
			'$team->Morale',
			'$team->City',
			'(SELECT areID from Arenas WHERE areNAME = $team->Arena)',
			'$team->Division'
		   		), ";}

$sql .= substr($sql_addition, 0, -2);

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

echo "<br>Table Teams populated from XML.<br><br>";
echo $sql;

I would like the value of (SELECT areID from Arenas WHERE areNAME = $team->Arena) to be inserted into the table. Is it doable?

Thanks in advance,
Jens
Sweden

r937 · #2 (**permalink**) 06-05-08, 09:19

i know mysql pretty good, but not perl

have you tested that code? what happened?

PenguinsGM · #3 (**permalink**) 06-05-08, 10:07

Heya,

It's actually PHP, but this is what the output looks like (the $sql variable anyway):

INSERT INTO Teams (teaID, teaNAME, teaABBREVIATION, teaMORALE, teaCITY, teaARENA, teaDIVISION) VALUES ('1', 'Ducks', 'ANA', '50', 'Anaheim', '(SELECT areID from Arenas WHERE areNAME = Honda Center)', 'Pacific' ), ('2', 'Thrashers', 'ATL', '50', 'Atlanta', '(SELECT areID from Arenas WHERE areNAME = Phillips Arena)', 'Southeast' ), ('3', 'Bruins', 'BOS', '50', 'Boston', '(SELECT areID from Arenas WHERE areNAME = TD Banknorth Garden)', 'Northeast' ), ('4', 'Sabres', 'BUF', '50', 'Buffalo', '(SELECT areID from Arenas WHERE areNAME = HSBC Arena)', 'Northeast' ), ('5', 'Flames', 'CGY', '50', 'Calgary', '(SELECT areID from Arenas WHERE areNAME = Pengrowth Saddledome)', 'Northwest' ), ('6', 'Hurricanes', 'CAR', '50', 'Carolina', '(SELECT areID from Arenas WHERE areNAME = RBC Center)', 'Southeast' ), ('7', 'Blackhawks', 'CHI', '50', 'Chicago', '(SELECT areID from Arenas WHERE areNAME = United Center)', 'Central' ), ('8', 'Avalanche', 'COL', '50', 'Colorado', '(SELECT areID from Arenas WHERE areNAME = Pepsi Center)', 'Northwest' ), ('9', 'Blue Jackets', 'CBJ', '50', 'Columbus', '(SELECT areID from Arenas WHERE areNAME = Nationwide Arena)', 'Central' ), ('10', 'Stars', 'DAL', '50', 'Dallas', '(SELECT areID from Arenas WHERE areNAME = American Airlines Center)', 'Pacific' ), ('11', 'Red Wings', 'DET', '50', 'Detroit', '(SELECT areID from Arenas WHERE areNAME = Joe Louis Arena)', 'Central' ), ('12', 'Oilers', 'EDM', '50', 'Edmonton', '(SELECT areID from Arenas WHERE areNAME = Rexall Place)', 'Northwest' ), ('13', 'Panthers', 'FLA', '50', 'Florida', '(SELECT areID from Arenas WHERE areNAME = BankAtlantic Center)', 'Southeast' ), ('14', 'Kings', 'LA ', '50', 'Los Angeles', '(SELECT areID from Arenas WHERE areNAME = Staples Center)', 'Pacific' ), ('15', 'Wild', 'MIN', '50', 'Minnesota', '(SELECT areID from Arenas WHERE areNAME = Xcel Energy Center)', 'Northwest' ), ('16', 'Canadiens', 'MTL', '50', 'Montreal', '(SELECT areID from Arenas WHERE areNAME = Bell Center)', 'Northeast' ), ('17', 'Predators', 'NSH', '50', 'Nashville', '(SELECT areID from Arenas WHERE areNAME = Gaylord Entertainment Center)', 'Central' ), ('18', 'Devils', 'NJ ', '50', 'New Jersey', '(SELECT areID from Arenas WHERE areNAME = Prudential Center)', 'Atlantic' ), ('19', 'Islanders', 'NYI', '50', 'New York', '(SELECT areID from Arenas WHERE areNAME = NVM Coliseum)', 'Atlantic' ), ('20', 'Rangers', 'NYR', '50', 'New York', '(SELECT areID from Arenas WHERE areNAME = Madison Square Garden)', 'Atlantic' ), ('21', 'Senators', 'OTT', '50', 'Ottawa', '(SELECT areID from Arenas WHERE areNAME = Scotiabank Place)', 'Northeast' ), ('22', 'Flyers', 'PHI', '50', 'Philadelphia', '(SELECT areID from Arenas WHERE areNAME = Wachovia Center)', 'Atlantic' ), ('23', 'Coyotes', 'PHX', '50', 'Phoenix', '(SELECT areID from Arenas WHERE areNAME = Jobing.com Arena)', 'Pacific' ), ('24', 'Penguins', 'PIT', '50', 'Pittsburgh', '(SELECT areID from Arenas WHERE areNAME = Mellon Arena)', 'Atlantic' ), ('25', 'Blues', 'STL', '50', 'Saint Louis', '(SELECT areID from Arenas WHERE areNAME = Scottrade Center)', 'Central' ), ('26', 'Sharks', 'SJ ', '50', 'San Jose', '(SELECT areID from Arenas WHERE areNAME = HP Pavilion)', 'Pacific' ), ('27', 'Lightning', 'TB ', '50', 'Tampa Bay', '(SELECT areID from Arenas WHERE areNAME = St. Pete Times Forum)', 'Southeast' ), ('28', 'Maple Leafs', 'TOR', '50', 'Toronto', '(SELECT areID from Arenas WHERE areNAME = Air Canada Center)', 'Northeast' ), ('29', 'Canucks', 'VAN', '50', 'Vancouver', '(SELECT areID from Arenas WHERE areNAME = General Motors Place)', 'Northwest' ), ('30', 'Capitals', 'WSH', '50', 'Washington', '(SELECT areID from Arenas WHERE areNAME = Verizon Center)', 'Southeast' )

healdem · #4 (**permalink**) 06-05-08, 10:13

why no try it out in a sql query tool such as MySQL's Query browser (part of the MySQL GUI tools available from MySQL.COM
then having proved the SQL is valid move on to inserting it into PHP

mike_bike_kite · #5 (**permalink**) 06-05-08, 10:24

I think you just put your curly bracket in the wrong place. It's currently adding each new line of data to the end of the sql without actually running it until the end. You also seemt o be missing the end ')' in the insert. I was unsure whether the sql_addition was a string or not so I put quotes around anyway, I also moved it outside the loop as it doesn't seem to change. The test areNAME = $team->Arena seems to be missinig quotes as well.

I think you should be doing something like :

Code:

// Populate Teams

$tmp_bit = substr($sql_addition, 0, -2);

foreach ($xml->Team as $team) {

    $sql = "INSERT INTO Teams (teaID,
			    teaNAME,
			    teaABBREVIATION,
			    teaMORALE,
			    teaCITY,
			    teaARENA,
			    teaDIVISION) 
                  VALUES ('$team->Number',
		    	'$team->Name',
			'$team->Abbre',
			'$team->Morale',
			'$team->City',
			(SELECT areID from Arenas WHERE areNAME = '$team->Arena'),
			'$team->Division',                                    
                       '$tmp_bit' )";

    mysql_query($sql) or die(mysql_error());
}

echo "<br>Table Teams populated from XML.<br><br>";
echo $sql;

It's always a good idea to indent your code to make it easier to read. A good idea would be to print out the sql before running it so we can see what's actually getting passed. I can't test any of the above cause don't have MySQL or your data with me.

Mike

PenguinsGM · #6 (**permalink**) 06-05-08, 12:34

Thanks Mike. The $sql_addition is what is added at the end of the sql-string for every instance of <Team> in the XML-file. Rather than querying the database every for every <Team> in the XML-file, it's more efficient to pile it up in one huge query. I'm gonna try and run it in an SQL browser.

Thanks,
Jens

mike_bike_kite · #7 (**permalink**) 06-05-08, 12:39

Efficiency comes in many forms - if you're dealing with < 1000 rows and not doing it 100's of times a day then I'd just stick to making it efficient to understand (ie one row at a time). Saving a fraction of a second is all well and good but not if it takes a couple of hours of your day to try and debug it.