Results 1 to 11 of 11
Thread: SQL query help?
-
06-27-2006, 05:44 PM #1Web Hosting Master
- Join Date
- Mar 2003
- Location
- Duluth MN
- Posts
- 3,863
SQL query help?
I'm trying to figure out how to format the following query to do the following:
I have 2 tables, one containing a list of locations and IP addresses:
[id][location_id][ip0][ip1][ip2]
And then a table containing pins, or access codes
[id][location_id][pin][active]
I need a query that will return the pin row based on the following details.
I'm given a pin, and an IP address. So I need to match the pin to the location of the IP address. The IP address could be one of the 3 in the locations table.
It's been a LONG time since I've done this kind of join/sql query. So any help in the right direction is much appreciated!
-
06-27-2006, 07:03 PM #2Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,303
PHP Code:$query = "SELECT loc.*, p.* FROM `pins` p
INNER JOIN `locations` loc ON (p.location_id = loc.location_id) WHERE p.pin ='$pin' AND (loc.ip0 ='$ip' OR loc.ip1='$ip' OR loc.ip2 = '$ip') LIMIT 1";
-
06-27-2006, 07:42 PM #3Junior Guru
- Join Date
- Dec 2002
- Location
- Dallas, TX
- Posts
- 210
Originally Posted by orbitz
Or maybe he should hire a programmer since he didn't say his abilities.My Name is Joe!
ICQ: 280 889 133
-
06-27-2006, 07:47 PM #4Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,303
Originally Posted by JoeBannonLast edited by orbitz; 06-27-2006 at 07:54 PM.
-
06-27-2006, 08:05 PM #5Junior Guru
- Join Date
- Dec 2002
- Location
- Dallas, TX
- Posts
- 210
What ego? Oh, that's right, you like to assume.
BTW, it' wasn't off topic. I gave advice ;-).My Name is Joe!
ICQ: 280 889 133
-
06-27-2006, 08:28 PM #6Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,303
your advice works! such a kiddo. I stopped here
-
06-27-2006, 09:17 PM #7Disabled
- Join Date
- Apr 2006
- Location
- Devon, UK
- Posts
- 193
Can I ask what the point of having Limit 1 at the end is? I mean if it does select more then 1 its wrong anyway? How is it helping?
-
06-27-2006, 09:22 PM #8Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,303
Originally Posted by KalyseLast edited by orbitz; 06-27-2006 at 09:26 PM.
-
06-28-2006, 10:34 AM #9Web Hosting Master
- Join Date
- Mar 2003
- Location
- Duluth MN
- Posts
- 3,863
Location ID is not unique, but the Pin + Location combination is unique.
Thanks for the JOIN syntax, I was having a beast of a time trying to remember the exact syntax, and was having problems trying to figure it out based on the mysql documentation at http://dev.mysql.com
As for questioning my programming ability, I take that as a direct putdown against me. For you assume because I have a question about SQL query syntax, that I do not know how to program. Every programmer has a brain fart every now and then, and good programmers know when to ask for help, rather than waste valuable (billable) time because they have an ego problem and are afraid to ask for help.
FYI, this query is being used in a mysql 5.0 Stored Procedure being called by a RADIUS server for authentication, with a RoR app managing the datbase on the back-end.
-
06-28-2006, 10:40 AM #10Web Hosting Master
- Join Date
- Mar 2004
- Posts
- 1,303
Originally Posted by Utaria-Aaron
-
06-28-2006, 01:26 PM #11Web Hosting Master
- Join Date
- Dec 2002
- Posts
- 1,304
He writes the same garbage in all his recent posts. Carry on ...
"The only difference between a poor person and a rich person is what they do in their spare time."
"If youth is wasted on the young, then retirement is wasted on the old"