Two id's where clause clarification

PHP
1

Hi,
I have been working through this book: PHP and MySQL: Novice to Ninja. All good so far but I’m struggling to get my head around the section below. Does anyone have a CLEAR explanation?

*** page 253-4

function updateJoke($pdo, $fields) {
$query = ' UPDATE `joke` SET ';
foreach ($array as $key => $value) {
$query .= '`' . $key . '` = :' . $key . ','
}
$query = rtrim($query, ',');
$query .= ' WHERE `id` = :primaryKey';
// Set the :primaryKey variable
$fields['primaryKey'] = $fields['id'];
query($pdo, $query, $fields);
}

You’ll notice I set the primaryKey key manually with this line:

/ Set the :primaryKey variable
$fields['primaryKey'] = $fields['id'];

This is so that the WHERE clause in the query is provided with the relevant ID to
update. :id can’t be used, because it has already been used in the query, and each
parameter needs a unique name.
With this version of the updateJoke function, it’s now possible to run it as we
designed earlier:

updateJoke($pdo, [
'id' => 1,
'joketext' => '!false - it\'s funny because it\'s true']
);

Thanks in advance.
Mike

2

Are you (or the Author of the book) sure that you should be editing/updating the id column? The id(identifier) that’s defined in a table is used to uniquely reference that row of data, and provide a reference for related data in other tables. If you alter the id value, you are breaking the references to that row of data. Once an id has been assigned, it should never be altered.

3

Hi.

Thank you for your response.

I fully understand your point: yes pk id’s should never be altered once created.

I don’t think this is what the author is saying though. I think he is using the ID to identify which record to update (standard for an update query) not to actually update the ID itself.

This seems to be the crux of the logic:

"This is so that the WHERE clause in the query is provided with the relevant ID to
update. :id can’t be used, because it has already been used in the query, and each
parameter needs a unique name."

I’m trying to follow the logic of how the function is working “line by line” but can’t get me head around where the two id’s are (the row to be edited only has one?)

This is where I am…
If we call the function like this:

updateJoke($pdo, [
'id' => 1,
'joketext' => '!false - it\'s funny because it\'s true']
);

the function automatically generates this:

UPDATE `joke` SET `id`  = :id, `joketext` = :joketext WHERE `id` = :primaryKey

What the author is saying is that we can’t have it automatically generate this:

UPDATE `joke` SET `id`  = :id, `joketext` = :joketext WHERE `id` = :id

because this query would be invalid (two uses of “id”). So we have to use a placeholder for the id while the query is generated but then change the placeholder to the value we already have from $fields.

I suppose the function does update the id but it’s being updated to what it was already anyway so no problem. The author is trying to develop a generic function which can handle any arguments (hence the unusual way of handling the PK)

My main problem is that $fields has been passed in as an argument and the function has already run its course using its arguments so changing the argument $fields at the end of the function isn’t going to change the query as the query has already been generated. I’m wrong but don’t understand why I’m wrong.

Mike

4

The only way the Author’s statement is true, that the :id place-holder has already been used in the query, at the point of building the WHERE term, is if it was put into the SET … term. Since you are in agreement that it should not be in the SET … term, because you should never be updating/editing it, then the Author’s statement is false.

The code should NOT blindly loop over all the field names to build the SET … term (which I hope will never come from external data, as that would allow sql injection, since they are being put directly into the query.)

To do this properly, with a general-purpose, reusable update function, the input data to the function would need to supply the table name, distinguish between field(s) that are part of the SET term, and field(s) that are part of the WHERE term.

5

Well for starters, you’re talking about a SP book, so… why don’t we ask the author what his intentions were?

@TomB , can you take a butcher’s at the page in question? I’m afraid I can’t identify its location, being that I have the electronic version.

6

The query has been generated, but when you use prepare/execute, the query and values are supplied separately.

Going back a stage and ignoring everything there for a second and just looking at PDO’s prepared statements, you could do this:

$stmt = $pdo->prepare('UPDATE `joke` SET `id`  = :id, `joketext` = :joketext WHERE `id` = :primaryKey');

The query has been prepared (and even sent to the databases–possibly–) but it hasn’t been run yet as there are placeholders instead of real data.

At a later stage, you provide the corresponding values for each of the placeholders and execute the query:

$fields = [
'id' => 1,
'joketext' => '!false - it\'s funny because it\'s true',
'primaryKey' => 1
]

$stmt->execute($fields);

In the example above:

function updateJoke($pdo, $fields) {
$query = ' UPDATE `joke` SET ';
foreach ($array as $key => $value) {
$query .= '`' . $key . '` = :' . $key . ','
}
$query = rtrim($query, ',');
$query .= ' WHERE `id` = :primaryKey';
// Set the :primaryKey variable
$fields['primaryKey'] = $fields['id'];
query($pdo, $query, $fields);
}

The query is generated in $query but nothing gets sent to the database until the very last line query($pdo, $query, $fields); so when this line: $fields['primaryKey'] = $fields['id']; runs, the query hasn’t been executed yet, only generated as a string.

1 Like
7

Apologies for the delay in replying and thanks very much for chipping in here Tom.

That’s why I was wrong: “nothing gets sent to the database until the very last line”. I hadn’t fully understood the effect of using prepared statements.

Perfect explanation.

Cheers,
Mike

1 Like
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.