#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    1
    Rep Power
    0

    Advice for Pokedex in SQL


    So this is sorta my first time doing an Online RPG (MMORPG), It's a browser based-Pokemon game.

    In the Database "RPG", l've created 2 tables;

    1.Table 1 - "Pokedex"
    Columns: ID#, PokemonName, PokemonType, Level, Exp, HPoints, ATT, DEF

    2.Table 2 - "Users"
    Columns: ID, Full Name, E-Mail, Username, Password) - (Also a confirmation email gets sent to the user)

    In the Register field, they put in their info (User, Pass, Email), then chooses a Starter Pokemon to fight with. My question is how would i interpret that into a SQL/PHP command that joins the starter pokemon to that User or vise versa?

    Far as l know it's

    SELECT * FROM Users WHERE NAME = "$username";
    SELECT * FROM Pokedex WHERE NAME = "Bulbasaur"; (Let's say the user chose the creature Bulbasaur)

    What im having alittle trouble understanding is how would l select the user who is logged to be able to choose the Bulbasaur and have the entry from the Pokemon Table copy one for the User?

    I asked this question before on another site but l didn't get much help that l could fully understand. I was told a variable would be ideal to use but may be risked to SQL Injections, l do have an MD5 hash for the users so their accounts is safe.


    Also, for the Pokedex, how could l create it that easier to use, l also was told to join tables, is Joining a table a one way process and must be executed on every script? or could l use the 'include_once' statement to bring up the Database connection file..

    This may be alittle confusing to read, l'll try and explain it more clearly if l can get some advice on this subject, thanks for the help!

    EDIT: thepokemonrpg.x10.mx incase anyone wants to see what l mean, the game is still in the beginning state. I have only implemented a basic Register-Login-Logout feature. Just wanted to break everything down peice by peice.
    Last edited by FahhhhhQ; September 17th, 2013 at 09:15 AM. Reason: Spelling mistakes
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2007
    Posts
    765
    Rep Power
    929
    Often you would do this through a third table. E.g.

    OwnedPokemon ( UserID, PokemonID )

    And then you can "INSERT INTO OwnedPokemon (UserID, PokemonID) VALUES ( ?, ? )" to add one to a user's list, "SELECT PokemonID FROM OwnedPokemon WHERE UserID=?" to pull all the ones assigned to a particular user.

    JOINs are a related matter. In the above, to produce a list of pokemon by name for a user you need two queries: one to get the list of IDs from the OwnedPokemon table, and one to get the name from the Pokedex table. By using a JOIN you can treat the separate tables as one large table: "SELECT Pokedex.PokemonName FROM OwnedPokemon JOIN Pokedex ON OwnedPokemon.PokemonID = Pokedex.ID WHERE UserID=?" returns rows from a table where every row in OwnedPokemon has the row with the same PokemonID in Pokedex included.
    sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    1
    Rep Power
    0
    Thank you this made alot of sense. I just have one question

    If l were to send multiple queries would this be ideal?

    <?php
    require_once 'login.php';
    $db_server = mysql_connect($db_hostname, $db_username, $db_password);
    if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
    mysql_select_db($db_database)
    or die("Unable to select database: " . mysql_error());

    // This would pull the current user who is logged in

    $query = "SELECT Username FROM Users WHERE NAME = "$username";

    // Up to this part im stuck on creating multiple queries, would l need to add a $query2 = Whatever, INSERT or UPDATE etc..

    $result = mysql_query($query);
    if (!$result) die ("Database access failed: " . mysql_error());

    ?>

    I know some If and Else statements should go in there just need to understand if putting multiple queries on a script can be safe or not, trying to avoid injections but if l got a session open is that still possible?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2007
    Posts
    765
    Rep Power
    929
    I imagine you can call mysql_query() as many times as you want with whatever query string you want. As for injection, you need to ensure that user input is never spliced into a query string.

    It has been many years since I touched PHP though. There are specific forums for PHP and MySQL here that might be more helpful.
    sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);

IMN logo majestic logo threadwatch logo seochat tools logo