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ї"parrent_id"])." <A HREF="?groep=".$group_id."">".$rowї"name"]."</A>";
}
return;
}
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!!!

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'
Thanks!