Sign in to follow this  
Cubist

[web] PHP SQL UPDATE Query not working.

Recommended Posts

I have encountered an error that I think is the first of its kind in history.
<?php

session_start();
mysql_connect("localhost", "root", "password");
mysql_select_db("db_name");
$id = $_GET[id];

if ($_SESSION['auth'] == "yes")
{
	$query = mysql_query("SELECT * from submissions WHERE id = $id");
	$query = mysql_fetch_array($query);

	echo "Name: $query[name]<br>";
	echo "Email: $query[email]<br>";
	echo "Phone: $query[phone]<br>";
	echo "Body: $query[body]<br>";

	mysql_query("UPDATE submissions SET read = '1' where id = '$id'");
}

else echo "you don't have permission to view this resource";

?&gt;

This UPDATE will not execute. It throws no error. The name,email,phone,and body information display but the UPDATE does not execute. I have tried every combination of single quotes. I put them around the field names, the values, neither, and both. When I try to execute this query in phpmyadmin's console directly I get this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql_query("UPDATE submissions SET read = '1' where id = '$id'")' at line 1 And here is the funny part: When I have phpmyadmin generate the query itself it has me reference submissions like this: db_name.submissions But this is outrageous. There is no reason why my original UPDATE query should not work.

Share this post


Link to post
Share on other sites
This is the only way I can get it to work:

mysql_query("UPDATE `db_name`.`submissions` SET `read` = '1' WHERE `submissions`.`id` =$id");

If I change any of the ` to ' it screws up.

If I dare not reference db_name and submissions here it screws up.

In four years of PHP coding, this has never occurred.

I have never had to use this type of syntax for an UPDATE query.

This is an absolute first and based on my google searches, I am the first person in history to encounter this problem.

Share this post


Link to post
Share on other sites
Quote:
Original post by ianmclean0001
Hi Cubist

try "UPDATE submissions SET read = '1' where id = ".$id


mysql_query("UPDATE submissions SET read = '1' where id = ".$id);

I tried this. It did not work.

Share this post


Link to post
Share on other sites
submissions table has a simple structure:

id (int) (auto_increment)
name (varchar)
email (varchar)
phone (varchar)
body (varchar)
read (int)


read is set to 0 for a submission. i want to set it to 1 when a submission is read.


I'm able to access the read and id values of any row no problem.

SELECT statements involving either of them work perfectly.

Share this post


Link to post
Share on other sites
Quote:
Original post by ianmclean0001
it depends how Mysql is set up
it may have strict SQL set that means columns,tables and statements
become case sensitive.


all my SELECT and INSERT statements are totally simple and easy and were never strict about anything

also, i am using all the correct cases for fields table names and values here.

Share this post


Link to post
Share on other sites
mysql_query("UPDATE `submissions` SET `read` = '1' WHERE `id` = $id");

works.

But why? Why in the world do I need to use these ridiculous ` symbols everywhere?

I've written countless update statements in my life and I've never had to do anything so ridiculous.

Share this post


Link to post
Share on other sites
Quote:
Original post by ianmclean0001
I have found out read is a reserved word so if you want to use
a column call read you need to let the server know it a column by using the stupid `

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html


you're absolutely right. i figured this out just before reading your post.

read is reserved and that was my problem.

using submissions.read or `read` fixes the problem

and now i know that a ` is called a 'grave accent'

Share this post


Link to post
Share on other sites
I strongly recommend you add the following lines to the start of your script, as they'll reveal bugs:

error_reporting(E_ALL);
ini_set('display_errors', true);

For example, $_GET[id] should be $_GET['id'].

Note that you have an SQL injection bug; if I set $_GET['id'] to 1 OR 1, the executed query is UPDATE `submissions` SET `read` = '1' WHERE `id` = 1 OR 1, which would end up updating all records. Put quotes around all values (even integers) and escape values inserted into queries with mysql_real_escape_string.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this