PHP and MySQL: How should I handle these DBs? + small questions

[DoctorTheFonz]

mysql ver: 5.0.51a

php ver: 5.2.5

 

This problem sounds complicated to me, so I will share some background with this program that I'm writing. At first, it seemed pretty simple. It's a program for an automotive shop that does repairs and services. What I need it to do is to create work orders so that the shop can bill the company that it's working for.

 

I made a form where the user inputs the date, the invoice number(small question later), the item, quantity, description of repair/service, and rate. I'm using the rate for both the rate of pay for the labor, as well as the price of a part, so that the math is easier and I can just multiply the rate times the quantity .

 

The form looks like this(more on the dynamic invoice # later):

<tr><td align="left">Date (mm/dd/yyyy):</td><td align="right"><input name="date" type="text"/></td></tr>
<tr><td align="left">Invoice #:</td><td align="right"><input name="invoice" type="text" 
        value='<?php echo $row[2]; mysql_close(); //output dynamic invoice # ?>' /></td></tr>
<tr><td align="left">Truck/Trailer #:</td><td align="right"><input name="number" type="text"/></td></tr>
<tr><td align="left">Item:</td><td align="right"><input name="item" type="text"/></td></tr>
<tr><td align="left">Quantity:</td><td align="right"><input name="quantity" type="text"/></td></tr>
<tr><td align="left">Description:</td><td align="right"><input name="description" type="text"/></td></tr>
<tr><td align="left">Rate:</td><td align="right"><input name="rate" type="text"/></td></tr>
<tr><td colspan="3"><hr></td></tr>
<tr><td><input type="submit" value="Create Invoice"></td><td><input type="reset" name="Reset"></td></tr>

 

So, what I do is shoot this information to another page that inserts it into an "orders" database, then off to another page where I have an invoice template that I whipped up with the holes filled in with variables, so whatever information that is entered into the form shows up on the invoice and can be printed. Now, this was all pretty easy until I realized that there may be many charges on one invoice. Cue screeching halt to progress.

 

I am vexed presently because I cannot figure out how to handle this. I need a database to hold a great many invoices, but I also need it to hold the individual items per invoice and still share the same date and invoice number as other items coexisting in one physical invoice. It feels as though I need a database within a database to hold information that will probably not appear on every invoice, as sometimes there may only need to be one item charged to the invoice.

 

QUESTION 1: Do I need/How should I execute a separate database exclusively for instances of multiple item charges?

 

I'm not really sure where to go with that because both my PHP and my MySQL knowledge is rudimentary at best(I could get our projects done in high-school, but they were never really that hard), and I'm feeling a little(very) overwhelmed by this, as I've never had to deal with such a convoluted database problem.

 

Small Questions

 

Preface: As I alluded to earlier, I'm also having trouble making dynamic invoice numbers. I want it such that each time the user finishes creating an invoice, that invoice number will increase by one, so that the next time the user goes to create an invoice, the form will spit out the automatically incremented invoice number. Now, this may be an easy fix, but I'm not sure where to handle this in case the databases become more convoluted and will need to pass the same invoice number with multiple instances perhaps through more than one database. So, really, I need my first problem resolved before I can fix this.

 

QUESTION 2: How should I organize my invoice numbers so that they can be passed around more than one database, but also so that the number will be dynamic when a wholly separate invoice is created?

 

Preface 2: This program is also going to be used to organize, sort, and store information regarding trucks at this company. We have somewhere around 50 trucks, and the user needs to be able to add, edit, and delete trucks, as well as organize the trucks by identification number or year. I've already done this, but I also need to create a weekly and monthly report in such a manner that the user can click a button and the last week's worth of repairs shows up in a simple table and can be printed out. The problem with this is that I'm not sure how to store dates and compare them. I've looked it up, but it's still a bit of a mystery to me, but it seems like a pretty simple thing that one of you gurus may know how to do. I just need a dynamic table that interfaces with my databases and sorts and limits by date weekly and monthly, and I need a way to let the user choose a start and an end date for a custom report, which I also don't know how to do. So:

 

QUESTION 3: How do I compare dates in such a manner as to be able to create a pre-made, dynamic table displaying information limited by these dates(weekly, monthly, and custom)?

 

As this post appears huge and long, and my problems seem like they can be solved by any skilled programmer(not I), I'm omitting more hunks of code for readability's sake. I can, however, definitely post more code upon request, as I'd like to fix these problems as soon as possible since every other part of the program is working and I need the whole thing done relatively soon.

 

I welcome any and all comments that may seem even slightly helpful because I may need that extra push in the right direction for it to click. Thank you tremendously in advance for any help.

[fenway]

1 - you need to loop through all of the results in php, and then display an appropriate html block -- with substitution for the invoice #, etc. -- accordingly with variables.

 

2 - I'm not sure what you mean -- sounds like you need an invoice UID in the DB.

 

3 - Again, I'm not sure what you mean -- if you store your dates in YYYY-MM-DD (SQL-99) format, you'll have no issue whatsoever.