PHP - Turning an array of mysql results into a local variable array?

Question

I have the following code:

$catsQuery = "
        SELECT id, category
        FROM categories
        ORDER BY category
";
$catsSql = mysqli_query($link, $catsQuery);

$cats = array();

mysqli_data_seek($catsSql, 0);
while($rowCats = mysqli_fetch_assoc($catsSql))
{
    $cats[$rowCats['id']]['catName'] = $rowCats['category'];
}

foreach ($cats as $catKey => $cat['$catName'])
{
    $pageContent .= "<p>$catKey | $catName</p>
    ";
}

The two columns id and category contain data like this:

id | category
1 | shoes
2 | shirts
3 | hats

What I would like to do is turn the all of the rows in the table categories into a local variable called cats

In the while loop a local array is generated as each rows array key becomes the category id and then a category name is assigned to the key. var_dump($cats) shows that each row is added to the variable as i have described.

But in the foreach, for some reason, each id it returned as a new row, but the name of the category displays as the last possible category name, accross all of the keys.

Why is it that a dump on the variable shows one set of data but a printed loop in this manner of its contents shows this strange hiccup?

Like this:

1 | hats
2 | hats
3 | hats

If anyone could shed some light on what i am doing wrong to cause this, it would be greatly appreciated.

Thank You!!

EDIT:

The functional code created as a result of the answer below:

//categories to local array
$catsQuery = "
    SELECT id, category, catDirPath
    FROM categories
    ORDER BY category
";
$catsSql = mysqli_query($link, $catsQuery);
$cats = array();
mysqli_data_seek($catsSql, 0);
while($rowCats = mysqli_fetch_assoc($catsSql))
{
    $cats[$rowCats['id']]['catName'] = $rowCats['category'];
    $cats[$rowCats['id']]['catPath'] = $rowCats['catDirPath'];
}
// subCats to local array
$subsQuery = "
    SELECT id, subCat, category_id, subDirPath
    FROM subCats, sub_categories
    WHERE subCats.id = sub_categories.sub_id
    ORDER BY category_id
";
$subsSql = mysqli_query($link, $subsQuery);
$subs = array();
mysqli_data_seek($subsSql, 0);
while($rowSubs = mysqli_fetch_assoc($subsSql))
{
    $subs[$rowSubs['id']]['subName'] = $rowSubs['subCat'];
    $subs[$rowSubs['id']]['catId'] = $rowSubs['category_id'];
    $subs[$rowSubs['id']]['subPath'] = $rowSubs['subDirPath'];
}
// loop through categories and if subs exist, add to resultset and display  
foreach ($cats as $catId => $cat)
{
    $pageContent .= "
    <div style='margin-bottom: 10px; border black solid 1px;'>
    <ul>
    <li style='list-style-type: none; margin-bottom: 5px;'><a href='$docPath/category/{$cat['catPath']}/'>{$cat['catName']}</a></li>
    ";
    foreach ($subs as $subId => $sub)
    {
        if($sub['catId'] == $catId)
        {
        $pageContent .= "
        <li style='list-style-type: circle; padding-left: 15px; font-size: 70%;'><a href='$docPath/category/{$cat['catPath']}/{$sub['subPath']}/'>{$sub['subName']}</a></li>
        ";
        }
    }
    $pageContent .= "
    </ul>
    </div>
    ";
}

dan-lee · Accepted Answer · 2012-04-22 13:44:22Z

2

As you have built your array, you'd have to do your foreach loop like this:

foreach ($cats as $catId => $cat)
{
    $pageContent .= "<p>$catKey | ".$cat['catName']."</p>";
}

But why not just $cats[$rowCats['id'] = $rowCats['category']; in the first place? Then this would be enough:

foreach ($cats as $catId => $catName)
{
    $pageContent .= "<p>$catKey | $catName</p>";
}

answered Apr 22, 2012 at 13:44

dan-lee

14.5k6 gold badges55 silver badges81 bronze badges

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

3 Comments

Craig van Tonder Over a year ago

thanks for your input, out of interest sake, php allows you to do this: $pageContent .= "<p>$catKey | {$cat['catName']}</p>";

dan-lee Over a year ago

Yeah it's possible, but I dont like that, it becomes so unreadable and has too much "black magic" in it for me :) I keep my code clean and predictable

Craig van Tonder Over a year ago

Please see the code added to my op as a result of your answer :) The skeleton is there and i can now work with that! Thank you so much!!

liquorvicar · Accepted Answer · 2012-04-22 13:44:50Z

2

Your code looks ok. Have you tried var_dump on your $cats array after the while loop? I suspect that this line is causing you the problems

foreach ($cats as $catKey => $cat['$catName'])

You should probably change it ot

foreach ($cats as $catKey => $catDetails)
{
    $catName = $catDetails['catName'];
    $pageContent .= "<p>$catKey | $catName</p>";
}

answered Apr 22, 2012 at 13:44

liquorvicar

6,1462 gold badges19 silver badges21 bronze badges

Collectives™ on Stack Overflow

PHP - Turning an array of mysql results into a local variable array?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related