Multiple SQL queries in a PHP string?

Discussion in 'Web Design and Development' started by MythicFrost, Aug 11, 2010.

  1. macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #1
    Hi,

    Right now when I do I query its like this:

    PHP:
    $sql "INSERT INTO someTable (rowName) VALUES ('someText')";
    Can I do this:

    PHP:
    $sql "INSERT INTO someTable (rowName) VALUES ('someText'); INSERT INTO anotherTable (rowName) VALUES ('someText')";
    mysql_query($sql$con);
     
  2. Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #2
    Best way to find out is to give it a try. Also look into SQL "transactions."
     
  3. thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
  4. macrumors member

    Joined:
    Jul 9, 2010
    Location:
    Rowland Heights, California
    #4
    In short...yes.

    But, if you're inserting multiple rows into the same table:
    PHP:
    $sql "INSERT INTO someTable (rowName) VALUES ('someText'), ('someMoreText'), ('someMoreText');";
    All together:
    PHP:
    $sql "INSERT INTO someTable (rowName) VALUES ('someText'), ('someMoreText'), ('someMoreText');
    INSERT INTO anotherTable (rowName) VALUES ('someText');
    INSERT INTO yetAnotherTable (rowName) VALUES ('someText'), ('someText');"
    ;
    mysql_query($sql,$con);
     
  5. thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
  6. thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #6
    Hi again,

    I'm basically doing:
    PHP:
    sql "INSERT INTO table1 (row1) VALUES ('SomeValue'); INSERT INTO table2 (row2) VALUES ('SomeValue');"
    if (!
    mysql_query($sql$con)){
        die(
    mysql_error());
    }  
    And, all I get is an error that says:
    Any idea whats wrong?
     
  7. macrumors regular

    Joined:
    Nov 9, 2007
    #7
    Yeah - you can't execute multiple SQL statements from PHP in the way that you're doing it (semi-colon separated), you just can't.
    Have a look into using PDO for your database transactions: http://php.net/manual/en/book.pdo.php
     
  8. macrumors regular

    Joined:
    Apr 23, 2010
    Location:
    Behind You
    #8
    Whenever you do MySQL queries, try them in phpMyAdmin if possible and split everything up in lines until you zero-in on the problem.
     
  9. thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
  10. macrumors member

    Joined:
    Jul 9, 2010
    Location:
    Rowland Heights, California
    #10
    Sorry, I was mistaken when showing you how to execute multiple queries using mysql_query, as I've been using mysqli for the past few years and have gotten used to it.
    Using mysqli's multi-query you can accomplish what you need, although you'll have to use mysqli instead of mysql.
     
  11. thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #11
    Oh ok, thanks. It's not a big deal, I've found a way to do it.
     

Share This Page