Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    4

    Unanswered: General question regarding INSERT INTO - SELECT

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i know mysql pretty good, but not perl

    have you tested that code? what happened?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Posts
    4
    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' )

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527

    Thumbs up

    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

  6. #6
    Join Date
    Jun 2008
    Posts
    4
    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

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  8. #8
    Join Date
    Jun 2008
    Posts
    4
    I will eventually import 4000 rows with 15+ columns in them on a daily basis, and it's a huge difference. Even to the extent where PHP times out.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    fair enough then

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •