Activate db fields after migration?

kamy99

Hi all:

I have a weird/stupid problem, which I ran to on another project, but because it was a small job, it didn't bother me, now with the bigger job it is a headache!

The problem:

We've moved to a new server. PHP 4.4.4 , mySQL 5, register_globals ON (just in case it is an issue...)
Needless to say my script is not working.

The code:

$sql=mysql_query("UPDATE `articles` SET  `article_title_eng` = '".$_POST['article_title_eng']."' , `article_body_eng` = '".$_POST['article_body_eng']."' , `article_title_far` = '".$_POST['article_title_far']."' , `article_body_far` = '".$_POST['article_body_far']."' , `article_title_krd` = '".$_POST['article_title_krd']."' , `article_body_krd` = '".$_POST['article_body_krd']."' ,  `article_title_lat` = '".$_POST['article_title_lat']."' , `article_body_lat` = '".$_POST['article_body_lat']."' , `article_title_fre` = '".$_POST['article_title_fre']."' , `article_body_fre` = '".$_POST['article_body_fre']."' ,  `article_date` = '".$_POST['article_date']."' , `article_publish` = '".$_POST['article_publish']."' ,  `article_comments` = '".$_POST['article_comments']."',  `article_feature_krd` = '".$_POST['article_feature_krd']."',  `article_feature_eng` = '".$_POST['article_feature_eng']."',  `article_feature_far` = '".$_POST['article_feature_far']."',  `article_author` = '".$_POST['article_author']."',  `article_disclaimer` = '".$_POST['article_disclaimer']."'   WHERE `article_id` = '$_POST[article_id]' ");

When I submit the form it does not update.

Weird part:
If I delete all fields , but one, it works, for example:

 $sql=mysql_query("UPDATE `articles` SET  `article_title_eng` = '".$_POST['article_title_eng']."'    WHERE `article_id` = '$_POST[article_id]' ");

Then, if I add a second field, it works for 2 fields:

 $sql=mysql_query("UPDATE `articles` SET  `article_title_eng` = '".$_POST['article_title_eng']."' , `article_body_eng` = '".$_POST['article_body_eng']."'    WHERE `article_id` = '$_POST[article_id]' ");

Then if I add the third field, it works for the 3 and so on......
You might say "just suck it up" and do 17 update/upload/tests.
But I have two problmes with that:

1) Why does this happen? It seems like I have to activate/wake up each field, one by one, It's weird.

2) I have 3 other scripts with different tables, fields, etc, so it'll take a long time.

Any thoughts?

tomhath

Check the return code from mysql_query by calling mysql_error(). It might tell you what's going on.

nemonoman

You are not "activating" a field. The query is failing--as you add more elements to the query, you discover the elements the DON'T Fail. Eventually you will find the element that DOES fail.

The query is failing because one of the fields being updated is not being referenced correctly. The problem may be that the newer version you are using is being more particular about a keyword being used a column name. For example, I upgraded from MySQL 4 to 5 and a query of mine started to fail. One of my columns was called 'sql'. In order to get the query to work, I needed to use the ` sign:
Worked version 4, but failed version 5:
UPDATE mytable SET sql='Something';

Worked version 5:
UPDATE mytable SET sql='Something'

Column names that use traditionally reserved SQL words such as timestamp, date, insert, etc . might be causing this problem.

Roger_Ramjet

nemonoman wrote:
Column names that use traditionally reserved SQL words such as timestamp, date, insert, etc . might be causing this problem.

Which is the reason you should NEVER use reserved words for db element names - even if you plan to use the backticks. There are enough words in the english language and enough other options for naming things that using reserved words is just not necessary.

Roger_Ramjet

Kamy the first thing to do is to change your coding style

$query = "UPDATE `articles` SET  

`article_title_eng` = '".$_POST['article_title_eng']."' , 
`article_body_eng` = '".$_POST['article_body_eng']."' , 
`article_title_far` = '".$_POST['article_title_far']."' , 
`article_body_far` = '".$_POST['article_body_far']."' , 
`article_title_krd` = '".$_POST['article_title_krd']."' , 
`article_body_krd` = '".$_POST['article_body_krd']."' ,  

`article_title_lat` = '".$_POST['article_title_lat']."' , 
`article_body_lat` = '".$_POST['article_body_lat']."' , 
`article_title_fre` = '".$_POST['article_title_fre']."' , 
`article_body_fre` = '".$_POST['article_body_fre']."' ,  

`article_date` = '".$_POST['article_date']."' , 
`article_publish` = '".$_POST['article_publish']."' ,  

`article_comments` = '".$_POST['article_comments']."',  

`article_feature_krd` = '".$_POST['article_feature_krd']."',  

`article_feature_eng` = '".$_POST['article_feature_eng']."',  

`article_feature_far` = '".$_POST['article_feature_far']."',  

`article_author` = '".$_POST['article_author']."',  

`article_disclaimer` = '".$_POST['article_disclaimer']."'   

WHERE `article_id` = '$_POST[article_id]' ";
$result = mysql_query($query) or die (mysql_error() . '<br />' . $query);

Creating the query string before you use mysql_query allows you to echo it out for debugging. Chances are that you'll spot the syntax error once you see the string - could well be something in the user input data that is screwing things up.

Roger_Ramjet

Or it could just be this

WHERE `article_id` = '$_POST[article_id]' ";

Now what is wrong with that line then ?? 🙂