Updating a database!?

Gareth · 01-13-2008, 04:28 PM

Hi guys, I have a question for you :)

This has been really annoying me recently,

I am trying to update my database via a form which will be found in the ACP. I manage to display the original values in the forms and then when i click submit it says Forum CANNOT be updated.

I have a tried a few methods, my last attempt being here.

Can someone help me fix this or give me a version of an update form which will definitely work, please?

Gareth
SBB Alpha 2 (Powered by SBB)

Alan @ CIT · 01-13-2008, 05:16 PM

Hi Gareth,

Try changing your "Forum cannot be updated" error text to echo mysql_error() instead. That may give some clues as to why it isn't updating.

A handy trick when debugging these sorts of mysql problems is to do the following:

PHP Code:


			
$something = mysql_query("your query here..") or die(mysql_error());

That will stop the script and show the mysql error message in the event that the query fails. Shouldn't be used in a live enviroment for it's handy for debugging :)

Alan.

Gareth · 01-13-2008, 05:27 PM

Aha!

It comes up with:

Quote:

ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( title, desc, cat, category ) VALUES ( Public, n/a, 1, 0 ) WHERE id='7'' at line 1

EDIT: It now says

Quote:

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 'desc='n/a' , cat='1' , category='0' ' at line 5

for

PHP Code:


			
 $update_forum = mysql_query("
                                
                                                            UPDATE 
                                                                sbb_forums 
                                                            SET 
                                                                title='$title' ,
                                                                desc='$desc' ,
                                                                cat='$cat' ,
                                                                category='$category'                                                        
                                                            WHERE
                                                                id='$id'") 
                                                                
                                        or die(mysql_error());            
                         
                            if ($update_forum) {
                                echo '<div class="sub_cat_content">';
                                echo 'Forum Updated';
                                echo '</div>';
                            } else {
                                echo '<div class="sub_cat_content">';
                                echo 'Forum <b>CANNOT BE</b> Updated';
                                echo '</div>';
                                }

Salathe · 01-13-2008, 05:30 PM

You'll need to format the SQL query correctly. At the moment you have:
... VALUES ( Public, n/a, 1, 0 ) but you need it to look like: ... VALUES ( 'Public', 'n/a', 1, 0 )

Alan @ CIT · 01-13-2008, 05:36 PM

Ahh, then the problem is with your update query syntax :)

Try changing it to:

PHP Code:


			
"UPDATE sbb_forums SET title = '$title', $desc = '$desc', cat = '$cat', category = '$category' WHERE id = '$id'"

That should do the trick.

More info on the SQL Update syntax here: MySQL AB :: MySQL 5.0 Reference Manual :: 11.2.10 UPDATE Syntax

Alan.

danielneri · 01-13-2008, 05:47 PM

Also, be careful using "desc", it's a mysql keyword!!

Alan @ CIT · 01-13-2008, 05:52 PM

Quote:

Originally Posted by Gareth

EDIT: It now says "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 'desc='n/a' , cat='1' , category='0' ' at line 5"

What daniel said :) As "desc" is a reserved SQL word you'll run into problems using it. I would recommend changing it to something else but if you really want to use it, wrap it in backticks (` - next to the 1 key :)).

Alan.

Gareth · 01-13-2008, 05:56 PM

You guys are great! Thank you so much :)

I need to remember about desc, will change it sometime :P

danielneri · 01-13-2008, 05:57 PM

Yeah I've run into that terrible desc error so many times and never knew what it was!

Annoying little thing isn't it... haha

Salathe · 01-13-2008, 06:22 PM

Offtopic

There are over 200 reserved words in MySQL (the link points to MySQL 6.0) which you'll want to avoid using as table names, column names, etc. Thankfully if you stick to the convention of using descriptive names then generally you won't hit too many problems. If in doubt, always `quote` the words.