Using PHP to get data from multiple database tables

Question

I am trying to query five tables. I am able to query one of the tables with

$query = "SELECT * FROM Stats_player WHERE player='$user'";

However, when I try to query another table with

$query = "SELECT * FROM Stats_player, Stats_block WHERE player='$user'";

the website breaks. Here is the code I am using to echo the data on the screen

<?php 
if ($result = $mysqli->query($query)) {
echo "<img src=\"https://minotar.net/avatar/{$user}/100\"><h1>{$user}</h1><br/>";
    while ($row = $result->fetch_assoc()) {
        //variables
        $play_time = $row['playtime']/3600;
        $play_time = round($play_time, 1);
        $xpgained = $row['xpgained'];
        $damagetaken = $row['damagetaken'];
        $toolsbroken = $row['toolsbroken'];
        $itemscrafted = $row['itemscrafted'];
        $itemseaten = $row['omnomnom'];
        $commandsused = $row['commandsdone'];
        $teleports = $row['teleports'];
        $itemspickedup = $row['itempickups'];
        $itemsdroped = $row['itemdrops'];
        $lastseen = date("F j, Y ", strtotime($row['lastjoin']));
        //end of variables
            echo "<p>Time on Server: {$play_time} HRS</p>";
            echo "<p>Last Seen: {$lastseen}";
            echo "<p>Commands Used: {$commandsused}";
            echo "<p>XP Gained: {$xpgained}"; 
            echo "<p>Blocks broken: {$row['blockID']}"; //this is data from the table Stats_block
        }
    $result->free();
} 
$mysqli->close();
?>

Any ideas on how I might do this?

Table structor of Stats_player: | counter | player | Playtime |

Stats_block is: | counter | player | blockID |

show your table structure (stats_player, stats_block), please. I would guess we can find a player row in both tables (or at least columns with same name). By the way, your query doesn't make really sense without a join. — Raphaël Althaus
– Raphaël Althaus, Commented May 22, 2013 at 14:56
I know it doesnt make sense thats why i came here for help. Though it doesnt matter. The mods or who ever marked it down because I dont know exactly what im doing. I wish i could ask a question and get help but instead i just get marked down because i might have had an error in the way i asked. — Tomas
– Tomas, Commented May 22, 2013 at 16:37
Not the downvoter... Some people seem to easily downvote "newcomers". Anyway, don't mind the downvotes and try to edit your question. — Raphaël Althaus
– Raphaël Althaus, Commented May 22, 2013 at 16:40
Alrighty thanks. I did end up figuring out a work around. I guess my ex gf's dad is a php developer lol who woulda known! Anyway thanks for being nice to me when the other people who downvoted me weren't =( next year I will be one of the people helping people. =) — Tomas
– Tomas, Commented May 22, 2013 at 19:29

ChristopheBrun · Accepted Answer · 2013-05-22 17:20:45Z

1

$query = "SELECT * FROM Stats_player, Stats_block WHERE player='$user'"

That's very likely wrong. You should use a JOIN operator.

Here, you are just doing a cartesian products of the two tables, that's hardly what you want. And that may overrun your resource (memory etc.) if the tables have a lot of rows.

Something like

$query = "SELECT * FROM Stats_player p, Stats_block b 
WHERE p.block_id = b.id AND p.player='$user'"

or

$query = "SELECT * FROM Stats_player p INNER JOIN Stats_block b ON p.block_id = b.id
WHERE p.player='$user'"

or maybe LEFT OUTER JOIN...

The exact query will depend on your schema.

edited May 22, 2013 at 17:20

answered May 22, 2013 at 15:01

ChristopheBrun

1,2077 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tomas Over a year ago

so what does the p and b stand for in "Stats_player p, Stats_block b" are those just kinda like variables?

ChristopheBrun Over a year ago

b and p are aliases. They're easier to use than full table names, but far from mandatory. Stand for : SELECT * FROM Stats_player, Stats_block WHERE Stats_player.block_id = Stats_block.id AND Stats_player.player='$user'

Kermit · Accepted Answer · 2013-05-22 14:56:46Z

0

Without seeing the structure of both tables, something like this could work.

SELECT columnList
FROM Stats_player a
LEFT JOIN Stats_block b ON b.player = a.player
WHERE a.player = '$user'

answered May 22, 2013 at 14:56

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Comments

ramonovski · Accepted Answer · 2013-05-22 14:59:26Z

0

Have you tried something like this:

SELECT 
table1.field1, table1.field2, table2.field1, table2.field2
FROM
table1, table2
WHERE 
table1.field1 = " " and table2.field1 = " ";

answered May 22, 2013 at 14:59

ramonovski

4043 silver badges16 bronze badges

Collectives™ on Stack Overflow

Using PHP to get data from multiple database tables

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related