Can't update web page

$sql = "UPDATE `recent_happenings` 
SET (`recent_title` , `recent_date`, `recent_pic_file`) 
VALUES ('$recent_title' , '$recent_date' , '$target_path'  
WHERE pic_number = pic_number)"; 

Comes up with this error! UpDate Recent Pictures

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(recent_title , recent_date, recent_pic_file) VALUES (‘liked’ , ‘2019-12-1’ at line 1

Are you sure you have your 2nd closing bracket in the right place?

Since that’s not the correct syntax for an update query, perhaps do what the error message suggests and check the documentation -

NEVER EVER put variables in your query. You need to use Prepared Statements. If you are not using it already I would highly recommend you use PDO. Here is a good tutorial to get you going.

This is the proper syntax for UPDATE.

UPDATE table_name
SET column1 = value1 , column2 = value2 , …
WHERE condition ;

$sql = “UPDATE recent_happenings SET [recent_title=$recent_title] [,recent_date=$recent_date] [,recent_pic_file=$target_path] WHERE [pic_number = pic_number]”;

Still getting this error.
UpDate Recent Pictures

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘[recent_title=likes] [,recent_date=2019-12-12] [,recent_pic_file=uploads/i’ at line 1

Why have you got square brackets there?

The [] that are in the syntax prototype definition are not part of the actual sql syntax. They indicate optional elements. I’m not sure if the MariaDb documentation contains a definition of these. Here’s the definition from the MySQL documentation - This is also where reading examples showing actual usage comes in handy.

One thing I’d suggest is trying to run your UPDATE statement directly on the database for starters to make sure you get the syntax correct (caveat: never in production, of course - but then, you’re not developing your PHP code in production, either. Are you.). Because if you can’t get it to work there, it probably won’t work when you try to translate your SQL to be used by PHP.

So, like other people already said, there are no square brackets in an UPDATE statement.

I only use MySQL, but I believe MariaDB is basically the same as it’s a fork. So here’s the corresponding MySQL UPDATE statement:

UPDATE recent_happenings SET recent_title = 'blah', recent_date = 'blah1', recent_pic_file = 'blah2' WHERE pic_number = 1;

(An INSERT will use parentheses, and I wonder if you’re getting confused with that type of command.)

We don’t know whether you are using mysqli or pdo in your PHP. Here’s how you might do it using mysqli (for some reason I assume you’re using this):

$conn = mysqli_connect(host, username, password, database); // use your params

$sql = "UPDATE recent_happenings SET recent_title = '".
mysqli_real_escape_string($conn, $recent_title).
"', recent_date = '".mysqli_real_escape_string($conn, $recent_date).
"', recent_pic_file = '".
mysqli_real_escape_string($conn, $recent_pic_file).
"' WHERE pic_number = '".
mysqli_real_escape_string($conn, $pic_number).
mysqli_query($conn, $sql);

It looks horrible, and it is horrible. mysqli_real_escape_string will usually be required to make the SQL work with your variables - it rewrites strings that have special characters so that they will be read correctly by the database machinery.

Sometimes you can get away without mysqli_real_escape_string (like for quick and dirty testing). But it won’t prevent SQL injection attacks. So, like @benanamen recommended, learn to use PDO and prepared statements.

1 Like

$sql = “UPDATE recent_happenings SET recent_title=$recent_title ,recent_date=$recent_date ,recent_pic_file=$target_path WHERE pic_number = $pic_number”;

UpDate Recent Pictures

The file 2018family3.jpg has been updateded

Unknown column ‘106’ in ‘where clause’

Comes up with this clause even if I put 106 as the pic_number or 118 or other # and there is a column with

Did you try echoing $sql; and did you try posting the query into PhpMyAdmin which I hope you have installed.

For the benefit of other users I find it helpful if the $sql statement is formatted.

Pasting the script into PhpMyAdmin → Sql → Format

if(FALSE) :
   $recent_title = 'RECENT_TITLE';
   $recent_date  = 'RECENT_DATE';
   $target_path  = 'TARGET_PATH';
   $pic_number   = 'PIC_NUMBER';

// using php strings Heredoc syntax
$sql  = <<< ____EOF
        recent_title    = $recent_title,
        recent_date     = $recent_date, 
        recent_pic_file = $target_path 
          pic_number    = $pic_number

echo '<pre>', $sql, '</pre>';  die;

You might want to show more of your code.
I see this:

and it kinda makes me think your update statement worked. But I can’t tell because your code snippet is too short.
Is it possible that this - “Unknown column ‘106’ in ‘where clause’” - is a new error?

If what appears to be a number is being treated as a column name, it must start with non-numerical character(s). You either have a non-printing character (new-line, tab) or some html markup as part of the value. What does the ‘view source’ in your browser of the sql error message show? How are you entering/selecting the pic_number in the form and what is all the code between that point and the sql query?

It uploads a new picture but not the new file name or date etc.

Her is more of the script as requested by some.

$ID = $_POST['ID'];
$recent_pic_file = $_POST['recent_pic_file'];
$target_path = "uploads/images/";

$pic_number = $_POST['pic_number'];
$recent_date = $_POST['recent_date'];
$recent_title = $_POST['recent_title'];

/* Add the original filename to our target path. Result is "uploads/filename.extension" */
$target_path = $target_path . ( $_FILES['uploadedfile']['name']);

// This is how we will get the temporary file...

if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
    echo "The file ". basename( $_FILES['uploadedfile']['name']). " has been updateded<br><br>";


$sql = "UPDATE `recent_happenings` SET `recent_title`=$recent_title ,`recent_date`=$recent_date ,`recent_pic_file`=$target_path  WHERE `pic_number` = `$pic_number`";   


} else{
    echo "There was an error uploading the file, please try again!";
	$result = mysqli_query($conn, $sql) or die (mysqli_error($conn));
	echo "<br><a href='index.php'>Return to Admin Index</a><br>";



<form enctype="multipart/form-data" action="update_happenings.php" method="POST">
<input type="hidden" name="subclick" value="1">

 $query = "UPDATE `recent_happenings` (`pic_number` , `recent_title`, `recent_date`,
`recent_pic_file`) VALUES ('$pic_number' , '$recent_title' , '$recent_date' , '$target_path')";

} else{
    echo "There was an error uploading the file, please try again!";
	$result = mysqli_query($conn, $sql) or die (mysqli_error($conn));
	echo "<br><a href='index.php'>Return to Admin Index</a><br>

<form enctype="multipart/form-data" action="update_happenings.php" method="POST">
<input type="hidden" name="subclick" value="1">

 style="text-align: left; width: 525px;"
 border="0" cellpadding="1" cellspacing="1" cols="1">  


      <td width="40%" valign="top" bgcolor="#f3f3f3"><strong>
      Recentpic #: </strong></td>

      <td width="51%" valign="top">
      <select name="pic_number"><option value="" selected>Choose... </option>

<option value="101">Pic #1</option>

<option value="102">Pic #2</option>

<option value="103">Pic #3</option>

<option value="104">Pic #4</option>

<option value="105">Pic #5</option>

<option value="106">Pic #6</option>

<option value="107">Pic #7</option>

<option value="108">Pic #8</option>

<option value="109">Pic #9</option>

<option value="110">Pic #10</option>

<option value="112">Pic #12</option>

<option value="113">Pic #13</option>

<option value="114">Pic #14</option>

<option value="115">Pic #15</option>

<option value="116">Pic #16</option>

<option value="117">Pic #17</option>

<option value="118">Pic #18</option></select>


      <td width="50%" valign="top" bgcolor="#f3f3f3"><strong>Happening Date: <font color=red>YYYY-MM-DD</strong></td>

      <td width="50%" valign="top"> <input type="text" name="recent_date" value=""></td>
<tr><td width="50%" valign="top" bgcolor="#f3f3f3"><strong>Recent Happening: </strong></td>

      <td width="50%" valign="top"> <input type="text" name="recent_title" value=""></td>


    <tr><td width="50%" valign="top" bgcolor="#f3f3f3"><strong>Recent Picture:</strong></td>
	     <td width="170px" valign="top">
	     <input type="hidden" />
<input name="uploadedfile" type="file" /></td></tr>
<td width="100px" valign="top"></td><td><input type="submit" value="Upload File" />

Your script has numerous syntax errors.

Please modify your post and included all source code between lines starting and finishing with the back ticks.

Also try adding these the lives to the PHP script.


// Your script goes here
1 Like

Can you log that $sql so we can see what it looks like? I’m guessing there’s a problem with quoting or backslashes not being escaped.

Now that your actual sql statement has been posted as being code, without the forum software operating on characters within it, the reason for the numerical value being treated as a column is clear. You are surrounding the value with back-tacks, which is the identifier (database, table, column) quote character.

I would ask why you are doing that, but I already know. You saw this somewhere and are just repeating what you saw, without learning what it is used for and when to use it -

Now would be the time for you to read the link that has been given to the PDO information in this thread, switch to use the PDO extension, and use a prepared query when supplying external/unknown values to a query. A prepared query simplifies the sql query syntax, by replacing each value with a simple ? place-holder.

Are you still getting this error?

From what I see, 106 is a character index not a name.

i.e. fix the grouping parentheses

UpDate Recent Pictures

The file 2017goodfood.jpg has been updateded

Unknown column ‘likes’ in ‘field list’

I have no Idea what’s going on! now i"m getting this error and “likes” is the recent title!
here is the script for it.

$sql = “UPDATE recent_happenings SET recent_title = $recent_title ,recent_date= $recent_date , recent_pic_file= $target_path WHERE pic_number = pic_number”;

OP, how about putting ALL your code on a repo or provide a zip download from dropbox or something. Preferably a repo. People don’t like to download zip files.