Reputation: 1093
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.
Upvotes: 3
Views: 3372
Reputation: 49877
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
Upvotes: 12
Reputation: 8781
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'");
}
}
Upvotes: 3
Reputation: 4211
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.
Upvotes: 0