multi db $'s into 1 array

Anon

I'm sure some wizard out there might be a bit more of an array wizard than I'm proving to be :-))
The prob ....

I've set up a birthday list - all data is chucked into a mysql db.
The db is set up such, that I've got the main person and his/ her respective kids.
The fields are: christ_name, birthdate, birthmonth, kid1name, kid1date, kid1month, kid2name, kid2date, kid2month, kid3name, kid3date, kid3month ..etc

Right, here comes the problem:
Trying to list them in cronological order, I've taken this approach which seems to cock up:

include('includes/db_connect.php');
$sql = "SELECT christ_name, birthdate, birthmonth, kid1name, kid1date, kid1month, kid2name, kid2date, kid2month, kid3name, kid3date, kid3month, kid4name, kid4date, kid4month FROM birthdays WHERE birthmonth='$thismonth' OR kid1month='$thismonth' OR kid2month='$thismonth' OR kid3month='$thismonth' OR kid4month='$thismonth' GROUP BY birthdate, kid1date, kid2date, kid3date, kid4date ASC";
$result = mysql_query($sql, $conn);

if ($result) {
$number = mysql_num_rows($result);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

$b_month = $row[2];
$kid1_month = $row[5];
$kid2_month = $row[8];
$kid3_month = $row[11];
$kid4_month = $row[14];

if ($thismonth == $b_month) {
$name = $row[0];
$birthday= "$row[1].$row[2]";
}
if ($thismonth == $kid1_month) {
$name = $row[3];
$birthday= "$row[4].$row[5]";
}
if ($thismonth == $kid2_month) {
$name = $row[6];
$birthday= "$row[7].$row[8]";
}
if ($thismonth == $kid3_month) {
$name = $row[9];
$birthday= "$row[10].$row[11]";
}
if ($thismonth == $kid4_month) {
$name = $row[12];
$birthday= "$row[12].$row[13]";
}

// this is where I tried the arrays

echo $birthday;

echoing $birthday works fine - but not in chronological order!?!
I HAVE tried varying array statements (which I have not included here). Can anyone put me on the right track??

Thx
Kev

Anon

Instead of storing the birthday as 3 fields, stoer them as one DATE/TIME/TIMESTAMP field. It'll be easier to sort this way, coz you can just do:

ORDER BY the_date_time_field ASC

Diego

Anon

There's no ORDER BY clause.

But I think the real problem you're having may be a conceptual one - first thing I ask myself is "what if they have five kids?"

What I would recommend is to separate the kids out into their own table. The person's own information remains where it is, and in the kids' table is the kids' information, plus a field that contains the main person's primary key. Something like:

table kids

name | birthdate | birthmonth | parent_id
-----+-----------+-------------+-----------

Then finding kids with a birth month this month for a particular person in chronological order would be a matter of

SELECT kid_name, kid_date, parent_id FROM kids WHERE birth_month='$this_month' AND parent_id='$parent_id' ORDER BY birthdate

The main person's birthday is of course still in the main table. A separate query to get that one.

Or alternatively:

SELECT k.kid_name, k.kid_date, b.christ_name, FROM birthdays b, kids k WHERE k.birth_month='$this_month' AND k.parent_id=b.id ORDER BY k.kid_date, b.christ_name

I may have those to sort terms back to front; the idea being to sort the kids under the same parent together and then under each parent have them sorted chronologically.

But the basic database design principle is a normalisation one: a table contains in each record only the information that applies to the thing the record represents. The birthdays of a person's kids are not properties of the person.