SQL Where clause

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
adult.swim
Forum Newbie
Posts: 9
Joined: Sun Jul 09, 2006 8:00 pm

SQL Where clause

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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).
adult.swim
Forum Newbie
Posts: 9
Joined: Sun Jul 09, 2006 8:00 pm

\" usage

Post by adult.swim »

o, ok thank you. \" is like using ', but in that particular context, its a bad usage of it.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
Post Reply