Hello, I have added a new DB update code to a script and the thing that really gets me is that when I echo the sql query it looks correct. I can manually copy and paste the query the script creates into phpmyadmin and it works perfectly. However for unknown reasons it will not update this part of the db in the script. I have never seen this problem before where everything looks correct but doesn't work. I am totally stumped. Here is part of the code:

$sql="UPDATE " . USERS_TABLE . " SET user_items='" . prep_item_sql($useritems) . "' WHERE user_id='" . $userdata['user_id'] . "'";
if ( !($uresults = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Fatal Error Updating User Information', '', __LINE__, __FILE__, $sql);
}
}
echo $sql;

I used echo $sql; as a test to view the query and it looks correct. Other parts of this php file also use the same $sql query which performs the sql action so I know that it is making a connection to the database. However it is not working. What should I do?

If you execute the query from phpMyAdmin does it work?

If you know your SQL syntax is correct then it's time to re-examine your database connection, permissions and error handling.

If you execute the query from phpMyAdmin does it work?

Yes it works.

Ok please answer me this. Is there another command I can use to update that table instead of SET? Because SET seems to output everything in that table and add the new entry at the end. Is it possible to just use a simple line of code to add the item at the end?

Ok please answer me this. Is there another command I can use to update that table instead of SET? Because SET seems to output everything in that table and add the new entry at the end. Is it possible to just use a simple line of code to add the item at the end?

What are you talking about?

Sigh. I cannot figure it out, no programmers that I know are online, and I cannot find anyone to pay for simple php tasks.

Let's take it from the start. As you say, from pma it executes but not from the script. Can you replace the $sql with an other simple query and see if it enters the database?

if ( !($uresults = $db->sql_query("INSERT INTO ....")) )

if ( !($uresults = $db->sql_query($sql)) )
So you're adding this to an existing script that provides the sql_query function. Do you know what this function is supposed to return after an update statement? It looks like you're assuming it will follow the convention of mysql_query (true for sucessful update, false on error) - you might want to check the class code to confirm that it does.

-Did you instantiate the $db object?

To test: <?=(is_object($db) ? "Yes" : "No");?>

-When you initialize the $db object does the database connection and selection occur implicitly or do you manually need to connect & select? (i.e $db->connect(); $db->select(); If not have you manually connected & selected?

-As someone said, can you perform other simple queries? For example,

$sql = "INSERT INTO users (user_id) VALUES ('')";

Oh sorry I wasn't aware of these replies since I didn't get emails. I will try to do more testing with your suggestions thanks.

Except for the extra "}" , there is nothing wrong with the above code.

My suggestion would be to take another look sql_query() function. Is it returning a value as you expect it to?

Yes the echo $sql test is returning the query I want but when I check the db I see that nothing has changed. When I manually copy and paste that echoed query in phpmyadmin it works. The extra curly bracket is from an if statement in the beginning of the code that I didn't paste here. In an earlier test I replaced $sql with a another update command in the same file that was more simple and it updated the db fine. That is why I am totally stumped as to the problem. I wonder if there is some time of additional security preventing this or something?

Check whether the user account you are using to connect from PHP has the "UPDATE" privileges.

If you are talking about going into Cpanel to see if the DB user has priveleges yes that user has them all. As I have said every other update command in that script works.

Ok I asked this question before but noone understood. Instead of using the SET command, what is the other mysql command to add something into the table without having to pull all of the data and insert the new data at the end? When I use the SET command it pulls all of the data in that table and includes the new data at the end. A few years ago a programmer friend gave me a simple mysql command to insert new data without using the SET command.

For example, this command is incorrect but I would need a query that works:

UPDATE phpbb_users ADD user_items='ßBizzare HornÞ' WHERE user_id='173'

ADD or INSERT doesn't work with that command, so what command would work?

No-one understood the first time because what you wrote made no sense. It still doesn't. SET is a required part of the syntax of the UPDATE command (which BTW doesn't "pull all of the data in that table"). If you prefer you could try an INSERT command, as already suggested more than once.

You've been given some good suggestions for debugging your code, eg this one (http://www.webhostingtalk.com/showpost.php?p=4660616&postcount=10) - try to understand and act on them.

I really didn't understand what that guy meant I am not an expert.

what is the other mysql command to add something into the table without having to pull all of the data and insert the new data at the end?

INSERT does not "pull all of the data" and neither does UPDATE.

INSERT => Add new records
UPDATE => Update existing records

As for the debugging info posted by Codelphious, add this line of code just above the $sql = "UPDATE statement:

<?=(is_object($db) ? "Yes" : "No");?>

Execute the script. If it prints "No", that means that you have not instantiated the $db object.

The $db object is not something standard from PHP - it depends on classes defined in the script you're adapting. Furthermore it won't just magically come into existence - you need to create (instantiate) it first. Depending on where you put your code in the existing script you may find this has already been done for you, or not. And then maybe you'll also need commands to connect it to the database...

FWIW, first Google result on "db->sql_query" goes here (http://www.phpnoise.com/tutorials/43/6). If that's the class you're using you should find the whole tutorial helpful, if not at least you'll find something to compare to your own code.