CODEWITHSUNDEEP
phpmysqldatabase
Lucas Moreira
Lucas Moreira

Reputation: 1

Update query doesn't work

Today I'm having a big problem with PHP. I'm trying to update a row on MySQL database. The biggest problem is because the query works if I insert directly in phpMyAdmin but not in my PHP script.

Follow my query (this is a value of $sqlQuery):

UPDATE my_table SET id = '64', title = 'test', another_column = 'asdasdasdasd 2', category = '1', author = '1', status = '0', column = '0', date_created = '2011-08-13 15:33:54' WHERE id = '64'

In my script, I created a loop to generate the code between "SET" and "WHERE" because it come from array.

Also, before you ask me If the connection are opened, I've checked with this code (below) and returned "opened":

if (!$conn->connection) echo "closed";
else echo "opened";

My script to run the query:

if (mysql_query($sqlQuery, $conn->connection)) {
    $sqlResult = array(
        "status" => "ok",
        "result" => "edited"
    );
} else {
    $sqlResult = array(
        "status" => "error",
        "result" => "$sqlQuery"
    );
}

It always return the "else" array (status = error and result = $sqlQuery).

Upvotes: 0

Views: 738

Answers (3)

Paul
Paul

Reputation: 141829

You should echo mysql_error(); to output the error message before trying to debug, but your error is probably cause by column = '0' since column is a reserved word in MySQL you must surround it with backticks like `column` = '0'

Upvotes: 3

Brian Driscoll
Brian Driscoll

Reputation: 19635

Is your id field an auto-incrementing primary key? If so, then you will not be able to set it in your query (you shouldn't be anyway, since it's part of your WHERE clause). In general, you should only update the fields that are actually being changed (and primary key values typically should not change as part of an update).

Upvotes: 1

SeanCannon
SeanCannon

Reputation: 77966

Why are you setting id = '64' WHERE id = '64'? Id's are INT anyway, you should not be quoting them.

Also, change "result" => "$sqlQuery" to "result" => $sqlQuery and do a var_dump($sqlQuery) to assist with debugging.

Upvotes: 2

Related Questions