1

I got some great help in my last question, someone directed me to go learn about database tables and stuff, since then I blasted through most of the things I was stuck on!

Unfortunately I've reached another problem which I can't seem to fix.

I can merge two tables and get results, but I can't seem to get the results of the user that is logged in. For example, I display the amount of 'gold' the user has at the top left corner, I have 7 users that have 100 gold assigned to them and I only want them to be seen when the user that the gold belongs to is logged in, if you get me? Here's what it looks like all the time, whether logged in or not: https://i.stack.imgur.com/sqn3f.jpg

here's the code

$sql = 'SELECT `stats`.`id`, `stats`.`gold`, `users`.`id` FROM stats, users WHERE username =     username';
$con=mysqli_connect("localhost","root","","game");
mysqli_select_db($con,'game');
$retval = mysqli_query($con,$sql);
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
echo "Gold :{$row['gold']}  <br> ".

     "--------------------------------<br>";

I'm 90% sure it's to do with the "select/from/where" part but I've done lots of research and can't get it right :(

Database structure: https://i.stack.imgur.com/glMdK.jpg (Sorry, I don't know how to get it without the command line)

Ryan McKenna
  • 83
  • 8
  • show your db structure , so that i can help you in creating correct query – Eshant Sahu Sep 19 '14 at 13:07
  • Have you tried using MYSQL join? You join the stats table and users table. – andrex Sep 19 '14 at 13:08
  • from your query, you are creating a cartesian join - meaning that each row from one table is joined to each row from the other table. You would probably do well to read this Q&A that I put together a while back to help in instnances exactly like this. http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables You need to specify how the tables are linked together to correctly match data in each. – Fluffeh Sep 19 '14 at 13:08
  • I've put a link to my database structure. Where do I do MYSQL join? in the command line?? – Ryan McKenna Sep 19 '14 at 13:31
  • When a user signs up, they are given a users.id and an identical stats.id, I thought by simply joining these it would merge the stats with the user? – Ryan McKenna Sep 19 '14 at 13:41
  • @RyanMcKenna you should have a unique identifier with gold belongs to whom, does this have a login, you should have saved the username in a session – Kevin Sep 19 '14 at 13:45
  • The username is saved in the login.php, but this is located in the functions.php – Ryan McKenna Sep 19 '14 at 13:47
  • @RyanMcKenna well, you could just save it in a session right before redirection to the next page, should be fairly easy `$_SESSION['username'] = username` – Kevin Sep 19 '14 at 13:52
  • @Ghost login.php saves the session and then goes to main.php. Functions.php contains the code that is created the "Gold: 100". functions.php isn't even called at all so why is it producing the "gold: 100"? – Ryan McKenna Sep 19 '14 at 14:11
  • @RyanMcKenna most likely you called the function that executed that? i'm just assuming you include `functions.php` in `main.php` right? – Kevin Sep 19 '14 at 14:15
  • @Ghost I've removed functions.php from everywhere I could find and it has indeed gone. I think I'm going to have a seperate stats page for showing the gold rather than a function. Do you think it's a database problem rather than a query? All I've done is ran a query from the phpmyadmin but I'm not sure if that "saves" the connection between a user and the gold – Ryan McKenna Sep 19 '14 at 14:21
  • @RyanMcKenna yeah most likely because of the inclusion of functions.php, actually its much better to tweak the tables itself rather than the query, if the tables are properly connected, the query should be a breeze – Kevin Sep 19 '14 at 14:25
  • Tables must not be properly connected then. I've followed so many guides as well as tried it myself. Do you know of a good guide? – Ryan McKenna Sep 19 '14 at 14:27
  • @RyanMcKenna yeah just like the user id should be also present in that gold table, you don't have an identifier which user gold belong to, well you could just check out table relationships – Kevin Sep 19 '14 at 14:37
  • @Ghost Oh I see, I've been using the actual ID rather than creating a user ID, that's where I've been going wrong! I'll have a go at that and then get back with a report! – Ryan McKenna Sep 19 '14 at 14:45
  • @Ghost wait so for example a user ID is 1, and there's 100 gold with the ID of 1, so the user ID 1 should have 100 gold? If so, that's what I've been doing, but it's the joining and then calling I'm having problems with. guides don't really help as they're based from 'static' things like car makes while mine can change with the user – Ryan McKenna Sep 19 '14 at 14:49
  • @RyanMcKenna yes you forgot about that, after that, your select should be fairly straightforward since you already have a connection between them, yes something like that. `id, user_id, gold` => `1(statsid), 24(user_id), 100 (gold)` – Kevin Sep 19 '14 at 14:51
  • @Ghost I use procedural not object orientated but I can convert – Ryan McKenna Sep 19 '14 at 14:53
  • @RyanMcKenna no thats just a representation row, just an example – Kevin Sep 19 '14 at 14:54
  • Ah okay lol Well I've recreated the tables just in case, followed guides even though I've done it a bazillion times. Just a quick one as well, how would I increase the gold for id 1? Currently have "INSERT INTO stats (gold) VALUES (100)" but it doesn't work lol – Ryan McKenna Sep 19 '14 at 15:03
  • Also, my site is live (www.velrania.com) so you can see for yourself if you want, lots of errors though – Ryan McKenna Sep 19 '14 at 15:04

2 Answers2

1

I see that you have the gold in one table and the users in another. The gold in the gold table should point to what user owns the gold. Like for example owner_id pointing to the users id. Then you should be able to do like this:

$sql = "SELECT stats.id, stats.gold, users.id, users.username FROM stats, users WHERE users.username = '$username' AND stats.owner_id = users.id";

This tells to find user with the username specified in $username, and the gold with owner_id the same as the users id matching that username.

Hope this helps

Ole Haugset
  • 3,709
  • 3
  • 23
  • 44
0

Try: 

SELECT `stats`.`id`, `stats`.`gold`, `users`.`id` FROM stats, users WHERE stats.username =     users.username

Because username is common to both tables (I assume), it's important to explicitly state which table username is coming from.

Edited to add:

You would still need to specify which username you want from the results of the joined tables. 

$sql = 'SELECT `stats`.`id`, `stats`.`gold`, `users`.`id` FROM stats, users WHERE `stats`.username =     `users`.username AND `users`.`'.$yourUserVariable.'`;
JNevill
  • 46,980
  • 4
  • 38
  • 63