I have used this piece of code which is working great. It allows me to add different items from combo boxes to my order form. The problem is that i dont know how to extract the data from my db to display in a form view when i want to amend the i.e the number of items. Each record has a different number of items in it but are all lnked to one id. Is this possible to do. Any help on this would go along way. also if the code below helps others please feel free to take as u please.

stdhead("Home");

?>
<script type="text/javascript">
var counter = 0;

function add_phone() {
    counter++;

    var newFields = document.getElementById('add_phone').cloneNode(true);
    newFields.id = '';
    newFields.style.display = 'block';
    var newField = newFields.childNodes;
    for (var i=0;i<newField.length;i++) {
        var theName = newField[i].name
        if (theName)
                newField[i].name = theName + counter;

        }
        var insertHere = document.getElementById('add_phone');

        insertHere.parentNode.insertBefore(newFields,insertHere);
}
</script>
<?

if ($action == "neworder"){
	
$res50 = mysql_query("SELECT compid FROM eventcompany WHERE company = '$company'") or die;
$res60 = mysql_fetch_array($res50);
$id = $res60['compid'];
if ($id == ''){
begin_frame ("error");
#print"$id";
#print "$company";
print "You have not selected a company";
end_frame();
}else{

$res90 = mysql_query("SELECT * FROM eventcustomers left join eventcompany on eventcompany.compid = eventcustomers.compid WHERE eventcustomers.compid = $id") or die;
$res100 = mysql_fetch_array($res90);
$compid = $res100['compid'];	
$company = $res100['company'];	
$address = $res100['address'];




$sql = "SELECT * FROM products ORDER BY productid";
$result = mysql_query($sql);
$result1 = mysql_query($sql);
begin_frame ("test3");
print "<b>$company</b><br>$address";
?>
<form name="add_a_phone" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
<fieldset>
<div id="phone">
<?
print"<input type=hidden name=compid value=$compid>";
echo "<select name=prodid_0>";
while($row = mysql_fetch_array($result))
{
echo "<option value=\"".$row['productid']."\">".$row['product']."</option> \n ";
}
echo "</select>";
?>
    <input type="text" name="productitems_0" value="" />
    <input type="text" name="productcost_0" value="" /><br>
</div>
<div id="add_phone" style="display: none;">
<?
echo "<select name=prodid_>";
while($row = mysql_fetch_array($result1))
{
echo "<option value=\"".$row['productid']."\">".$row['product']."</option> \n ";
}
echo "</select>";
?>

    <input type="text" name="productitems_" value="" />
    <input type="text" name="productcost_" value="" />
    <input type="button" value="Remove Item"
		onclick="this.parentNode.parentNode.removeChild(this.parentNode);" />
</div>
<input type="button" id="add_phone()" onclick="add_phone()" value="Give me more fields!" /><br>
<input type="submit" name="submit" value="submit" />
</fieldset>
</form>

<?

end_frame();
}}
    if(isset($_POST['submit']))
##This checks to make sure submit was clicked
    {
        echo "You clicked submit!<br>";
        echo "Here is your data<br>";
        echo "<br>";
        if ($_POST['productitems_0'])
##This checks that the proper field has data
        {
                $continue = FALSE;
                $i = 0;
                $compid = $_POST['compid'];
                while ($continue == FALSE)
                {
                    if (isset($_POST['prodid_'.$i]))
#This looks for an entry after 0 and increments
                    {
                    echo $_POST['prodid_'.$i] . " = " . $_POST['productitems_'.$i] . " = " . $_POST['productcost_'.$i] . "<br>";
#Echoing the data
		    $productid = $_POST['prodid_'.$i];
                    $productitems = $_POST['productitems_'.$i];
                    $productcost = $_POST['productcost_'.$i];
                    $query = "INSERT INTO constructionorderdetails (conorderid, productid, productitems, productcost) VALUES ('$compid','$productid', '$productitems', '$productcost')";
                    $result = mysql_query($query);
#The four lines above is the example on how the data would be put into a MySQL database. It's not used here
                }
                else
                {
                    $continue = TRUE;
                }
                $i++;
            }
        }
    }











stdfoot();

Recommended Answers

All 14 Replies

Kardklub,

