PHP Array select result from other array

Question

I have a sql table with some category, i get them in a array.. all fine but when i try to get data from another table foreach category, always return me for first category selected.

This is my code:

$gameguidecategoryes = array();
$gameguides = array();  


$dbselectgameguidecategoryes = new DB_MSSQL;
$dbselectgameguidecategoryes->Database=$newweb_db;
$dbselectgameguidecategoryes->query("Select GameGuideCatNr,GameGuideCatName_$languageid as GameGuideCatName,GameGuideCatImage from GameGuide_Category where GameGuideCatVisible = 1 order by GameGuideCatOrder asc");
for($i=0;$i < $dbselectgameguidecategoryes->num_rows();++$i) { 
    if ($dbselectgameguidecategoryes->next_record()){
        $GameGuideCatNr = $dbselectgameguidecategoryes->f('GameGuideCatNr');
        $GameGuideCatName = $dbselectgameguidecategoryes->f('GameGuideCatName');
        $GameGuideCatImage = $dbselectgameguidecategoryes->f('GameGuideCatImage');
    }
    $gameguidecategoryes_temp = array(
        'ggcname' => $GameGuideCatName,
        'ggcimg' => $GameGuideCatImage,
    );

    $gameguidecategoryes[$i] = $gameguidecategoryes_temp;

    $dbselectgameguide = new DB_MSSQL;
    $dbselectgameguide->Database=$newweb_db;
    $dbselectgameguide->query("Select GameGuideID,GameGuideName_$languageid as GameGuideName from GameGuide_Content where GameGuideCat = $GameGuideCatNr and GameGuideVisible = 1 order by GameGuideOrder asc");

    for($ii=0;$ii < $dbselectgameguide->num_rows();++$ii) {
        if ($dbselectgameguide->next_record()){
            $GameGuideID = $dbselectgameguide->f('GameGuideID');
            $GameGuideName = $dbselectgameguide->f('GameGuideName');
        }
        $gameguides_temp = array(
            'ggid' => $GameGuideID,
            'ggn' => $GameGuideName,
        );

        $gameguides[$ii] = $gameguides_temp;
    }
}

Why $gameguides return data only from first category?

Thank you

Not 100% sue on this. Few things come to mind. Step 1 stick in a few debug statements with printing out the contents of $gameguidecategoryes and $i as well as $ii. Step 2. isn't it $i++ or $i=$i+1 Step 3. I think you should bind the category_id in the query. Step 4. don't use num_rows() - or if you do stick it in a temp variable as it has to count the rows each time. Instead a something like while($result = $dbselectgameguide->next_record()) might be better. Step 5 - put the query into a variable that you can echo out — Richard Housham
– Richard Housham, Commented Jan 18, 2016 at 15:18

Marc B · Accepted Answer · 2016-01-18 15:33:11Z

1

Your second loop keeps getting trashed by the first loop. e.g. Consider what happens:

You fetch your categories, and (let's pretend) there's 4 of them.
You store some information in $gameguidecategoryes[0]
You run the second query, get some content for category #0, say, 3 records
That gets stored in $gameguides[0], [1], [2]
Your outer loop ticks again, and you start on categoryes[1]
The inner loop ticks again, you get 4 records, and now you're storing them into the SAME again: $gameguides[0], [1], [2], [3], etc...
You've now trashed the data you fetched in the first loop, and will

do so for every category you fetch.

This code is very inefficient. You should learn how to use JOINs, and fetch into a single structure, e.g.

SELECT category.id, category.name, ...., content.id, content.name
FROM categories
LEFT JOIN content ON categories.id = content.category_id
ORDER BY ...

and then something like

$data = array();
while($row = fetch row from db) {
    if (!isset($data[$row['category.id']]) {
       $data[$row['category.id']] = array(
           'name' => $row['category.name'],
           'content' => array()
       );
    }
    $data[$row['category.id']]['content'][] = array(
          ... save content data here
    );
};

answered Jan 18, 2016 at 15:33

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

1 Comment

MeTa Over a year ago

Thank you very much for your idea.

jamek · Accepted Answer · 2016-01-18 15:20:09Z

Better work on clean code

$gameguidecategoryes = $gameguides = $gameguidescategoryids = array();  

$dbselectgameguidecategoryes = new DB_MSSQL;
$dbselectgameguidecategoryes->Database=$newweb_db;
$dbselectgameguidecategoryes->query("Select GameGuideCatNr,GameGuideCatName_$languageid as GameGuideCatName,GameGuideCatImage from GameGuide_Category where GameGuideCatVisible = 1 order by GameGuideCatOrder asc");

while ($dbselectgameguidecategoryes->next_record()) {
    $gameguidescategoryids[] = $dbselectgameguidecategoryes->f('GameGuideCatNr');

    $gameguidecategoryes[] = array(
        'ggcname' => $dbselectgameguidecategoryes->f('GameGuideCatName'),
        'ggcimg' => $dbselectgameguidecategoryes->f('GameGuideCatImage'),
    );
}

if (count($gameguidescategoryids)) {
    $dbselectgameguide = new DB_MSSQL;
    $dbselectgameguide->Database=$newweb_db;
    $dbselectgameguide->query("Select GameGuideID,GameGuideName_$languageid as GameGuideName from GameGuide_Content where GameGuideCat IN (".implode(',', $gameguidescategoryids).") and GameGuideVisible = 1 order by GameGuideOrder asc");

    while ($dbselectgameguide->next_record()){
        $gameguides[] = array(
            'ggid' => $dbselectgameguide->f('GameGuideID'),
            'ggn' => $dbselectgameguide->f('GameGuideName'),
        );
    }
}

Collectives™ on Stack Overflow

PHP Array select result from other array

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