[SOLVED] help with query

[crazyglue71]

hi can someone please help me out i've been trying to solve this problem for a week and cant come up with a solution

 

i have a table `Drinks` with about 10000 entries

 

in that table there's  field names `ID` `Title` `DrinkUri`

 

what i'm trying to do is take the `Title` and put it in `DrinkUri` with a - between the words

 

so example:

 

ID= 1 , Title= Some drink name, DrinkUri would look like Some-drink-name.

 

here is what i've come up with.. it works but it enters the first ID into all of the entries.

 

 

 

the WHERE ID= ?? I need to be incremented somehow using a while loop or something

 

so when i execute the sql.php file it will select ID 1 do its thing the select ID 2 do its thing and so on.....

 

 

 

<?

 

 

require_once( 'inc/header.inc.php' );

require_once( BX_DIRECTORY_PATH_INC . 'design.inc.php' );

require_once( BX_DIRECTORY_PATH_INC . 'admin_design.inc.php' );

require_once( BX_DIRECTORY_PATH_INC . 'languages.inc.php' );

echo "START .....<br /><br />";

 

echo "UPDATING DATABASE .....<br /><br />";

 

    $sTitle = db_value("SELECT `Title` FROM `Drink` WHERE ID = ?? ");

 

 

$sDrinkUri = uriGenerate($sTitle, 'Drink', 'DrinkUri');

 

$res = db_res("UPDATE `Drink` SET  `DrinkUri`='$sDrinkUri' WHERE ID = ?? ");

 

 

 

?>

 

 

 

[PFMaBiSmAd]

If uriGenerate() is literally just replacing spaces with -, you can do this in a single UPDATE query (without a WHERE clause.)

 

What is the code for uriGenerate()?

 

If you are doing more in uriGenerate(), you would just need the first SELECT query to select all the rows (no WHERE clause), then loop through the result set, set $sDrinkUri and execute the UPDATE query for each row.

 

[crazyglue71]

thanks for your prompt reply

 

can you please show me an example of doing it with a single query

 

Thanks

[PFMaBiSmAd]

"UPDATE `Drink` SET  `DrinkUri`= REPLACE(`Title`,' ','-')"

[crazyglue71]

you my friend are a life saver!! thank you VERY much it worked!!