Here's the scope. I'm developing a content management system for a client. One of the functions they need is the ability to create/edit/delete Practice Areas and News Articles. I can do all this without any problems but now they've requested the ability to specify on what Practice Areas certain News Articles should appear on the right hand column.
I would love to simply Query the MySQL database, get the Practice Area titles and list them all with checkboxes beside each one. This would allow the client to check the box next to each Practice Area that they want the News Article to appear on.
The problem I have is that the number of Practice Areas is going to be a variable number as the client will be adding/deleting them often.
So, how can I setup a table to store the News Article and which Practice Areas it should be associated with?
Any ideas?
Interesting CMS Question
Moderator: General Moderators
-
curseofthe8ball
- Forum Commoner
- Posts: 73
- Joined: Sun Jun 01, 2003 12:33 am
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
If i understand your question you can just have a table holding the pratice areas, and then create a dynamic list of all the ones currently available for them to check. And then when you delete a practice area - show a warning to say if there are any news items in that area and if they still want to delete the area remove the reference to it/or delete the article.
Storage-wise i would have all the areas in one table with the name and id, and then have a column in the article table that holds comma seperated numbers that relate to the id in the area table.
Storage-wise i would have all the areas in one table with the name and id, and then have a column in the article table that holds comma seperated numbers that relate to the id in the area table.
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
*Mods, think this is a Database related post.
Well, it seems to me that a News Article can appear in multiples Practice Areas and a Practice Area can show multiples News Articles. You could set up an N:M relation between Practice Area and News Article. This new relation will be a table with two primary foreign keys. Let's call the table 'PAshowNA'. Here is the relational model:
PracticeArea (id, content, ...)
NewsArticle (id, content, ...)
PAshowNA (id_parea, id_narticle)
+ - - PAshowNA[id_parea] p-> PracticeArea[id] (the 'p' means propagate in case of deletion)
+ - - PAshowNA[id_narticle] p-> NewsArticle[id]
With this relation you can solve your problem since you know wich NA is shown in which PA.
What is your DBMS?
-- Scorphus
Well, it seems to me that a News Article can appear in multiples Practice Areas and a Practice Area can show multiples News Articles. You could set up an N:M relation between Practice Area and News Article. This new relation will be a table with two primary foreign keys. Let's call the table 'PAshowNA'. Here is the relational model:
PracticeArea (id, content, ...)
NewsArticle (id, content, ...)
PAshowNA (id_parea, id_narticle)
+ - - PAshowNA[id_parea] p-> PracticeArea[id] (the 'p' means propagate in case of deletion)
+ - - PAshowNA[id_narticle] p-> NewsArticle[id]
With this relation you can solve your problem since you know wich NA is shown in which PA.
What is your DBMS?
-- Scorphus
-
curseofthe8ball
- Forum Commoner
- Posts: 73
- Joined: Sun Jun 01, 2003 12:33 am
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
These are concepts of database systems. You can learn a lot from the Wikipedia:
There is also one good book on the subject: Fundamentals of Database Systems, by Navathe.
How did you design your database?
-- Scorphus
- http://en.wikipedia.org/wiki/Database
- http://en.wikipedia.org/wiki/Foreign_key
- http://en.wikipedia.org/wiki/Relational_model
There is also one good book on the subject: Fundamentals of Database Systems, by Navathe.
How did you design your database?
-- Scorphus
-
curseofthe8ball
- Forum Commoner
- Posts: 73
- Joined: Sun Jun 01, 2003 12:33 am
Scorphus,
Quick question for you. If I load my practice area titles and IDs into a SELECT MULTIPLE field like so:
How do I write the code to insert EACH of the selected fields into a new row of a database? I've never worked with arrays or loops to execute the insert statement. Any help you could provide would be great!
Furthermore, when the client goes to create a new News Article, it won't have an ID number until it is actually submitted to the DB. Is there a way that I can get the ID number of this new article to insert into the 3rd table along with the Practice ID number?
Quick question for you. If I load my practice area titles and IDs into a SELECT MULTIPLE field like so:
Code: Select all
<select name='practice_groupsї]' multiple>";
$query = "SELECT * FROM practice_groups ORDER BY order_id,id ASC";
$result = mysql_query($query) or die(mysql_error());
while ($data = mysql_fetch_assoc($result)) {
$id = $dataї'id'];
$title = $dataї'title'];
echo "<option value='$id' "; if ($practice_areas == "$id") { echo "selected"; } else { echo ""; } echo ">$title</option>"; }
echo "</select>Furthermore, when the client goes to create a new News Article, it won't have an ID number until it is actually submitted to the DB. Is there a way that I can get the ID number of this new article to insert into the 3rd table along with the Practice ID number?
use [php_man]foreach[/php_man] to loop through the selected fields..... and do many inserts..curseofthe8ball wrote: How do I write the code to insert EACH of the selected fields into a new row of a database
mysql has [php_man]mysql_insert_id[/php_man]curseofthe8ball wrote: Furthermore, when the client goes to create a new News Article, it won't have an ID number until it is actually submitted to the DB.