Re: [PHP] Categories like wordpress
On Thu, May 1, 2008 at 9:23 AM, Ryan S [EMAIL PROTECTED] wrote: Hey! Heres what i have to do, upload pics and each pic can be a part of x number of categores for example: A picture of a rose can be for birthday, as well as anniversary, or miss you, or love etc exactly the same as how in wordpress an article can be in multiple categories like tech, current news, gadgets etc Was wondering how to do this, and please tell me if I am on the wrong path but: in the mysql db next to img name have a field called cate (or whatever for categories) and have a comma seperated list for each of the categories that the pic falls into and then do a mySql LIKE '%$param%' search if the user wants 'birthday'? Is this how its done or am I barking up the wrong tree? TIA /R ** Well you could go with a comma separated list of keywords, or you could build a keywords table, and then a table for associating each picture with a given keyword. So you have your images table with your image information, then you have your keywords table with your keyword information, then an associations table with the image id and keyword id link. Either way should work ok. -- -Dan Joseph Build a man a fire, and he will be warm for the rest of the day. Light a man on fire, and will be warm for the rest of his life.
Re: [PHP] Categories like wordpress
Is this how its done or am I barking up the wrong tree? You have multiple options: 1. Fully normalised, where you have three tables - one for your articles, one for your categories and a link table. 2. A SET MySQL datatype which will allow upto 32 predefined categories. 3. A simple CHAR type with the category names, (or if you use numeric IDs, you could have many more categories). The former is, IIRC, faster, but more work to implement. Whereas the latter two are very fast to implement but somewhat slower to query. For the last two cases the MySQL function FIND_IN_SET() will be very useful. -- Richard Heyes ++ | Access SSH with a Windows mapped drive | |http://www.phpguru.org/sftpdrive| ++ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Categories like wordpress
On May 1, 2008, at 743AM, Richard Heyes wrote: Is this how its done or am I barking up the wrong tree? You have multiple options: 1. Fully normalised, where you have three tables - one for your articles, one for your categories and a link table. This is the route that I would suggest, it's the most flexible and fastest to query. Just to expound a little... images table: - image_id - image_filename category table - category_id - category_name image_category_mapping table: - image_id - category_id Then when you want to get all of the images for a category: SELECT image_id, image_filename FROM images JOIN image_category_mapping ON images.image_id = image_category_mapping.image_id WHERE category_id = 10; Getting all the categories an image is in: SELECT category_id FROM image_category_mapping WHERE image_id = 42; It seems like more work than the route you described, but it's way more flexible, and as Richard pointed out, it's fully normalized. Brady -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Categories like wordpress
it's the most flexible and fastest to query. Mmm, that's debateable. Not knowing the ins and outs of MySQL I'd hazard a guess that reading an extra 255 bytes from the table file might be faster in some circumstances than opening two extra table files. And I don't see why it's more flexible...? The latter two solutions I suggested are certainly easier to work with and (with todays hardware), speed isn't always a factor. -- Richard Heyes ++ | Access SSH with a Windows mapped drive | |http://www.phpguru.org/sftpdrive| ++ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Categories like wordpress
Hey! Thanks for replying guys! clip 1. Fully normalised, where you have three tables - one for your articles, one for your categories and a link table. This is the route that I would suggest, it's the most flexible and fastest to query. /clip Anyone happen to have a little PHP code as an example here ( a little more than Brady gave me)? or links to where i can read up on this mySql and php side as I have never done anything like this before. TIA, R Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Categories like wordpress
On May 1, 2008, at 843AM, Richard Heyes wrote: it's the most flexible and fastest to query. Mmm, that's debateable. Not knowing the ins and outs of MySQL I'd hazard a guess that reading an extra 255 bytes from the table file might be faster in some circumstances than opening two extra table files. And I don't see why it's more flexible...? Good point, I should have said can be more flexible and faster to query. I've found that every time I try to do something like this without having a mapping table that I eventually end up wanting to move to the mapping table setup. Of course that might just be because I'm so used to doing it that way. Certainly that doesn't mean that other solutions are wrong or bad in any way. I need to learn to not speak in absolutes. :) The latter two solutions I suggested are certainly easier to work with and (with todays hardware), speed isn't always a factor. Yes, another great point. Brady -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Categories like wordpress
Hey, Am not really used to using the JOIN in SQL so am a bit confused as to what kind of data I need to enter into this table: image_category_mapping table: - image_id - category_id for all of this to work... mind explaining a bit? Thanks! Ryan Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Categories like wordpress
Ryan S wrote: Hey, Am not really used to using the JOIN in SQL so am a bit confused as to what kind of data I need to enter into this table: image_category_mapping table: - image_id - category_id It comes down to database normalization (http://en.wikipedia.org/wiki/Database_normalization). The idea behind database normalization is you only ever have one copy of the data, you don't have to update multiple tables to reflect a change. So you only store the image in the 'images' table: create table images ( image_id int auto_increment primary key, image_name text, .. ); For multiple categories to share a particular image, there needs to be a link between a category and an image. If you only ever want one image per category, you could put the image_id in the categories table: create table categories ( category_id int auto_increment primary key, category_name text, image_id int, . ); However the problem with that is if you want more than one image, instead of adding another field to the categories table: create table categories ( category_id int auto_increment primary key, category_name text, image_id int, image_id_two int, . ); which means if you want a category to have 3 images, you need another field and so on, so instead you create a separate table: create table category_images ( category_id int, image_id int ); which means you could have 10 images for that category and no db changes are necessary. So you create the image as normal (insert into images ...). Then when you link it to a particular category, you insert into the separate table: insert into category_images (category_id, image_id) values (.); This also means if you update the image (eg change the name or alt-value or whatever), it's automatically reflected for all categories. How do you get an image for the particular category? SELECT i.* from images i INNER JOIN category_images ci ON (image_id) WHERE category_id='X'; That will give you (multiple if necessary) images for that category. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
