How can I change a table setting on all images within a specific category?

[JohnnyDoomo]

I have a script that the developer has stopped supporting.

 

The script has the ability to mark images "mature", but only single images, and one by one. The script has no ability to mark an entire category mature. Is what I want to do is perform a mysql command in phpmyadmin that tells it to auto change the setting for all images in a category to be marked as mature.

 

Can someone help me with what command I would use to do this?

 

My database name: site_database

Category table: pp_categories

How cats are identified: "catorder"

Photo table: pp_photos

How photos are identified as being in the same category: "cat"

table that I need to change: "ismature"

 

So I was hoping for a command that I can insert into phpmyadmin, to say all images within a certain category id, change the photos table of "ismature" to 1. From my understanding of the script, this would then mark all the photos in that album on the site as mature.

 

Can anybody help me with what I would need to type in?

[mac_gyver]

you would use an UPDATE query with a WHERE clause to control which rows get updated. the following is the UPDATE query syntax definition of the commonly used parts highlighted -

 

UPDATE [LOW_PRIORITY] [iGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

 

Edited July 14, 2016 by mac_gyver

[Muddy_Funster]

@mac_gyver, Just looking over that UPDATE syntax and something's bugging me - what case would cause someone to want to ORDER BY during an update? O_o

[Jacques1]

Be aware that an UPDATE query will only change the current images. A new photo will be unmarked until you run the query again.

 

If you want to automatically update the flag (like you said in the original post), a quick workaround would be a trigger. But of course none of this is a replacement for an actual developer who knows what he/she is doing and can fix the application itself.

 

 

 

@mac_gyver, Just looking over that UPDATE syntax and something's bugging me - what case would cause someone to want to ORDER BY during an update? O_o

 

To use it in conjuction with LIMIT or to prevent constraint violations caused by an unordered UPDATE. There are several examples in the manual.