MySQL Data REPLACE Question...
 

So I know that I can call information from the SQL database and output it and replace certain words using the REPLACE string but, how would I go about replacing everything in a specific column with a word, or set of words, without actually removing that columns data?

How do I replace whatever is in that 'pets' column with the word 'zombie' no matter if its dog, fish, cat, porcupine, etc? There are a bunch of different variations of 'pet'.

Is there something like REPLACE_ALL I can use across the column or can I only replace certain substring occurrences? :helpme

[quickedit]

Also, does it matter if the string is numeric or alphanumeric too as far as the command goes?

Your description is kind of odd but I think you are looking for CONCAT.

SELECT CONCAT(`animal`,'zombie') AS animal FROM pets WHERE `item` = 'dog'

If you mean you want to replace dog, cat, and fish with 'zombie' then
REPLACE(`animal`,'dog','zombie')

If you want to replace multiple, then something like
REPLACE(REPLACE(REPLACE(`animal`,'fish','zombie'), 'cat','zombie'),'dog','zombie')

Thanks, going to lookup CONCAT now. [edit] No, not what I'm wanting, the replace is what I am trying to do but being lazy about it and not having to type out a few hundred individual words to replace with 'zombie' lol

What if there are thousands of rows with different items, say 100 different items plus, is the only way to replace them by using each items value?

Is there a command such as REPLACE_COLUMN which will just change the data in that specific 'pets' column irrespective of the rows value?

Your description is still incredibly confusing, but there is no other replace query in mysql.
You can do this with php

Okay, guess I need to write out all the different values to replace in $replace = array :1orglaugh

Thank you for the help, I appreciate it :)

Well, you're gonna have to input them one way or the other. You can pull them into the array from another mysql table if that helps.

UPDATE table SET pets = 'zombie';

This, and you can mimic replace behavior with a where statement.

Or if you're replacing multiple values

But this is only for columns holding single term values. If you are replacing words inside strings then you need to use the REPLACE statement.

You are overthinking it, I think. You do not need to do a replace.

Just select the string you want.

Select 'zombie' as petname, other field, otherfield, etc. from pets where whatever.

That way you get the data from the record but instead of the name you get zombie.

But you can also do that when you print out the data.

Select * from pets.

While($row=...)
{
echo 'Pet name: Zombie' . ' ' . $row['pet_price'] . ' ' . whetever other data you want
}

I think that is what it sound like you are trying to do.

.

Yeah but that is changing the data in the table and he said he did not want to do that.

.

My bad, I got a little lost reading through the replies.

In that case I would explicitly state the columns I'm selecting where one would simply be a string.

ie:

So instead of pulling data from the 3rd column I'm replacing it with a specific string, or number for that matter.

OOPS, second on the ball.

Too early morning here. So to clarify, sarettah gave the correct answer, and with an eye for detail with the "as petname" part so you can still access the column by its original name.

Apologies for the confusion, am in need of more coffee.

The thing is, that the way he explained it I still don't really know what he's after. As far as I can tell he has one column that he wants to replace several words in but not replace the whole column.

So the row has a record with a piece of text that says 'I have a dog and a cat' and he wants to replace it with "I have a zombie and a zombie".

I want to replace the contents of the entire column, without changing the data in that specific column.

So whether the row says dog, cat, bird, fish, etc. I want the output in the page to say Zombie.

Going to play around with the suggestions above, thank you all for your help :)

If you don't need the data from the database and don't need to change it there's no reason to even access the database. Are you trolling?

$pet = 'zombie';



It may not be what he wanted, but he said it repeatedly:

"How do I replace whatever is in that 'pets' column with the word 'zombie' no matter if its dog, fish, cat, porcupine, etc?"

"the replace is what I am trying to do but being lazy about it and not having to type out a few hundred individual words to replace with 'zombie'"

"Is there a command such as REPLACE_COLUMN which will just change the data in that specific 'pets' column irrespective of the rows value?"

UPDATE yourTable
SET pets = zombie

?

:1orglaugh:1orglaugh This topic is golden.

Yeah, that's the answer to what he was repeatedly asking for, as posted here.

But apparently his actual requirement is simply "How do I make the word 'zombie' appear on a web page".

You don't get this on Stackoverflow.

no kidding.. fun read thanks

And this is where you lost everyone. You want to go left but you want to go right :1orglaugh

"SELECT 'zombie' AS animal, id, some, other, columns FROM pets"

If this is the solution to your problem, you need to take a step back and look at where you fucked up because you're fixing an issue you shouldn't have to begin with.

This is what I thought earlier, like just use Zombie.. But I was pretty high so I didn't reply lol. :upsidedow

:1orglaugh

I suspect OP is just looking for something like string replace and just wants to change the output...

$stringFromDB = "my cat can't swim...";
$newString = str_replace("cat", "zombie", $stringFromDB); //replace
echo $newString; //print

No clue how that animal name from the table column/row got inside that string in the first place... I mean, why the string isn't "my zombie can't swim...", in the first place... But hey, if it helps.:thumbsup

I think the OP was pretty high too. :)

I can only think they were doing stuff that does involve the database and just carried that on with this 'pets' value, thinking everything has to be pulled even if you're just replacing it anyway.

Would be a lot easier if they had posted some code though, rather than everyone else having to guess.

Just to clarify, the reason I need to change the output in a specific column is because the data from that column is used in several blocks of text across a site.

Example:

My best friend has a 'cat' for a pet that he feeds 'cat'food to on a daily basis at 9am.

My friends 'cat' only likes a certain brand of 'cat'food.

On another page its, a section with specifics that I do not want the word 'cat' to appear. instead something like 'pet' as placeholder text while I edit the row data to actually have a value instead of currently being empty so that once the column has data in it, I do not have to go in and edit the HTML or code on the page:

Animal Type: 'cat'
Brand: BrandName
Cost: $12

Some rows do not have a value in them for 'cat' so until I get a value in there, I just want to use a generic word so that the page still 'reads' correctly, even if there is no specific data being displayed.

So whether the word is dog, cat, fish, etc I want to use 'zombie' (or 'pet') as the word being displayed hence changing the output data without actually changing the entire data set, because what is in there, is correct for the other pages.

<?php if(!$row['animal']) { $row['animal'] = 'pet'; } ?>

Assuming you are somewhere within the <?php { //your code } ?>, where you are getting data from the selected row. Basically, the code where you get the database data, you can use str_replace.


So, something like this:


{
//your code
$newString = str_replace("cat", "pet", $row['pets']);
// replaces cat with the word pet.
echo $newString; //print output if you want
}

Replace $row['pets'], with the actual variable you are using $row[pet_name] or whatever.


Edit: Nevermind, usek0nr4d's comment instead. It replaces empty values and prints pet as placeholder.

Awesome, thank you so much and apologies for all the confusion in this thread, I knew what I wanted to do just wasn't sure what the terminology was :1orglaugh

Thank you everyone for your help with this.