Page 1 of 1

Interesting CMS Question

Posted: Sun Dec 05, 2004 9:54 pm
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?

Posted: Sun Dec 05, 2004 10:31 pm
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.

Posted: Sun Dec 05, 2004 10:46 pm
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

Posted: Sun Dec 05, 2004 10:54 pm
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?

Posted: Sun Dec 05, 2004 11:33 pm
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

Posted: Mon Dec 06, 2004 1:14 pm
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?

Posted: Mon Dec 06, 2004 1:45 pm
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]