MySQL update failed!

[steadythecourse]

I cannot figure out why the update failure is occurring!

I am simply trying to update the table. I have only been developing for a little over a year, so I'm extremely green. Any help would be greatly appreciated. I hope I've provided the necessary information, if not please let me know.

Thank you,

steadythecourse

System:
XAMPP: version 1.7.2
PHP: version 5.3.0
MySQL: Server version: 5.1.37

query:

$query = "UPDATE subjects
SET menu_name = '{$menu_name}',
position = {$position},
visible = {$visible}
WHERE id = {$id}";

where id is the row id of the table {subjects}

and

$id = mysql_form_input_encoding($_GET['subj']);
where $_GET['subj'] is .php?subj=1

error message received:

The subject update failed
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 '' at line 5


mysql_form_input_encoding():

function mysql_form_input_encoding($value) {
//$active checks to see if magic quotes is active on the server.
$active = get_magic_quotes_gpc();
//$version checks to see if php version is
//new enough to use magic quotes.
$version = function_exists("mysql_real_escape_string");
if ($version) {
if ($active) {
$value = stripslashes($value); }
$value = mysql_real_escape_string($value); }
else {
if(!$active) {
$value = addslashes($value); }
}
return $value;
}

[Fumigator]

Echo $query and try to run the output into MySQL directly using phpMyAdmin (or similar). You will likely get the same error, but now you're in a position to determine what is wrong with the query and then go back to your PHP code and fix the problem.

90% of the time when a query fails that was previously working, it's because the variables you're plugging into the query are missing or in the wrong format.

[steadythecourse]

Fumigator,

I tried echoing the $query and got the following

UPDATE subjects SET menu_name = 'About Clickitchip', position = 1, visible = 1 WHERE id =

The subject update failed
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 '' at line 5

So it looks like you are correct id should be = $id but $id does not seem to load.

I still can not see the issue in the code, so I have attached the the functions and the edit_subject script. Please tell me if you can see the error.

[Coyote6]

Sounds like the error you get with a null id... because the query adds the id inside php when an id value is empty sql thinks that the query was not completed.

So to mysql it looks like this

UPDATE subjects SET menu_name = 'About Clickitchip', position = 1, visible = 1 WHERE id =;


The solution, is to add a single quote around the id..

$query = "UPDATE subjects
SET menu_name = '{$menu_name}',
position = {$position},
visible = {$visible}
WHERE id = '{$id}'";

[steadythecourse]

Thanks Coyote6,

I tried the single quotes around {$id}, now it just sends back


UPDATE subjects SET menu_name = 'About Clickitchip', position = 1, visible = 1 WHERE id = ''

The subject update failed


the top part is because I'm echoing the $query variable
and bottom part is because of the following code

$message = "The subject update failed";
$message .= "<br/>" . mysql_error();

but it doesn't give me a mysql_error().

and it's still not passing in the id value.

I appreciate the help.

[Coyote6]

Is the id coming through at this point?


$id = mysql_form_input_encoding($_GET['subj']);


May try if you haven't already.

echo $_GET['subj'] . '<br>';
echo $id = mysql_form_input_encoding($_GET['subj']);


Should output.

1
1

[steadythecourse]

Coyote6,

yes, it is echoing back

1
1

steadythecourse

[Coyote6]

Hmmm then try making sure it is getting all the way to the query.


echo $id;
$query = "UPDATE subjects
SET menu_name = '{$menu_name}',
position = {$position},
visible = {$visible}
WHERE id = '{$id}'";


If it is echo 1 there too then I'm baffled... If not then it is losing its value somewhere between the $_GET and the query and you will need to post your code to try and find the spot.

[abduraooft]

The solution, is to add a single quote around the id.. No! Don't use quotes around a variable that's supposed to hold numeric values.

Instead, you should validate your variables before passing to mysql query and make sure that the values hold by them are valid.

@steadythecourse: Could you please post your complete code?

[steadythecourse]

Coyote6,

strange I know, I also know it will end up being something stupid as it always is, but I just can't see the error.
I've attached the functions and the script. If you have the time take a look tell me what you think,

again much appreciated!

Steadythecourse

[steadythecourse]

abduraooft,
I all ready tried that and it didn't work, I've attached the two necessary scripts, If you could take a look I would appreciate it.
Thanks
steadythecourse

[Coyote6]

Steady,


No! Don't use quotes around a variable that's supposed to hold numeric values.


I think what abduraooft is saying by this is you should place a check before using the update statement instead of the single quotes.


// And check other values.
if (($id != NULL) && (is_numeric($id))) {
// Insert update.
}


The problem may lie in that if value inside an update is an INT and is allowed to be NULL then you must literally write the word NULL...

$q = "INSERT INTO table (foreign_key) VALUES (NULL);"


So if your $id is null then you have to add a text to the $id value to make it say NULL.


if (is_null($id)) {
$id = 'NULL';
}
$q = "INSERT INTO table (foreign_key) VALUES ($id);"


If you have the INT column value defined as having a default of 0 and not being null then you have to change the null to a 0.


if (is_null($id)) {
$id = 0;
}
$q = "INSERT INTO table (foreign_key) VALUES ($id);"


But if you use '' it will automatically insert on both NULL and defined table types with both, but that being said it may have an unexpected side affect too. When inserted as an '' the value entered into the database is 0 not a NULL, which is one of the reasons I use a defined default value of 0 and not NULL in these types of tables. In the case of searching by an INT column (as is the case) using the single quotes works for finding 0 values and not null but it will at least allow you to search without receiving an error.

But I agree it is not the best way to achieve the desired results. Rather it is a shortcut that uses MySQL's ability to understand integers inside of single quotes as an integer and not a string to achieve the desired results. And yes it will not work on any other database type that I know of.

As for finding an error in your code Steady, I'm baffled... I do not see where the error is because the only possible location is in the mysql_form_input_encoding() function but I do not see any errors in it... :confused:

[Old Pedant]

Since I'm not at all a PHP person, I haven't the foggiest notion what mysql_form_input_encoding is supposed to do.

But I *do* know how to find out if it is the culprit:

Replace the line:
$id = mysql_form_input_encoding($_GET['subj']);

with some debug code:

$id_before = $_GET['subj'];
$id = mysql_form_input_encoding( $id_before );
echo "<hr>id before encode is ==" . $id_before . "==, after encoding is ==" . $id . "==<hr>";

DEBUG DEBUG DEBUG.

The ==xx== is of course to help find any bogus spaces, etc., in the values.

You may need to use VIEW==>>SOURCE in the browser to see them, if they are there.