How to replace a string in entire mysql database php

Question

I have a php website which images files used to stored in /uploads/ folder before now I have made a new folder /files1/ and images being stored in new folder and previous images also has been moved to new folder. But previous images links in database still containing old path www.example.com/uploads/text.png , is there any way i can replace "/uploads/" to "/files1/" using mysql , php in entire database

Thanks

Update your database design so that your paths are only stored in one table, giving each record a unique id, and reference that id in your other tables. That is the advantage of normalising your database (which nowadays no one seems to care any more about): if those paths need a bulk change, you only need one update statement. — trincot, Jan 19 '19 at 07:38

Professor Abronsius · Answer 1 · 2019-01-19T08:17:00.227

If you have no objections to creating and running a stored procedure on the database then you can do it entirely without using PHP.

create procedure `speditalltables`()
begin
    declare _strsql varchar(512);
    declare _table varchar(128);
    declare done int default false;
    declare res integer default 0;

    declare _cursor cursor for select `table_name` as 'name' 
            from `information_schema`.`tables` 
            where `table_type`='base table' and `table_schema`=database();

    declare continue handler for not found set done=true;



    open _cursor;

        repeat

        fetch _cursor into _table;

        if not done then


            /* different column names ~ edit as appropriate */
            set @strsql=concat("update `",database(),"`.`",_table,"` 
                set 
                `filepath`=replace( `filepath`, '/uploads/', '/files1/' ),
                `filepath2`=replace( `filepath`, '/uploads/', '/files1/' ),
                `filepath3`=replace( `filepath`, '/uploads/', '/files1/' ),
                `filepath4`=replace( `filepath`, '/uploads/', '/files1/' )");

            prepare stmt from @strsql;
            execute stmt;
            deallocate prepare stmt;


        end if;


        until done end repeat;

    close _cursor;

    select 'finished' as 'result';

    set done=null;
    set @res=null;
    set @_table=null;
end

I will need to run query on every tables around 200 tables , that's ok — arvindpundir, Jan 19 '19 at 07:53
how many different column names are you talking about here? If there are not too many simply edit the sql query in the stored procedure to include the additional columns — Professor Abronsius, Jan 19 '19 at 08:07

How to replace a string in entire mysql database php

1 Answers1