Interesting CMS Question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Interesting CMS Question

Post by curseofthe8ball »

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?
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

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.
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

*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
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

Scorphus, your expertise with MySQL is greater then mine. I'm not sure I quite understand your ideas. N:M relations... foreign keys... am I over my head here?
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

These are concepts of database systems. You can learn a lot from the Wikipedia:*follow other links there...

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

Post by curseofthe8ball »

Scorphus,

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&#1111;]' 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)) &#123;
					$id = $data&#1111;'id'];
					$title = $data&#1111;'title'];
					
echo "<option value='$id' "; if ($practice_areas == "$id") &#123; echo "selected"; &#125; else &#123; echo ""; &#125; echo ">$title</option>"; &#125;

echo "</select>
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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

curseofthe8ball wrote: How do I write the code to insert EACH of the selected fields into a new row of a database
use [php_man]foreach[/php_man] to loop through the selected fields..... and do many inserts.. ;)
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.
mysql has [php_man]mysql_insert_id[/php_man]
Post Reply