Return data from more than one column

[mackin]

 

 

 $searchterm_standard_listing = "-1";
    if (isset($_POST['searchterm'])) {
      $searchterm_standard_listing = $_POST['searchterm'];
    }
    mysql_select_db($database_contractors, $contractors);

    $query_standard_listing = sprintf("SELECT * FROM hotels WHERE est_town LIKE  %s AND featured = 'N' AND premier = 'N'", GetSQLValueString($searchterm_standard_listing, "text"));

    $standard_listing = mysql_query($query_standard_listing, $contractors) or die(mysql_error());
    $row_standard_listing = mysql_fetch_assoc($standard_listing);

    $totalRows_standard_listing = mysql_num_rows($standard_listing);

[quote][/quote]

 

 

I have this code which dreamweaver "helped" me create - i want it to use the searchterm to return the contents of 2 more columns (est_county and est_postcode) I have tried numerous ways to make this work such as

 

 

 

 WHERE est_town OR est_county OR est_postcode LIKE ........

 

but that returns Warning: sprintf() [function.sprintf]: Too few arguments.

 

Any suggestions to make this work?

[DavidAM]

sprintf("SELECT * FROM hotels WHERE est_town LIKE  %s AND featured = 'N' AND premier = 'N'", 
  GetSQLValueString($searchterm_standard_listing, "text"))

"%s" is a placeholder that is replaced by the value (in this case returned by "GetSQLValueStrin()"

 

Your proposed SQL code is not valid

WHERE est_town OR est_county OR est_postcode LIKE ........

 

You would have to use

 

WHERE est_town LIKE ... OR est_county LIKE ... OR est_postcode LIKE ...

 

To do that with sprintf() you can use numbered placeholders:

 

sprintf("SELECT * FROM hotels 
WHERE (est_town LIKE '%1\$s' OR est_county LIKE '%1\$s' OR est_postcode LIKE '%1\$s') 
AND featured = 'N' AND premier = 'N'", 
  GetSQLValueString($searchterm_standard_listing, "text"))

 

which will put the first value in each "%1$s" placeholder. Take note of the parenthesis to separate the OR's from the AND's; and the backslashes to keep PHP from trying to interpret the "$s" as a variable. Also, note that you have to use wildcards to make the LIKE effective. The "%" symbol will match any number of characters.  So you would use "%me%' to match the term "me" with anything before it and/or anything after it. Also, the term needs to be in quotes. Without seeing the GetSQLValueString() definition, we can't tell if it is returning the "%" and/or the quotes. The final SQL would look something like:

 

SELECT * FROM hotels 
WHERE (est_town LIKE '%here%' OR est_county LIKE '%here%' OR est_postcode LIKE '%here%') 
AND featured = 'N' AND premier = 'N'

 

Using LIKE with a "%" at the beginning of the term will NOT allow the query to use an index. So it will have to scan every row in the table. Using LIKE without any "%" is useless and would be better written as "=" (instead of LIKE).

[mackin]

SELECT * FROM hotels WHERE (est_town LIKE %1\$s OR est_county LIKE %1\$s OR est_postcode LIKE %1\$s) AND featured = 'N' AND premier = 'N'", GetSQLValueString($colname_standard_listing, "text")

 

Cheers David - I have gone with the above code - had to remove the apostrophes around the %1\$s for it to work but now seems to be working like a dream - will have to test it to death now - thanks very much for your help

[DavidAM]

Glad to hear it works.

 

Your GetSQLValueString() function may be returning the string with the quotes already around it. I use one like that as well.