[SOLVED] structure of mysql database and sending data via php

[dinvinci]

I have set up a database with a single table called referrals. It holds records of families referred for their children to receive services from my organization. The form collects one parent's name and anywhere from 1 to 6 kids (sometimes more).

 

So right now I have a single table with the fields parentLastName, child1name, child2name, etc.

 

I realize this is kind of a dumb way to do it, and that I should really have one table with the parent's name, and a related table called children which has a record for each child. Doing it in this way would also make it much easier to extract data about just the children (like which ages of kids we serve the most, etc.)

 

The problem is I don't really know how to send related data to two different tables via php. Right now I am sending all the information collected on the web form with code more or less like this:

 

$agency = $_POST['agency'];
$parentLastName = $_POST['parentLastName'];
$child1name = $_POST['child1name'];
$child2name = $_POST['child2name'];

mysql_query("INSERT INTO `referral` (agency, parentLastName, child1name, child2name) VALUES ('$agency', '$parentLastName', '$child1name', '$child2name')");

 

It seems like I need to split up my insertion statement so that I insert the child data into a table called child. But I don't exactly understand how all the data stays linked together. I have an auto-incrementing field called autonumber on the parent record. I guess I need to create the parent record, obtain the autonumber that was assigned, and assign this as a field on the child records? I don't quite know how to write this process out in php?

 

Am I on the right track as to what needs to happen, or is there another way?

[MadTechie]

Okay i only skimmed the post but i think you want something like this

 

3 tables (as examples)

Table: users

userID, UserName, FirstName

 

Table: Gifts

GiftID, GiftName, GiftPrice

 

Table: UserGifts

uID, UserID, GiftID

 

 

now the 'Users' table has a list of users and the 'Gifts' table a list of gifts..

so to link a gift to a users you add a new records in 'UserGifts' with the usersID and the GiftID..

 

make sense ?

 

of course Gifts could be the children and the user could be the parent..

or even users be the family name and the gifts members of that family

 

as a side note for autonumbers.. if you do an insert use mysql_insert_id() to get the autonumbers..

 

hope this helps

 

 

EDIT: oh the uID on UserGifts table is UniqueID, (most tables should have one)

[dinvinci]

I see what you're saying and I agree, now I'm trying to figure out the php to make it work. If I send what I have below, there's no link between the parent record and the children record. Also I want child1, child2, etc to actually be separate records.

 

$agency = $_POST['agency'];
$parentLastName = $_POST['parentLastName'];
$child1name = $_POST['child1name'];
$child1age = $_POST['child1age'];
$child2name = $_POST['child2name'];
$child2age = $_POST['child2age'];

mysql_query("INSERT INTO `parents` (agency, parentLastName, child1name, child2name) VALUES ('$agency', '$parentLastName', '$child1name', '$child1age', '$child2name', '$child2age')");

mysql_query("INSERT INTO `children` (child1name, child1age, child2name, child2age) VALUES ('$child1name', '$child1age', '$child2name', '$child2age')");

[dinvinci]

 

as a side note for autonumbers.. if you do an insert use mysql_insert_id() to get the autonumbers..

 

Okay I figured out the insert_id thing...but I still don't know how to separate my sets of child data into separate records. This is the form btw:

 

http://jwchendersonville.org/referral.php

[premiso]

Ok, you want to do this in 3rd Normal Form, as it is faster and makes it much less of a headache.

 

Google that for more information (or 3NF)

 

Basically you want 3 tables for this.

 

Agency, Parents, Children

 

Using the mysql_insert_id() you want to insert the parent first.

 

Parent Table:

parent_id

parent_name

 

Child Table:

child_id

parent_id

child_name

child_age

 

Agency Table:

agency_id

parent_id

agency_name

 

Using that structure you insert parent first, then children using the parent id then agency using the parent id also. This will allow you to associate multiple records with one parent with fast sql searching and a single parent can have unlimited children.

 

I am rushed right now, but will reply back if needed later on.

[dinvinci]

