Update every row in a table with PHP in mySQL database

Question

  Table
*------------------------------------------------*
|    id    |    title   |    filename            |
*------------------------------------------------*

Lets say this is the structure of the table. I want to update every single row of this table.

Lets say i want to replace every space in Filename with a underscore.

$new_filename = str_replace(" ", "_", $filename);

mysql_query("UPDATE table SET Filename = '$new_filename'");

This does not work. Each row has different Filename.

so each row has a different filename and in each row you want to replace spaces with underscores in each of those filenames? — dldnh
– dldnh, Commented Mar 3, 2012 at 15:56
Brian, because the Filename is not set, the Filename of all rows in the table are empty after the command — 2by
– 2by, Commented Mar 3, 2012 at 19:05

2by · Accepted Answer · 2012-03-04 01:28:07Z

12

I would simply do this:

mysql_query("UPDATE table SET Filename = REPLACE(Filename, ' ', '_')");

This way you only execute one command and depending on your table size, this should be pretty quick

*edited

edited Mar 4, 2012 at 1:28

2by

1,0935 gold badges25 silver badges40 bronze badges

answered Mar 3, 2012 at 15:55

Book Of Zeus

49.9k18 gold badges176 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

2by Over a year ago

Thank you! What if i wanted to search for something and replace any rows that had a Filename that matches the search?

Book Of Zeus Over a year ago

You just add a where clause eg: WHERE filename = 'Some file'`. read more: dev.mysql.com/doc/refman/5.0/en/where-optimizations.html and also dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

iankit Over a year ago

Its a great way, but I would be more interested in a general answer. Letsay, what if the change was not so simple. For example the database entry has a short HTML snippet containing embed code of a video and few other things. I want to change that embed code to HTML5 complaint and change those nubmers to f(original nos). This I guess would require querying through php and then updating the row in it.

Rolf ツ · Accepted Answer · 2012-03-03 16:04:27Z

3

First answer is way better :)

So your filename is in every row the same? otherwise this code would replace all the filenames with one name. You will need a for loop get the data from the table and update it.

Like this:

    $query = "SELECT id, filename FROM table";
    $result = mysql_query($query); 
    if($result){
        $num = mysql_numrows($result);
        $i=0;
        while ($i < $num) {
            $new_filename = str_replace("_", " ", mysql_result($result,$i,"filename"));
            $id = mysql_result($result,$i,"id");
        mysql_query("UPDATE table SET filename = '$new_filename' WHERE id='$id'");
        }
    }

edited Mar 3, 2012 at 16:04

answered Mar 3, 2012 at 15:58

Rolf ツ

8,8216 gold badges51 silver badges75 bronze badges

1 Comment

Jason Kennaly Over a year ago

This helped me more than the chosen answer because the update I needed would have been very tough to do in SQL.

christophmccann · Accepted Answer · 2012-03-03 15:55:09Z

0

That will work, provided you have connected to the database using mysql_connect() first.

If you are just doing this as a one-off your solution is fine - but if you are allowing users to do this via a form of some kind then you have a security flaw called SQL Injection. If the variable $filename is submitted by a user, then they could end the query and start a new one. You should never place unsanitised data into a MySQL query.

To get round this put $filename through mysql_real_escape_string or even better use prepared statements provided by MySQLi or PDO.

answered Mar 3, 2012 at 15:55

christophmccann

4,2218 gold badges44 silver badges66 bronze badges

Collectives™ on Stack Overflow

Update every row in a table with PHP in mySQL database

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related