Messages

1

General PHP Help / Re: SELECT Query Not Working

« on: April 20, 2011, 10:56:45 AM »

Actually, this SQL statement is going to get much more complicated, not simpler, but I need to build it up and test it piece by piece. I corrected the unbalanced parentheses, but I am still not getting any results when I add in the DOB_SQL criteria. I'm thinking of changing this to a UNION instead of the OR. Thoughts?

2

General PHP Help / Re: SELECT Query Not Working

« on: April 20, 2011, 12:24:35 AM »

SELECT SOR_ID, FIRST_C, FIRST_MIDDLE_NAME, FULL_NAME, DOB_SQL, if(LOCATE(' ', first_middle_name)=0, first_middle_name, substr(first_middle_name, 1, LOCATE(' ', first_middle_name))) as FIRST_NAME, if(LOCATE(' ', first_middle_name)=0, '', substr(first_middle_name, LOCATE(' ', first_middle_name)+1)) as MIDDLE_NAME, if(LOCATE(' ', first_middle_name)=0, '', substr(first_middle_name, LOCATE(' ', first_middle_name)+1, 1)) as MIDDLE_C FROM offenders WHERE (LAST_NAME LIKE '%SMITH%' AND (GENDER='M' OR GENDER IS NULL) ) HAVING (( DOB_SQL='1959-05-16' AND FIRST_C='T' AND MIDDLE_C='A' AND MIDDLE_C<>'' ) OR ( (DOB_YEAR='1959' AND FIRST_NAME='TIMOTHY' AND MIDDLE_NAME='ALAN' ))

3

General PHP Help / Re: SELECT Query Not Working

« on: April 19, 2011, 09:04:25 PM »

I changed the MIDDLE_C from being referenced in the WHERE clause to being referenced in a HAVING clause and now the SELECT query works. However, now I am having another problem. I now have a compound HAVING clause, basically my SELECT statement is now:

SELECT * FROM offenders WHERE 'some criteria' HAVING ( ('criteria a') OR ('criteria b') )

WIthout 'criteria b' I get 2 records in my result set. If I add in 'criteria b' I now get 0 records -- but it's part of an OR condition. That doesn't make any sense.

4

General PHP Help / SELECT Query Not Working

« on: April 19, 2011, 06:59:56 PM »

I have the following SELECT statement, which works:

SELECT FIRST_C, FIRST_MIDDLE_NAME, FULL_NAME, DOB_SQL, if(LOCATE(' ', first_middle_name)=0, '', substr(first_middle_name, LOCATE(' ', first_middle_name)+1, 1)) as MIDDLE_C FROM offenders WHERE (LAST_NAME LIKE '%SMITH%' AND (GENDER='M' OR GENDER IS NULL) ) AND ( DOB_SQL='1959-05-16' AND FIRST_C='T' )

However, when I modify it as follows:

SELECT FIRST_C, FIRST_MIDDLE_NAME, FULL_NAME, DOB_SQL, if(LOCATE(' ', first_middle_name)=0, '', substr(first_middle_name, LOCATE(' ', first_middle_name)+1, 1)) as MIDDLE_C FROM offenders WHERE (LAST_NAME LIKE '%SMITH%' AND (GENDER='M' OR GENDER IS NULL) ) AND ( DOB_SQL='1959-05-16' AND FIRST_C='T' AND MIDDLE_C='A')

it yields no results. I know there is a record that matches the criteria specified. What am I doing wrong?

If I list out the field values, I see the matching value in the MIDDLE_C named field.

Thanks.

5

General PHP Help / Re: Using strpos or strrpos in a SELECT statement

« on: April 15, 2011, 06:41:41 PM »

Then why does the substr function work in my SELECT statement?

6

General PHP Help / Using strpos or strrpos in a SELECT statement

« on: April 14, 2011, 04:00:27 PM »

I am trying to do the following SELECT statement:

SELECT first_c, substr(first_middle_name, strrpos(first_middle_name, " "), 1) as middle_c, first_middle_name, full_name FROM offenders LIMIT 0,50

It just fails. I need to extract the 1st character after the blank space in the first_middle_name field.

Thanks.