What is wrong in mysql_query "update concat"?

PHP
1

I want to make a post form. After users login in my space, they can input something in the database. My database fields are: id(automatic raise 1) ,user_id, key, data.
When the database field ‘data’ is empte, insert the $post directly, when the database field ‘data’ has already generated, erase the last 4 character then insert the $post.

my code is:


mysql_select_db($db, $link);
$result = mysql_query("SELECT * FROM book1 where user_id='$user_id' and key='14' and data IS NULL");
if(mysql_num_rows($result) > 0) {
    $result = mysql_query("update book1 set data=(concat(left(data,char_length(data)-4),'$post'))where user_id='$user_id' and key='14'");
}else{
    $result = mysql_query("INSERT INTO book1 VALUES ( '', '$user_id', '14', '$post')");
}

But now I can only insert a new value, I can’t update the database.
What’s wrong in my “update concat” code or because of the automatic id field? Pls help me. Thanks.

2

Ah… I thought you let me eliminate the ‘unhappy’ icon in my post titile.
:slight_smile:

3

Wonderful, it is run. Thanks for your help.

4

Really? But you use INSERT when the row with key 14 doesn’t exist for that user, not when the value of ‘data’ is NULL.

Try to put some echo’s in your code to see what parts are executed, how the query looks like, etc.
For example:


mysql_select_db($db, $link);
$query = "
  SELECT * 
  FROM book1 
  WHERE user_id = '$user_id' 
  AND key = '14' 
  AND data IS NULL
";

echo "select query: $query </br >";

$result = mysql_query($query) or die("mysql error: " . mysql_error());

echo "number of rows returned: " . mysql_num_rows($result) . "<br />";

if(mysql_num_rows($result) > 0) {
    $query = "
      UPDATE book1
      SET data = CONCAT(LEFT(data, CHAR_LENGTH(data)-4), '$post')
      WHERE user_id = '$user_id' 
      AND key = '14'
    ";
    echo "update query: $query </br >";
    $result = mysql_query($query) or die("mysql error: " . mysql_error());
}else{
    $query = "
      INSERT INTO book1 
      VALUES ( '', '$user_id', '14', '$post')
    ";
    echo "insert query: $query </br >";
    $result = mysql_query($query) or die("mysql error: " . mysql_error());
}

Please post the result here.

When you’ve resolved your problem, you can eliminate the echo statements.

5

Thanks for your quick reply.
When the users first input something ,the value of the ‘data’ column is NULL, so just use INSERT INTO; But when they want to input more, the value of the ‘data’ column is always use update method(delete last four character and input new $post.

6

Does your code ever enter the ‘update’ part of the if?
Is the value of the ‘data’ column ever NULL? Or is it ‘’?

7

Not here in the forum. In your script. :slight_smile:

8

Because when a row with userid 2 and key 14 exists, the value of ‘data’ is not NULL.

Change the first query in this

$query = "
  SELECT *
  FROM book1
  WHERE user_id = '$user_id'
  AND key = '14'
";
9

Excuse me: how to eliminate the echo statements?
I can not find the edit buttom.

10

Thanks for Guido, I will cancel the echo statements.
But I still can not update the database.
It always insert a new record with field ‘id’ increase 1.

my database table:
id | use_id | key | data
17| 2 | 14 | aaaaaa
16| 2 | 14 | bbbbbb

The echo is:
select query: SELECT * FROM book1 WHERE user_id = ‘2’ AND wkey = ‘14’ AND data IS NULL
number of rows returned: 0
insert query: INSERT INTO book1 VALUES ( ‘’, ‘2’, ‘14’, ‘bbbbbb’)

select query: SELECT * FROM book1 WHERE user_id = ‘2’ AND wkey = ‘14’ AND data IS NULL
number of rows returned: 0
insert query: INSERT INTO book1 VALUES ( ‘’, ‘2’, ‘14’, ‘aaaaaa’)