Need help to setup a apache web based system to store, retrieve and log data
using PHP, Apache and MySQL database.
Here is the list of tables involved in the module.
Customer Table:
CREATE TABLE customer (
customer_id int(11) NOT NULL auto_increment,
cust_fname varchar(50) NOT NULL,
cust_lname varchar(50) NOT NULL,
business varchar(50),
cust_type varchar(50) NOT NULL,
cust_add1 varchar(50) NOT NULL,
cust_add2 varchar(50),
cust_city varchar(50) NOT NULL,
cust_state varchar(2) NOT NULL,
cust_zip varchar(10) NOT NULL,
cust_country varchar(50) NOT NULL,
cust_since datetime NOT NULL,
cust_phone varchar(10),
cust_fax varchar(10),
cust_email varchar(100),
PRIMARY KEY (customer_id)
Card Table:
CREATE TABLE card (
lot_no int(11) NOT NULL,
start_job_no int(11) NOT NULL,
end_job_no int(11) NOT NULL,
customer_id int(11) NOT NULL,
invoice_no int(11) NOT NULL,
item_id int(11) NOT NULL,
notes varchar(500),
PRIMARY KEY (invoice_no,job_no),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (invoice_no) REFERENCES invoice(invoice_no),
FOREIGN KEY (item_id) REFERENCES item(item_id)
Audit Table:
CREATE TABLE audit (
customer_id int(11) NOT NULL,
invoice_no int(11) NOT NULL,
create_date datetime NOT NULL,
user_name varchar(50)NOT NULL,
ipaddress varchar(50) NOT NULL
Invoice Table:
CREATE TABLE invoice (
invoice_no int(11) NOT NULL,
invoice_date datetime NOT NULL,
customer_id int(11) NOT NULL,
item_id int(11) NOT NULL,
invoice_amt int(11) Not NUll,
PRIMARY KEY (invoice_no),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (item_id) REFERENCES item(item_id)
My goal is to input customer_id(customer table) so it would take me to the invoice screen.
It should display the regular invoice title and then display the customer info on the left side and the invoice id
the invoice date(date() and user name from the web login which i got it using sessions($_SESSION["username"]) on the right side.
Next thing is to display the item_desc in a drop down box and display price for that particular item when picked.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Item + ItemPrice + Quantity + SalePrice + Net Total Price + LotNo + StartJobNo + EndJobNo +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The sale price is defaulted to the item price but should be able to change if the user wants to. The net total price is the quantity * saleprice.
The lotno, startjobno and endjobno is just a text field.
Once the first line is picked it should pop the next line for populating( for the remianing items)
It should be able to take some notes regarding the invoice.
once all this is done.
All the info needs to be inserted into the respective tables, Invoice, Card, Audit
The invoice should be able to be printed for mailing it to the customer.
Thanks for the help.
Here is what I have done so far.
//invoice.html
PHP:
<form name="form" action="invoice.php" method="get">
<input type="text" name="customer_id" />
<input type="submit" name="Submit" value="Create Invoice" />
</form>
//invoice.php
<?
session_start();
if (!($SESSION["username"]) || ($SESSION["username"] == "")) {
Header("Location: ./index.html");
exit();
}
?>
<center>
<tr>
<td>
<?
$hostname = "localhost";
$username = "root";
$password = "psswd";
$dbName = "mydb";
$search = $_GET['customer_id'];
$db_connect = MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
mysql_select_db($dbName, $db_connect) or die("Unable to select database");
$XX = "No Record(s) Found, to search again please go back to the main search page!";
$query = mysql_query("SELECT * FROM customer WHERE customer_id = '$search'");
while ($row = @mysql_fetch_array($query))
{
$variable1=$row["customer_id"];
$variable2=$row["cust_fname"];
$variable3=$row["cust_lname"];
$variable4=$row["cust_add1"];
$variable5=$row["cust_add2"];
$variable6=$row["cust_city"];
$variable7=$row["cust_state"];
$variable8=$row["cust_zip"];
$variable9=$row["cust_country"];
$variable10=$row["cust_since"];
$variable11=$row["cust_phone"];
$variable12=$row["cust_fax"];
$variable13=$row["cust_email"];
//print ("<p><H2>Invoice</H2></p>");
//print ("<P> $variable1 </P>");
//print ("<P> $variable2 </P>");
//print ("<P> $variable3 </P>");
//print ("<P> $variable4 </P>");
//print ("<P> $variable5 </P>");
//print ("<P> $variable6 </P>");
//print ("<P> $variable7 </P>");
//print ("<P> $variable8 </P>");
//print ("<P> $variable9 </P>");
//print ("<P> $variable10 </P>");
//print ("<P> $variable11 </P>");
//print ("<P> $variable12 </P>");
//print ("<P> $variable13 </P>");
$inv_query = mysql_query("select max(invoice_no) from invoice");
$inv_id = $inv_id + 1;
$order_date = date("Y-m-d HⓂs");
$user = $_SESSION["username"];
?>
<html>
<title>Invoice Screen</title>
<H2><center>Invoice</center></h2>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<body>
<hr width="100%">
<br>
<p align="left">
<b>Customer ID:</b> <? print $variable1 ?> <b>Invoice No:</b> <? print $inv_id ?>
<br><b>Customer Name:</b> <? print $variable2 ?>
<? print $variable3 ?> <b>Purchase Date:</b> <? print $order_date ?><br>
<b>Address:</b> <? print $variable4 ?>
<? print $variable5 ?> <b> SalesPerson: </b> <? print $user ?><br>
<? print $variable6 ?>,
<? print $variable7 ?>,
<? print $variable8 ?>,
<? print $variable9 ?> <br>
<b>Phone:</b><? print $variable11 ?> <br>
</P>
<br>
<br>
<br>
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td bordercolor="#000000">
<TR>
<td><th>Card Name
<p align="center">
<td><select Name="Cards">
<OPTION selected label="Please Select a Card" value="none">Please Select a item for sale.</OPTION>
<?
$item_query = mysql_query("select item_id, item_desc, item_cost from item");
while ($items = @mysql_fetch_array($item_query))
{
$var1=$items["item_id"];
$var2=$items["item_desc"];
$var3=$items["item_cost"];
?>
<OPTION value="$var1"><? print $var2 ?></OPTION>
<?
}
?>
</SELECT>
</p>
</table>
</body>
</html>
<?
}
//below this is the function for no record!!
if (!$variable1)
{
print ("$XX");
}
//end
?>
