geegeeWap geegeeWap - 2 months ago 6
SQL Question

PHP Select More than 1 result in prepared statment

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 :)

Answer

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 .

Comments