[SOLVED] how many tables will i need?

[uswege]

I am creating a website for my client the address is http://www.gcntcommunitycaretz.org/ (the site under construction)

 

I want main part of the site to get its contents from the database.

from http://www.gcntcommunitycaretz.org/ i have six headings,

1. STREET CHILDREN

2. ORPHANS

3. HANDICAPPED CHILDREN

4. HIV/AIDS

5. DOMESTIC CHILD LABOUR

6. HIV/AIDS IN CHRISTIAN DENOMINATIONS

 

I want all the six headings to get its contents from the DB, i will display for example the first five lines on the main page, if the reader wants to read more s/he clicks the link (will will be provided)

 

My question is, need i have six different tables for each heading? is there a way to put all this in one table? if yes, how will i have to about it.

 

 

[kathas]

one table will do...

 

Just add a column named category or sth.

 

Then for getting everything on STREET CHILDREN u just have to use a query

" ......... WHERE category='STREET CHILDREN' "

 

Kathas

[uswege]

Thanx Kathas,

 

wonder if i got u right or not, i've used the query below to create a table and thereafter phpmaker to create php scripts.

 

  CREATE TABLE main (

    id int NOT NULL auto_increment,

    street_children text,

    orphans text,

    handicapped_children text,

    hiv_aids text,

    child_labor text,

    christian_denomination text,

    category varchar (20),

    PRIMARY KEY (id)

  );

 

The problem is, is if i want to input (add or edit) all comes in one page (as if all info are for one item).

 

To be specific,

(1) Did i get the table structure right?

(2) If i got it right how am i supposed to go about getting the functionality i want (i.e be able to edit/update a category)

[steelmanronald06]

Table: category

cat_id

cat_name

 

Table: Child

child_id

child_name

cat_id

 

So, you enter all your categories into the first table.  The id will be a number, and the name will of course be the name. Then on your second table you enter in the children. The child cat_id should equal the same as the category id you want the child to have.  Then in your query do:

 

<?php

 

// first get the category id

$sql = "SELECT cat_id FROM category WHERE cat_name='hiv_aids'";

$query = mysql_query($sql) or die(mysql_error());

$cat_id = mysql_result($query);

 

$sql = "SELECT * FROM Child WHERE cat_id='$cat_id'";

$query = mysql_query($sql) or die (mysql_error());

 

while ($r = mysql_fetch_array($query)

{

echo $r['child_name'] . "<br />";

}

?>

 

 

[kathas]

Late Reply...

 

Well you got what i said totally wrong... 

 

I meant sth like this which requires less queries to the DB than Ronald's

 

CREATE TABLE main (
    id int NOT NULL auto_increment,
     content text,
     category varchar (20),
     PRIMARY KEY (id)
   );

 

So you just use a query like this to get everything for 'handicapped_children'

 

<?php
$query = mysql_query("SELECT * FROM main WHERE category='handicapped_children'");

while while ($r = mysql_fetch_array($query)
{
echo $r['content'];
}
?>

 

Put more stuff in the table like 'Title','time_written','Author' stuff like that

 

---

Kathas

[HuggieBear]

I meant like this which requires less queries to the DB than Ronald's

 

CREATE TABLE main (
    id int NOT NULL auto_increment,
     content text,
     category varchar (20),
     PRIMARY KEY (id)
   );

 

 

Put more stuff in the table like 'Title','time_written','Author' stuff like that

 

You're right, it would reduce the queries but it doesn't make the data as 'flexible'.  Check out Database Normalization

 

Regards

Huggie

[Daniel0]

Table: category

cat_id

cat_name

 

Table: Child

child_id

child_name

cat_id

 

So, you enter all your categories into the first table.  The id will be a number, and the name will of course be the name. Then on your second table you enter in the children. The child cat_id should equal the same as the category id you want the child to have.  Then in your query do:

 

<?php

 

// first get the category id

$sql = "SELECT cat_id FROM category WHERE cat_name='hiv_aids'";

$query = mysql_query($sql) or die(mysql_error());

$cat_id = mysql_result($query);

 

$sql = "SELECT * FROM Child WHERE cat_id='$cat_id'";

$query = mysql_query($sql) or die (mysql_error());

 

while ($r = mysql_fetch_array($query)

{

echo $r['child_name'] . "<br />";

}

?>

 

 

 

<?php
$query = mysql_query("SELECT * FROM Child LEFT JOIN category ON category.cat_id=Child.cat_id WHERE category.cat_name='hiv_aids'") or die(mysql_error());
while($r = mysql_fetch_array($query)
{
echo $r['child_name'] . "<br />";
}
?>

 

With one query only...

[steelmanronald06]

I used two queries in hopes he would understand it better, but one query can be used. My way means that you can update the name of a category with one query and it will apply to every child because they use the cat_id and that never changes.  In this way, if you ever need to update my way is easier on your system. If you needed to update doing it the other way suggested, you would have to search the ENTIRE table data (which can get encumbersome if you have large amounts of data) and replace where each rule was met. See how my way gives you an extra table, but also gives you that extra free system resources when it comes down to mass alterations?

[kathas]

Yep guys

 

I have to agree with you...!

 

Thanks for pointing that out!

 

---

Kathas

[uswege]

thanks guys, it worked perfectly well but still running in my local server. in the nxt few days will post the address under (web critique section) for your review.