PHP Select More than 1 result in prepared statment

Question

Ok, so let's assume i have a database like this

//table name is: exampleTable
 _________________________
| ID | NAME | EXAMPLEDATA |
|----|------|-------------|
| 0  | TOD  | a123        |
| 1  | JEF  | 142b        |
| 2  | MAX  | c412        |
| 3  | TOD  | 124d        |
| 4  | TOD  | e634        |
| 5  | MAX  | 233f        |
| 6  | MAX  | g444        |
|____|______|_____________|

How would i get all the values of EXAMPLEDATE with a query like this ( using prepared statements )

SELECT EXAMPLEDATA FROM exampleTable WHERE NAME = 'TOD'

and store results into an array so i can access it like this :

$todArray[0] // equals a123
$todArray[1] // equals 124d
$todArray[2] // equals e634

The only way i know how to use prepared statements to get results is to use bind_result but that doesn't take the result(s) and put it in an array like what i showed above.

So here is what I've tried but it failed to work

$TOD = "TOD";
$dbCON = new mysqli(blah, blah, blah, blah);
$getData = $dbCON->prepare("SELECT `EXAMPLEDATA` FROM `exampleTable` WHERE `NAME`=?");
$getData->bind_param("s", $NAMEVAR);
$NAMEVAR = $TOD;
$getData->execute();
$getData->bind_result($todArray);
$getData->fetch();
$getData->close();

Any help would be appreciated thanks :)

score 0 · Accepted Answer · edited May 23 '17 at 11:48

You can do this :

First and i prefer that you change your db connect from mysqli to PDO .

Now you can can type the query like this with PDO :

<?php 

            $servername = "your server name";
            $username  = "your username";
            $password   = "your password";
            $database  = "your database name";

            try {
                    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
                    // set the PDO error mode to exception
                    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                }
            catch(PDOException $e)
                {
                    echo "Connection failed: " . $e->getMessage();
                }
            // End of database connection 

            $TOD = "TOD";
            $fetchdata = $conn->prepare("SELECT EXAMPLEDATA FROM exampleTable WHHERE NAME = ?");
            $fetchdata->execute(array($TOD));

            if($fetchdata)
                {
                    while($fd = $fetchdata->fetch(PDO::FETCH_ASSOC))
                    {
                        echo $fd['EXAMPLEDATA']."\n";
                    }
                }
            else
                {
                    echo "Query did not executed";
                }

?>

Now you can store the fetched data in array by doing this :

if($fetchdata)
        {
            $ResultsArray = array();

            while($fd = $fetchdata->fetch(PDO::FETCH_ASSOC))
                {
                    $ResultsArray[] = $fd['EXAMPLEDATA'];
                }
        }

Edit : This is MYSQLI code ( Not tested )

$TOD = "TOD";
$dbCON = new mysqli(blah, blah, blah, blah);
$getData = $dbCON->prepare("SELECT `EXAMPLEDATA` FROM `exampleTable` WHERE `NAME`=?");
$NAMEVAR = $TOD;
$getData->bind_param("s", $NAMEVAR);
$getData->execute();
call_user_func_array(array($mysqli_stmt_object,"bind_result"),$AnArray);
$ResultsArray = array();

while ($mysqli_stmt_object->fetch()) {
    $results[] = $AnArray;
}

$getData->close();

?>

You can find more here :

Mysqli - Bind results to an Array

Im sorry im not good in MYSQLI , hope the code works , but i truly recommend that you start using PDO , its much easier .

Thanks but PDO doesn't work for me.. It doesn't exist for me for some reason, if i try to call it i get a php error saying that the function doesn't exist... Which is why i can only use mysqli — geegeeWap, Sep 25 '16 at 15:39

PHP Select More than 1 result in prepared statment

1 Answers1