Inserting multiple rows into database using MySQL

Question

I have the following code which inserts 1 row into the database:

public function fixed($fieldDay, $fieldNight) {

    $pdo = new SQL();
    $dbh = $pdo->connect(Database::$serverIP, Database::$serverPort, Database::$dbName, Database::$user, Database::$pass);

    $this->sql = "INSERT INTO tblfixedfare (SELECT NULL, MAX(FixedFareID)+1, '1', '$fieldDay' FROM tblfixedfare)";

try {
        // Query
        $stmt = $dbh->prepare($this->sql);

        $stmt->execute();

        $count = $stmt->rowCount();

        echo $count.' row(s) inserted by SQL: '.$stmt->queryString;

        $stmt->closeCursor();

    }

    catch (PDOException $pe) {
        echo 'Error: ' .$pe->getMessage(). 'SQL: '.$stmt->queryString;
        die();
    }

    // Close connection
    $dbh = null;
}

}

At the same time this is being executed, I want to insert another row into the same table:

$this->sql = "INSERT INTO tblfixedfare (SELECT NULL, MAX(FixedFareID)+1, '2', '$fieldNight' FROM tblfixedfare)";

How would I go about doing this?

EDIT

Also, for both inserts the value for MAX(FixedFareID)+1 needs to be the same value.

You're using prepared statements, but you're not binding any parameters, at the same time you're passing raw variables to the queries? Kinda pointless, isn't it? — N.B.
– N.B., Commented Oct 12, 2012 at 12:41

DaveRandom · Accepted Answer · 2012-10-12 13:01:03Z

3

Change the SQL query definition to this:

$this->sql = "
    INSERT INTO tblfixedfare 
        SELECT NULL, MAX(FixedFareID) + 1, '1', :fieldDay
        FROM tblfixedfare
        UNION
        SELECT NULL, MAX(FixedFareID) + 1, '2', :fieldNight
        FROM tblfixedfare
";

...and change the execute() call to this:

$stmt->execute(array(
    'fieldDay' => $fieldDay,
    'fieldNight' => $fieldNight
));

edited Oct 12, 2012 at 13:01

DaveRandom

88.8k11 gold badges159 silver badges174 bronze badges

answered Oct 12, 2012 at 12:38

Matei Suica

8591 gold badge7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

nsilva Over a year ago

Say for example MAX(FixedFareID)+1 = 5, would that use value 5 for both inserts?

DaveRandom Over a year ago

@nsilva Yes. What else would you expect?

nsilva Over a year ago

I've just tried this and I get the following: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT NULL, MAX(FixedFareID)+1, '1', '100' FROM tblfixedfare), ' at line 2SQL: INSERT INTO tblfixedfare VALUES (SELECT NULL, MAX(FixedFareID)+1, '1', '100' FROM tblfixedfare), (SELECT NULL, MAX(FixedFareID)+1, '2', '200' FROM tblfixedfare)

DaveRandom Over a year ago

@nsilva Oh right yes actually you can't do this, duh. Try

INSERT INTO tblfixedfare (SELECT NULL, MAX(FixedFareID)+1, '1', '$fieldDay' FROM tblfixedfare UNION SELECT NULL, MAX(FixedFareID)+1, '2', '$fieldNight' FROM tblfixedfare)

- although also note that N.B.'s comment on the question is valid, there is no point in preparing the statement if you just drop raw variables into the query string - use placeholders instead.

DaveRandom Over a year ago

@nsilva Yeh that was wrong as well. I'm not doing well here am I? Try it without the parens:

INSERT INTO tblfixedfare SELECT NULL, MAX(FixedFareID)+1, '1', '$fieldDay' FROM tblfixedfare UNION SELECT NULL, MAX(FixedFareID)+1, '2', '$fieldNight' FROM tblfixedfare

- that really should work, I've done it many time before.

|

Collectives™ on Stack Overflow

Inserting multiple rows into database using MySQL

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related