I don't really need any information about the agency other than the name so I'm not sure it needs to be in its own table?

 

I was kind of thinking this code would work:

 

mysql_query("INSERT INTO `parents` (agency, contact, email, telephone, p_first, p_last, p_phone, comments, certify) VALUES ('$agency', '$contact', '$email', '$telephone', '$p_first', '$p_last', '$p_phone', '$comments', '$certify)");

$parentID = mysql_insert_id();

mysql_query("INSERT INTO `children` (parentID, child1name, child1age, child1sex, child1shirt. child1pants, child1comment) VALUES ('$parentID', '$child1name', '$child1age', '$child1sex', '$child1shirt', '$child1pants', '$child1comment')");

mysql_query("INSERT INTO `children` (parentID, child2name, child2age, child2sex, child2shirt. child2pants, child2comment) VALUES ('$parentID', '$child2name', '$child2age', '$child2sex', '$child2shirt', '$child2pants', '$child2comment')");

 

But nothing is getting inserted into the table ???

 

[premiso]

I would highly suggest you read up on proper database coding, and 3rd normal form. It will save headaches later on.

 

 

To get your code to work

 

mysql_query("INSERT INTO `parents` (agency, contact, email, telephone, p_first, p_last, p_phone, comments, certify) VALUES ('$agency', '$contact', '$email', '$telephone', '$p_first', '$p_last', '$p_phone', '$comments', '$certify)") OR DIE(mysql_error());

$parentID = mysql_insert_id();

mysql_query("INSERT INTO `children` (parentID, child1name, child1age, child1sex, child1shirt. child1pants, child1comment) VALUES ('$parentID', '$child1name', '$child1age', '$child1sex', '$child1shirt', '$child1pants', '$child1comment')") OR DIE(mysql_error());

mysql_query("INSERT INTO `children` (parentID, child2name, child2age, child2sex, child2shirt. child2pants, child2comment) VALUES ('$parentID', '$child2name', '$child2age', '$child2sex', '$child2shirt', '$child2pants', '$child2comment')") OR DIE(mysql_error());

 

Add

 

OR DIE(mysql_error());

 

At the end of each mysql_query call. This will tell you if your data that you are putting into the query is flawed (which chances are it is).

[dinvinci]

I would highly suggest you read up on proper database coding, and 3rd normal form. It will save headaches later on.

 

OR DIE(mysql_error());

 

 

Hey, that helped a LOT! Thanks!

 

[dinvinci]

I've got the insertion working into the parent and child tables, with parentID being stored as a value on the child records so I can match up the right kids to the parents later when I display the results.

 

I've run into a new problem -- I have up to 6 children which can be entered on the html form, and I'm inserting them as separate mysql queries like this:

 

$child1 = "INSERT INTO `children` (parentID, childname, childage) VALUES ('$parentID', '$child1name', '$child1age')";
mysql_query($child1) or die(mysql_error());

$child2 = "INSERT INTO `children` (parentID, childname, childage) VALUES ('$parentID', '$child2name', '$child2age')";
mysql_query($child2) or die(mysql_error());

et cetera through child6...

 

However if some of the html fields are blank (because the parent only has 1 child) then blank rows are getting inserted into the child table. How can I prevent that?

[premiso]

Do an if statement before the insert.

 

If child2 field is not blank then write the insert statement.

 

Basic programming there...

[dinvinci]

I am not a programmer, just a volunteer figuring this out as I go along. Thanks for the if suggestion, I did this and it works:

 

if ($child1name=="") {
  // do nothing
} else {
  // insert the fields
}

[premiso]

I am not a programmer, just a volunteer figuring this out as I go along. Thanks for the if suggestion, I did this and it works:

 

if ($child1name=="") {
  // do nothing
} else {
  // insert the fields
}

 

Written like a pro, I would suggest this to avoid the extra else =)

 

if (trim($child1name) != "") {
  // insert
}

 

A bit more efficient.

[dinvinci]

Thanks! That's very helpful!