How to enter each post together in one field for the database via form?

[djfox]

I`m working on this invoice creator for work, and I`m having a bit of trouble for this one function. I have created a checkbox list of services a clerk can check off that the customer wants to have done and another checkbox list of which of their pets is involved (for those who have multiple pets). I have the form to send each checked entry to the next page just fine. However, I want the entries to go into the database in one field. For example, for the checkboxes of the pets, it should list off 23,24,25,26 in a single field "pets", while the services 1,2,3,4 should be in another single field together.

 

Right now I can only get it to list off each entry of the checkboxes that have been made. But I have no idea how I can get them into the database.

 

Here is the form invoice_start.php

<?php
//Date: June 30 2010

$Title = "Start an Invoice";

require_once "header.php";
require_once "parsecomment.php";
session_start();

$id = $_GET['id'];

$res = mysql_query("SELECT id, name, homephone, street, city, state, zip, cellphone, workphone, date FROM customer WHERE id='$id'");
$rows = mysql_fetch_row($res);
mysql_free_result($res);

$name = "Start an Invoice for $rows[1]";
include "box_start.php";


echo "Use this form to start an invoice for services for a customer. You will still have to use the register system to add in the costs to the system, but this will track what services were done in the past for a customer, create a record of what critters are currently boarding and will allow to print a nice invoice for customers to take home.";
echo "<p>";
echo "<form action='invoice_start2.php' method='post'>";
echo "<input type='hidden' name='customer' value='$id'>";
echo "<table border=0 width=100%>";
echo "<tr>";
echo "<td valign=top width=50%>";
echo "<b>Check all pets that apply to this invoice:</b>";
echo "<br>";
$query = "SELECT id, name, species
    FROM pets
    WHERE customer='$id' 
    ORDER BY name ASC";
  
  $result = mysql_query($query)or die( mysql_error() . "<HR>".$query);
  
  while($chap = mysql_fetch_row($result)){
    echo "<input type='checkbox' name='POLL[".$chap[0]."]'> $chap[1] <br>";
  }
echo "<td valign=top width=50%>";
echo "<b>Check all pets that apply to this invoice:</b>";
echo "<br>";
$query2 = "SELECT id, breed, type, cost
    FROM groom_prices 
    ORDER BY type ASC";
  
  $result2 = mysql_query($query2)or die( mysql_error() . "<HR>".$query2);
  
  while($chap2 = mysql_fetch_row($result2)){
    echo "<input type='checkbox' name='COST[".$chap2[0]."]'> <b>$chap2[2]</b> <i>$chap2[1]</i> @ $ $chap2[3]<br>";
  }
echo "</table>";
echo "<p>";
echo "<b>Any Additional Products or Other Purchases, enter the amount (including tax), do NOT enter the $ symbol.</b>";
echo "<br><input type='text' name='add' size=75 maxlength=100>";
echo "<p>";
echo "<b>Any Additional Notes to Be Made in regards to today`s service or any notes to be made to customer:</b>";
echo "<br><textarea rows=6 cols=60 name='bod'></textarea>";
echo "<p>";
echo "<input type='submit' name='submit' value='Start Invoice'>";
echo "<input type='reset' name='reset' value='Clear Form'>";
echo "</form>";


include "box_end.php";
?>
<p>
<?php
include "footer.php";
?>

 

