I am trying to write a database for my cub scout unit..

[wsipple]

I am trying to build a database for my scout pack that will allow me to search each scouts awards and achievements and list those achievements on an website... Each scout has multiple achievements and awards and I want to list each one with scout name / award name / date earned / date received..

I am trying to get it to look something like this

Name            Award Name          Date Earned    Date Received
Ryan James      Webelos Badge       2009-05-22     2009-06-14
                Bobcat Badge        2007-04-09     2009-05-04
                Arrow of Light      2010-02-10     2010-02-22
 
Mike Toliver    Bobcat Badge        2007-07-21     2007-10-14

and so on... I just need the information to be pulled from different tables

How do I organize the tables to let me access a scout name and then every award that scout has earned...

Please help!!!

Thanks for your assistance in this matter

Bill

[Atli]

Hey.

I would recommend a typical many-to-many (N:M) structure, like this:

+--------+      +-----------------+     +---------+
| scouts |      | scout_awards    |     | awards  |
+--------+      +-----------------+     +---------+
| id (PK)|>---->| scout_id (PK,FK)|  |-<| id (PK) |
| name   |      | award_id (PK,FK)|<-|  | name    |
| etc... |      | earned          |     | etc...  |
+--------+      | received        |     +---------+
                +-----------------+

It allows you to simply list all the scouts in the "scouts" table, all the awards in the "awards" table, and then link each scout to their respective awards via the intermediary table "scout_awards".

You can then list all the awards each scout has by joining the three tables. Somewhat like this:

SELECT  s.`name` AS 'scout',
        a.`name` AS 'award',
        sa.`earned`,
        sa.`received`
FROM    `scouts` AS s
INNER JOIN `scout_awards` AS sa
    ON  sa.`scout_id` = s.`id`
LEFT JOIN `awards` AS a
    ON  a.`id` = sa.`award_id`

Which would give you a simple table of data:

+--------------+----------------+---------------------+---------------------+
| scout        | award          | earned              | received            |
+--------------+----------------+---------------------+---------------------+
| Mike Toliver | Bobcat Badge   | 2007-07-21 00:00:00 | 2007-10-14 00:00:00 |
| Ryan James   | Webelos Badge  | 2009-05-22 00:00:00 | 2009-06-14 00:00:00 |
| Ryan James   | Bobcat Badge   | 2007-04-09 00:00:00 | 2009-05-04 00:00:00 |
| Ryan James   | Arrow of Light | 2010-02-10 00:00:00 | 2010-02-22 00:00:00 |
+--------------+----------------+---------------------+---------------------+

You would then use whatever front-end language you are using for you website development to format this the way you want it to be. For example, using PHP, you could do this to get the exact plain/text format you posted above:

<?php
// Connect to database
$dbLink = new mysqli('localhost', 'user', 'passwd', 'dbName');
if(mysqli_connect_errno()) {
    die("Failed to connect to database: " . mysqli_connect_error());
}
 
// Fetch all the data
$sql = "SELECT  s.`name` AS 'scout',
                a.`name` AS 'award',
                sa.`earned`,
                sa.`received`
        FROM    `scouts` AS s
        INNER JOIN `scout_awards` AS sa
            ON  sa.`scout_id` = s.`id`
        LEFT JOIN `awards` AS a
            ON  a.`id` = sa.`award_id`";
$result = $dbLink->query($sql);
 
// Fetch the data and put it into an array
// grouped by the scout name.
$data = array();
if($result && $result->num_rows > 0)
{
    while($row = $result->fetch_assoc())
    {
        $name = array_shift($row);
        $data[$name][] = $row;
    }
    $result->close();
}
$dbLink->close();
 
// Print the data.
header('Content-type: text/plain; charset=UTF-8');
echo "Name            Award Name          Date Earned     Date Received    \n";
foreach($data as $_name => $_awards)
{
    $printName = true;
    foreach($_awards as $_award)
    {
        // Name column. Make it empty for all
        // but the first award row.
        if($printName) {
            printf("%-16s", $_name);
            $printName = false;
        }
        else {
            printf("%-16s", "-");
        }
 
        // Award data
        printf("%-20s", $_award['award']);
        printf("%-16s", date('Y-m-d', strtotime($_award['earned'])));
        printf("%-16s", date('Y-m-d', strtotime($_award['received'])));
        echo "\n";
    }
}
?>

Which prints:

Name            Award Name          Date Earned     Date Received    
Mike Toliver    Bobcat Badge        2007-07-21      2007-10-14      
Ryan James      Webelos Badge       2009-05-22      2009-06-14      
                Bobcat Badge        2007-04-09      2009-05-04      
                Arrow of Light      2010-02-10      2010-02-22      

Hope that helps.

[wsipple]

Thank you for your help.. I will try this code out as soon as I can.. It looks like it will do what I need it to... again thank you..

Ok I had some trouble getting the code to work so I re-wrote the database and put all the information into one table

Now I would just like to fix the syntax of the PHP page to make the dates be -- Example--> January 30, 2010

$f2=mysql_result($result,$i,"scout_birthdate");

<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2 ?></font></td>

how can I change the syntax to give me F-j-Y... thank you again for all your help.

[code green]

Take a look at the date() function.
It can format dates in many ways

[Atli]

A combination of the strtotime and the date functions can do that.

$formatted = date("F-j-Y", strtotime($dateFromMysql));

The date function converts a Unix timestamp into a formatted string, based on the pattern. The strtotime converts most known date formats into a Unix timestamp, including the format MySQL uses.

[wsipple]

Thanks guys that really did the trick for me.. I appreciate everyones help...

You guys are the best...