You have to go through the pain barrier of getting your mind round the "relational model", ie. the way related data gets stored in the records of two or more database tables, which are associated with each other by "foreign keys" (integers) which point to "primary keys", thus implementing a set of many-to-one relationships. (Many-to-many relationships can't be implemented directly, but can be created with an intermediate table with two "back-to-back" many-to-one relationships).

I'll try to give you the solution in words rather than code ....

In a "shop" database, you will typically have several tables. Let's consider the tables ORDER, ORDERED_ITEM, PRODUCT and CUSTOMER (there will undoubtedly be others).

Each order generates a record in the ORDER table, and this records is given a unique order_id (primary key, indexed) plus fields for basic order details (eg. date, time), but excluding ordered items.

At the same time as the ORDER record is generated, one or more ORDERED_ITEM records are generated - one per ordered item. These records are associated with the ORDER record by being given a foreign key (order_number) which points to the primary key of the ORDER record just generated. These records will also typically each be associated with a record in a PRODUCT table via another foreign key.

The ORDERED_ITEM records will also contain a quantity field, and you should also copy across the price from the associated PRODUCTS record to capture the price at the moment the goods were ordered (prices in the PRODUCTS table will change over time). Product variant data also needs to be stored in these records, eg size (S/M/L/XL), color (red/green/blue), in accordance with the user's selections.

Going back to the ORDER record, it will also contain a foreign key pointing to a record in a CUSTOMER file, containing the customers name, password (encrypted), address, email etc - everything specific to the customer that was captured when he/she created their account.

And so on, and so on .....

This is the way a relational database works and the analyst/programmer's skill lies in knowing how to (a) "normalise" data into interrelated tables, (b) create meaningful, friendly user-interfaces, and (c) write structured SQL scripts that sit between the UI and the database in order to query and update it. These three aspects are often referred to (formally and informally) as Model, View, Controller (MVC).

I have been doing this sort of stuff over 20 years now. Given an internet shop project, would I sit down and create my own database and write my own code? NO WAY. I would choose one of many good off-the-shelf e-commerce products representing at least several thousand man-hours of work, which makes them secure, reliable, quick to install, quick to tailor, fast running etc. etc. etc.

But please don't let me put you off. Maybe you are ambitious enough to write a world-beating application.

Please remember old Airshow when you get rich.

Airshow

Thanks for ur reply.

Yes i have all my tables created in mysql. I have the orders table (which has the order num and details of customer) linked to the extraorders table (which holds the details of the order.) The program i am creating will allow monthly invoices to be created if stock is still out on hire. The above code adds the orders to the tables but on an instance the user puts in the wrong data or would like to amend the number of items i do not know how to redisplay the data on a form where the number of products on each order are different sizes. As above i allow them to add extra fields to the form using javascript but as soon as they press save the info is stored but i have no clue on how to get them out. Im not saying i dont know how to retrieve data but im sure javascript will have to be used when identifying how many combo boxes to be displayed as to how many products in the order.


The reason i am doing it all myself is so i can build the program to my needs and not have all the extra stuff we may never use.

Any help, or pointing in the right direction would be helpful.


P.s i have created the above program in access 2000 but i would love to put it on the web using php etc

The reason I gave the answer above is because there's no attempt to use the relational model in the statements at lines 113-117.

If I understand correctly that this is a whole-page-build-and-serve, then what you need to do is pretty standard fare with regard to building pages from a back-end database.

I can't write it for you because I don't know how the data is normalised but this is the general approach:

  1. Retrieve data from database with an SQL statement
  2. Loop through the retrieved records
  3. Build HTML (typically table rows containing further HTML elements and data) inside the loop.

Any questions here should be addressed in the PHP and/or SQL forums.

Javascript is not involved in identifying how many combo boxes need to be displayed. All of that is server-side as per the general approach above.

Javascript will be involved if:

  • The built HTML needs to have associated onclick/onchange etc. handlers to respond to user events (which is probably the case but this should be javascript that you have already written to give the page its add/delete items functionality).
  • Page segments are dynamically obtained from a server-side script via AJAX (which appears not to be the case).

Airshow

Thanks for the interest airshow.

Reading what u have typed has given me an idea. thanks airshow. If it works ill let u know.

;)

Kardklub

Oooh :icon_exclaim: Sounds interesting.

i know this is more of a php problem now. but as i try to extract the data from the database i have a problem with the <select>(combobox). Each record is printed but the only one in a combo box is the first record. the next 2 fields in the order just display a blank combo box. The good news is that in the first combo box the selected field is displayed with the list of products in the box ready to be selected.

any ideas
[IMG]http://www.fencehiresouthern.com/screenshot.png[/IMG]
[IMG]http://www.fencehiresouthern.com/screenshot1.png[/IMG]

<?
require "backend/functionsemail.php";
#require  "backend/order.php";
dbconn();

$res90 = mysql_query("SELECT * FROM eventcustomers left join eventcompany on eventcompany.compid = eventcustomers.compid left join constructionorderdetails on constructionorderdetails.conorderid = eventcustomers.compid left join products on products.productid = constructionorderdetails.productid WHERE eventcustomers.compid = '36'") or die;

$sql = "SELECT * FROM products ORDER BY productid";
$result = mysql_query($sql);
stdhead("Home");
$num = 1;
begin_frame ("test3");

while($row = mysql_fetch_array($res90))
{ 
if ($row[conorderid] == '36'){
print "$row[productid]";
print "<select name=\"".$row['orderid']."\">";
while($row1 = mysql_fetch_array($result))
{
echo "<option value=\"".$row['product']."\">      ".$row1['product']."</option> \n ";
}
echo "</select>";


print "$row[product]";
print "$row[productitems]";

print "$row[productcost]";
print "<br>";
$num++;
}else{
	print " no records";
}}
end_frame();
stdfoot();



