[RESOLVED] A Tricky Situation

[drax007]

Hi there,

Im building a database with web front end for an ainimal welfare charity and am currently focusing on 3 tables of the dataabase.

The Animal table (animal details, name, age , type etc..),

Experience table(experience that the animal may have i.e. onlead, off lead, cars, alone)

and finally the Animal Experience table (combines the animal table with the experience table)

Until now the Experience table and Animal Experience table didnt exist and the experiences just existed as attributes of the animal table.. In that setup it is easy to see how you could present the experiences in a form using check boxes....

PLEASE SEE Screen Print Attachment & table structure below for clarity!

Since seperating the tables I have wondered how to present the Animal Experience informarton in a form using check boxes.

It doesnt seem to work that way? As each record entry in the Animal Experience table would require an AnimalID and a ExpID...So following this concept...in order to list all the experiences in a checkbox format each tick in a check box would represent an insertion into the Ainimal Experience table!

Also the AnimalID would be the same for each of the Animal Experiences as in that instance they would be added to a specific animal.

So really my question is... with the structure of the tables is it possible to present the Ainimal Experiences in a form using check boxes (of all experiences) and when the user checks the checkboxes a record is inserted(AnimalExpID, AnimalID, ExpID) for each experience they have checked???

Any ideas on how to do that if at all possible??

Thanks for Listening

Animal Table
AnimalID
AnimalTypeID
Name
BreedID
Age

Experience
ExpID
Name

Animal Experience
AnimalExpID
ExpID
AnimalID

[NogDog]

Each checkbox would have the same name, with "[]" at the end in order to make a sub-array of the $POST data:

<label><input type="checkbox" name="exprience[]" value="1">Off Leash</label>
<label><input type="checkbox" name="exprience[]" value="2">Cars</label>
etc....

Then in your form processor, you would look for any checkbox entries, and insert them into the table after you insert the animal and get its ID. Assuming MySQL and the basic mysql_*() functions:

// insert to animal table:
$result = mysql_query($insertSql);
$animalId = mysql_insert_id();
if(!empty($_POST['experience']))
{
   $experiences = array();
   foreach($_POST['experience'] as $exp)
   {
      $experiences[] = sprintf('(%d, %d)', (int)$animalId, (int)$exp);
   }
   $sql = sprintf(
      "INSERT INTO animal_experience (AnimalID, ExpID) VALUES %s",
      implode(', ', $experiences)
   );
   $result = mysql_query($sql);
}

That use of implode will result in a comma-separated list of value expressions, so the query will look something like:

INSERT INTO animal_experience (AnimalID, ExpID) VALUES (33, 1), (33, 3), (33, 4)

[drax007]

Hi there,

Thanks for the help, i see what your saying. I'll test it out today.

Thank you

[drax007]

Hi there,
Im little bit confussed with this 1 line of the code:

$animalId = mysql_insert_id();

Is this the function that will insert the same AnimalID record into the table (as many times as there are AnimalExperiences)????? If so i think i get it fully 🙂

Thanks

[NogDog]

No, that line gets the value of the auto-incremented primary key field from the previous insert query. This, of course, assumes that the AnimalID column is an auto-incremented primary key. 🙂 That value is then used in the AnimalID column of the insert values for the Animal_Experience relation table.