Page 1 of 1

Replacing in MySQL?

Posted: Thu Apr 24, 2003 12:04 am
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

Posted: Thu Apr 24, 2003 3:22 am
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.

Posted: Thu Apr 24, 2003 5:38 pm
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')

:?

Posted: Fri Apr 25, 2003 2:51 am
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.

Posted: Fri Apr 25, 2003 3:10 am
by []InTeR[]
And it's not maintainable, like you'r now expirencing.

You have to change many records for just one correction.

Posted: Fri Apr 25, 2003 7:25 pm
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?

Posted: Sat Apr 26, 2003 7:08 am
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.

Posted: Sun Apr 27, 2003 2:54 am
by Sevengraff
recursive is the word your looking for, i think.

Posted: Sun Apr 27, 2003 8:32 pm
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

Posted: Sun Apr 27, 2003 8:52 pm
by Sevengraff
i think you mean update.

Code: Select all

UPDATE table SET name = jim WHERE name = fred

Posted: Sun Apr 27, 2003 11:24 pm
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!