Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
 
why don't you just use Auto_increment? –  jcho360 Oct 12 '12 at 12:37
 
Just UNION the two SELECT statements. –  DaveRandom Oct 12 '12 at 12:37
 
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. Oct 12 '12 at 12:41
add comment

1 Answer

up vote 3 down vote accepted

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
));
share|improve this answer
 
...or this would work as well. –  DaveRandom Oct 12 '12 at 12:40
 
Say for example MAX(FixedFareID)+1 = 5, would that use value 5 for both inserts? –  nsilva Oct 12 '12 at 12:40
 
@nsilva Yes. What else would you expect? –  DaveRandom Oct 12 '12 at 12:42
 
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) –  nsilva Oct 12 '12 at 12:45
2  
@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. –  DaveRandom Oct 12 '12 at 12:54
show 8 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.