Tech MySQL Data REPLACE Question... - GoFuckYourself.com

Publisher Bucks · 09-16-2021, 12:50 PM

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?

Quote:

SELECT animal FROM pets WHERE item=dog REPLACE zombie

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?

[quickedit]

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

k0nr4d · 09-16-2021, 01:03 PM

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

Publisher Bucks · 09-16-2021, 01:07 PM

Quote:

Originally Posted by k0nr4d

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?

Publisher Bucks · 09-16-2021, 01:10 PM

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

k0nr4d · 09-16-2021, 01:18 PM

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

Code:

$replace = array("cat","dog","fish");
$with = "zombie";
$result = mysqli_query($dblink,"SELECT animal,id FROM pets");
while($row = mysqli_fetch_assoc($result)) { 
   $row['animal'] = mysqli_real_escape_string($dblink,stri_replace($replace,$with,$row['animal']));
   mysqli_query($dblink,"UPDATE pets SET animal = '".$row['animal']."' WHERE record_num = '".$row['id']."'");
}

Publisher Bucks · 09-16-2021, 01:20 PM

Quote:

Originally Posted by k0nr4d

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

Code:

$replace = array("cat","dog","fish");
$with = "zombie";
$result = mysqli_query($dblink,"SELECT animal,id FROM pets");
while($row = mysqli_fetch_assoc($result)) { 
   $row['animal'] = mysqli_real_escape_string($dblink,stri_replace($replace,$with,$row['animal']));
   mysqli_query($dblink,"UPDATE pets SET animal = '".$row['animal']."' WHERE record_num = '".$row['id']."'");
}

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

Thank you for the help, I appreciate it

k0nr4d · 09-16-2021, 01:30 PM

Quote:

Originally Posted by Publisher Bucks

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

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.

ZTT · 09-16-2021, 01:43 PM

UPDATE table SET pets = 'zombie';

natkejs · 09-16-2021, 07:42 PM

Quote:

Originally Posted by ZTT

UPDATE table SET pets = 'zombie';

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

Code:

UPDATE table SET pets = 'zombie' WHERE pets = 'cat';

Or if you're replacing multiple values

Code:

UPDATE table SET pets = 'zombie' WHERE pets IN ('cat','dog','mouse');

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.

sarettah · 09-16-2021, 08:17 PM

Quote:

Originally Posted by Publisher Bucks

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?

[quickedit]

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

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.

.

sarettah · 09-16-2021, 08:18 PM

Quote:

Originally Posted by natkejs

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

Code:

UPDATE table SET pets = 'zombie' WHERE pets = 'cat';

Or if you're replacing multiple values

Code:

UPDATE table SET pets = 'zombie' WHERE pets IN ('cat','dog','mouse');

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.

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

.

natkejs · 09-16-2021, 08:34 PM

Quote:

Originally Posted by sarettah

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:

Code:

SELECT column1,column2,'zombie',column4 FROM table;

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.

natkejs · 09-16-2021, 08:43 PM

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.

k0nr4d · 09-16-2021, 10:52 PM

Quote:

Originally Posted by natkejs

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

Publisher Bucks · 09-16-2021, 11:06 PM

Quote:

Originally Posted by k0nr4d

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

ZTT · 09-17-2021, 03:01 AM

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';

Quote:

he said he did not want to do that..

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?"

zijlstravideo · 09-17-2021, 03:08 AM

Quote:

Originally Posted by ZTT

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

?

zijlstravideo · 09-17-2021, 03:33 AM

Quote:

Originally Posted by Publisher Bucks

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

Quote:

Originally Posted by ZTT

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';

This topic is golden.

ZTT · 09-17-2021, 03:38 AM

Quote:

Originally Posted by zijlstravideo

UPDATE yourTable
SET pets = zombie

?

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

Quote:

Originally Posted by zijlstravideo

This topic is golden.

You don't get this on Stackoverflow.

_Richard_ · 09-17-2021, 04:18 AM

Quote:

Originally Posted by ZTT

You don't get this on Stackoverflow.

no kidding.. fun read thanks

k0nr4d · 09-17-2021, 04:32 AM

Quote:

Originally Posted by Publisher Bucks

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

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

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

Colmike9 · 09-17-2021, 05:24 AM

Quote:

Originally Posted by ZTT

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';

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

zijlstravideo · 09-17-2021, 05:39 AM

Quote:

Originally Posted by Colmike9

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

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.

ZTT · 09-17-2021, 06:36 AM

Quote:

Originally Posted by Colmike9

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

I think the OP was pretty high too.

Quote:

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

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.

Publisher Bucks · 09-17-2021, 08:21 AM

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.

k0nr4d · 09-17-2021, 08:31 AM

Quote:

Originally Posted by Publisher Bucks

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'; } ?>

zijlstravideo · 09-17-2021, 08:33 AM

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.

Publisher Bucks · 09-17-2021, 08:37 AM

Quote:

Originally Posted by k0nr4d

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

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

Thank you everyone for your help with this.

09-16-2021, 01:03 PM	#2
k0nr4d Confirmed User Industry Role: Join Date: Aug 2006 Location: Poland Posts: 9,228	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') __________________ Mechanical Bunny Media Mechbunny Tube Script \| Mechbunny Webcam Aggregator Script \| Custom Web Development

09-16-2021, 01:18 PM	#5
k0nr4d Confirmed User Industry Role: Join Date: Aug 2006 Location: Poland Posts: 9,228	Your description is still incredibly confusing, but there is no other replace query in mysql. You can do this with php Code: $replace = array("cat","dog","fish"); $with = "zombie"; $result = mysqli_query($dblink,"SELECT animal,id FROM pets"); while($row = mysqli_fetch_assoc($result)) { $row['animal'] = mysqli_real_escape_string($dblink,stri_replace($replace,$with,$row['animal'])); mysqli_query($dblink,"UPDATE pets SET animal = '".$row['animal']."' WHERE record_num = '".$row['id']."'"); } __________________ Mechanical Bunny Media Mechbunny Tube Script \| Mechbunny Webcam Aggregator Script \| Custom Web Development

09-16-2021, 01:43 PM	#8
ZTT Confirmed User Industry Role: Join Date: Apr 2019 Posts: 657	UPDATE table SET pets = 'zombie'; __________________ __________________

09-16-2021, 08:43 PM	#13
natkejs Confirmed User Industry Role: Join Date: Jan 2003 Location: Nomad Land Posts: 1,601	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. __________________

09-17-2021, 08:33 AM	#27
zijlstravideo Confirmed User Industry Role: Join Date: Sep 2013 Location: The Netherlands Posts: 805	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. __________________ Contact: email

09-16-2021, 01:10 PM	#4
Publisher Bucks Confirmed User Industry Role: Join Date: Oct 2018 Location: New Orleans, Louisiana. / Newcastle, England. Posts: 1,111	Is there a command such as REPLACE_COLUMN which will just change the data in that specific 'pets' column irrespective of the rows value?

09-17-2021, 08:21 AM	#25
Publisher Bucks Confirmed User Industry Role: Join Date: Oct 2018 Location: New Orleans, Louisiana. / Newcastle, England. Posts: 1,111	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.