Page 1 of 1

SQL Where clause

Posted: Tue Jul 11, 2006 10:13 pm
by adult.swim
I've just found the code I've been looking for that Uses PHP to search a MySQL database and return paged results.

I've been modifying the code to fit the specifiactions that I need. My question is what is the significance of:

\"%$trimmed%\"

in the SQL Query. I dont know how the backslashs work, or what they mean in this context. If u know please explain, Thank You.

Code: Select all

<?php

  // Get the search variable from URL
  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","username","password"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("database") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select * from the_table where 1st_field like \"%$trimmed%\"  
  order by 1st_field"; // EDIT HERE and specify your table and field names for the SQL que

Posted: Tue Jul 11, 2006 10:19 pm
by RobertGonzalez
I believe that is an inappropriate use of double quotes in a SQL string. Replace them each \" with a single quote ('). The run it.

FYI the backslash is a PHP escape character when including double quotes inside of double quoted strings (and single quotes inside of single quoted strings).

\" usage

Posted: Tue Jul 11, 2006 10:33 pm
by adult.swim
o, ok thank you. \" is like using ', but in that particular context, its a bad usage of it.

Posted: Tue Jul 11, 2006 10:39 pm
by bdlang
For the sake of argument, you technically can use the slash to escape a double quote within a set of double quotes, and single within single. It just looks messy. The SQL statement sent to MySQL will look like

Code: Select all

SELECT * FROM the_table WHERE 1st_field LIKE "%somedata%" ORDER BY 1st_field
Just as Everah mentioned, I prefer using single within double, looks better and it's obvious what is being sent to the database. As long as you understand that the variable $trimmed (or any variable included in the SQL string) is evaluated only within double quotes, otherwise you have to escape the string and concatenate, like

Code: Select all

$query = 'select * from the_table where 1st_field like "%' .$trimmed. '%" order by 1st_field';
I don't know that this method looks any better, but it's certainly possible. You can also do

Code: Select all

$query = "select * from the_table where 1st_field like '%{$trimmed}%' order by 1st_field";
This is especially handy when using an array index, such as $somearray['value']. The curly braces { } protect the single quotes within the array index.

Posted: Tue Jul 11, 2006 10:51 pm
by RobertGonzalez
I prefer to use single quotes to double quotes. There was a user on PHP.net that made some benchmarks and found that on average, a single quoted string parsed about four times faster than it's equivalent double quoted string. Things got worse when parsing vars in double quoted strings. For speed, I'd recommend single quotes. In fact, I only use double quotes in SQL strings and I will be moving away from that as I develop my current and future projects.

PS Concatenating single quoted strings and vars was still way faster than straight double quoted strings and much, much faster than concatenating double quoted strings and vars. If I can find the benchmark I will post it.

Posted: Tue Jul 11, 2006 10:55 pm
by bdlang
Everah wrote:I prefer to use single quotes to double quotes. There was a user on PHP.net that made some benchmarks and found that on average, a single quoted string parsed about four times faster than it's equivalent double quoted string. Things got worse when parsing vars in double quoted strings. For speed, I'd recommend single quotes. In fact, I only use double quotes in SQL strings and I will be moving away from that as I develop my current and future projects.

PS Concatenating single quoted strings and vars was still way faster than straight double quoted strings and much, much faster than concatenating double quoted strings and vars. If I can find the benchmark I will post it.
Exactly.