SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    Last edited by XploreR; May 6, 2001 at 19:53.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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;
    PHP Code:

    $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.

  5. #5
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    freakysid, I have a go with your coding and it works. Jusst what i wanted. A million thanks!

  6. #6
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Opps

    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

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    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
    PHP Code:
    echo '<table>';
    foreach(
    $games AS $game) {
       echo 
    "<tr><td>$game</td></tr>";
    }
    echo 
    '</table>'

  8. #8
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Assigning url data

    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
    Last edited by XploreR; May 10, 2001 at 21:36.

  9. #9
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    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.

  10. #10
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  11. #11
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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;

  12. #12
    SitePoint Enthusiast XploreR's Avatar
    Join Date
    May 2001
    Location
    Singapore
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    jump.php

    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •