0

I have a query in PDO which contains multiple actions. I first looked at this question to know wether or not multiple queries are even possible.

My code looks like this:

$stmt = $db->prepare(
    "UPDATE 
        tbl_user_dashboards
     SET 
        is_active = 0
     WHERE 
        id_user_key_fk = 1;
     INSERT INTO 
        tbl_user_dashboards(id_user_key_fk, dashboard_name, dashboard_description, is_active)
     VALUES 
        (1, 'bla', 'blabla', 1);
     SELECT
        id_dashboard AS did,
        dashboard_name AS dname,
        dashboard_description AS ddesc,
        is_active
     FROM
        tbl_user_dashboards
     WHERE
        id_user_key_fk = 1
     ORDER BY
        id_dashboard ASC;"
);
$stmt->execute();

$data = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $data[] = $row;
}

My problem is, is that the UPDATE and INSERT work fine, but the SELECT doesn't work (my JSON object stays empty and I don't get any errors, just [] as my output).

When I isolate the UPDATE and the INSERT and run the SELECT as a seperate statement after the UPDATE and INSERT, then it does work but that's not my preferable solution.

Any thoughts on this?

dexter
  • 155
  • 2
  • 15
  • 4
    Is there any reason for doing it this way as opposed to using multiple prepare/executes? – Nigel Ren Mar 15 '19 at 09:42
  • why do you wanna do this? – Masivuye Cokile Mar 15 '19 at 09:43
  • @NigelRen: I found this better readable with one statement, but that's obvious personal preference.. – dexter Mar 15 '19 at 09:45
  • 1
    It's not always about what you prefer. I would think it is highly uncommon to do it this way and so if anyone else comes to your code, they will also wonder why did you do it this way. Separating SQL into individual statements also helps with tracking down errors, at the moment you have a mass of statements where something may go wrong. – Nigel Ren Mar 15 '19 at 09:47
  • @NigelRen: thanks for pointing this out. I will then seperate the code into 3 seperate statements. Better safe than sorry :-) – dexter Mar 15 '19 at 09:49
  • 2
    selecting data you have just inserted seems a little bit of waste too. – danblack Mar 15 '19 at 09:57

0 Answers0