Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite 
$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?

share|improve this question
You're reusing the variable $category, is this intentional? I feel this will cause a lot of errors since you're already using the exact name for the loop. – Dave Chen Jun 10 at 1:01
It is intentional as the array name relates to the category field in the database (although that's not to say I can't change the array name if in your experience you feel it would likely lead to an error). – user2457571 Jun 10 at 1:05

1 Answer

up vote 3 down vote accepted

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];
    }
}
share|improve this answer
+1 for moving that query outside of the loop and explaining what happens when a string is treated as an array. – Dave Chen Jun 10 at 1:07
Super! Thank you, Steven. Many thanks for the code, explanation and pointers. – user2457571 Jun 10 at 1:22
It worked like a charm ... I almost pee'd. PS Waiting for 15 reputation to up-tick! – user2457571 Jun 10 at 1:44
OK, stuck again: Having stored the image filenames into the $category array, how do I then reference a particular part of the array in order to retrieve the 'image_filename' from it? I've tried echo $category['Alpha'][1] but I only seee the word 'Array()' being echoed. Could you advise please? – user2457571 Jun 10 at 23:11
try print_r($category['Alpha']); or var_dump($category['Alpha']); – Steven Moseley Jun 11 at 2:57
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.