Selecting user specific records for logged in user using multiple tables PHP MYSQL

Question

I have 2 tables one called users and one called tv shows. Im storing the name of the user in a variable called username by doing. The users table holds the user_id PK, username, password and the tv shws table stores the tv_id PK, user_id FK, TV Show Name

$username=$_SESSION['username'];

i want to be able to display all the tv shows for the specific user that has logged in and im guessing i would need to show all the results for the user id assigned to the user that has logged in because the user_id in the tv shows table is a foreign key of the primary key user id in the users table.

Code:

$user = "SELECT user_id FROM users where username='$username'";

if(!$rs = mysql_query("SELECT * FROM tv shows WHERE user_id='$user'")) {

When i run this code i get "cannot select table"

SELECT user_id FROM users where username='$username' JOIN tv shows ON user_id_id='$user' ? — v0d1ch
– v0d1ch, Commented Nov 30, 2012 at 12:25
SELECT user_id FROM users JOIN tv shows ON user_id_id='$user' where username='$username' and now ? — v0d1ch
– v0d1ch, Commented Nov 30, 2012 at 12:32
Well you have to have variable value to get this query to work. Check why $user is not defined and what is the value of it, and put user in curly braces {$user} — v0d1ch
– v0d1ch, Commented Nov 30, 2012 at 12:40

Toretto · Accepted Answer · 2012-11-30 14:29:47Z

1

Try this

$query = 'SELECT * FROM tv_shows where user_id=(SELECT user_id FROM users where username="'.$username.'")';

edited Nov 30, 2012 at 14:29

answered Nov 30, 2012 at 12:42

Toretto

4,7115 gold badges29 silver badges46 bronze badges

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

7 Comments

Toretto Over a year ago

any error did you get?and can you please provide the sample data so we can figure it out what is wrong?And please check hat which username you try to log in is presented on database or not

Christopher Ward Over a year ago

cwstudios.co.uk/code.txt there is my code now with your query i cant seem to select the table.

Toretto Over a year ago

Where is your code where you storing the value of username to $_SESSION

Christopher Ward Over a year ago

$username=$_SESSION['username']; that? the username variable works because it says logged in as John. when you are logged in.

Toretto Over a year ago

@ChristopherWard Sorry just a mistake in my code i have updated my answer please paste the updated query and see if it works

|

Gaurav · Accepted Answer · 2012-12-03 06:47:15Z

1

okay ,try this:

<?php
$user = musql_query("SELECT * FROM users where username='$username'");
$result = mysql_fetch_array($user);
$userid = $result['user_id'];

$sql = "SELECT * FROM tv shows WHERE user_id=".$userid;
$get_tv = mysql_query($sql);
$make_array = mysql_fetch_assoc($get_tv);
 print_r($make_array);
?>

Happy coding!!

answered Dec 3, 2012 at 6:47

Gaurav

6386 silver badges18 bronze badges

Comments

Jaffer · Accepted Answer · 2012-11-30 12:29:17Z

0

First of all, as a better design, you can have user_id in your $_SESSION.. so that you can avoid unnecessary query...

Here the problem could be due to Single quote ... So please escape your Single quote ..

Thanks

answered Nov 30, 2012 at 12:29

Jaffer

2,97819 silver badges29 bronze badges

1 Comment

Christopher Ward Over a year ago

Now it says "no records found"

bensiu · Accepted Answer · 2012-11-30 13:13:14Z

0

SELECT username,user_id 
FROM users as a 
JOIN tv shows as b ON b.user_id_id=a.user_id 
WHERE a.username='{$username}'

edited Nov 30, 2012 at 13:13

bensiu

25.7k56 gold badges81 silver badges117 bronze badges

answered Nov 30, 2012 at 12:53

v0d1ch

2,7481 gold badge24 silver badges27 bronze badges

Comments

Christopher Ward · Accepted Answer · 2012-11-30 15:29:43Z

0

managed to figure it out myself with help from Tornado

if(!$rs = mysql_query("$query2")) {
$query2 = 'SELECT * FROM tv_shows where user_id=(SELECT user_id FROM users where username="'.$username.'")';

answered Nov 30, 2012 at 15:29

Christopher Ward

698 bronze badges

Collectives™ on Stack Overflow

Selecting user specific records for logged in user using multiple tables PHP MYSQL

5 Answers 5

7 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related