Query categories and games

[Davie33]

Hi all am haiving abit of a problem am trying to query 6 categories with 6 games each.With the code i done is only showing one cat with 6 games.

How can i fix this ?.

If you go to games-flash.co.uk you will see at the bottom of the home page that only one cat shows.

 

php code.

    <div class="catgames">    <?php    $query = yasDB_select("SELECT * FROM categories WHERE active='yes' AND home='yes' ORDER BY id DESC LIMIT 6",false);    while ($row = $query->fetch_array(MYSQLI_ASSOC)) {    $catid = $row['id'];    $query = yasDB_select("SELECT id, title, thumbnail, description, plays, active FROM games WHERE category=".$catid." AND active='1' ORDER BY id DESC LIMIT 6",false);    echo '<ul><li class="even"><div id="catheadergms">';    if ($setting['seo'] == 'yes') {    echo '<a href="' . $setting['siteurl'] . 'category/' . $row['id'] .'/1.html">'.$row['name'].'</a>';    } else {    echo '<a href="' . $setting['siteurl'] . 'index.php?act=cat&id=' . $row['id'] .'">'.$row['name'].'</a>';    }    echo '</div>';    while ($games = $query->fetch_array(MYSQLI_ASSOC)) {    $pic_settings = array('w'=>115,'h'=>72);    $gameurl = prepgame($games['title']);    $games['description'] = stripslashes($games['description']);    if (file_exists($games['thumbnail'])) {    $thumbnail = urldecode($games['thumbnail']);    } else {    $thumbnail = $setting['siteurl'].'templates/'.$setting['theme'].'/skins/'.$setting['skin'].'/images/nopic.jpg';    }    if ($setting['seo']=='yes') {    $gurl = $setting['siteurl'] . 'game/' . $games['id'] . '/' . $gameurl . '.html';    } else {    $gurl = $setting['siteurl'] . 'index.php?act=game&id=' . $games['id'];    }    $hinttext = '<h2 class="title">'.$games['title'].'</h2>'.$games['description'];    $hinttext = str_replace(array("\r\n", "\r", "\n"), '<br/>', $hinttext);    $hinttext = str_replace(array('"',"'"),array('"','’'),$hinttext);    ?>                    <div class="inner_box4"><a href="<?php echo $gurl;?>"><img align="absmiddle" src="<?php echo resize($thumbnail, $pic_settings);?>" title="<?php echo $games['title'];?> - <?php echo $games['plays'];?> Plays" width="115" height="72" /></a>    </div>       <?php }    echo '</li></ul>';    }    $query->close();    ?>    <div class="clear"></div>        </div>

Edited May 12, 2014 by Davie33

[Barand]

Probably because you use $query for both sets of query results, the second overwriting the first

[Davie33]

So i should $result the first one instead of the first $query ?.

[Davie33]

Thanks i got it working now just to fix my css for it.

[Barand]

You should be using a single query, joining the two tables, and not running queries inside a loop.

[Davie33]

Hi how can i do that ?.Sorry my php coding is not that good.

[Jacques1]

This has nothing to do with PHP. Joins belong to the basics of SQL. If you don't know them, you should learn them now, because you'll need joins over and over again.

[Barand]

The query would be something like this

SELECT c.id as catid
    , c.name 
    , g.id as gameid
    , g.title
    , g.thumbnail
    , g.description
    , g.plays
    , g.active
FROM categories c
    INNER JOIN games g ON c.id = g.category AND g.active='1' 
WHERE c.active = 'yes' AND c.home='yes' 
ORDER BY catid DESC , gameid DESC

[Davie33]

Thanks Barand,sry was reading up on what Jacques1 said.

I think i need to recode the code again to get it to work with what you got but with a small tweak on the sql as you don't really need g. or c.added.

As i have seen this in my script which i forgot about i had something like that sql for my forum.

[Barand]

... but with a small tweak on the sql as you don't really need g. or c.added.

 

You do if you have the same column names in both tables otherwise you get an ambiguous column name error. And purely as a form of documentation I prefer to be explicit about which tables the columns are coming from

[Davie33]

Ok thanks for all the help Barand .I will try it out some other day when i get the time to recode it thanks again.

Edited May 12, 2014 by Davie33