(Mysql + PHP) How to replace a value in every Row in a table

Question

I have the following table:

**ID** | **User** | **Password** | **Category**
1      | Test     | PW1          | Cat1
2      | Test2    | PW2          | Cat2

I have the following PHP function to change a single value:

if($_GET['handler'] == "changerang"){
$ID = $_GET['param2'];  
$NewPW = $_GET['param3'];
$mysqli->query("UPDATE User SET Password='".$NewPW."' WHERE User = '".$ID."'");
echo "success"; 
}

That works, but now I want to replace every "Cat" in the entire table in row "Category" with another string, but I don´t know how to change my function with the Replace function..

Could you help me maybe?

Thank you very much :) Best regards, René

Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values — RiggsFolly, Mar 05 '20 at 13:58
This look totally unsafe. You should not be storing plain text passwords, but ones hashed and stored using `password_hash()`. I hope that this isn't a live site or going live because you stand at getting hacked, *eventually*. It's just a matter of time really. — Funk Forty Niner, Mar 05 '20 at 14:10
Do you want to replace "cat1" and "cat2" with "feline", or do you want to change them to "feline1" and "feline2". The question isn't clear about what you want. — Jason Aller, Mar 05 '20 at 16:12

GMB · Answer 1 · 2020-03-05T14:01:52.863

2

now I want to replace every "Cat" in the entire table in row "Category" with another string

Just:

update user 
set category = :new_string 
where category like 'Cat%'

Important notes: you should be using parameterized queries to make your code safe from sql injection - see How can I prevent SQL injection in PHP?

edited Mar 05 '20 at 14:01

answered Mar 05 '20 at 13:56

GMB

216,147
25
84
135

1

`USER` is not reserved, it's a keyword https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-U - But, `USE` is. See the `(R)`? Two different animals ;-) – Funk Forty Niner Mar 05 '20 at 13:59

score 0 · Answer 2 · edited Mar 05 '20 at 14:01

0

use

$mysqli->query("UPDATE User SET Category = 'NEWCAT' WHERE Category LIKE 'cat%'");

edited Mar 05 '20 at 14:01

RiggsFolly

93,638
21
103
149

answered Mar 05 '20 at 13:56

Bernd Buffen

14,525
2
24
39

? Sanity check please ? – RiggsFolly Mar 05 '20 at 13:59
**Note:** On some systems, `Cat` and `cat` are strangely enough and can be two different animals. – Funk Forty Niner Mar 05 '20 at 14:02

(Mysql + PHP) How to replace a value in every Row in a table

2 Answers2