Here is what I have for invoice_start2.php so far (I don`t know how to continue it:

<?php
//Date: June 30 2010

$Title = "Start an Invoice";

require_once "header.php";
require_once "parsecomment.php";
session_start();

$name = "Start an Invoice";
include "box_start.php";


echo "Use this form to start an invoice for services for a customer. You will still have to use the register system to add in the costs to the system, but this will track what services were done in the past for a customer, create a record of what critters are currently boarding and will allow to print a nice invoice for customers to take home.";
echo "<p>";
$id = $_POST['customer'];
$add = $_POST['add'];
$bod = $_POST['bod'];

echo "ID: $id <br>Additional: $add <br>Notes: $bod<p>";

foreach($_POST['POLL'] As $PollItem=>$PollValue){
   $poll = $PollItem;
   echo "$poll<br>";
}
echo "<p>";
foreach($_POST['COST'] As $CostItem=>$CostValue){
   $cost = $CostItem;
   echo "$cost<br>";
}
echo "<p>";
echo $_POST['POLL'];



include "box_end.php";
?>
<p>
<?php
include "footer.php";
?>

 

The database I have setup for the invoice, the fields are:

id (int)

customer (int)

date (varchar)

pets (text)

services (text)

additional (int)

notes (text)

 

How do I get this entered into the database?

[trq]

I want the entries to go into the database in one field

 

Why? This wreaks of poor design. You should look into database normalization techniques.

[ChemicalBliss]

Aside from having a solution to the problem of imploding data into a variable, please read below;

 

The good thing about traditional databases are they can handle complex queries so as to keep your data properly categorised, eg - ideally the table structure would be something like;

 

Database Structure

--
-- Table structure for table `activeservices`
--

CREATE TABLE IF NOT EXISTS `activeservices` (
  `id` int(25) NOT NULL AUTO_INCREMENT,
  `serviceid` int(255) NOT NULL,
  `ownerid` int(255) NOT NULL,
  `petid` int(255) NOT NULL,
  `cost` double(5,2) NOT NULL,
  `status` varchar(255) NOT NULL,
  `datetime` bigint(16) NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `owners`
--

CREATE TABLE IF NOT EXISTS `owners` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `balance` double(5,2) NOT NULL,
  `phone` bigint(16) NOT NULL,
  `altphone` bigint(16) NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `pethistory`
--

CREATE TABLE IF NOT EXISTS `pethistory` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `petid` int(255) NOT NULL,
  `datetime` bigint(16) NOT NULL,
  `vetid` int(255) NOT NULL,
  `duration` int(255) NOT NULL,
  `cost` double(5,2) NOT NULL,
  `whathappened` text NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

--
-- Table structure for table `pets`
--

CREATE TABLE IF NOT EXISTS `pets` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `ownerid` int(255) NOT NULL,
  `speciesid` int(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `breed` varchar(255) NOT NULL,
  `birthdate` bigint(16) NOT NULL,
  `status` varchar(255) NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

--
-- Table structure for table `petservicelist`
--

CREATE TABLE IF NOT EXISTS `petservicelist` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `cost` double(5,2) NOT NULL,
  `requirements` text NOT NULL,
  `speciesid` int(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `species`
--

CREATE TABLE IF NOT EXISTS `species` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `vets`
--

CREATE TABLE IF NOT EXISTS `vets` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `datetime` int(16) NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

 

Structure Explanation

Take a look at the first table "owners" - This table only handles information specific to an "owner".

Then look at the "pets" table, They also only contain information specific to a pet. With the "ownerid" field, you can select the pets from specific owners easily.

*Note: You can copy/paste this SQL text into the "SQL" Tab field in PHPMyAdmin.

 

That's really the main point, seperate data where necessary, never try to stuff as much info as possible into a single field/table, it makes it harder to modify/read/understand and work with the data.

 

Some Example MySQL Queries

<?php

// include your mysql file
include("mysql.php");

// Select Every Pet (with the Species name), From Every Owner with the lastname of "Croge"
$query = "SELECT 
`owners`.`id` As ownerid,
`owners`.`firstname` As ownerfirstname,
`owners`.`lastname` As ownerlastname,
`owners`.`address` As owneraddress,
`owners`.`balance` As ownerbalance,
`owners`.`phone` As ownerphone,
`owners`.`altphone` As owneraltphone,
`owners`.`notes` As ownernotes,
`pets`.`id` As petid,
`pets`.`name` As petname,
`pets`.`breed` As petbreed,
`pets`.`status` As petstatus,
`species`.`id` As petspeciesid,
`species`.`name` As petspecies
FROM `owners`
LEFT JOIN `pets` on `owners`.`id`=`pets`.`ownerid`
LEFT JOIN `species` on `pets`.`speciesid`=`species`.`id`
WHERE `owners`.`lastname`='Croge'
ORDER BY petname";

$result = mysql_query($query, $conn) or die($query."\n\n<BR>\n\n".mysql_error());

while($row = mysql_fetch_array($result)){
print_r($row);
}
echo("\n\n<HR>\n\n");

// Select Every Event Record (History) (with the Vets names), From Every Pet with the id of 1
$query = "SELECT 
`pets`.`id` As petid,
`pets`.`name` As petname,
`pets`.`breed` As petbreed,
`pets`.`status` As petstatus,
`pethistory`.`id` As eventid,
`pethistory`.`datetime` As eventdate,
`pethistory`.`duration` As eventtime,
`pethistory`.`cost` As eventcost,
`pethistory`.`whathappened` As eventwhathappened,
`pethistory`.`notes` As eventnotes,
`pethistory`.`datetime` As eventtime,
`vets`.`id` As eventvetid,
`vets`.`firstname` As eventvetfirstname,
`vets`.`lastname` As eventvetlastname
FROM `pets`
LEFT JOIN `pethistory` on `pets`.`id`=`pethistory`.`petid`
LEFT JOIN `vets` on `pethistory`.`vetid`=`vets`.`id`
WHERE `pets`.`id`='1'
ORDER BY eventdate";

$result = mysql_query($query, $conn) or die($query."\n\n<BR>\n\n".mysql_error());

while($row = mysql_fetch_array($result)){
print_r($row);
}

?>

 

This is a simple script that just selects information. I have provided this to make it a little easier for you to change over easily. With this structure, you can see how you could use the pethistory table as a way of checking invoices, You could also remove "balance" from "owners" and create your own "invoices" table which would contain all the payments that are pending and paid, with the relevant information.

 

Hope this Helps (Took ages to write, was very interested in this one for some reason).

-cb-

 

PS: If you want some data just to test it out, import/execute this:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `vets`
--

--
-- Dumping data for table `activeservices`
--


--
-- Dumping data for table `owners`
--

INSERT INTO `owners` (`id`, `firstname`, `lastname`, `address`, `balance`, `phone`, `altphone`, `notes`) VALUES
(1, 'Pete', 'Croge', '54 Some Road, SomeCity, 53214, SomeCounty, SomeCountry', 0.00, 5551234567, 5557654321, 'some notes aaaa'),
(2, 'John', 'Krum', 'Flat A, 12 SomeRoad, SomeCity, 53261, SomeCounty, SomeCountry', 10.99, 555643634, 555967253, 'afjk awegl gklfaef aef ae');

--
-- Dumping data for table `pethistory`
--

INSERT INTO `pethistory` (`id`, `petid`, `datetime`, `vetid`, `duration`, `cost`, `whathappened`, `notes`) VALUES
(1, 1, 124135135, 1, 10, 10.99, 'Flea Removal', '...'),
(2, 1, 124137456, 2, 5, 2.99, 'Checkup on Fleas', '...'),
(3, 2, 124134365, 2, 24, 124.99, 'Surgery on broken leg', 'Went Well.'),
(4, 3, 124133456, 2, 10, 0.00, 'Free Checkup for Pet', '...');

--
-- Dumping data for table `pets`
--

INSERT INTO `pets` (`id`, `ownerid`, `speciesid`, `name`, `breed`, `birthdate`, `status`, `notes`) VALUES
(1, 1, 1, 'Duke', 'German Shepard', 0, 'Healthy', 'Last Seen in healthy condition'),
(2, 1, 2, 'Tibbles', 'Unknown', 0, 'OK', 'Nails needed clipping, But Healthy.'),
(3, 1, 1, 'Wilud', 'Cockerspaniel', 0, 'DECEASED', 'Died in the surgery kennels naturally due to cancer.'),
(4, 2, 1, 'Stella', 'Rott-Weiler', 0, 'Healthy', 'Last seen in good condition.');

--
-- Dumping data for table `petservicelist`
--


--
-- Dumping data for table `species`
--

INSERT INTO `species` (`id`, `name`, `description`, `notes`) VALUES
(1, 'Dog', 'Err, Dog.. Mammal. Sharp Teeth, Predator.', 'Must be kept inside Kennels at room B2.'),
(2, 'Cat', 'Feline Species, Jumps high etc.', 'Must be kept in Feline Cages room C1.');

--
-- Dumping data for table `vets`
--

INSERT INTO `vets` (`id`, `firstname`, `lastname`, `datetime`, `notes`) VALUES
(1, 'Vet', 'number 1', 123134365, 'Experience Etc etc.'),
(2, 'Vet', 'Number 2', 123135365, 'Experience etc etc.');

[djfox]

@Thorpe: Your post tells me nothing.

 

@ChemicalBliss:

Wait wait wait, are you telling me that the best way to do this is to have the clerk go through and do something like:

Create an invoice

Select Owner 1

Select Pet A

Select Service A

Create an invoice

Select Owner 1

Select Pet A

Select Service B

Create an invoice

Select Owner 1

Select Pet A

Select Service C

Create an invoice

Select Owner 1

Select Pet B

Select Service A

 

Etc etc etc? That`s ridiculous! Not to mention VERY inefficient for a business! Vet offices and other professional places do not do this.

 

Second of all, you`re telling me to rebuild my owner and pets tables and rebuild how the owners pets are id`ed to the owner? Why? My system of bringing up the owner`s pets are working just fine.

 

Unless I have TOTALLY misunderstood everything you`ve written...Which is quite possible.

[ChemicalBliss]

No, Read through my post carefully.

You would have an invoice table, the invoice would be far easier to create, select the name of the owner (as you would need to), select any pethistory events that are included in this invoice, voila - Enter any invoice specific details there you go - you can do this with one single html form.

 

When someone spends over 2 hours trying to make it as easy as possible to move you inthe right direction make sure you know what your saying:

other professional places do not do this.

 

In fact yes, every "truly Proffesional" Organization will have a well-designed and normalized data base. Like the one i have created (although they can get much more advanced with linked keys etc).

 

If your unsure/worried about drastically changing your database, create a new one, work with that - you dont have to destroy anything.

As i stated - it will save you hours in the ling run, and you will be actually advancing your good-practice knowledge rather than writing messy code you will have to scrap later because its just to complicated to add anything to it.

 

-cb-

 

PS: The reason i havnt provided the Invoice table in that table structure is because once you understand what im saying you will have _no_ problem adding it in.

[djfox]

Sorry to say that I haven`t a single clue of what it is you`re trying to explain to me to do. I`ve read over that post 6 times, read over your second post twice, I am not getting it at all.

[Stooney]

Thorpe actually told you what you needed.  You need to google 'database normalization techniques'.

[djfox]

Thorpe actually told you what you needed.  You need to google 'database normalization techniques'.

 

Why? What do I need it for? What does that do? What kind do I go for?  I`ve searched on this and all I`m finding are sources on books to buy, I can`t find anything online. Now I`m getting frustrated about trying to get this project done and no one here is making any sense or providing anything that is actually being helpful.

[litebearer]

Being as you insist on the 'sledge-hammer' approach to resolving your situation (anad YES I too fall victim to using that approach from time to time)...

 

simply impode the form data using some delimiter. that will create the string you desire.

 

Why NORAMILZE?  simple example . Your client wants to send out immuization reminders. IF you had a table that was simply pet id and proceedure id, it would be quit simple and quick to cull all the pets whose immunizations were coming due. YES you could create some convoluted mechanizim (sp) to get it from your method, but speed, accuracy  and simplicty are important is a business.

 

just my 2 cents

[djfox]

simply impode the form data using some delimiter. that will create the string you desire.

 

What? I don`t know what that is or how to do it.

 

Your client wants to send out immuization reminders.

Haven`t a clue of what that is either.

 

IF you had a table that was simply pet id and proceedure id, it would be quit simple and quick to cull all the pets whose immunizations were coming due.

 

Ok but wouldn`t this still require the clerk to sit there and fill out form after form after form just for one single client to do EACH job for a pet, and then do it all over again for their second pet, etc?

[trq]

normalization

[litebearer]

No offense but if you are doing work for a vet and have no idea what an immuization reminder is, you need to rethink some things.

 

As to form after form after form - the form is totally a seperate task/function. What you do with the form data does NOT require the clerk to be involved. You can have a gazillion (ok ok I exaggerate) fields on one form. NORMALIZATION cares less about what fields are on a form. It simply takes the data and stores in a more orderly and efficient manner.

 

 

As to imploding - it is similar to concatenation ie cat . "," . dog . "," . bird  gives you a string cat,dog,bird --- imploding takes and array and effecitively produces a similar result

 

 

 

[djfox]

No offense but if you are doing work for a vet and have no idea what an immuization reminder is, you need to rethink some things.

Yep, that`s the proper way to help me understand things...

 

I`m having an extremely difficult time understanding what it is that you guys are trying to tell me to do. All I can get out is that more tables have to be made and the clerk has to fill out multiple forms. That`s all that I`m getting from here.

[trq]

I`m having an extremely difficult time understanding what it is that you guys are trying to tell me to do. All I can get out is that more tables have to be made and the clerk has to fill out multiple forms. That`s all that I`m getting from here.

 

Did you read the tutorial I pointed you to? the idea of storing relationships in a comma separated field is poor design and in the long run is going to make things more difficult.

[djfox]

I`ve looked it over but I don`t see how that could possibly be done in one form. I can only see it being done with multiple forms.

[trq]

How your form interacts with the back end is beside the point at this point in time. You need to get your database design sorted.

 

I will however say that a single form is indeed possible and these techniques are used in allot of web applications.

[ChemicalBliss]

The only forms you would need are New Invoice/Edit Invoice/Delete Invoice.

 

Forms are just front-ends, you can put as many fields and different table data into a single form, and have the proces.php sort through all the different data and put them into the fields as necessary.

 

But for something like an invoice, with the table structure i outlined above, you would only need 1 single statement to add/edit/delete any invoices. and 1 Form.

 

If you can manage to get your head around the way the tables work, you will be like wow, why didnt i think of that.

 

Example End-User;

User selects a customer (Goes to a customer manage page)

  -> User chooses to issue an invoice to that customer

      -> User selects pet histories to attach to the invoice.pet services or whatever), you could use checkboxes

      -> User clicks submit. Everything is automatic, prices/address etc are calculated using the pet service history data.

 

Ideology of form Script; (This assumes a normalized database as i outlined above).

Gets Customer ID from GET variable

Uses Customer ID for Query to select all pet history (you can use a mysql flag to set which ones have been issued an invoice).

Presents Pet history in Rows with Descriptions etc and checkboxes.

( -> User submits form)

Script Gets selected Pet History IDs.

Script inserts a new row inside invoices table with all details of price/owner id/address issued to/date issued etc.

Script Updates selected pet history items to mark Invoice Issued.

Script Ends.

 

I find lists like these can help (to make a list you can actually do erquires a little experience with php and knowing what is possible).

If you do try to create this script (by doing each line one by one), you will need to start with a database like what i posted above (you can literally copy that code into phpmyadmin). i'm sure we will be able to help you quickly if you get stuck on that.

 

-cb-

 

PS, If your polite you will get many more responses, if you are too harsh-toned, people wont help you. Bare in mind this is a free service and everyone here is helping you in their free time, for free.

[djfox]

@ChemicalBliss

My lack of being able to understand continues. For each service that has to be done with each pet, a new entry has to be made into the whatever database table? Wouldn`t this mean that still a form has to be entered for each service for each pet? I`m not seeing how in a list of pets each with a list of services could be done in one form.

[ChemicalBliss]

If you want to issue an invoice for every single procedure/appointment etc that happens you could create the pet history row and the invoice at the same time in one form.

 

Though are you not required to keep a record of exactly what your doing to these pets? Thereby forcing someone to keep a record somewhere (which would be easily typed into this pet history table). If someone has a dispute or you want to knwo the history of a pet, jst do a search on the pet history table etc.

 

-cb-

[djfox]

No see, we -want- that history. That`s why I was trying to get this stuff entered into the database in the first place. The thing is to get the pets and services selected and chosen in one form, which will automatically create the history and the invoice would be built from that, rather than making copy A into history and copy B in invoice. What I wanna do is get it all done with just the one form. I can get the pets from the one customer to show up. I can get the list of services to show up. What I can`t do, is get all that to enter into the database with that one click of the submit button.

[djfox]

So anyone going to give me a hand with the code?

[ChemicalBliss]

If you want someone to code for you - i've done enough myself with providing you with an hours work on database design. If you want someone to finish it off for you then i suggest heading over to the freelancing forum if it's urgent.

 

If not i suggest you start trying what everyone here has posted, i would reccommend using my code as a template, or at least a reference.

Don't forget you can use a single form (and single button), to insert as many mysql queries as you want.

 

If you want help quick, i'll help you, but you gotta start listening and doing what everyone is suggesting or we will just end up ignoring you completely.

 

-quick start-

Build a form that you think is what should be needed for an invoice. (HTML)

Build the PHP counterpart - code as much as you can, get the submit working (not necessarily the mysql - we can help you with that).

Create the table structure as outlined above in my earlier post.

 

Submit any problems your getting with the code.

-cb-

 

PS, Everyone gets agrvated when they want somethign that seems really simple but dont get it. Thats pretty much what programming is like, you hardly ever get exactly what you want the way you want it, but when you start to get a picture of Design, not just Code and Logic, Then you will understand this topic and why everyone is willing you to "do the right thing".

[djfox]

I`ve already long since got the database stuff, now it`s the code I need. I`ve looked over the code you provided but I am not getting it. I`ve been trying to explain many times that I`m not understanding what it is being said and I`m not seeing how this can be done in one form, and yet the database structure keeps getting brought up, but that isn`t helping me understand how the code is going to get done.

 

I can`t spend money to have someone else do it, I`m poor, or else I would`ve just hired someone else to do it for me and would`ve skipped all this (I`m not getting paid to have this code done anyhow).

[ChemicalBliss]

Sorry mate but if you cant even start any code then you need to stick to some basic tutorials before you write something like this.

 

As i stated earlier:

Ideology of form Script; (This assumes a normalized database as i outlined above).

Gets Customer ID from GET variable

Uses Customer ID for Query to select all pet history (you can use a mysql flag to set which ones have been issued an invoice).

Presents Pet history in Rows with Descriptions etc and checkboxes.

( -> User submits form)

Script Gets selected Pet History IDs.

Script inserts a new row inside invoices table with all details of price/owner id/address issued to/date issued etc.

Script Updates selected pet history items to mark Invoice Issued.

Script Ends.

 

do each line one at a time, here ill do the first couple for you:

<?php
// Gets Customer ID from GET variable
$cid = mysql_real_escape_string($_GET['cid']);

// Uses Customer ID for Query to select all pet history (you can use a mysql flag to set which ones have been issued an invoice).
$query = "SELECT 
`owners`.`id` As ownerid,
`owners`.`firstname` As ownerfirstname,
`owners`.`lastname` As ownerlastname,
`owners`.`address` As owneraddress,
`owners`.`phone` As ownerphone,
`pets`.`id` As petid,
`pets`.`name` As petname,
`pets`.`breed` As petbreed,
`pets`.`status` As petstatus,
`pethistory`.`id` As eventid,
`pethistory`.`datetime` As eventdate,
`pethistory`.`duration` As eventtime,
`pethistory`.`cost` As eventcost,
`pethistory`.`whathappened` As eventwhathappened,
`pethistory`.`notes` As eventnotes,
`pethistory`.`datetime` As eventtime,
`vets`.`id` As eventvetid,
`vets`.`firstname` As eventvetfirstname,
`vets`.`lastname` As eventvetlastname
FROM `owners`
LEFT JOIN `pets` on `owners`.`id`=`pets`.`ownerid`
LEFT JOIN `pethistory` on `pets`.`id`=`pethistory`.`petid`
LEFT JOIN `vets` on `pethistory`.`vetid`=`vets`.`id`
WHERE `owners`.`id`='".$cid."'
ORDER BY eventdate";

$result = mysql_query($query) or die($query."<hr>".mysql_error());

// Presents Pet history in Rows with Descriptions etc and checkboxes.
// you can handle it from here. to know whats inside the result:
while($row = mysql_fetch_assoc($result)){
   print_r($row);
}
// In the loop above you should be echoing inside a form, and you wold want checkboxes for each event history item you want to invoice. With the current query and table structure there is no way to tell wether it has already been invoiced - but an extra WHERE condition and an extra pethistory field should take care of that.

 

Now the only thing we should hear from you is - im stuck at ... eg "Script inserts a new row inside invoices table with all details of price/owner id/address issued to/date issued etc.".

 

Good Luck.

-cb-

 

PS: Dont forget to include your mysql.php file at the top.

[djfox]

What is the LEFT JOIN stuff?