CODEWITHSUNDEEP
phpmysqlmultidimensional-array
user2457571
user2457571

Reputation: 33

PHP multidimensional array + MySQLi $row array data

$category = array(
    "Alpha",
    "Beta",
    "Gamma",
    "Delta",
    "Epsilon",
    "Zeta"
);
for ($count = 0; $count < 5; $count++) {

    $query = "SELECT * FROM random_walk WHERE category = '$category[$count]'";
    $result = $mysqli->query($query) or die($mysqli->error . __LINE__);
    $row_cnt = $result->num_rows;

    if ($result->num_rows > 0) {
        $row_counter = 0;

        while ($row = $result->fetch_assoc()) {
            $category[$count][$row_counter] = $row['image_filename'];
            $row_counter++;

            echo $category[$count][$row_counter];
        }
    }
}

I am trying to store MySQLi $row data into a PHP 2 dimensional array $category[][].

I have initialized an array named $category which contains contains the category names I wish to use. I now want to retrieve records from my database and store the contents of the record field image_file (eg. poly_interpolated.jpg) into the second dimension and loop until there are no more images files for that category in the database.

However, when I echo the array I only see a single character which is not what I was expecting to happen at all as $row['image_file'] returns a filename of multiple characters in length. I would have thought that $category[$count][$row_counter] = $row['image_filename']; would store the name of the file but it appears I'm some way off in that assumption.

Can someone please take a look at the above code and point me in the right direction?

Upvotes: 3

Views: 1899

Answers (1)

Steven Moseley
Steven Moseley

Reputation: 16325

This is what you're looking for.

Some insight - you're only seeing one character because you don't have a multidimensional array. You have a single-dimensional array of strings, and when you access an index on those strings, you get a character in the string.

You want an associative array of keys representing arrays.

I added other code to concatenate your searches into a single search with an IN clause, so it'll do something like WHERE category IN ('Alpha', 'Beta', etc.). This will be much faster.

Finally, for each record, you want to add it to the category array with the matching index. This will sort your data into the category collection.

Oh, also no need for a row counter. Just adding the row to the end of its category will index it properly in the array.

$category = array(
    "Alpha" => array(),
    "Beta" => array(),
    "Gamma" => array(),
    "Delta" => array(),
    "Epsilon" => array(),
    "Zeta" => array()
);

// Concatenate the categories for searching
$categories = array_keys($category);
for ($i = 0; $i < count($categories); $i++) {
    $categories[$i] = "'{$categories[$i]}'";
}
$categories = implode(",", $categories);

// Query on an IN clause
$query = "SELECT * FROM random_walk WHERE category IN ({$categories})";
$result = $mysqli->query($query) or die($mysqli->error . __LINE__);
$row_cnt = $result->num_rows;

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $category[$row['category']][] = $row['image_filename'];
        echo $category[$row['category']][count($category[$row['category']]) - 1];
    }
}

Upvotes: 4

Related Questions