Filtering Records For Logged in User Only? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Filtering Records For Logged in User Only?

Author

Topic

Palermo
Starting Member

25 Posts

Posted - 2014-06-19 : 07:48:14

I hope this is the right forum.

I use the following query to display records by joining form data with image data.

SELECT * FROM soccer JOIN soccerimages ON soccer.id = soccerimages.id ORDER BY soccer.id

This displays all records, form data with one image, but how do I only display the record for the logged in user? I need to make this private to avoid copyright issues, if users upload images from websites for example. The logged in user is stored in a session variable

$user = $_SESSION['UserName'];

I have tired

SELECT * FROM tcgsoccer JOIN tcgsoccerimages ON tcgsoccer.id = tcgsoccerimages.id ORDER BY tcgsoccer.id `WHERE '$user'='$user'`

But I get syntax errors. I don't understand how to structure the query? I am testing it in phpmyadmin so have removed the ;

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-19 : 08:18:18

move the ORDER clause after WHERE clause

something like this.Best is to have an object : a stored procedure ...


SELECT * FROM tcgsoccer JOIN tcgsoccerimages ON tcgsoccer.id = tcgsoccerimages.id WHERE user = 'XXXX' ORDER BY tcgsoccer.id

sabinWeb MCP

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-19 : 08:19:58

Is there a user_id or username column in the soccer table or soccerimage table? Assuming there is, the syntax should be this - i.e., the WHERE clause should come before the order by clause

SELECT * 
FROM tcgsoccer JOIN tcgsoccerimages ON tcgsoccer.id = tcgsoccerimages.id 
WHERE user_column = 'theusernameyouwanttouse'
ORDER BY tcgsoccer.id

If you are constructing the query in you client code you need to construct it including the single quotes. So, theusernameyouwanttouse should come from your $user variable.

Palermo
Starting Member

25 Posts

Posted - 2014-06-19 : 08:57:18

Yes there is a username field but the username is stored as a session variable after a user logs in e.g. :

$_SESSION['UserName'] = $PassWordMatch['UserName'];

Then I declare this as a variable :

$user = $_SESSION['UserName'];

I need to use the session variable in the query and not the username field. In my edit script I have

'$user'='$user'"

in the WHERE clause to enable the logged in user to edit their records only but the same method isn't working in this query.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-19 : 09:27:00

I will create a stored procedure , like this :


CREATE PROCEDURE dbo.usp_Soccer_GET
(
	@I_userName NVARCHAR(50)
)
AS
BEGIN
	SELECT * 
		FROM tcgsoccer JOIN tcgsoccerimages 
		ON tcgsoccer.id = tcgsoccerimages.id 
	WHERE userName = @I_userName
	ORDER BY tcgsoccer.id
END

Then call to this sp like :


EXEC usp_Soccer_GET @I_userName = $user

sabinWeb MCP

Palermo
Starting Member

25 Posts

Posted - 2014-06-19 : 09:29:25

I cannot use a stored procedure, this is for a website.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-19 : 09:36:44

key is to use the column of your table [userName] in WHERE clause


WHERE userName =   'UserName'
ORDER BY tcgsoccer.id

[

sabinWeb MCP

Palermo
Starting Member

25 Posts

Posted - 2014-06-19 : 10:22:52

That gives :

Column 'UserName' in where clause is ambiguous

I have also tried it with quotes it dind't help.

Also I tried :

ORDER BY tcgsoccer.id WHERE $user = '$user'

Which gave :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE admin = 'admin'' at line 1

So the variable value is being passed.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-20 : 00:51:31

1. This is a Microsoft SQL Server's forum .From what I see , you have a MySQL server.

2. If it's MS SQL, the order of ORDER clause, should be always last ( SELECT...FROM... WHERE ... ORDER BY ...)

sabinWeb MCP

Palermo
Starting Member

25 Posts

Posted - 2014-06-20 : 07:41:09

Got it. SELECT * FROM tcgsoccer JOIN tcgsoccermages ON tcgsoccer.id = tcgsoccerimages.id WHERE tcgsoccer.username='$user' ORDER BY tcgsoccer.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources