Jump to content

[SOLVED] A tricky one?


dreamwest

Recommended Posts

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?

Link to comment
Share on other sites

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]

 

}

Link to comment
Share on other sites

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());


}

 

Link to comment
Share on other sites

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());

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.