Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    ubh
    ubh is offline
    Regular Coder ubh's Avatar
    Join Date
    Apr 2008
    Location
    Portland, Oregon U.S.A.
    Posts
    443
    Thanks
    108
    Thanked 15 Times in 14 Posts

    Two Table SELECT, Unique Arrays Keys

    So I got this script that performs two SELECTs, on two different tables in the db and then begins to writes out a JS object with two arrays in a "key value" pair based on the column name and column/row value.

    Correct me if I'm wrong here but I could speed this thing up by using a single SELECT right? However the larger question is how to output data from one table or the other, into unique arrays after using a single select? Anyone?

    Here is the code in question:

    PHP Code:
    <?php
        
    include('dbconnect.php');
        
    $gamerName mysql_real_escape_string($_GET['gamerName']);
        
        
    mysql_query("START TRANSACTION");
        
        
    $playerRecord mysql_query("SELECT id FROM players WHERE gamerName='$gamerName'");
        
    $player mysql_fetch_array$playerRecord );
        
    $id $player['id'];

        
    $weaponResults mysql_query("SELECT 
        m4a1, rpk, barret50cal, m1014, ak47, _870mcs,
        m16, mk18_mod0, mp5, riot_shield, aug,
        famas, fnspr_a4, g3, l85_a1, m79, m249,
        msg90, p90, m9, g22, desert_eagle,
        g18, ingram, ingram_sliencer, shorty, frag, smoke,
        c4, claymore,ammo, health FROM weapons WHERE playerId='$id'"
    ) or die(mysql_error());     

        
    $columns mysql_num_fields($weaponResults);
        echo 
    '{';
        echo 
    '"weapons":[';
        
    $index 0;

        while(
    $row mysql_fetch_array$weaponResults )) {

            for(
    $i 0$i $columns$i++) {
                echo 
    '{';
                    echo 
    '"name":"'.mysql_field_name($weaponResults,$i).'",';
                    echo 
    '"purchased":"'.$row[mysql_field_name($weaponResults,$i)].'"';
                if(
    $columns-== $i){ echo '}'; } else { echo '},'; }
            }         
        }
        echo 
    '],';

        
    $achievementResults mysql_query("SELECT 
            freefall, marathon, tea_bagger, 
            head_streak, kill_streak, captures, 
            destroys FROM achievements WHERE playerId='$id'"
    ) or die(mysql_error());
                
        
    $columns mysql_num_fields($achievementResults);        
        echo 
    '"achievements":[';
            while(
    $row mysql_fetch_array$achievementResults )) {
                for(
    $i 0$i $columns$i++) {
                    echo 
    '{';
                        echo 
    '"name":"'.mysql_field_name($achievementResults,$i).'",';
                        echo 
    '"rate":"'.$row[mysql_field_name($achievementResults,$i)].'"';
                    if(
    $columns-== $i){ echo '}'; } else { echo '},'; }
                }         
            }    
        echo 
    ']';
        
        echo 
    '}';
        
    mysql_query("COMMIT");

    ?>

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,105
    Thanks
    23
    Thanked 594 Times in 593 Posts
    Once more into the sql. Try this in a separate program. I am using mysqli because the old mysql will be gone next php release.
    PHP Code:
    <?php
    $playerRecord 
    mysqli_query("SELECT p.id, w.m4a1, w.rpk, (AND more weapons), a.freefall, a.marathon, a.tea_bagger, (AND more achievements)
    FROM players AS p
    JOIN weapons AS w
    ON w.playerId = p.id
    JOIN achievements AS a
    ON a.playerId = p.id
    WHERE p.gamerName = '$gamerName'"
    );

    WHILE(
    $item mysqli_fetch_assoc$playerRecord );{
        echo 
    'User id is : '.$item['id'] . '<br>';
        echo 
    'User has m4a1 : '.$item['m4a1'] . '<br>';
        echo 
    'User id is rpk : '.$item['rpk'] . '<br>';
        echo 
    'User has won : '.$item['freefall'] . '<br>';
        echo 
    'User has won  : '.$item['tea_bagger'] . '<br>';
        }
    ?>
    I don't know how you mark things in the tables so you might need to do an 'isset' or a '!equal to' before the echos.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #3
    ubh
    ubh is offline
    Regular Coder ubh's Avatar
    Join Date
    Apr 2008
    Location
    Portland, Oregon U.S.A.
    Posts
    443
    Thanks
    108
    Thanked 15 Times in 14 Posts
    Hey thanks for the reply! But how would you to divide the data up into two arrays, each array only containing data from one table, or the other? So array one would never contain data from table two and vice versa.

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,105
    Thanks
    23
    Thanked 594 Times in 593 Posts
    Don't use $item['m4a1'] when your doing the $item['tea_bagger'] things. You aren't using arrays, why would you need them now?
    If you ran a test you should see why.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #5
    ubh
    ubh is offline
    Regular Coder ubh's Avatar
    Join Date
    Apr 2008
    Location
    Portland, Oregon U.S.A.
    Posts
    443
    Thanks
    108
    Thanked 15 Times in 14 Posts
    Sorry, I think I'm not stating my problem correctly, heh bare with me.

    In my code above, with each select (mind you there are three selects in my code above, I'm just looking to combine the weapons and achievement table selects or rather the last two.) I'm building up javascript literal objects that contains each tables information for that particular users weapons and achievements they have earned.

    PHP Code:
        echo '"weapons":['
        
    $index 0

        while(
    $row mysql_fetch_array$weaponResults )) { 

            for(
    $i 0$i $columns$i++) { 
                echo 
    '{'
                    echo 
    '"name":"'.mysql_field_name($weaponResults,$i).'",'
                    echo 
    '"purchased":"'.$row[mysql_field_name($weaponResults,$i)].'"'
                if(
    $columns-== $i){ echo '}'; } else { echo '},'; } 
            }          
        } 
        echo 
    '],'
    Now in the database there are a ton of columns for both tables, and we frequently add new columns to these tables for new weapons or achievements our game puts out. So when we create these javascript objects (I should also mention this php script is ran via XHR post to obtain these Javascript objects) I have a need to not be explicit in the column names, but rather loop through the columns and echo the name of the column out in these Javascript objects.

    So if I have the need to not be explicit in knowing each column name, but rather need to loop over / echo out the names of the columns in these Javascript objects, how does one split the data up between the two tables under a single select of the two db tables under these circumstances?

    Does that make sense? Sorry hard to explain I guess.
    Thanks for taking the time to help though!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Now in the database there are a ton of columns for both tables, and we frequently add new columns to these tables for new weapons or achievements our game puts out. So when we create these javascript objects (I should also mention this php script is ran via XHR post to obtain these Javascript objects) I have a need to not be explicit in the column names, but rather loop through the columns and echo the name of the column out in these Javascript objects.
    UGH! That's a pretty terrible DB design. No wonder you have problems.

    You should have a separate table for weapons and almost surely a separate table for achievements, done in such a way that you NEVER have to alter the number of columns in any table.

    I strongly suspect that the bad DB design is thus leading you to ever more complex and uglier SQL queries and is likely not doing the PHP code any favors, either.

    Time to read up on database normalization, I would think.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    In other words, instead of having your weapons table look like this:
    Code:
    CREATE TABLE weapons(
        playerid INT,
        m4a1 INT,
        rpk INT,
        barret50cal INT,
        ... etc. ...
    );
    You should simply have:
    Code:
    CREATE TABLE weaponsList (
        weaponid INT AUTO_INCREMENT PRIMARY KEY,
        weaponname VARCHAR(100),
        price DECIMAL(8,2)
    ) ENGINE INNODB;
    
    CREATE TABLE weapons (
        playerid INT,
        weaponid INT,
        quantity INT,
        CONSTRAINT FOREIGN KEY weaponid REFERENCES weaponsList(weaponid),
        CONSTRAINT FOREIGN KEY playerid REFERENCES players(id)
    ) ENGINE INNODB;
    Or similar.

    And now to add a weapon, you simply add a new entry in the weaponsList table and it's automatically now available to all players to put in their own weapons.

    Now, to get a list of all weapons owned by a user, it's trivial:
    Code:
    $sql = "SELECT P.playername, WL.weaponname, W.quantity " 
         . " FROM players AS P, weapons AS W, weaponsList AS WL "
         . " WHERE P.id = W.playerid AND W.weaponid = WL.weaponid " 
         . " ORDER BY P.playername, WL.weaponname";
    $result = mysql_query($sql);
    $curp = "";
    while ( $row = mysql_fetch_assoc($result) )
    {
        $pname = $row["playername"];
        $wname = $row["weaponname"];
        $qty = $row["quantity"];
        if ( $pname != $curp )
        {
            if ( $curp != "" ) echo "</ul>\n";
            echo "Player " . $pname . "<ul>";
            $curp = $pname;
        }
        echo "<li>" . $wname . " : " . $qty . "</li>";
    }
    echo "</ul>\n";
    Or something along those lines. Obviously you could similarly create JS objects for each player.
    Last edited by Old Pedant; 04-24-2013 at 11:49 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    ubh (04-25-2013)

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Closer to your original code:
    Code:
    $sql = "SELECT WL.weaponname, W.quantity " 
         . " FROM players AS P, weapons AS W, weaponsList AS WL "
         . " WHERE P.id = W.playerid AND W.weaponid = WL.weaponid " 
         . " AND P.gamername = '" . $gamername . "' "
         . " ORDER BY WL.weaponname";
    $result = mysql_query($sql);
    echo '{ "weapons":['; 
    $delimiter = "";
    while ( $row = mysql_fetch_assoc($result) )
    {
        $wname = $row["weaponname"];
        $qty = $row["quantity"];
        echo $delimiter . '{ "name": "'. $wname . '", "purchased" : ' . $qty . '}';
        $delimiter = ",\n";
    }
    echo "}";
    And or course you should do achievements in the same way. A pair of tables, etc.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    And, finally, if you wanted to show all weapon names, even if the quantity purchased is zero, that's as simple as changing the query slightly. If you are also going to get achievements in the same way, I'd run a separate query to get the playerid:
    Code:
    $sql = "SELECT if FROM players WHERE gamername = '" . $gamername . "'";
    ... get that id into $playerid ...
    
    ... and then ...
    
    $sql = "SELECT WL.weaponname, IFNULL(W.quantity,0) AS quantity " 
         . " FROM weaponsList AS WL "
         . " LEFT JOIN weapons AS W "
         . " ON WL.weaponid = W.weaponid AND W.playerid = $playerid "
         . " ORDER BY WL.weaponname";
    And now you will, indeed, get all the weapons with a quantity of zero if there is no entry in the weapons table for that weaponid and that playerid.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    ubh (04-25-2013)

  • #10
    ubh
    ubh is offline
    Regular Coder ubh's Avatar
    Join Date
    Apr 2008
    Location
    Portland, Oregon U.S.A.
    Posts
    443
    Thanks
    108
    Thanked 15 Times in 14 Posts
    Thanks Old Pedant, once again you have tough me a valuable lesson. I now see the trade off of having a normalized approach or rather what it really means, and how the trade off can be beneficial.

    Thanks a ton!


  •  

    Posting Permissions

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