?>

if this is now in the wrong forum please feel free to move it ;)

After the first time through while($row1 = mysql_fetch_array($result)) , the internal data pointer will be set beyond the final row. According to a comment here, you can reset the pointer with mysql_data_seek($result,0) . Personally, I would change the while to a for loop with a counter under my control.

Airshow

It works !!. many thanks, all the combo boxes have the product list in them now. my only problem is that the first product in the order is selected in all of the combo boxes. Am i missing something really easy or is it not as simple as abc?

kard

<?
require "backend/functionsemail.php";
#require  "backend/order.php";
dbconn();

$res90 = mysql_query("SELECT * FROM eventcustomers left join eventcompany on eventcompany.compid = eventcustomers.compid left join constructionorderdetails on constructionorderdetails.conorderid = eventcustomers.compid left join products on products.productid = constructionorderdetails.productid WHERE eventcustomers.compid = '36'") or die;

$sql = "SELECT * FROM products ORDER BY productid";
$result = mysql_query($sql);
stdhead("Home");

begin_frame ("test3");
$num=0;
while($row = mysql_fetch_array($res90))
{ 
if ($row[conorderid] == '36'){
print "$row[productid]";
print "<select name=\"".$row['orderid']."\">";
while($row1 = mysql_fetch_array($result))
{
echo "<option value=\"".$row['product']."\">      ".$row1['product']."</option> \n ";

}
echo "</select>";
mysql_data_seek($result,0);

print "$row[product]";
print "$row[productitems]";

print "$row[productcost]";
print "<br>";
$num++;
}else{
    print " no records";
}}
end_frame();
stdfoot();



?>

Sorted Ignore last thread ;)

Congrats Kard. It's easy when you know how!

Airshow

i thought i had this but it was a mistake . ill try and explain

<?
require "backend/functionsemail.php";
#require  "backend/order.php";
dbconn();

$res90 = mysql_query("SELECT * FROM eventcustomers left join eventcompany on eventcompany.compid = eventcustomers.compid left join constructionorderdetails on constructionorderdetails.conorderid = eventcustomers.compid left join products on products.productid = constructionorderdetails.productid WHERE eventcustomers.compid = '36'") or die;

$sql = "SELECT * FROM products ORDER BY productid";
$result = mysql_query($sql);

stdhead("Home");

begin_frame ("test3");
$num=0;
while($row = mysql_fetch_array($res90))
{ 
if ($row[conorderid] == '36'){
	print "$row[orderid]";
print "$row[productid]";
print "<select name=\"".$row['orderid']."\">";
while($row1 = mysql_fetch_array($result))
{

echo "<option value=\"".$row['product']."\">      ".$row['product']."</option> \n ";

}
echo "</select>";
mysql_data_seek($result,0);

print "$row[product]";
print "$row[productitems]";

print "$row[productcost]";
print "<br>";
$num++;
}else{
	print " no records";
}}

end_frame();
stdfoot();



?>

on line 24

echo "<option value=\"".$row['product']."\">      ".$row['product']."</option> \n ";

i get this
[IMG]http://www.fencehiresouthern.co.uk/screenshot2.png[/IMG]
the data is in the combo boxes but each combo box is filled with the same data i.e first one is all fixed lefg barriers and th 2nd one is all rubber blocks

when i change the code on line 24 to this

echo "<option value=\"".$row1['product']."\">      ".$row1['product']."</option> \n ";

and upload the new file and refresh it works properly but only if i keep changing the $row to $row1 and refreshing.
[IMG]http://www.fencehiresouthern.co.uk/screenshot3.png[/IMG]

Where am i going wrong. I did this earlier and thought i had done it only to find out that when i started the page again it didnt work.

Kard,

You definately need to use $row1 inside the inner loop.

To set each of the select menus to a particular option, you need to construct the required option in the format <option value="productName" selected="selected">Product Name</option> .

To do this, the inner while loop will look something like this (untested):

while($row1 = mysql_fetch_array($result))
		{
			$sel = ($row1['productid'] == $row['productid']) ? "selected=\"selected\"" : "";
			printf("<option value=\"%s\" %s>%s</option>\n", $row1['productid'], $sel, $row1['product']);
		}

Note: $row1 is used for the option value, not $row1, which is likely to be unreliable when the form is submitted. Product names are not necessarily unique, whilst product IDs generally are (or should be).

Airshow

absolute star. thankyou. works perfect. im marking this thread closed. thanks Airshow ;)

Ur inbox is full so i just want to let u know u have been a great help. do u own the forums here or are u just one of those gods out there happy to help ;)

thanks again

steve

That's great. Pleased to be of some help.

Thanks for letting me know about my box. I'll go trim it now.

Airshow

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.