Geek Answers Handbook

How to load MySQLi result set into two-dimensional array?

I have a problem with mysqli result set. I have a table containing a bunch of posts. Each row of the table represents one message. I have several columns, such as ID, heading, body and "public". The common column contains booleans that indicate whether the message should be displayed to everyone, or only to the person who posted it. I have a page where I want to display all public messages, and if you click on the message, you will get a page with a single message and some additional options. For this, I want to load the result of the mysqli query into a two-dimensional array. This will mean an array of messages, and each message will be an array on itself with an identifier, header, body, etc. In the form of columns.

So, I started with the following code. The $ link variable contains the mysqli connection (the witch works fine).

$result = $link->query("SELECT * FROM messages WHERE public = '1'");
$array = $result->fetch_assoc();

print_r($array);

This only results in a one-dimensional array with the last message. So I tried the following while loop:

$result = $link->query("SELECT * FROM messages WHERE public = '1'");

while($message = $result->fetch_assoc()){
 $title = $message['title'];
 $body = $message['body'];
 # etc... 
}

This works in a way: it displays all the messages, but does not put them in an array (after all, I want to perform tasks based on the identifier and the position of the message array in the containing array.) Does anyone know how to convert this query result to a good two-dimensional array? Or a completely different, great way to do this? Thanks in advance.

PS. Sorry for my English, I'm not a native speaker.

+5
source share
3 answers

You are almost there, you will need to change a few things:

$result = $link->query("SELECT * FROM messages WHERE public = '1'");
$messages = array();
while($message = $result->fetch_assoc()){
   $messages[] = $message;
}

This will result in something like this:

array(
  0 => array('message' => ..., 'subject' => ...), 
  1 => array('message' => ..., 'subject' => ...), 
  2 => array('message' => ..., 'subject' => ...), 
);

, :

$messages = array();
while($message = $result->fetch_assoc()){
   $messages[ $message["id"] ] = $message;
}

:

array(
  123 => array('message' => ..., 'subject' => ...), 
  456 => array('message' => ..., 'subject' => ...), 
  789 => array('message' => ..., 'subject' => ...), 
);

PHP 5.3 , , , :

$messages = $result->fetch_all(MYSQLI_ASSOC);
+15

, -, :

$result = $link->query("SELECT * FROM messages WHERE public = '1'");

$messages = array();
while($singleMessage = $result->fetch_assoc()){
  $messages[$singleMessage]['title'] = $singleMessage['title'];
  $messages[$singleMessage]['body'] = $singleMessage['body'];
}

2- , .

0

What should your last array look like?

Try the following:

$result = $link->query("SELECT title, body FROM messages WHERE public = '1'");
$array = array();
while ($array[] = mysql_fetch_assoc($result)) {}
-1
source

More articles:


All Articles