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']}%'
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…”
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.
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.
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
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.
<?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();