How do I modify this MySQL INSERT Query for multiple rows?

Question

I am attempting to insert records for Artists, Songs and Record Labels, whilst checking that the data does not already exist in the Database.

The following code is from Mike Fenwick.

    <?php
    $query = "SELECT id FROM table WHERE unique1=value1 AND unique2=value2…";
    $select_result = mysql_query($query);
    if (!mysql_num_rows($select_result)) {
            $query = "INSERT INTO table SET unique1=value1 AND unique2=value2…";
            $insert_result = mysql_query($query);
            $id = mysql_insert_id();
    }
    else {
            $row = mysql_fetch_assoc($select_result);
            $id = $row['id'];
    }
    return $id;
    ?>

I need to modify this to check if three unique values exist already (from 3 separate tables), and if not, insert them. Here is my attempt:

<?php   

            $query = "SELECT id FROM artistsTable WHERE artistName='Beyonce'";
            $select_result = mysql_query($query);
            if (!mysql_num_rows($select_result)) {
                    $query = "INSERT INTO table SET artistName='Beyonce' AND artistImage='beyonce.jpg'";
                    $insert_result = mysql_query($query);
                    $artistID = mysql_insert_id();
            }
            else {
                    $row = mysql_fetch_assoc($select_result);
                    $artistID = $row['artistID'];
            }
            return $artistID;


            $query = "SELECT id FROM recordLabelTable WHERE labelName='Columbia Records'";
                $select_result = mysql_query($query);
                if (!mysql_num_rows($select_result)) {
                    $query = "INSERT INTO table SET labelName='Columbia Records'";
                    $insert_result = mysql_query($query);
                    $labelID = mysql_insert_id();
            }
            else {
                    $row = mysql_fetch_assoc($select_result);
                    $labelID = $row['labelID'];
            }
            return $labelID;



            $query = "SELECT id FROM songTable WHERE trackTitle='Crazy in Love' AND artistID=".$artistID." AND labelID=".$labelID."";
                $select_result = mysql_query($query);
                if (!mysql_num_rows($select_result)) {
                    $query = "INSERT INTO songTable SET trackTitle='Crazy in Love' AND artistID=".$artistID." AND labelID=".$labelID."";
                    $insert_result = mysql_query($query);
                    $songID = mysql_insert_id();
            }
            else {
                    $row = mysql_fetch_assoc($select_result);
                    $songID = $row['songID'];
            }
            return $songID;


    ?>

I'm assuming that there must be a more efficient way to do this. Any ideas would be much appreciated.

possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — t1gor, Jun 09 '14 at 08:49
I would suggest using INSERT.....ON DUPLICATE KEY... syntax (which can be tweaked to return the id of the row that already exists, rather than actually doing an update). This would reduce this to 3 insert statements without the need for the selects — Kickstart, Jun 09 '14 at 10:14
Ignore below - I think I understand this now - I will comment again with the code: Thanks @Kickstart. Appreciate the input. I'm not sure how ON DUPLICATE KEY works... If I change to the following code, would I still need 3 insert statements? `$query = "INSERT INTO table SET unique1=value1 AND unique2=value2… ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)"; $insert_result = mysql_query($query); $id = mysql_insert_id(); return $id;` — drandom, Jun 09 '14 at 10:31
You would still need 3 insert statements, but better than 3 selects and 3 inserts. You could possibly use INSERT IGNORE instead, and just do the inserts based on SELECTs. — Kickstart, Jun 09 '14 at 10:36

score 2 · Accepted Answer · answered Jun 09 '14 at 10:46

Using basic inset / ignore syntax you could do something like this.

A couple of inserts to put in the artist details and label details, then an INSERT based on a SELECT:-

