dreamwest Posted January 12, 2009 Share Posted January 12, 2009 Heres a tricky one, im trying to strreplace while executing a mysql command, not sure how i should go about it... mysql_connect("localhost", "user", "pass") or die(mysql_error()); mysql_select_db("dbname") or die(mysql_error()); $result = mysql_query("SELECT * FROM video") or die(mysql_error()); while($row = mysql_fetch_array( $result )){ mysql_query("UPDATE video SET title='".$row['full_video_name'].", ' WHERE VID='".$row['id']."'") or die(mysql_error()); } Basically this script transfers the column "full_video_name" to column "title". Full video name is something like: i-like-crosscountry.wmv I need to strip the '-' and extension using: <?php $theFile = 'I-like-crosscountry.wmv'; $theFile = str_replace("I like crosscountry.wmv","I like crosscountry",$theFile); echo $theFile; //outputs I like crosscountry ?> So how does one execute a strreplace with this query? Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/ Share on other sites More sharing options...
trq Posted January 12, 2009 Share Posted January 12, 2009 No need for loops, or indeed php at all. Should be able to get this done with a simple query. UPDATE video SET title = REPLACE(SUBSTRING_INDEX(full_video_name, '.', 1), '-', ' '); Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735173 Share on other sites More sharing options...
nadeemshafi9 Posted January 12, 2009 Share Posted January 12, 2009 No need for loops, or indeed php at all. Should be able to get this done with a simple query. UPDATE video SET title = REPLACE(SUBSTRING_INDEX(full_video_name, '.', 1), '-', ' '); nice function dude, but if he dosent have teh correct myhsql version he can use pseudo while mysql = fetch row { title = get the title new title = ereg_replace(-, '', title) update the record where id = row[id] } Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735175 Share on other sites More sharing options...
dreamwest Posted January 12, 2009 Author Share Posted January 12, 2009 No need for loops, or indeed php at all. Should be able to get this done with a simple query. UPDATE video SET title = REPLACE(SUBSTRING_INDEX(full_video_name, '.', 1), '-', ' '); Still not seeing it....how can i integrate this into the above script?? Like this??: $result = mysql_query("SELECT * FROM video") or die(mysql_error()); while($row = mysql_fetch_array( $result )){ mysql_query("UPDATE video SET title= REPLACE(SUBSTRING_INDEX(full_video_name, '.', 1), '-', ' '), ' WHERE VID='".$row['id']."'") or die(mysql_error()); } Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735181 Share on other sites More sharing options...
trq Posted January 12, 2009 Share Posted January 12, 2009 Still not seeing it....how can i integrate this into the above script?? Like this??: Nope, like this.... mysql_connect("localhost", "user", "pass") or die(mysql_error()); mysql_select_db("dbname") or die(mysql_error()); $sql = "UPDATE video SET title = REPLACE(SUBSTRING_INDEX(full_video_name, '.', 1), '-', ' ');"; mysql_query($sql) or die(mysql_error()); Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735188 Share on other sites More sharing options...
nadeemshafi9 Posted January 12, 2009 Share Posted January 12, 2009 this will work if ur mysql dosent have that function or if u are finding it hard <?php mysql_connect("localhost", "user", "pass") or die(mysql_error()); mysql_select_db("dbname") or die(mysql_error()); $result = mysql_query("SELECT * FROM video") or die(mysql_error()); while($row = mysql_fetch_array( $result )){ $title_altered = ereg_replace("\-", "", $row['full_video_name']); mysql_query("UPDATE video SET title='".$title_altered .", ' WHERE VID='".$row['id']."'") or die(mysql_error()); } ?> you have an error in the above code here: le='".$title_altered .", ' WHE look at the ' ' its after the , eg '".$title_altered .", ' it should be '".$title_altered ."' , plus teh first one and second one are different chars one is a top tick and one is a single quote ' ' but it wont show on myh machine use this <?php mysql_connect("localhost", "user", "pass") or die(mysql_error()); mysql_select_db("dbname") or die(mysql_error()); $result = mysql_query("SELECT * FROM video") or die(mysql_error()); while($row = mysql_fetch_array( $result )){ $title_altered = ereg_replace("\-", "", $row['full_video_name']); mysql_query("UPDATE video SET title='".$title_altered ."', WHERE VID='".$row['id']."'") or die(mysql_error()); } ?> you need to sort this bit out WHERE VID='".$row['id']."'") its either WHERE id='".$row['id']."'") or WHERE VID='".$row['VID']."'") you need to check what its called in the db also same for mysql_query("UPDATE video SET title='".$title_altered .", ' WHERE VID='".$row['id']."'") the feild title is it title or video title you need to check in the db whatever is in row will be the name of teh feild eg row['video_title'] exactly like in teh db or row['title'] if its that, if u whant tgo set it it should be the name in the db not any random name video_title = 'blah' if thats what it is in the db Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735197 Share on other sites More sharing options...
dreamwest Posted January 12, 2009 Author Share Posted January 12, 2009 Hey not bad!! Works well Thanks Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735201 Share on other sites More sharing options...
trq Posted January 12, 2009 Share Posted January 12, 2009 SUBSTR_INDEX and REPLACE have been part of mysql since day dot. No need for multiple queries, php loops and especially any ereg* functions. Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735213 Share on other sites More sharing options...
nadeemshafi9 Posted January 12, 2009 Share Posted January 12, 2009 SUBSTR_INDEX and REPLACE have been part of mysql since day dot. No need for multiple queries, php loops and especially any ereg* functions. i knew you were gona say that , yeh cool like you said but at least he has more than one choice i agree with you thorpe it will make it slower using ereg Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735229 Share on other sites More sharing options...
nadeemshafi9 Posted January 12, 2009 Share Posted January 12, 2009 Hey not bad!! Works well Thanks what you should do now is comment out the code that i gave u if it works, and try make thorpes way work Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735237 Share on other sites More sharing options...
dreamwest Posted January 12, 2009 Author Share Posted January 12, 2009 Hey not bad!! Works well Thanks what you should do now is comment out the code that i gave u if it works, and try make thorpes way work Did it thorpes way to begin with Thanks for your help anyways Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735252 Share on other sites More sharing options...
nadeemshafi9 Posted January 12, 2009 Share Posted January 12, 2009 cool, thats the proper way anyhow Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735373 Share on other sites More sharing options...
dreamwest Posted January 12, 2009 Author Share Posted January 12, 2009 Thorpe can you post a link to this syntax, i want to add a ucfirst to the command mysql_connect("localhost", "user", "pass") or die(mysql_error()); mysql_select_db("dbname") or die(mysql_error()); $sql = "UPDATE video SET title = REPLACE(SUBSTRING_INDEX(full_video_name, '.', 1), '-', ' ');"; mysql_query($sql) or die(mysql_error()); Thanks Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735732 Share on other sites More sharing options...
trq Posted January 13, 2009 Share Posted January 13, 2009 mysql does not have a ucfirst function. You would either need to apply php's when you display your data or use logic similar to that of what nadeemshafi9 pointed out (loop through your records and perform an update). You can see mysql's string functions in the manual (funnily enough). Quote Link to comment https://forums.phpfreaks.com/topic/140484-solved-a-tricky-one/#findComment-735795 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.