MySQL stops running queries after if statement

Question

I've been stuck on this for a few hours now ...

Here's my code:

$SQLQuery1 = $db_info->prepare("SELECT COUNT(ID) FROM menusize WHERE typesize=:typesize");
        $SQLQuery1->bindValue(':typesize',$_POST['typesize'],PDO::PARAM_STR);
        $SQLQuery1->execute();
        if($SQLQuery1->fetchColumn() > 0) {
            $SQLQuery2 = $db_info->prepare("INSERT INTO menucatagorysize (menucatagory_ID,menusize_ID) VALUES (:catagoryid,(SELECT ID FROM menusize WHERE typesize=:typesize))");
            $SQLQuery2->bindValue(':typesize',$_POST['typesize'],PDO::PARAM_STR);
            $SQLQuery2->bindValue(':catagoryid',$_POST['catagoryid'],PDO::PARAM_STR);
            $SQLQuery2->execute();
        } else {
            $SQLQuery2 = $db_info->prepare("INSERT INTO menusize (typesize) VALUES (:typesize);
                                            SET @menusizeid=LAST_INSERT_ID();
                                            INSERT INTO menucatagorysize (menusize_ID,menucatagory_ID) VALUES (@menusizeid,:catagoryid)");
            $SQLQuery2->bindValue(':typesize',$_POST['typesize'],PDO::PARAM_STR);
            $SQLQuery2->bindValue(':catagoryid',$_POST['catagoryid'],PDO::PARAM_STR);
            $SQLQuery2->execute();
        }
        $SQLQuery3 = $db_info->prepare("SELECT DISTINCT(menuitem_ID) FROM menuprice WHERE menucatagory_ID=:catagoryid");
        $SQLQuery3->bindValue(':catagoryid',$_POST['catagoryid'],PDO::PARAM_STR);
        $SQLQuery3->execute();
        $rows = $SQLQuery3->fetchAll(PDO::FETCH_ASSOC);

So, it will run through the if statement fine, running $SQLQuery1 and $SQLQuery2 (Which ever one is required) without any problems, errors or warnings. But, if it runs the else { part of the code, it will not run $SQLQuery3. Any thoughts?

Thanks :D

EDIT: Got it to work by doing $SQLQuery2=NULL in the else statement ... Sucks that I still cant figure out why it wouldnt work the original way.

Prepare takes one query, and one query alone. So, make those 3 statements 3 seperate calls. — Wrikken, Jan 15 '14 at 23:28
@Wrikken: Not *entirely* true - see http://stackoverflow.com/a/6461110. — eggyal, Jan 15 '14 at 23:31
@eggyal: Ah, yes, only _real_ prepares, and depending on driver as well. You've certainly got a point, but as it would be a bit of bad practise and confusing for a beginner, let's not delve into that here ;) — Wrikken, Jan 15 '14 at 23:40
Yeah ... Had a look around and still not found anything ... I've not had any problems before using multiple prepared statements in this way ... Just never before used it in an IF statement this way ... I'm just all confused now :( — Just Lucky Really, Jan 15 '14 at 23:42

eggyal · Answer 1 · 2014-01-16T00:19:45.267

It appears that you're trying to enforce a uniqueness constraint over the typesize column of your menusize table from within your application code. However, the database can do this for you—which will make your subsequent operations much simpler:

ALTER TABLE menusize ADD UNIQUE (typesize)

Now, one can simply attempt to insert the posted value into the table and the database will prevent duplicates arising. Furthermore, as documented under INSERT ... ON DUPLICATE KEY UPDATE Syntax:

If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. Exception: For updates, LAST_INSERT_ID() is not meaningful prior to MySQL 5.1.12. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:
INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

Therefore, you can do:

$db_info->prepare('
  INSERT INTO menusize (typesize) VALUES (:typesize)
    ON DUPLICATE KEY UPDATE typesize=LAST_INSERT_ID(typesize)
')->execute(array(
  ':typesize' => $_POST['typesize']
));

$db_info->prepare('
  INSERT INTO menucatagorysize
    (menusize_ID, menucatagory_ID)
  VALUES
    (LAST_INSERT_ID(), :catagoryid)
')->execute(array(
  ':catagoryid' => $_POST['catagoryid']
));

$stmt = $db_info->prepare('
  SELECT DISTINCT menuitem_ID
  FROM   menuprice
  WHERE  menucatagory_ID = :catagoryid
');
$stmt->execute(array(
  ':catagoryid' => $_POST['catagoryid']
));
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  // etc.
}

(As an aside, the English word is spelled cat*e*gory, not cat*a*gory.)

MySQL stops running queries after if statement

1 Answers1