<?php   
    $query = "INSERT IGNORE INTO artistTable (artistName, artistImag) VALUES('Beyonce', 'beyonce.jpg')";
    $insert_result = mysql_query($query);

    $query = "INSERT IGNORE INTO labelTable (labelName) VALUES('Columbia Records')";
    $insert_result = mysql_query($query);

    $query = "INSERT IGNORE INTO songTable (trackTitle, artistID, labelID)
            SELECT 'Crazy in Love', a.artistID, b.labelID
            FROM artistTable a
            INNER JOIN labelTable b
            ON a.artistName = 'Beyonce'
            AND a.artistImag = 'beyonce.jpg'
            AND b.labelName = 'Columbia Records'";
    $insert_result = mysql_query($query);
    $songID = mysql_insert_id();
    return $songID;
?>

As @LoganWayne says, you probably should use MySQLi .

Logan Wayne · Answer 2 · 2014-06-09T09:17:15.527

<?php   

/* ESTABLISH CONNECTION */

$con=mysqli_connect("Host","Username","Password","Database"); /* REPLACE NECESSARY DATA */

if(mysqli_connect_errno()){

echo "Error".mysqli_connect_error();
}


/*              FOR artistsTable TABLE              */

$query = "SELECT id FROM artistsTable WHERE artistName='Beyonce'";
$select_result = mysqli_query($con,$query); /* EXECUTE QUERY */
if (mysqli_num_rows($select_result)==0) { /* IF QUERY'S RESULT IS 0 */

   $query = "INSERT INTO table SET artistName='Beyonce' AND artistImage='beyonce.jpg'";
   $insert_result = mysqli_query($con,$query); /* EXECUTE INSERT QUERY */

} /* END OF IF */

else {

   while($row = mysqli_fetch_array($select_result)){
   $artistID = mysqli_real_escape_string($con,$row['artistID']); /* ESCAPE STRING */
   } /* END OF WHILE LOOP */

} /* END OF ELSE */


/*              FOR recordLabelTable TABLE              */

$query = "SELECT id FROM recordLabelTable WHERE labelName='Columbia Records'";
$select_result = mysqli_query($con,$query); /* EXECUTE SELECT QUERY */

if (mysqli_num_rows($select_result)==0) { /* IF QUERY'S RESULT IS 0 */
   $query = "INSERT INTO table SET labelName='Columbia Records'";
   $insert_result = mysqli_query($con,$query); /* EXECUTE INSERT QUERY */
}

else {
   while($row = mysqli_fetch_array($select_result)){
   $labelID = mysqli_real_escape_string($con,$row['labelID']); /* ESCAPE STRING */
   }
}


/*              FOR songtable TABLE              */

$query = "SELECT id FROM songTable WHERE trackTitle='Crazy in Love' AND artistID='$artistID' AND labelID='$labelID'";

$select_result = mysqli_query($con,$query); /* EXECUTE SELECT QUERY */
if (mysqli_num_rows($select_result)==0) {

   $query = "INSERT INTO songTable SET trackTitle='Crazy in Love' AND artistID='$artistID' AND labelID='$labelID'";
   $insert_result = mysqli_query($con,$query); /* EXECUTE QUERY */

} /* END OF IF */

else {
   while($row = mysqli_fetch_array($select_result)){
   $songID = mysqli_real_escape_string($con,$row['songID']);
   } /* END OF WHILE LOOP */
}

?>

SUMMARY:

Used at least MySQLi instead of deprecated MySQL.
Replaced fetch_assoc() function with fetch_array() function.
Used mysqli_real_escape_string() function to prevent some of SQL injections.
Cleaned your code. You have misplaced apostrophes(') and double quotes(") hanging around.

Thanks so much for tidying this up! Really appreciate it. I must start using MySQLi. Thanks for getting me started. In your opinion, is running 3 select/insert statements the best way to do this? I assumed there would be a single MySQL statement that would run all 3 somehow. — drandom, Jun 09 '14 at 10:29
@drandom - you're inserting data into three different tables. As far as I know, you can only insert multiple data in a single query IF you're inserting it into the same table. — Logan Wayne, Jun 09 '14 at 11:15

How do I modify this MySQL INSERT Query for multiple rows?

2 Answers2

SUMMARY: