Page 1 of 1

Need Help in PHP coding *****SOS*******

Posted: Mon Oct 24, 2005 6:36 pm
by pagalreddy
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.

Posted: Mon Oct 24, 2005 6:38 pm
by John Cartwright
Not quite sure of the point of this post?
What exactly are you asking?

Programming help

Posted: Mon Oct 24, 2005 7:09 pm
by pagalreddy
Im a newbie to PHP, I have done little of coding but Im stuck when I came to do this. Im unable to do this program. I needed help

Posted: Mon Oct 24, 2005 7:22 pm
by Charles256
have you attempted it?what errors did you get? etc. etc. etc.

Posted: Mon Oct 24, 2005 8:01 pm
by pagalreddy
I have tried a little, but lost what i was doing.

Posted: Mon Oct 24, 2005 8:03 pm
by Charles256
erm well then...

This is what i did so far before i got stuck

Posted: Mon Oct 24, 2005 8:07 pm
by pagalreddy
Jcart | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


//invoice.html

Code: Select all

<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:m: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 
?>

Jcart | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Mon Oct 24, 2005 8:23 pm
by Charles256
use appropriate code tags please...then tell me what's not working...

Posted: Tue Oct 25, 2005 1:01 am
by n00b Saibot
This thing looks a bit off-road to me :arrow:

Code: Select all

$inv_query = mysql_query("select max(invoice_no) from invoice"); 
$inv_id = $inv_id + 1;

Posted: Tue Oct 25, 2005 2:52 am
by phpdevuk
there must be an off the shelf solution for something like this, why not try having a look for something over at http://www.hotscripts.com or even http://www.sourceforge.net