All we need is an easy explanation of the problem, so here it is.
I have an images database, the default sort is the “name” column, and the “description” column content is the exact same in all files in the table. what I need is to replace certain text in the “description” with numbers respectively.
Example of the “description” entry to all items:
"Photo number # more text text"
The query should replace the
"#" symbol with sequential numbers, ordered by ‘name’ column. Where the first row should be
Photo number 1, and second
Photo number 2.. etc.
I tried some queries with row_number (in phpmyadmin), but I am not sure if it is the right way to do that or what.. as they didn’t work.
Server version: 5.7.24-0ubuntu0.16.04.1 – (Ubuntu)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Use a variable to get an auto-incremented value and update
description using this value. Or use Rick James‘s answer and add a sequenced column to your table as a number for each picture.
create table t (name varchar(10), description varchar(100)); insert into t values ('b', '"Photo number # - Gallery name"'); insert into t values ('c', '"Photo number # - Gallery name"'); insert into t values ('a', '"Photo number # - Gallery name"'); insert into t values ('x', '"Photo number # - Gallery name"');
update t join (select name, @num := @num + 1 as num from t, (select @num := 0) t order by name) t2 on t2.name = t.name set description = replace(description, '#', cast(num as varchar(10)));
select * from t order by name;
name | description :--- | :------------------------------ a | "Photo number 1 - Gallery name" b | "Photo number 2 - Gallery name" c | "Photo number 3 - Gallery name" x | "Photo number 4 - Gallery name"
CREATE TABLE foo ( photo_num INT AUTO_INCREMENT, gallery_name VARCHAR(155), PRIMARY KEY(photo_num) );
INSERT INTO foo (gallery_name) SELECT gallery_name FROM elsewhere ORDER BY gallery_name;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