...

View Full Version : Two Table SELECT, Unique Arrays Keys



ubh
04-24-2013, 09:19 AM
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
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-1 == $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-1 == $i){ echo '}'; } else { echo '},'; }
}
}
echo ']';

echo '}';
mysql_query("COMMIT");

?>

sunfighter
04-24-2013, 03:38 PM
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
$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.

ubh
04-24-2013, 05:27 PM
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.

sunfighter
04-24-2013, 08:18 PM
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.

ubh
04-24-2013, 08:57 PM
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.



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-1 == $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!

Old Pedant
04-24-2013, 11:33 PM
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.

Old Pedant
04-24-2013, 11:40 PM
In other words, instead of having your weapons table look like this:


CREATE TABLE weapons(
playerid INT,
m4a1 INT,
rpk INT,
barret50cal INT,
... etc. ...
);

You should simply have:


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:


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

Old Pedant
04-24-2013, 11:59 PM
Closer to your original 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.

Old Pedant
04-25-2013, 12:07 AM
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:


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

ubh
04-25-2013, 01:12 AM
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!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum