Creating a 3D array from MYSQL results in php

Question

I am trying to populate a grid with information from a database table. I managed to achieve this using brute-force and mysql, but would like to do it in a more elegant way now that I am upgrading the code to mysqli. This is my desired output:

|--------------------|--------------------|
|col0row0 title      |col1row0 title      |
|col0row0 author     |col1row0 author     |
|col0row0 email      |col1row0 email      |
|-----------------------------------------|
|col0row1 title      |col1row1 title      |
|col0row1 author     |col1row1 author     |
|col0row1 email      |col1row1 email      |
|-----------------------------------------|
| and so on for subsequent rows

I managed to do that in the past using extremely ugly code as shown:

for($x=0;$x<$numberSides;$x++) {
    for ($y=0;$y<$numberRows;$y++) {        
        $queryString[$x][$y] = "SELECT * from comments WHERE topic_id = '1' AND col = '{$x}' AND row = '{$y}' ";
    }
}


$col0Row0_mysqlQuery = mysql_query($queryString[0][0] , $commenting_conn) or die(mysql_error());
$col0Row1_mysqlQuery = mysql_query($queryString[0][1] , $commenting_conn) or die(mysql_error());
$col0Row2_mysqlQuery = mysql_query($queryString[0][2] , $commenting_conn) or die(mysql_error());
$col1Row0_mysqlQuery = mysql_query($queryString[1][0] , $commenting_conn) or die(mysql_error());
$col1Row1_mysqlQuery = mysql_query($queryString[1][1] , $commenting_conn) or die(mysql_error());
$col1Row2_mysqlQuery = mysql_query($queryString[1][2] , $commenting_conn) or die(mysql_error());

// load those into output arrays
$col0Row0_output = mysql_fetch_assoc($col0Row0_mysqlQuery);
$col0Row1_output = mysql_fetch_assoc($col0Row1_mysqlQuery);
$col0Row2_output = mysql_fetch_assoc($col0Row2_mysqlQuery);
$col1Row0_output = mysql_fetch_assoc($col1Row0_mysqlQuery);
$col1Row1_output = mysql_fetch_assoc($col1Row1_mysqlQuery);
$col1Row2_output = mysql_fetch_assoc($col1Row2_mysqlQuery);

and then I would echo the col0row0_output['title'], col0row0_output['author'], etc for each field in the grid for my desired output. This is not the efficient way to code this, but I'm a beginner so I did what I had to make it work for a demo.

Each comment in my table already contains a column and a row field, so I would like to actually translate that directly into the grid. To explain further: each grid square could have more than one comment. so the records could look like this:

Table fields: "title" / "author" / "email" / "row" / "col" Record 0: TitleA /AuthorA /EmailA /0 /0 Record 1: TitleB /AuthorB /EmailB /1 /0 Record 2: TitleC /AuthorC /EmailC /0 /1 Record 3: TitleD /AuthorA /EmailA /1 /1 Record 4: TitleE /AuthorB /EmailB /0 /0

Hence Record 0 and Record 4 are both in grid square 0,0. I'm now trying to move to a loop and a 3D array to make it work. I would like to be able to create the 3D array such that my echo command would be something like output[0][0]['title'] for the title field for col0row0, output[1][0]['author'] for the col1row0.

I'm also moving away from mysql and replacing it with mysqli.(PDO is a bit too advanced for me, but if people think that PDO has the best solution, I guess I can learn from your responses).

For example, can I create a loop where the SQL result is stored as an array? eg:

for($x=0;$x<$numberSides;$x++) {
    for ($y=0;$y<$numberRows;$y++) {        
        $queryString[$x][$y] = "SELECT * from comments WHERE topic_id = '1' AND col = '{$x}' AND row = '{$y}' ";
        $sqlResult[$x][$y] = mysqli_query($connection, $queryString[$x][$y]);
        $resultArray[$x][$y] = mysqli_fetch_assoc($sqlResult[$x][$y]);
    } 
}

Would that work? Is there an elegant way to achieve what I am hoping to do? I appreciate any guidance on this. Thanks!

Arrsy[0] = array[''][''] is this 3d

zod
– zod

2014-05-16 19:17:06 +00:00
Commented May 16, 2014 at 19:17 — zod
– zod, Commented May 16, 2014 at 19:17

Styx · Accepted Answer · 2014-05-16 21:24:41Z

1

table:

CREATE TABLE `comments` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(128) DEFAULT NULL,
    `author` varchar(128) DEFAULT NULL,
    `email` varchar(128) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

php:

<?php
// PDO
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->query("SELECT * from comments");
$results = $statement->fetchAll(PDO::FETCH_ASSOC);

$columns = 2;

//make two dimensional array
$multiArray = Array();
$row = Array();
$cellNumber = 1;
foreach ($results as $result) {
    $row[] = $result;
    if (0 == ($cellNumber % $columns)) {
        $multiArray[] = $row;
        $row = Array();
    }
    $cellNumber++;

}
var_dump($multiArray);

But you can store your data in flat table and display it in rows and columns.

answered May 16, 2014 at 21:24

Styx

1,3231 gold badge20 silver badges31 bronze badges

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

1 Comment

GK79 Over a year ago

Thanks @Styx . Each comment in my table already contains a column and a row field, so I would like to actually translate that directly into the grid. I think your solution does not account for that, so you are laying out the comments in a table. To explain further: each grid square could have more than one comment. so I the records could look like this:

Record 0: TitleA/AuthorA/EmailA/0/0 Record 1: TitleB/AuthorB/EmailB/1/0 Record 2: TitleC/AuthorC/EmailC/0/1 Record 3: TitleD/AuthorA/EmailA/1/1 Record 4: TitleE/AuthorB/EmailB/0/0

Hence Record 0 and Record 4 are both in grid square 0,0.

GK79 · Accepted Answer · 2014-05-17 21:42:05Z

0

Figured it out:

for($x=0;$x<$numberSides;$x++) {
for ($y=0;$y<$numberRows;$y++) {        
    $queryString[$x][$y] = "SELECT * FROM comments WHERE topic_id = '{$topicid}' AND side = '{$x}' AND row = '{$y}'";
    $result[$x][$y] = mysqli_query($db_connection,$queryString[$x][$y]) or die(mysqli_error($db_connection));
    while($eachcomment = mysqli_fetch_assoc($result[$x][$y])) {
        $array[$x][$y][] = $eachcomment;
    }
}

}

FTW. :)

answered May 17, 2014 at 21:42

GK79

2511 gold badge4 silver badges18 bronze badges

Collectives™ on Stack Overflow

Creating a 3D array from MYSQL results in php

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related