Convert SQL results into PHP array

Question

I'm fairly new to PHP and I've been looking around and can't seem to find the specific answer I'm looking for.

I want to make a SQL query, such as this:

$result = mysqli_query($connection, $command)
if (!$result) { die("Query Failed."); }

// Create my array here ... I'm thinking of maybe having to
// make a class that can hold everything I need, but I dunno    

while($row = mysqli_fetch_array($result))
{
    // Put the row into an array or class here...
}

mysqli_close($connection);

// return my array or class

Basically I want to take the entire contents of the result and create an array that I can access in a similar fashion as the row. For example, if I have a field called 'uid' I want to be able to get that via myData['uid']. I guess since there could be several rows, maybe something more like myData[0]['uid'], myData[1]['uid'], etc.

Any help would be appreciated.

what is you db structure ... ? you can do like 4myData['id'] = $row['id']; — Mayank K. Swami
– Mayank K. Swami, Commented Jun 12, 2013 at 1:28
But what if I don't know how many rows or fields will be returned? I also am attempting to make a global SQL class that can retrieve data for me, so I won't even know the names of the fields so I can't assign like that. My DB structure right now is just uid:int firstName:varchar(64) lastName:varchar(64) emailAddress:varchar(64) password:varchar(64) — Jonathan Plumb
– Jonathan Plumb, Commented Jun 12, 2013 at 1:44
This is what I'm attempting to do: call something like SQL::Get("SELECT uid FROM users WHERE email='".$emailAddress."'"); and have an array where I could just do $result[0]['uid'], or SQL::Get("SELECT firstName,lastName FROM users WHERE email='".$emailAddress."'"); and have an array where I could call $result[3]['firstName'] — Jonathan Plumb
– Jonathan Plumb, Commented Jun 12, 2013 at 1:48

karthikr · Accepted Answer · 2013-06-12 01:43:14Z

24

You can do:

$rows = [];
while($row = mysqli_fetch_array($result))
{
    $rows[] = $row;
}

edited Jun 12, 2013 at 1:43

answered Jun 12, 2013 at 1:29

karthikr

100k26 gold badges208 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathan Plumb Over a year ago

That doesn't work. I just attempted that and then did an echo($rows[0]['uid']) and got nothing...

Jonathan Plumb Over a year ago

Got it to work. I was doing $array[] = $row, but I actually needed to just do $array = $row.

J261 Over a year ago

with me not work with $rows = []; need to change to $rows = array();

BlitZ · Accepted Answer · 2013-06-12 01:59:05Z

6

You might try to use mysqli_result::fetch_all() for arrays:

$result = mysqli_query($connection, $command)

if (!$result) { die("Query Failed."); }

$array = $result->fetch_all();

$result->free();

mysqli_close($connection);

NOTE: This works with MySQLND only.

For class, you might try to use something like this:

$result = mysqli_query($connection, $command)

if (!$result) { die("Query Failed."); }

while($model = $result->fetch_assoc()){
    // Assuming ModelClass is declared
    // And have method push() to append rows.
    ModelClass::push($model);
}

$result->free();

mysqli_close($connection);

OR this:

// Base Model - abstract model class.
class ModelClass extends BaseModel {

    // constructor
    public function __construct(mysqli &$dbms){
        // $this->dbms is MySQLi connection instance.
        $this->dbms   = &$dbms;

        // $this->models is buffer for resultset.
        $this->models = array();
    }

    // destructor
    public function __destruct(){
        unset($this->dbms, $this->models);
    }

    public function read(){            
        $result = $this->dbms->query($command);

        if($this->dbms->errno){
            throw new Exception($this->dbms->error, $this->dbms->errno);
        }

        $this->models = $result->fetch_all();

        $result->free();
    }
}

edited Jun 12, 2013 at 1:59

answered Jun 12, 2013 at 1:32

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

4 Comments

BlitZ Over a year ago

@NullPoiиteя unfortunately, it not fits with Linux. I burned last time, when I was using it.

Mayank K. Swami Over a year ago

would you give documentation where they are saying this will only work with windows os only because afaik it should work with linux also ... :)

BlitZ Over a year ago

@NullPoiиteя yes... Not knew it is possible to install MySQLND on Linux.

Mayank K. Swami Over a year ago

mysqli has limitation and that sucks .... that is why most of us suggest to use pdo mysql

robertjoep · Accepted Answer · 2013-06-12 02:10:03Z

1

//object oriented style mysqli
//connect to your db
$mysqli = new mysqli("host", "user", "password", "dbname");
$result = $mysqli->query("SELECT * FROM `table`");

//use mysqli->affected_rows
for ($x = 1; $x <= $mysqli->affected_rows; $x++) {
    $rows[] = $result->fetch_assoc();
}

//this will return a nested array
echo "<pre>";
print_r($rows);
echo "</pre>";

edit this and put it on a class and just call it anytime you're going to make a query with your database.

answered Jun 12, 2013 at 2:10

robertjoep

495 bronze badges

Comments

user8588978 · Accepted Answer · 2020-10-28 09:12:31Z

1

fetch_array: https://www.php.net/manual/en/mysqli-result.fetch-array.php

$result = $mysqli_connection->query($sql);
$row = $result->fetch_array(MYSQLI_ASSOC);
            print_r($row);

answered Oct 28, 2020 at 9:12

user8588978

Collectives™ on Stack Overflow

Convert SQL results into PHP array

4 Answers 4

3 Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related