Push results from MYSQL Query into an Array

Question

I'm having trouble with the function below. Basically what it is supposed to do is check IF each item from the array ($idDiscontinuedArray) it's discontinued value from its respective table (either 1 or 0).

Now I’m not sure to push these answers in an array to make the next part more simpler. At the moment the its spits $rows out individually.

The result is: Array ( [discontinued] => 1, ) Array ( [discontinued] => 1 ) Array ( [discontinued] => 0 ) Array ( [discontinued] => 0 ) Array ( [discontinued] => 1 ).... where I would rather have Array [1] => 1 [2] => 1 [3] => 0 [4] => 0 [5] => 1....

The next part of the script is to check and see whether that ALL $rows = 1 which means end of script. If this is not the case this will run the function changeDiscontinued($dbh, $id, $idDiscontinuedArray).

function checkDiscontinued($dbh, $idDiscontinuedArray) {
try {
    foreach ($idDiscontinuedArray as $id) {
        $stmt = $dbh->query("SELECT discontinued FROM `$id` ORDER BY `date` DESC LIMIT 1");
        $rows = $stmt->fetch(PDO::FETCH_ASSOC);
        print_r($rows);
        }
        if $rows['discontinued'] == TRUE) { 
            //echo $id . "Action if true";
        } else {
            changeDiscontinued($dbh, $id, $idDiscontinuedArray);
            echo $id . "Items already discontinued!";
            }       
    }
    catch (PDOException $e) {
    echo $e->getMessage();
    }
}

your $id variable in your else statement will always be the last value in your $idDiscontinuedArray, is that your intent? what is changeDiscontinued() supposed to do? — KorreyD, Jan 15 '14 at 02:50
You're using $id for the table name, is that what you're intending to do? — Nick Coad, Jan 15 '14 at 03:37
Can someone say SQL Injection Vulnerabilities big time? You are already using PDO, prepare that statement... — Justin E, Jan 15 '14 at 03:50
see answer below. See Also: http://en.wikipedia.org/wiki/SQL_injection#Incorrect_type_handling — Justin E, Jan 15 '14 at 03:55
[can-php-pdo-statements-accept-the-table-name-as-parameter][1] The answer is no. [1]: http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter — Hemesh, Jan 15 '14 at 22:31

Justin E · Accepted Answer · 2014-01-16T15:30:01.560

1

I Did not test this code, but it goes over prepared statements, and how to push items into an array. If you want more help, we can go into chat.

$discont = array();

function checkDiscontinued($dbh, $idDiscontinuedArray) {
try {
    foreach ($idDiscontinuedArray as $id) {
        $sql = $dbh->prepare("SELECT discontinued FROM $id ORDER BY `date` DESC LIMIT 1");
        $stmt = $sql->execute(array($id));
        $rows = $stmt->fetch(PDO::FETCH_ASSOC);
        print_r($rows);
        }
        if $rows['discontinued'] == TRUE) { 
            //echo $id . "Action if true";
        } else {
            changeDiscontinued($dbh, $id, $idDiscontinuedArray);
            echo $id . "Items already discontinued!";
            array_push($doscont, $id);
            }       
    }
    catch (PDOException $e) {
        echo $e->getMessage();
    }
}

edited Jan 16 '14 at 15:30

answered Jan 15 '14 at 03:55

Justin E

1,252
16
30

If this worked for you, don't forget to mark it is the answer. – Justin E Jan 15 '14 at 04:39
Thanks for your reply - i get the error - SQLSTATE[42000]: 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 ''19474' ORDER BY `date` DESC LIMIT 1' at line 1 – Hemesh Jan 15 '14 at 09:50
Try putting back-ticks around the ?, i.e. `?` in the sql statement. – Justin E Jan 15 '14 at 21:02
Got it to work just because $id was not an array, it is a single number and the foreach loop handles the different id's. Now answer retrieved is false when the mysql table is in fact true. – Hemesh Jan 15 '14 at 21:52

Push results from MYSQL Query into an Array

1 Answers1