complex query

[adzie]

Is it possible to run this query.

 

each user has a group ID for example

 

abc0003A the ABC indentifies the group 0003 identifies the user and A identifies the location of the user

 

I'd like to run a query which identifies members by the group and then can break those members down again by location

 

I suspect this is not possible due to the 0003 in the middle.

 

I'll include a idea of how the database looks so you can tell me if its possible

 

member id name email password type userid

 

100001 john john@john.com password 1 egc0001A

100002 sam sam@sam.com password 1 egc0002B

100003 anne anne@anne.com password 4 abc0003A

100004 sally sally@sally.com password 1 egc0004D

100005 mike mike@mike.com password 3 def0005C

100006 tom tom@tom.com password 3 def0006A

100007 keith keith@keith.com password 2 reg0007D

 

[AV1611]

Sure,

 

Prolly someone smart like Barand could do it in one query with substrings alone, but I'll take a crack at it...

 

first select based on the substring of the abc the shoot off query from within the results with the other substring

 

select * from table where subst(`field`,0,3)='egc' and subst(`field`,-2,2) = '1A' from table...

 

would find john@john.com's record

 

[adzie]

ok so as suggested

 

do this look like it will work?

 

{

$sql9="select * from members where subst 'callsign`,0,3='abc' and subst `callsign`,-2,2  = 'V' from members";

$query9 = mysql_query($sql9);

$members9 = mysql_numrows($query9);

}

[AV1611]

Did this work for you?

[BlueSkyIS]

it shouldn't have. $sql9 is invalid SQL (2 from's on a single table)

[GingerRobot]

Im not entirely sure how you want to 'group those members by location again'

 

If you were to run a query such as:

SELECT *,SUBSTRING(`userid`,-1) AS location FROM `users` WHERE SUBSTRING(`userid`,1,3)='egc' ORDER BY location

 

You'd then have all the users with a particular group (egc in this case), ordered by their location. When you outputted the results, you could then group the users together by location by ending each 'group' when the location changes.

[AV1611]

it shouldn't have. $sql9 is invalid SQL (2 from's on a single table)

 

I mean't what I offered. I didn't look at what he actually typed.