CODEWITHSUNDEEP
phpmysqlarrayspdomysqli
GK79
GK79

Reputation: 251

Creating a 3D array from MYSQL results in php

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!

Upvotes: 0

Views: 525

Answers (2)

GK79
GK79

Reputation: 251

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

Upvotes: 0

Styx
Styx

Reputation: 1323

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.

Upvotes: 1

Related Questions