Replacing in MySQL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Mr. Tech
Forum Contributor
Posts: 205
Joined: Tue Feb 11, 2003 4:18 pm
Location: Australia

Replacing in MySQL?

Post by Mr. Tech »

Hi guys!

I've got this links directory script which allows users to have as many sub categories as they want.

Now in the MySQL database I have a field called path. Here are some examples:

Home
Home:Marketing
Home:Marketing:Subcat
Home:Marketing:Subcat:Subcat2

So what that is showing is the categories. Does that make sense? Now if I wanted to change the word Marketing to Marketing Stuff, on every other row that says Marketing would change to Marketing Stuff. Now it would look like this:

Home
Home:Marketing Stuff
Home:Marketing Stuff:Subcat
Home:Marketing Stuff:Subcat:Subcat2

Make sense? How would I do that?

THANKS!!!!

Tech
User avatar
d1223m
Forum Commoner
Posts: 80
Joined: Mon Mar 31, 2003 5:15 am
Location: UK, West Sussex

Post by d1223m »

i think youve got your database setup wrong.
for trees like that its much easier with a table set up like this :

id int primary key auto_increment,
categoryName char(100),
parentId int

so you would then have data like this:

0 Home 0
1 Marketting 0
2 Subcat 1
3 Subcat2 2

now when you change the name of a node the children only rely on the id so large text replaces are not needed.
Mr. Tech
Forum Contributor
Posts: 205
Joined: Tue Feb 11, 2003 4:18 pm
Location: Australia

Post by Mr. Tech »

Yes but then I can't have this many subcategories:

Home:Marketing:Subcat:Subcat2:Subcat3:Subcat4

I found this function but not sure if it is it:

path=REPLACE(path,'$slash_path','$target_path')

:?
User avatar
d1223m
Forum Commoner
Posts: 80
Joined: Mon Mar 31, 2003 5:15 am
Location: UK, West Sussex

Post by d1223m »

with my method you have as many children ( sub categories ) as you like!
i think your mis-understanding something.
your method take up much more storage than mine also - especially when you start talking _lots_ of children.
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

And it's not maintainable, like you'r now expirencing.

You have to change many records for just one correction.
Mr. Tech
Forum Contributor
Posts: 205
Joined: Tue Feb 11, 2003 4:18 pm
Location: Australia

Post by Mr. Tech »

Yes but with your way you can display the links in a certain ctageory on your pages:

Home > Marketing Stuff > Subcat > Subcat2

Or can you?
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

It's easy done.

Code: Select all

function show_group($group_id){
    $query = "SELECT parrent_id,name from groups where id='".$group_id."';
    if(!$result = mysql_query($query)){
      echo mysql_error();
      echo $query;
    }
    if($row = mysql_fetch_array($result)){
      return show_group($row&#1111;"parrent_id"])." <A HREF="?groep=".$group_id."">".$row&#1111;"name"]."</A>";
    &#125;
    return;
  &#125;
I didn't test it. But i think it will work.
Love this way of programming, i forgot the english word for it.
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

recursive is the word your looking for, i think.
Mr. Tech
Forum Contributor
Posts: 205
Joined: Tue Feb 11, 2003 4:18 pm
Location: Australia

Post by Mr. Tech »

I'll have a look at your way. Thanks.

But for the future, does anyone know how to replace in MySQL?

path=REPLACE(path,'$oldtitle','$newtitle')

??

THANKS!!! :D
User avatar
Sevengraff
Forum Contributor
Posts: 232
Joined: Thu Apr 25, 2002 9:34 pm
Location: California USA
Contact:

Post by Sevengraff »

i think you mean update.

Code: Select all

UPDATE table SET name = jim WHERE name = fred
Mr. Tech
Forum Contributor
Posts: 205
Joined: Tue Feb 11, 2003 4:18 pm
Location: Australia

Post by Mr. Tech »

I worked it out. I know you add update, I just didn't show it here:

update table set path=REPLACE(path,'$oldtitle','$newtitle') where path rlike '$oldtitle'

:D

Thanks!
Post Reply