Getting Results

--Apple-Mail-4--563076439
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Hello,
Can anyone tell me if I am doing this right? Or how to do this right?
Not sure on what to search for. What the search term would be for
this type of call to the database.

$q = "SELECT username,userlevel,email,timestamp FROM ".USERS." ORDER
BY username FROM ".VIP." ";

I am basically trying to get a list of user info from the user table
based on the usernames that are in the vip table.

TIA,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--Apple-Mail-4--563076439--

Re: Getting Results

--Apple-Mail-5--560824224
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Thanks Robbyka,
The username would be what your referring to with the .idvip

So..

"select username, userlevel,email, timestamp from ".USERS.", ".VIP."
where ".VIP.".username=".USERS.".username;

yes?

Karl

On Jan 20, 2011, at 7:24 PM, Robbyka Gheo wrote:

> i think its wrong, try this script?
> "select username, userlevel,email, timestamp from users, viptable
> where viptable.idvip=users.idvip;
>
> i asumsion the viptable.idvip is relation with users.idvip;
>
> On Fri, Jan 21, 2011 at 9:01 AM, Karl DeSaulniers
> wrote:
> Hello,
> Can anyone tell me if I am doing this right? Or how to do this right?
> Not sure on what to search for. What the search term would be for
> this type of call to the database.
>
> $q = "SELECT username,userlevel,email,timestamp FROM ".USERS."
> ORDER BY username FROM ".VIP." ";
>
> I am basically trying to get a list of user info from the user
> table based on the usernames that are in the vip table.
>
> TIA,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--Apple-Mail-5--560824224--

Re: Getting Results

--Apple-Mail-6--558478093
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Hi Robbyka,
.."USERS." and ."VIP." are constant variables used to hide the actual
names of the tables for security reasons.
Those variables will have content. Not sure what you mean by "semi
locon like => from ".users";"
The user names are set up to be unique. No two user names can be
identical and
the users in the VIP table come from the USERS table to begin with.
I am simply trying to get the VIP users info without having to fill
the VIP table up with duplicate info that
is already in the users table. The only thing going into the VIP
table is the username, and a time stamp.

Thanks for your help though, I think that is what I needed. Still
need to test.

Best,
Karl

On Jan 20, 2011, at 7:50 PM, Robbyka Gheo wrote:

> how many your tables? why you use semi locon like => from ".users";
> i think if you use that the query look ".users" as variabel and you
> must have content it that variabel..
> and better use unique id both of vip and users table.
>
> On Fri, Jan 21, 2011 at 9:39 AM, Karl DeSaulniers
> wrote:
> Thanks Robbyka,
> The username would be what your referring to with the .idvip
>
> So..
>
> "select username, userlevel,email, timestamp from ".USERS.",
> ".VIP." where ".VIP.".username=".USERS.".username;
>
> yes?
>
> Karl
>
>
> On Jan 20, 2011, at 7:24 PM, Robbyka Gheo wrote:
>
> i think its wrong, try this script?
> "select username, userlevel,email, timestamp from users, viptable
> where viptable.idvip=users.idvip;
>
> i asumsion the viptable.idvip is relation with users.idvip;
>
> On Fri, Jan 21, 2011 at 9:01 AM, Karl DeSaulniers
> wrote:
> Hello,
> Can anyone tell me if I am doing this right? Or how to do this right?
> Not sure on what to search for. What the search term would be for
> this type of call to the database.
>
> $q = "SELECT username,userlevel,email,timestamp FROM ".USERS."
> ORDER BY username FROM ".VIP." ";
>
> I am basically trying to get a list of user info from the user
> table based on the usernames that are in the vip table.
>
> TIA,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--Apple-Mail-6--558478093--

RE: Getting Results

Hi Karl,

What about:

$q =3D "SELECT username, userlevel, email, timestamp FROM Users_Table WHERE=
username IN (SELECT username from VIP_Table) ORDER BY username ASC";
This performs a query on the VIP table to get the usernames and then gets t=
he username, userlevel, email and timestamp from the Users table if the use=
rname is in this result.

There are some tutorials which I often refer to when I need a refresh (not =
all programming in my job unfortunately!):
http://www.tizag.com/mysqlTutorial/
http://www.w3schools.com/sql/default.asp

Hope this helps!

Andrew


-----Original Message-----
From: Karl DeSaulniers [mailto:karl@designdrumm.com]=20
Sent: 21 January 2011 01:02
To: php-db@lists.php.net
Subject: Getting Results

Hello,
Can anyone tell me if I am doing this right? Or how to do this right?
Not sure on what to search for. What the search term would be for =20
this type of call to the database.

