Newbie Needs help with Displaying Data For Mysql Table Joins

[XploreR]

Hi. Just registered here , hoping someone could help a newbie out.

I run a game site have created two tables (based on genres)called 'action' & 'adventure'. They both have the same columns 'gamename' & 'gamesystem' etc.

I just knew how to sort the data in one table but now i like to know how to sort the data in alphabetical order from the data in the columns 'gamename' from the two tables for the platform playstation.

This is what i have come up with and i hope someone can correct me if i'm wrong cause i never got any errors but because i don't know how to display the info in a table with php, i don't know if it's correct.

SELECT action.game, adventure.game, FROM action, adventure WHERE action.gamesystem=adventure.gamesystem and psxadventure.gamesystem='playstation'";

I also like to know how to display the datas in tables for all the gamename in a single row in php.

I'll appreciate it if someone out there will be able to help

Thanks,

XploreR

[freakysid]

Hi XploreR.

Not quite sure what you want. Can you explain in a little more detail.

Do you want the gamename of games which are listed in *both* action and adventure for which the gamesystem is playstation? (this requires an inner join which is what you have in your sql statement).

Or, do you want the gamename of all games from action and adventure for which the gamesystem is playstation? This requires a union, which MySQL doesn't support. In this case I would do two queries and throw all the data into a PHP array.

Let me know a bit more, and I will see if I can help further.

[XploreR]

HI freakysid,

First of all, thanks for your reply.

Well, actually I want the gamename of all the games which are listed in tables action and adventure for the gamesystem - playstation

Right now, i have difficulty showing in in HTML pages as i don't know how to do it. I only manage to do it for a single table but not for this. I want the data to be show in a table with all the gamename in alphabetical order.

Example:

Game A#1
Game A#2
and so on..

I also have each table a new column call alpha where i put in the first alphabeth of each gamename added inside so i can list like alphabeth a for page 1 and b for page 2. Do i use
it like this:

SELECT action.game, adventure.game, FROM action, adventure WHERE action.gamesystem=adventure.gamesystem and psxadventure.gamesystem='playstation'" and alpha='a'; for example

Hope to hear from you or any other members! Thanks!

[freakysid]

OK it seems to me (correct me if I'm wrong) that what you want to do is create a UNION of the two tables so that you have a result set which includes the names of games from action and adventure where the gamesystem is playstation. The bad news is that MySQL doesn not support UNION.

The SQL you have written will only return the games which appear in BOTH action and adventure tables. I'm assuming that this is not what you want. Another thing, this part of your sql and psxadventure.gamesystem='playstation' will cause an error because it implies a table psxadventure which is not in the scope of your query.

So, going by what I think you want to do, I would execute two seperate MySQL queries and place the contents of each into the one array, then sort the array.

Eg;

$tables = array("action", "adventure");

foreach ($tables AS $table) {

   $sql = "SELECT game FROM $table
             WHERE gamesystem='playstation'";
   $result = mysql_query($sql);

   foreach($row = mysql_fetch_array($result)) {
      $games[] = $row["game"];
   }
}
sort($games); 

You now have an array $games which contains the desired results sorted by alphabetic order.

[XploreR]

freakysid, I have a go with your coding and it works. Jusst what i wanted. A million thanks!

[XploreR]

Hi freakysid,

today i have another go at the coding but i got a parse error along this line

foreach($row = mysql_fetch_array($result)) {

any help? And how do i go about showing the data out? THANKS

[freakysid]

My bad

foreach($row = mysql_fetch_array($result))

Should be

while($row = mysql_fetch_array($result))

I'm suprised no one else picked that up.

To output the contents of the array - here is one way:

foreach($games AS $game) {
   echo $game . '<br>';
} 

That will output one game name per line.
You can more fancy and output the data in a table; for example

echo '<table>';
foreach($games AS $game) {
   echo "<tr><td>$game</td></tr>";
}
echo '</table>'; 

[XploreR]

freakysid, well, looks like i need help from ya a little bit more

With your help, i was able to generate the list of games in a table. But i would like to include an url link for each of the games. I have already specified the url link for each game entered in the database. Would you be kind enough to teach me how to output them together?

<a href='$url'>$game

So far, i've added :

$sql = "SELECT game, url FROM $table
WHERE gamesystem='playstation'";

while ($row = mysql_fetch_array($result)) {
$games[] = $row["game"];
$urls[] = $row["url"];
}

I wonder if that's correct, and from there, i don't know how to rewrite . Do i need to use sort($urls);

Thanks in advance

[freakysid]

Let me preface this reply by saying that arrays in PHP are just amazing. IMHO, this is one of the most impressive features of PHP. In PHP an array can also be simultaneously an associative array (aka dictionary, map in other languages). In a plain old C style array each element is identified by its index number. In an associative array, you can assign a key/value pairs to each element. Thus you can idenfify an element by its index or by its key.

So, to solve your problem, I create an array of key/value pairs where the key is the game name and the value is the url. Then I can use ksort() to sort the array by its keys. Note, this assumes that no two games have the same name as each key must be unique.

while ($row = mysql_fetch_array($result)) {
   // for convenience I use the function extract() to place
   // each element (result set field) contained in $row
   // into its own variable; ie, $game, $url
   extract($row);

   // Now insert into the array $games the key/value pair $game/$url
   $games["$game"] = $url;
}
// now sort the array by its keys
ksort($games); 

Run that up a flagpole and see if anyone salutes.

[XploreR]

Another valuable lesson learned today

Anyway, here's the code which i have tested and works


$tables = array("action", "adventure");

foreach ($tables AS $table) {

$sql = "SELECT game, url FROM $table
WHERE gamesystem='playstation'";

$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
// for convenience I use the function extract() to place
// each element (result set field) contained in $row
// into its own variable; ie, $game, $url
extract($row);

// Now insert into the array $games the key/value pair $game/$url
$games["$game"] = $url;
}

}
// now sort the array by its keys
ksort($games);

echo '<table>';
while (list ($key, $val) = each ($games)) {
echo "<tr><td><a href='$val'>$key</a></td></tr>";
}
echo '</table>';



Hope that's correct . A question tho, Are we only allow to have one value for each key?

[7stud]

Hi,

A question tho, Are we only allow to have one value for each key?

Well......yes and no. You can only have one value for each key, so the technical answer to your question is no, but the value of an element in an array can be another array, and the elements of the second array are accessed with a double key like this:

$array[1][0]=10;

[XploreR]

sure sounds confusing but i know what it means.

anyway, i saw on the site an article by Keith Reichley on using php/mysql to make the urls shorter.

I've been trying to get that to work, but all it does is to direct to my indexpage. Any help for my case?

i did as told and add - id INT NOT NULL AUTO_INCREMENT PRIMARY KEY into the tables. and have got all the entries to have their own unique ids.

I know the coding in jump.php in the article needs to be re-written for my case. Am i gonna use the foreach statments too?