Splitting my query

I have the following query which I want to split onto multiple lines to make it easier to understand, but I’m not sure where the new lines should go.

$query = "SELECT * FROM client WHERE name LIKE '%{$_POST['search_name']}%' OR phone LIKE '%{$_POST['search_name']}%' OR address LIKE '%{$_POST['search_name']}%' OR email LIKE '%{$_POST['search_name']}%' ";

and yes, I know I shouldn’t be using SELECT * - that’s one of the reasons for wanting to split it.

So the general rule of thumb is that you would split on the capital words (keywords).There’s a couple of exceptions that I would use (keeping the LIKE’s with their field names, particularly).

SELECT * 
FROM client 
WHERE name LIKE '%{$_POST['search_name']}%' 
OR phone LIKE '%{$_POST['search_name']}%'
OR address LIKE '%{$_POST['search_name']}%' 
OR email LIKE '%{$_POST['search_name']}%' 

Or, if you follow Rudy’s style of spacing, the first word of each line occupies 7 spaces (6 letters and a space…)

SELECT * 
FROM   client 
WHERE  name LIKE '%{$_POST['search_name']}%' 
OR     phone LIKE '%{$_POST['search_name']}%'
OR     address LIKE '%{$_POST['search_name']}%' 
OR     email LIKE '%{$_POST['search_name']}%' 
3 Likes

Also if you’re reading it out loud, the line breaks are sort of where the natural pauses in the sentence are… “SELECT * (pause) FROM client (pause) WHERE name like X (pause) OR phone like X…”

1 Like

Thanks @m_hutley. That works for me :wink: I’ve seen queries split with the keywords SELECT and WHERE on separate lines, but that’s maybe going too far…

I personally like to see the lines as… i want to say mini-sentences, but it’s a bit of a weird one to English speaking tongues because it’s a sentence in the form [verb] [subject]. It can get a bit confusing though…

SELECT a,b,c //simple
FROM   r //simple
JOIN   s ON r.id = s.rid //singular thought, even though it has a second verb.
WHERE  r.field1 = z //singular thought
AND    (    //beginning parenthetical statement: Implied "And WHERE all of..."
         s.field2 = y //Implied verb WHERE
  OR     s.field2 LIKE "%jones%" //Singular thought.
) //End parenthetical statement.

I’m close to what @m_hutley likes, except I take it a step further after I was introduced to this by Rudy. I put each select element on it’s own line. This makes it quicker to find what’s selected, especially if you have complex items in the select (CASE, IF, even COUNT, MIN and MAX)

I also do INNER JOIN vs JOIN. I know they’re the same but it’s personal preference.

SELECT a
     , b
     ,  CASE 
         WHEN c > 10 THEN 'More'
         WHEN c < 10 THEN 'Less'
         ELSE 'Equal'
        END AS comparison //simple
  FROM r //simple
 INNER JOIN s ON r.id = s.rid //singular thought, even though it has a second verb.
 WHERE r.field1 = z //singular thought
   AND (    //beginning parenthetical statement: Implied "And WHERE all of..."
        s.field2 = y //Implied verb WHERE
     OR s.field2 LIKE "%jones%" //Singular thought.
        ) //End parenthetical statement.
3 Likes

You really should use a prepared statement for this instead of using $_POST in the query directly. It’s super easy to do SQL injection with the code you’ve shown.

2 Likes

Oooh, I hadn’t spotted that!

1 Like

Okay, so I tried the following, figuring that by using named parameters I wouldn’t need to repeat the same thing twice.

if ($_POST['search_name'] == '') {
  $query = "SELECT client_id, name, phone, address, email FROM client";
} else {
  $query = "SELECT client_id, name, phone, address, email 
  FROM client
  WHERE name LIKE '%{:searchKey}%'
  OR phone LIKE '%{:searchKey}%'
  OR address LIKE '%{:searchKey}%'
  OR email LIKE '%{:searchKey}%' ";
}
$stmt = $pdo->prepare($query);
$stmt->bindParam('searchKey', $_POST['search_name']);
$stmt->execute();
$clients = $stmt->fetchAll();

But I got a fatal error on the execute()

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 25 column index out of range in C:\laragon\www.…\getClient.php:14 Stack trace: #0 C:\laragon\www.…\getClient.php(14): PDOStatement->execute() #1 {main} thrown in C:\laragon\www.…\getClient.php on line 14

Take a look at this…
Also, you are duplicating part of the query. You don’t need the else.

1 Like

Doing that, using the same named place-holder more than once in a query, only works when using emulated prepared queries, which should be avoided, both because the operation of the emulator is not identical to a real prepared query, and if you haven’t set the character set when you make the database connection to match your database tables, you are still open to sql injection, defeating the main reason for using a prepared query in the first place.

This won’t work for the case of no place-holder in the query. However, supplying an array of the input values to the ->execute(…) call, does work when there are no place-holders in the query and the supplied array is empty. You would need to build the array of input parameters inside the conditional logic that’s also building the WHERE … part of the query, so that the array will be empty for the case of no WHERE clause.

The wild-card characters are part of the value, not the sql syntax and only the place-holder goes into the sql query statement, without any quotes around it.

While repetition should be avoided whenever possible, in this case, benefiting from using a true prepared query is worth it. array_fill() could be used to build an array with the repeated entries.

2 Likes

For example -

<?php

// input parameters. default to an empty array.
$params = [];
// where term. default to empty/none.
$where = '';

// build any where clause
if($_POST['search_name'])
{
	$where = " WHERE name LIKE ?
		OR phone LIKE ?
		OR address LIKE ?
		OR email LIKE ?";
	$params = array_fill(0, 4, "%{$_POST['search_name']}%");
}

// build the complete query
$query = "SELECT client_id, name, phone, address, email
 FROM client
 $where
 ORDER BY name";

$stmt = $pdo->prepare($query);
$stmt->execute($params);
$clients = $stmt->fetchAll();
4 Likes

Many thanks @mabismad. That does the trick.

4 posts were split to a new topic: Emulated prepared statements VS prepared statements

Just to throw another alternative into the mix, I generally use Doctrine DBAL. For this case I would use their Query Builder.

That would look like this:

$queryBuilder = $connection->createQueryBuilder();

$stmt = $queryBuilder->select([
    'some',
    'fields'
)->from('client');

if (isset($_POST['search_name']) && $_POST['search_name'] !== '') {
    $queryBuilder->where(
        $queryBuilder->expr()->or(
            $queryBuilder->expr()->like('name', ':searchKey'),
            $queryBuilder->expr()->like('phone', ':searchKey'),
            $queryBuilder->expr()->like('address', ':searchKey'),
            $queryBuilder->expr()->like('email', ':searchKey'),
        )
    );
    $queryBuilder->setParameter('searchKey', '%' . $_POST['search_name'] . '%');
}

$results = $queryBuilder->fetchAllAssociative();

See https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html

This gets more useful as queries get more complex.

1 Like

As I’m using SQLite, I found the following also does the job:

$where = '';
$params = [];

if ($_POST['search_name']) {
  $where = "WHERE name || '^' || phone || '^' || address || '^' || email LIKE ?";
  $params = ["%{$_POST['search_name']}%"];
}

$query = "SELECT client_id, name, phone, address, email
  FROM client
  $where
  ORDER BY name";
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$clients = $stmt->fetchAll();