$q =3D "SELECT username,userlevel,email,timestamp FROM ".USERS." ORDER =20
BY username FROM ".VIP." ";

I am basically trying to get a list of user info from the user table =20
based on the usernames that are in the vip table.

TIA,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Getting Results

I did not know you could do a SELECT inside a SELECT. Very nice!
Thank you Andrew. And I take it ASC is "in ascending order"?
So to do descending order I'd put DES or DEC? Just curious about that
one.

Thanks again.
Best,

Karl

On Jan 21, 2011, at 4:48 AM, Andrew Holt wrote:

> Hi Karl,
>
> What about:
>
> $q = "SELECT username, userlevel, email, timestamp FROM Users_Table
> WHERE username IN (SELECT username from VIP_Table) ORDER BY
> username ASC";
> This performs a query on the VIP table to get the usernames and
> then gets the username, userlevel, email and timestamp from the
> Users table if the username is in this result.
>
> There are some tutorials which I often refer to when I need a
> refresh (not all programming in my job unfortunately!):
> http://www.tizag.com/mysqlTutorial/
> http://www.w3schools.com/sql/default.asp
>
> Hope this helps!
>
> Andrew
>
>
> -----Original Message-----
> From: Karl DeSaulniers [mailto:karl@designdrumm.com]
> Sent: 21 January 2011 01:02
> To: php-db@lists.php.net
> Subject: Getting Results
>
> Hello,
> Can anyone tell me if I am doing this right? Or how to do this right?
> Not sure on what to search for. What the search term would be for
> this type of call to the database.
>
> $q = "SELECT username,userlevel,email,timestamp FROM ".USERS." ORDER
> BY username FROM ".VIP." ";
>
> I am basically trying to get a list of user info from the user table
> based on the usernames that are in the vip table.
>
> TIA,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Getting Results

On 21 January 2011 11:58, Karl DeSaulniers wrote:
> I did not know you could do a SELECT inside a SELECT.

Commonly known as a "sub select".

You can use them like ...

SELECT columns
FROM ( SELECT columns FROM table)
WHERE column IN (SELECT column FROM table)

As part of a FROM or as part of a WHERE ... IN clause. They are the
probably the most common ones.

You also have (depending upon your SQL engine and version) something
called common table expressions.

(From MS SQL Books Online) ...

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO


CTE's are great for recursive queries, once you get your head around
them. I also use them to help me find the next and previous row to the
current row in a result set, where a single table is essentially bound
3 times, but with the CTE, additional optimization seems to be in play
and work a LOT faster overall.



--
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Getting Results

On 21 January 2011 11:58, Karl DeSaulniers wrote:
> So to do descending order I'd put DES or DEC? Just curious about that one.

ASC and DESC


--
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Getting Results

Ahh yes, I remember that now.
Thank you.

Karl


On Jan 21, 2011, at 6:17 AM, Richard Quadling wrote:

> On 21 January 2011 11:58, Karl DeSaulniers
> wrote:
>> So to do descending order I'd put DES or DEC? Just curious about
>> that one.
>
> ASC and DESC
>
>
> --
> Richard Quadling
> Twitter : EE : Zend
> @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Getting Results

Very informative,
Thank you Richard.

I will have to flag this one and come back to it.
My database structure may require something of this measure down the
line
with the auditing ability I plan on building into the admin section.

Never heard of USE AdventureWorks. Is that a SQL system function?
Or just a table in the examples database?

On Jan 21, 2011, at 6:16 AM, Richard Quadling wrote:
>
>
> You also have (depending upon your SQL engine and version) something
> called common table expressions.
>
> (From MS SQL Books Online) ...
>
> USE AdventureWorks;
> GO
> WITH DirReps(ManagerID, DirectReports) AS
> (
> SELECT ManagerID, COUNT(*)
> FROM HumanResources.Employee AS e
> WHERE ManagerID IS NOT NULL
> GROUP BY ManagerID
> )
> SELECT ManagerID, DirectReports
> FROM DirReps
> ORDER BY ManagerID;
> GO


Guess I will have to do some googling.. :)
Thanks again.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Getting Results

On 21 January 2011 12:39, Karl DeSaulniers wrote:
> Very informative,
> Thank you Richard.
>
> I will have to flag this one and come back to it.
> My database structure may require something of this measure down the line
> with the auditing ability I plan on building into the admin section.
>
> Never heard of USE AdventureWorks. Is that a SQL system function?
> Or just a table in the examples database?

USE [1] is a Transact SQL statement (MS call their SQL language T-SQL).

AdventureWorks is one of the demo DBs MS supply to help people learn
about using their server.


Regards,

Richard.

[1] http://msdn.microsoft.com/en-us/library/ms188366.aspx

--
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php