Click here to Skip to main content
15,845,829 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a mysql table: "append". There are these columns in each record:

id, weight, username, email, password

The weight column has a default NULL value. It is text type.

When I want to UPDATE the weight column and CONCAT it (when the value is still NULL), it does not work. There is no error and say it is successful. But there is no difference in the "weight" as I see in phpmyadmin after refresh.

But when I add values to weight by hand and after trying to UPDATE CONCAT it, it works perfectly.

How to modify the code to CONCAT it, when there is no value yet in "weight".
The problem may be with the NULL value! I do not want to add a default value to "weight".
Do I need an IF statement? But how?

What I have tried:

PHP
$sql = "UPDATE append SET weight = CONCAT(weight, ',$weight') WHERE id = '" . $_SESSION['user']['id'] . "'";

if ($conn->query($sql) === TRUE) {
    echo "Successful!";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
Posted
Updated 8-Feb-23 4:17am
Comments
Richard MacCutchan 8-Feb-23 8:37am    
You should read the record first to see if the weight value is NULL. What exactly are you storing in that column?
Dave Kreskowiak 8-Feb-23 8:49am    
The name 'weight' suggests a numerical value. Why are you storing text is that column? And why are you trying to append text to whatever is in the column?

It sounds more like you have a much bigger problem with the design of your database.
folza 8-Feb-23 8:58am    
The problem is solved. I had to set the default value to an empty string. ""
Richard Deeming 8-Feb-23 9:40am    
Your "password" column makes me suspect you may be storing your users' passwords in plain text. You should be using PHP's built-in functions to store the passwords securely instead:
PHP: password_hash[^]
PHP: password_verify[^]

Not an answer to your problem which you have solved by setting a default value to an empty string but something you need to get on top of now before you form bad habits!

Your code is vulnerable to sql injection attack - never concatenate strings to create your sql commands

See SQL Injection Attacks and Some Tips on How to Prevent Them[^]
SQL Injection and Cross-Site Scripting[^]
SQL Injection Prevention - OWASP Cheat Sheet Series[^]

Also take good note of the comment from Dave Kreskowiak - use the correct column type for the data you want to store in it.

Finally - you can learn how to handle nulls properly here - https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html[^]
 
Share this answer
 
While you have found a solution, it's probably not a good one going forward.
As Dave has said, weight is a numerical value, and as such storing it in text form will lead to problems later on should you ever want to actually use the data: to find a maximum value for example, or to calculate an average.

A better idea is to use a second table with a foreign key that relates back to the ID column of this table and which adds a single numeric weight to each row. You can then use JOIN to combine these back with the original table and you can process the data very easily. You also don't need an UPDATE (which can get seriously expensive if you add a lot of weights) but a simple INSERT using the weight and the ID values.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900