mysqli_query problems

[raytravel]

I wrote a simple mysql record update.

mysqli_query($link, "UPDATE photo01 SET PhotoText='abcd' WHERE id= '1' ");

Work fine until I use a variable:

mysqli_query($link, "UPDATE photo01 SET PhotoText= $ZZ  WHERE id= $x ");

If $ZZ contains only number it updated the cell.

ANY letters does not.

The cell is text and standard US ASCII

I tried:

mysqli_query($link, "UPDATE photo01 SET PhotoText={$ZZ} WHERE id={$x}");

same problem

 

Here is my loop:

<?php
for ($x = 0; $x < 131 ;$x++) {
$ZZ = $_POST["z".$x];
if ($ZZ != '') {

mysqli_query($link, "UPDATE photo01 SET PhotoText={$ZZ} WHERE id={$x}");
}
}

?>

[mac_gyver]

the single-quotes around the 'abcd' value in your first query have significance to the query. they indicate literal string data. you need to use that same syntax in the query for the case where php is supplying the string data value.

[raytravel]

This finally worked:

<?php

for ($x = 0; $x < 131 ;$x++)
{
$ZZ = $_POST["z".$x];
if ($ZZ != '')
{echo $x.' = '.$ZZ.'<br>';

$sql='UPDATE photo01 SET PhotoText = ? WHERE id = ?';
$PhotoText = $ZZ;
$id_equal_to = $x;
 
/* Prepare statement */
$stmt = $conn->prepare($sql);
if($stmt === false) {
  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
}
 
/* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */
$stmt->bind_param('si',$PhotoText,$id_equal_to);
 
/* Execute statement */
$stmt->execute();
 
$stmt->close();
}
}
?>

[mac_gyver]

for a prepared query, the only code that would be inside the loop are the statements setting the bound variables with each set of data and the $stmt->execute();.

[Jacques1]

This actually leaves a lot to be desired, even if it may “work” (whatever that means).

• Numbering variables or keys is awful, don't do that. We have arrays for this purpose: Simple use the name photo_descriptions[] (the brackets are important) for all input fields, and $_POST['photo_descriptions'] will be an array of all values.
• Please don't tell me you've created a separate table for every single photo.
• When you ask people the enter 131 friggin' photo descriptions, it's time to worry about write conflicts. Imagine the following scenario: User A and user B both open the form (it could also be one person with two tabs). A makes major changes and saves them. B only changes a few descriptions and also saves the form. Now B has unknowingly overwritten the entire work of A. I'd be pissed.
• Do you really want to run all 131 queries even if the user has only changed a single description?
• There's absolutely no need to manually check every query for errors. MySQLi can report errors itself.