PHP/mySQL - using result from 'CONCAT' and 'AS' in 'LIKE' clause

Question

Hi I have the following code;

if( ! empty( $post['search-bar'] ) ) {
    $search_data = preg_replace("#\s\s#is", '', preg_replace("#[^\w\d\s+]#is", '', $post['search-bar'] ) );
    $data_array = explode( " ", $search_data );
    $data_array = "'%" . implode( "%' OR '%", $data_array ) . "%'"; 
    $query =  "
SELECT CONCAT( PROFILE_PROFFESION, FIRST_NAME, LAST_NAME, DISPLAY_NAME) AS 'STRING'
FROM `" . ACCOUNT_TABLE . "` 
WHERE STRING LIKE ( " . $data_array . " ) 
  AND BUSINESS_POST_CODE LIKE '" . substr(P_BUSINESS_POST_CODE, 0, 4) . "%'";

    $q = mysql_query( $query, $CON ) or die( "_error_" . mysql_error() );   
    if( mysql_num_rows( $q ) != 0 ) {
        die();
    }
}

Problem is I want to use the temp col 'STRING' in the where clause but is returning 'unknown coloumn STRING Can any one point me in the right direction, regards Phil

I have printed out the query;

SELECT PROFILE_PROFFESION, FIRST_NAME, LAST_NAME, DISPLAY_NAME 
FROM nnn_accounts 
WHERE 
  CONCAT( PROFILE_PROFFESION, FIRST_NAME, LAST_NAME, DISPLAY_NAME) 
  LIKE ( '%web%' OR '%design%' ) 
AND BUSINESS_POST_CODE LIKE 'NG19%'

Mitch Dempsey · Accepted Answer · 2010-04-20 06:03:38Z

1

Wrap STRING in ` instead of '

Should be: ..AS `STRING`

And then anywhere you refer to STRING should be `STRING`

Edit: Here this should solve it:

SELECT CONCAT( PROFILE_PROFFESION, FIRST_NAME, LAST_NAME, DISPLAY_NAME) AS `STRING`
FROM `" . ACCOUNT_TABLE . "` 
WHERE BUSINESS_POST_CODE LIKE '" . substr(P_BUSINESS_POST_CODE, 0, 4) . "%'"
HAVING `STRING` LIKE ( " . $data_array . " ) ;

edited Apr 20, 2010 at 6:03

answered Apr 20, 2010 at 5:42

Mitch Dempsey

40.3k6 gold badges72 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Phil Jackson Over a year ago

still getting Unknown column 'STRING' in 'where clause'

Mitch Dempsey Over a year ago

Try using HAVING `STRING` LIKE ( " . $data_array . " ) (Do this after the WHERE clause

Phil Jackson Over a year ago

thanks for the input. Im going to have to mark yours as correct due to my question, but does the same as Col. Shrapnel instructed. My problem being i am not getting correct results. how come web or design will return results but web design will not??

Your Common Sense · Accepted Answer · 2010-04-20 05:59:26Z

1

Yes, it looks like you just can't use the temp col in the where clause just specify it as is:

WHERE CONCAT( PROFILE_PROFFESION, FIRST_NAME, LAST_NAME, DISPLAY_NAME) LIKE '%$data_array%')

answered Apr 20, 2010 at 5:59

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

3 Comments

Phil Jackson Over a year ago

I get no errors but getting no results. I have printed out the query; SELECT PROFILE_PROFFESION, FIRST_NAME, LAST_NAME, DISPLAY_NAME FROM nnn_accounts WHERE CONCAT( PROFILE_PROFFESION, FIRST_NAME, LAST_NAME, DISPLAY_NAME) LIKE ( '%web%' OR '%design%' ) AND BUSINESS_POST_CODE LIKE 'NG19%'

Phil Jackson Over a year ago

if i enter web i get results. if i type in design i get results but web design gets no results.

Your Common Sense Over a year ago

your use of LIKE clause is just ridiculous

codaddict · Accepted Answer · 2010-04-20 05:45:59Z

0

Remove the quotes around 'STRING'

answered Apr 20, 2010 at 5:45

codaddict

457k83 gold badges501 silver badges537 bronze badges

Comments

Phil Jackson · Accepted Answer · 2010-04-20 06:51:32Z

0

The syntax that was more suitable was

SELECT CONCAT( PROFILE_PROFFESION, FIRST_NAME, LAST_NAME, DISPLAY_NAME) AS `STRING` FROM `tablename` WHERE BUSINESS_POST_CODE LIKE 'NG19%' HAVING `STRING` LIKE '%web%' OR `STRING` LIKE '%design%'

answered Apr 20, 2010 at 6:51

Phil Jackson

10.3k23 gold badges99 silver badges131 bronze badges

Collectives™ on Stack Overflow

PHP/mySQL - using result from 'CONCAT' and 'AS' in 'LIKE' clause

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related