CODEWITHSUNDEEP
phpmysqlsqlsql-update
Mitch8910
Mitch8910

Reputation: 183

MySQL change ROW1 if ROW2 contains "string"

I have a list of items in a database. I have the columns item_name and is_tradable. I want to be able to go through the names in item_name and check if they contain a certain string, e.g. "Gold". If they contain that string, change is_tradable from 'yes' to 'no'.

e.g.: item_name "gold gloves" contains "gold", so is_tradable = "no"

I'm not sure how to check if a varchar contains a string.

My table shcema is something like this:

[Item_name, varchar(255), PrimaryKey]    
[is_tradable, varchar(255) ]    
[other random rows not needed here]

e.g.:

[item_name: gold_gloves, is_tradable: yes]

I want to be able to check if item_name contains "gold" so I can change is_tradable.

 [item_name: gold_gloves, is_tradable: no]

Upvotes: 1

Views: 93

Answers (3)

Afaan Bilal
Afaan Bilal

Reputation: 333

Assuming you are familiar with the connection details and the rest, and assuming that the table name is items, you could do:

Using PDO:

// Assuming $db contains the connection
$stmt = $db->prepare("UPDATE items SET is_tradable='no' WHERE Item_name LIKE '%Gold%' OR Item_name LIKE %gold%");
$stmt->execute();

What the above statement does is that it searches for every row where the Item_name "contains" the string Gold or gold and sets the row's is_tradable column to 'no'.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311823

You could use the like operator to check if the name contains gold:

UPDATE my_table
SET    is_tradable = 'no'
WHERE  item_name LIKE '%gold%'

Note that depending on the table's collation, LIKE may or may not be case sensitive. If you are unsure about the values in your table, you could just force all values to lower case:

UPDATE my_table
SET    is_tradable = 'no'
WHERE  LOWER(item_name) LIKE '%gold%'

Upvotes: 1

jde
jde

Reputation: 198

Iterate twice on you mysql results.

  • First to flag the string you're looking for with a boolean.
  • If boolean == TRUE, iterate a second time and make the modifications.

Upvotes: 0

Related Questions