Inefficient sql update with php script

dynamic update statement with multiple calls to db

[vkimura]

This code works but it's very inefficient. This script basically updates a field called "php_name" to equal an autoincrement field called "article_id"; however, the field "php_name" needed a prefix forward slash "/" and a suffex of ".php" appended to the autoincrement value from "article_id".

The problem with this code is that there were multiple calls to the database. I've only allowed "$i<=10" because a larger number such as 200 would be disallowed by my hosting provider. There must be a more efficient way to write this code perhaps in straight sql code. Any help would be greatly appreciated.

<?php
$connection = mysql_connect();
$db = mysql_select_db();

for ($i=1; $i<=10; $i++){
$article_id = "/".$i.".php";
$sql = "update mortgage_define set php_name = '$article_id' where article_id='$i'";
$sql_query = mysql_query ($sql, $connection)
}

?>

[RichD]

This should do it.

<?php
$connection = mysql_connect();
$db = mysql_select_db();

$sql = "update mortgage_define set php_name = CONCAT('/',article_id,'.php')";
$sql_query = mysql_query ($sql, $connection)
}
?>

That will update all lines in the table.

[Birdman]

Why not just add the slash and file extension on the fly when displaying the links and not even have the column with the true link?

[vkimura]

Thanks, RichD. That sql statement is so fast! Love it.

And thanks, Birdman. I would've never thought of that. Nice to hear other POVs.