inserting multiple items (# unknown) into a table using primary id from master table

[percept]

In my database I have a master table for products using a auto-num id column. One product may have several purchase options (items) which have to be entered into another table.

To enter a new product and items I use a form that gathers the information for the master product and one item. This information is then inserted into the database. For the items I use mysql_insert_id() to get the id into the items table.

My challenge is getting this same id for additional items. Once the user has input the initial product and first item they hit a submit button to call a form for adding any additional items, and this is repeated until all items for that product are entered.

The new items are inserted into the database okay, but always with an id of 0. The mysql_insert_id() no longer works. Is there a way to pass this id from the previous insertion to the new one by passing a variable? or echoing input type"hidden"?

Or even better yet, Im wondering if I can ask the user at the time of entering information for the product how many items that particular product has and then bring up that many form fields to enter multiple items? Can this be done using something like an incremented loop to call form fields?

any suggestions would be greatly appreciated. Thank you.

[raf]

this problem is common known as 'maintaining state'.

There are basically 5 options to maintain state:
- store the id in a cookie
- store it in a sessionvariable
- store it in a hidden fromfield and drag it along like that
- append it to the querystring
- store it inside the db.

The easiest option is the sessionvariable.
After you inserted the record in the master table, you store the id in a sessionvariable like

session_start(); // put this at the top of your code
$_SESSION['productID'] = mysql_insert_id() ;


Then when you insert your items, you insert this inside the valuelist. Like

$sql="INSERT INTO table (var1, var2) VALUES (" . $_SESSION['productID'] . ", 'blabla')";

Then, afer all records are inserted you remove that variable:

unset($_SESSION['productID']) ;


A quick rundown on the other methods
--> store the id in a cookie
mostly used for data that needs to remain available for the next sessions, or to identify a machine. For instance, the searchbox at php.net will automatically contain your last searchstring (even from previous sessions) This is most likely pulled out of a cookie.
Should only be used for trivial data. The removal of this cookie or manipulation of it by the user, should not pose any problems and all automatic processes you base on it, should also be made possible for non-cookie users
I only use it for applications where the users don't need to log in. I they identify themselfs through logins, then i only rely on the db to store data.

--> store it in a sessionvariable
Only for data wich needs to be used inside that session. Don't overuse it. Calling the sessionobject and maintaining the session-vars requires resources. So just use it to store 'keys'. Or for stuff like the mysql_insert_id , if you don't want to give the user any chance to alter it, or if your screenflow is so complex that it becomes a pain to work out another way to drag them along in a formfield or querystring. (Keep in mind that developmenttime and logical clearness of your code surely outweight minimal performance impacts caused by storing them in sessions.

What you however ! NEVER ! should do is select a row from a table, and then store all these values inside sessionvariables !! You just store an id, like the id of the newly generated record from the productstable, and you then use that ID in a where-clause to select the data.

--> store it in a hidden formfield and drag it along like that
In your particular case, i would do it like this. But it is a bit more complicated.
I always use multi-purpose pages for such things.
The first time that the page is loaded, you get the fields for the product. Then the page is posted to itself, formfields are validated, record inserted, id stored in a variable, second form for items loaded, with a hidden formfield in it that containt the ID value (through the intermadiate variable.)

But i would only use it like that if i know who will be using that page. If this is something in an admin section, then i am fairly comfident that the admins wount fumble with the form to change the hidden formfields value.

--> append it to the querystring
Only for quite irrelevant data that the user can change. ALWAYS validate that dat before processing it !! If you expect a 3 digit value, then make sure it is a 3digit, befor you call it inside your pages!

It's as easy as

if (!eregi('[0-9]{1,3}', $_GET['variable'])){
echo 'Invalid querystringvalue' ;
die();
}


Or if all your values are ID's, you can run something like

foreach ($_GET as $varName => $value){
if (!eregi('[0-9]{1,5}', $value)){
echo 'Invalid querystringvalue' ;
die();
}
}

Which will ensure that your code wount error on a non-numerical value and which will even take care of all sql-injection attempts.

Only use this for values that the user is allowed to change (like in this forum) but check the values to see if they are in the format that you expect them to be !!
--> store it inside the db.
My personal favorit. I store everything inside the db.
I even create a seperate sesionvalues table that contains a link with my sessiontable (table with some variables to identify the client (php's sessionID, the IP, cookieID, userkeys). Inside the sessionvalues-table, i have this id from the sessiontable, a column with the variable and a column with the value.
And i query this table instead of sessionvariables.
But this is mainly because to me, security and sessionmanagement have nothing to do with eachother. And because of that i do things like recreating the phpsession and resetting the few sessionvariables that i still use (so i need a db to have the varible-value pairs anyway.)
I'm basically moving the location where sessionvariables are stored, because i want to completely isolate it from the webserver and have full control over it (so that i can determine when they need to be dropped)
The logical consequence is that users need to log out. If they dn't log out, they get a reminder on their next login + i keep lists to see who the most careless users are so that i can contact them.
But i also use these table for a lott of other things like counters, securitychecks, redirecting based on userpreferences etc. So it all makes sense if you base all your applications critical processes solely on data from your database.

Most 'beginning' coders are a bit hesitant to rely heavily on databases, because of all that trafic etc, but i don't think there is any need to spare the db. If you have a good db-design and logical screenflow, then you can take on a few hundred simultanious users (if you ever get that lucky), even if you need a few querys on each page. (It's usually not the number of querys, but the returned recordsets that need to be kept as small as possible).
<edit> removed a few typos</edit>

[percept]

Thanks for this great info... I have elected to use hidden formfields for this particular project.

$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (".mysql_insert_id().", '$item_code', '$item_desc', $item_points, $item_cash)";

This is my first item record insertion which works great... now I want to set this value for ".mysql_insert_id()." into a variable so that I can enter it into a hidden formfield to go to the next form.

Can I not just do this:

echo "<form name='insert_info' method='post' action='item_get_form_next.php'>";

echo "<input type='hidden' name='id' value='$product_id'>";

echo "<input type='submit' name='next item' value='click here to add the next item for this product'>
</form>";

when I try this I just get a "0" in my id column for the items

You mention:Then the page is posted to itself and so I'm wondering if this is where I'm going wrong?

I also tried to pass the id value with sessions but I just kept getting 0's for a value.

Thanks

[raf]

I think you're missing something.

Inside the $add_item, you use mysql_insert_id(). But this will only contain the id of the new product, from the productstable, until you insert the (first) item inside the prod_items table !! Once you insert an item, mysql_insert_id() will contain the autonumer value of the inserted item. If there is no autonum column in that table, then it will contain 0.
So if you would insert 5 items, only the first item will have the right productID in it, the other will have a wrong one or 0. And so will the hidden formfield.

So immedeately after you insert the product into the productstable, you need to store that value in a variable, and then use that variable inside the $add_items and when, you set the hidden formfield.
So
isert into productstable

$result= etc
if (($result) and (mysql_insert_id()!= 0)){
$product_id = mysql_insert_id();
$add_item_=_"INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash) VALUES (". $product_id .", '$item_code', '$item_desc',_$item_points,_$item_cash)";
...

} else {
echo ('Problem. Product not inserted correctly');
}

about
echo_"<input_type='hidden'_name='id'_value='$product_id'>";

i would use

echo_('<input_type="hidden"_name="id" id="id"_value="' . $product_id .'" />');

I never just drop the variablesiside the strings because the syntax highlighting isn't as good and it's difficult to see when variablenames will be considered as string or as variable.
I also use single quotes for html because your attribevalues can then be enclosed in double quotes (which isthe propper way). It also just takes a few extra's to make it valid xhtml...
(Recently, i started including both the name and id attribute for formfields. I don't know if it will ever be usefull...)

[percept]

I'm getting close! I set the id into the variable $product_id and it inserts into the prod_items table no problem, but when I echo this variable using this syntax I get the following error. (I'm replacing part of path with asterics so it doesn't show that's all)

Fatal error: Call to undefined function: echo_() in /u/home/******/public_WWW/saveontravelbc.com/insert_info.php on line 150 (line 150 is this echo statement)echo_('<input_type="hidden"_name="id"
id="id"_value="' . $product_id .'" />'); So is this because this syntax is in function form because of brackets?

[raf]

You have an underscore in front of the bracket after the echo.
(+ also a few inside the input-tag.)
This might be cause if you copy-paste your code ... (that some spaces are replaced by _ )

[percept]

Thanks... I just re-typed it in as I saw it here (the echo statement that is) and I have now fixed it to this:echo ('<input type="hidden" name="id" id="id" value="' . $product_id .'" />'); Do you see anything wrong with this? Does the forward slash at the end belong there? If I echo the variable before passing it to the next page it shows up okay, but when I get an error on the next insert page and when I echo the variable on this page it won't show up... so I'm assuming it is in this statement. Perhaps because there were those underscores, something else is wrong with it?

[raf]

Do you see anything wrong with this? No. I don't think there is anything wrong with it.
Does the forward slash at the end belong there? Yes. With XHTML you need to close all openend tags. This is the tag minimalisation form of <input ...></input>
If I echo the variable before passing it to the next page it shows up okay, but when I get an error on the next insert page and when I echo the variable on this page it won't show up... so
Wait a minute.
If you want to use that value on the next page, then your hidden formfield needs to be inside a form that posts to that next page. And in that receiving page, you need to refer to the value with $_POST['id'].
This means that you need to display the form with the hidden formfield in, inside the page that inserts the product.

If you are redirecting to this second page, then you can not use a hidden formfield? You can then use the querystring and append it there or use a sessionvariable

If you want to use the variable in yet another page, or after you post the second page to itself, then you need to include it inside each page, inside a hidden formfield. For a selfreferencing page where you have the product-data form, the productinsertcode, the item-data form and the iteminsert code all in one page, it would look like

//inside the section that inserts the product on first processing, and that is only ran then
$product_id = mysql_insert_id();

//after that section or inside the selfreferencing page that can be reloaded infinitely, and that is processed in all processings, except for the first one when the product is inserted.
if (isset($_POST['id']) {
$product_id = $_POST['id']) ;
}

....
echo ('<input type="hidden" name="id" id="id" value="' . $product_id .'" />');


This way, you can keep posting the page to itself, insert the item and reload the page to enter the details for the next item.
In most cases, i set up my pages like that. The firsttime the page is loaded, i didplay the productsform. On first post (page posts to itself), i insert the product and store the mysql_insert_id in a variable, and then load the item-data-form.
This form also posts to itself and then the item is inserted. The product id is stored in the same variable (which this time wount be set by the productinsert-code, but by the posted hidden formfield.
Then i reload the item-data-form. And the productID is just always dragged along inside the hidden formfield.


If this isn't what your looking for, then you best explain your screenflow a bit + include a bit more code so that we can see what data you are posting to which pages.

[percept]

Well I'm getting there.... I am able to pass the id along to other pages now thanks to your help! however now I'm a bit stumped on the self calling part.

I start my process by adding a product and one item and inserting them to mysql, then I pass the id variable to a new page for adding subsequent items. This is the page that uses $_SERVER so that I can add as many items to the one product as needed. The id is being passed to this page so this is no longer a problem.

On this page I use an include for my html form to fill in the required fields and then this info gets inserted into mysql. I think my problem is that the script wants to run the insert query before the form is filled out and so the only field added is the id.

My question is how do I have the script wait for the form to be filled out before running the insert query? Here is the script I'm using:[PHP]include("item_form.inc");

include("misc.inc");

$connection = mysql_connect($host,$user,$password)
or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");

//insert items

$product_id = $_POST['id'];

if (strlen('$item_points') < 1) {
$item_points = 0;
} else {
$item_points = strip_tags(trim('$item_points'));
}

if (strlen('$item_cash') < 1) {
$item_cash = 0;
} else {
$item_cash = strip_tags(trim('$item_cash'));
}

$markup_points = array(',', '.', ' ');
$item_points = (str_replace($markup_points, "", $item_points));

$markup_cash = array(',', ' ');
$item_cash = (str_replace($markup_cash, "", $item_cash));

$item_code = strip_tags(trim('$item_code'));
$item_desc = strip_tags(trim('$item_desc'));
$item_desc = htmlentities($item_desc, ENT_QUOTES);

$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (".$product_id.", '$item_code', '$item_desc', $item_points, $item_cash)";

$resultitms = mysql_query($add_item) or die ("Couldn't execute query.");

echo "<form name='insert new item' method='post' action='$_SERVER[PHP_SELF]'>";
echo ('<input type="hidden" name="id" id="id" value="' . $product_id .'" />');
echo "<input type='submit' name='next item' value='click here to add the next item for this product'>
</form>";

[raf]

As you probably can guess, you need something to check against, to know if the forms is submitted or not.

For isnstance by checking if the submit-button was clicked.
This
if (isset($_POST['next item']){
will return true if the submitbutton was clicked.
So you could have

if (isset($_POST['next item']){
include("misc.inc"); // this is ?
/* if you only use one db (most apps do) then you
can just as well include the connectionstrings inside an include*/
$connection = mysql_connect($host,$user,$password)
or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");

//insert items

$product_id = $_POST['id'];

if (strlen('$item_points') < 1) {
$item_points = 0;
} else {
$item_points = strip_tags(trim('$item_points'));
}

if (strlen('$item_cash') < 1) {
$item_cash = 0;
} else {
$item_cash = strip_tags(trim('$item_cash'));
}

$markup_points = array(',', '.', ' ');
$item_points = (str_replace($markup_points, "", $item_points));

$markup_cash = array(',', ' ');
$item_cash = (str_replace($markup_cash, "", $item_cash));

$item_code = strip_tags(trim('$item_code'));
$item_desc = strip_tags(trim('$item_desc'));
$item_desc = htmlentities($item_desc, ENT_QUOTES);

$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (".$product_id.", '$item_code', '$item_desc', $item_points, $item_cash)";

$resultitms = mysql_query($add_item) or die ("Couldn't execute query.");
if (mysql_affected_rows() == 1){
$load="yes";
} else {
echo ('Item was not saved');
}

//close all brackets
} else {
$load='yes';
}
if ($load=='yes'){
echo ('<form name="insert new item" method="post" action="' . $_SERVER[PHP_SELF] .'">');
/* i suppose the include needs to come here
formfields must be inside the formtags to be posted !!*/
include("item_form.inc");
echo('<input type="hidden" name="id" id="id" value="' . $product_id .'" />
<input type="submit" name="next item" value="click here to add the next item for this product">
</form>');
}



Something like that.

[percept]

include("misc.inc"); // this is ?
/* if you only use one db (most apps do) then you
can just as well include the connectionstrings inside an include*/
This is the include for the connection strings. The book I'm sort of learning from uses this name... says it is inconspicuous and no obvious as to what it is. I didn't change the name cause I figure that the code can't be seen anyway.

I have changed my code to include what you have given me and I get a parse error right at this line:if (isset($_POST['next item']){ so I looked up isset() to see what it does and I can't understand why it's creating this error.

[raf]

so I looked up isset() to see what it does and I can't understand why it's creating this error.

The error is due to a typo. You need )){ at the end (so an extra bracket

quote:
--------------------------------------------------------------------------------
include("misc.inc"); // this is ?
/* if you only use one db (most apps do) then you
can just as well include the connectionstrings inside an include*/
--------------------------------------------------------------------------------


This is the include for the connection strings. The book I'm sort of learning from uses this name... says it is inconspicuous and no obvious as to what it is.

Hahahahahahahhhahahah heeeeeeeh hahahahaha. !!

This is hilarious. when you use server side includes, you indeed need to prevent that people can see the content.
There are indeed 3 things you need to do for that:
1: pick a dificult to guess filename. (because there exist listings of popular include names. So this will give you some protection (Like a mosquito-net does to keep out burglers.
check your Private messages.
2: Faaaaaaaaaaaaaaaaaaaaaar more important --> give the file a .php extension. If you do one thing today, then change that files extension and modify your code to this new filename (if you only use one db, then you can just as well immedeately remove the connection and db-selectionstrings (the mysql_select and select_db lines, that is) from your code + add them to the include). This will ensure that the file is parsed when the user should get the filename and requests it. Which makes it almost completely secure. Only if the server is 'falling over', the content could be accidentmy sent unparsed.
3: if you can, place the file in a folder that is not accesible from the web.

It's strange how such books can pick out the least securing measure and go on about that, and leave out the real securitymeasures.

[percept]

thank you kindly... :o

I'm so close now I can taste it... but can't quite chew... when I fill out the form and press submit, the page doesn't seem to want to take action... the formfields remain filled and nothing moves... I will continue to research this and try to figure it out, but thought I would repaste the code in case someone spots my problem.
if (isset($_POST['next item'])){

include("******");

// connection code is here but not showing here

//insert items

$product_id = $_POST['id'];

if (strlen('$item_points') < 1) {
$item_points = 0;
} else {
$item_points = strip_tags(trim('$item_points'));
}

if (strlen('$item_cash') < 1) {
$item_cash = 0;
} else {
$item_cash = strip_tags(trim('$item_cash'));
}

$markup_points = array(',', '.', ' ');
$item_points = (str_replace($markup_points, "", $item_points));

$markup_cash = array(',', ' ');
$item_cash = (str_replace($markup_cash, "", $item_cash));

$item_code = strip_tags(trim('$item_code'));
$item_desc = strip_tags(trim('$item_desc'));
$item_desc = htmlentities($item_desc, ENT_QUOTES);

$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (".$product_id.", '$item_code', '$item_desc', $item_points, $item_cash)";

$resultitms = mysql_query($add_item) or die ("Couldn't execute query.");

if (mysql_affected_rows() == 1){
$load="yes";

} else {
echo ('Item was not saved');
}
//close all brackets

} else {
$load='yes';
}

if ($load=='yes'){

echo ('<form name="insert new item" method="post" action="' . $_SERVER[PHP_SELF] .'">');

include("item_form.inc.php");

echo('<input type="hidden" name="id" id="id" value="' . $product_id .'" />
<input type="submit" name="next item" value="click here to add this new item">
</form>');
}

[raf]

no problem --> check you PM's: the connectionstrings should be inside the include.


Anbout your code, did you check if the item is inserted? Posting a page and inserting the item and reloading the form can happen within a fraction of a second. There is not confirmation inside your code. Maybe change it a bit to

if (mysql_affected_rows() == 1){
$load="yes";
echo ('Item inserted. Next Item?');
} else {
echo ('Item was not saved');
}


Another posseble explanation is the space inside the sublmitbuttons name. Try changing it to a name without a space.
Or print something after that condition like
if (isset($_POST['next item'])){
echo 'submitted';
...
I can't see you other formcode, but if you there also have this
value="' . $variable . '"
then the form will always be prefilled.

You normally do this so that when there was an invalid formvalue or so, the user gets the filled in form back, with an erromessage. But this means that you should set these variables to '' or so after a succesfull insert. Like

if (mysql_affected_rows() == 1){
echo ('Item inserted. Next Item?');
$load="yes";
$item_code = '';
$item_desc = '';
$item_points = '';
$item_cash = '';
} else {
echo ('Item was not saved');
}

[percept]

well I was tying my best to figure this one out on my own and I'm getting even closer yet. I'm now able to insert multiple items under the proper product id but I'm getting variable names in two columns and one extra record inserted with product id but 0values.

record - 95 $item_code $item_desc 333333 0.00
95=product id
variable names are where the code and description should be... the points and cash are okay

I don't have single quotes around these two variables in my insert query because they are stored as integer and float so I'm stumped. I'm putting my code for the insertion script here again + my code for the form which is the include used.

Insert Script
if (isset($_POST['next_item'])){

include("*******");

$connection = mysql_connect($host,$user,$password)
or die ("could not connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");

//insert items

$product_id = $_POST['id'];

if (strlen($item_points) < 1) {
$item_points = 0;
} else {
$item_points = strip_tags(trim($item_points));
}

if (strlen($item_cash) < 1) {
$item_cash = 0;
} else {
$item_cash = strip_tags(trim($item_cash));
}

$markup_points = array(',', '.', ' ');
$item_points = (str_replace($markup_points, "", $item_points));

$markup_cash = array(',', ' ');
$item_cash = (str_replace($markup_cash, "", $item_cash));

$item_code = strip_tags(trim('$item_code'));
$item_desc = strip_tags(trim('$item_desc'));
$item_desc = htmlentities('$item_desc', ENT_QUOTES);

$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (".$product_id.", '$item_code', '$item_desc', $item_points, $item_cash)";

$resultitms = mysql_query($add_item) or die ("Couldn't execute query.");

if (mysql_affected_rows() == 1){

echo ('Item inserted. Next Item?');

$load="yes";

$item_code = '';

$item_desc = '';

$item_points = '';

$item_cash = '';

} else {

echo ('Item was not saved');

}

//close all brackets

} else {
$load='yes';
}

if ($load=='yes'){

echo ('<form name="next_item" method="post" action="' . $_SERVER[PHP_SELF] . '">');

include("item_form_2.inc.php");

echo('<input type="hidden" name="id" id="id" value="' . $product_id . '" />
<input type="submit" name="next_item" value="enter">
</form>');
}
Include Form code
<table width="600" align="center">
<tr>
<td colspan=2> </td>
</tr>
<tr>
<td align="left" class="mytext" valign="top" height="2">
<p><b>Plu Code or Cash price<br>
</b><span class="small"><br>
eg. Plu 45345 or Cash price</span></p>
</td>
<td width="67%" class="small" height="2">
<p>
<input type="text" name="item_code" size="20" maxlength="50">
<br>
<span class="small">Please enter the &quot;Plu&quot; as part of the code.<br>
Please keep the upper/lower case consistent (Plu, Cash price) and put
a space between Plu and the number (just like the catalogue).</span></p>
<p>&nbsp;</p>

</tr>
<tr>
<td align="left" class="mytext" valign="top"><b>Item Description<br>
</b><span class="small">Eg. different tours by same company</span> </td>
<td width="67%" class="mytext">
<p>
<textarea name="item_desc" cols="50"></textarea>
<br>
<span class="small"><font color="#FF0000"><b>IMPORTANT:</b></font> This
is only for products that have item descriptions different than the main
descriptions.</span></p>
<p>&nbsp;</p>
</td>
</tr>
<tr>
<td align="left" class="mytext" valign="top"><b>Points Value</b></td>
<td width="67%" class="mytext">
<p>
<input type="text" name="item_points" size="20" maxlength="50">
<br>
<span class="small">Please leave blank if no points.</span></p>
<p>&nbsp;</p>
</td>
</tr>
<tr>
<td align="left" class="mytext" valign="top"><b>Cash Value</b></td>
<td width="67%" class="mytext">
<p>
<input type="text" name="item_cash" size="20" maxlength="50">
<br>
<span class="small">Please leave blank if no cash. <br>
<br>
Please do not enter &quot;$&quot; and you must enter the decimal for cents.
Eg. <b>99.95</b></span></p>
<p>&nbsp;</p>
</td>
</tr>
<tr>
<td align="left" class="mytext" colspan="2">
<div align="center">___________________________________________________</div>
</td>
</tr>
<tr>
<td colspan=2> </td>
</tr>
</table>

[raf]

Try changing
$item_code = strip_tags(trim('$item_code'));
$item_desc = strip_tags(trim('$item_desc'));
into
$item_code = strip_tags(trim('$_POST['item_code']));
$item_desc = strip_tags(trim('$_POST['item_desc']));
because i don't see you set them to the form-values anywhere

and then use

$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (".$product_id.", '". $item_code . "', '" . $item_desc ."', " . $item_points . ", " . $item_cash . ")";

i think that using sinle quotes inside a doublequoted string, will make the variables inside the single quotes literal
i always concatinate the strings with the variables, instead of including them --> so i don't need to remember which embedded quotecombinations make it literal etc + it gives better colourcoding which makes it easier to read (+ this way, it's the same logic as in ASP --> so even less to remember)

[percept]

The champagne is chilling!

I now have all subsequent items getting inserted proplerly with their proper values, the only thing I have left to deal with is for some reason I am getting an extra record inserted with the proper product id, but with 0 values. My logical side (which isn't very big) is telling me that this is happening when the add extra items script is called. Because this is a self-calling script with an insert query when I enter this page is it automatically inserting this extra record before the form is filled out?

If this is the case, can I somehow code the script to not insert a record until the form has been filled out? I don't want to play with the isset() because this is for the product_id if I'm correct.

The script has only changed with your last suggestions for concatenating the strings in my variables but I will paste again so you don't have to back track.if (isset($_POST['next_item'])){

include("********");

$connection = mysql_connect($host,$user,$password)
or die ("could not connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");

//insert items

$product_id = $_POST['id'];

if (strlen($_POST['item_points']) < 1) {
$item_points = 0;
} else {
$item_points = strip_tags(trim($item_points));
}

if (strlen($_POST['item_cash']) < 1) {
$item_cash = 0;
} else {
$item_cash = strip_tags(trim($item_cash));
}

$markup_points = array(',', '.', ' ');
$item_points = (str_replace($markup_points, "", $item_points));

$markup_cash = array(',', ' ');
$item_cash = (str_replace($markup_cash, "", $item_cash));

$item_code = strip_tags(trim($_POST['item_code']));
$item_desc = strip_tags(trim($_POST['item_desc']));

$item_desc = htmlentities($_POST['item_desc'], ENT_QUOTES);

$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (".$product_id.", '". $item_code . "', '" . $item_desc ."', " . $item_points . ", " . $item_cash . ")";


$resultitms = mysql_query($add_item) or die ("Couldn't execute query.");

if (mysql_affected_rows() == 1){

echo ('Item inserted. Next Item?');

$load="yes";

$item_code = '';

$item_desc = '';

$item_points = '';

$item_cash = '';

} else {

echo ('Item was not saved');

}

//close all brackets

} else {
$load='yes';
}

if ($load=='yes'){

echo ('<form name="next_item" method="post" action="' . $_SERVER[PHP_SELF] . '">');

include("item_form_2.inc.php");

echo('<input type="hidden" name="id" id="id" value="' . $product_id . '" />
<input type="submit" name="next_item" value="enter">
</form>');
}

[raf]

the only thing I have left to deal with is for some reason I am getting an extra record inserted with the proper product id, but with 0 values. My logical side (which isn't very big) is telling me that this is happening when the add extra items script is called. Because this is a self-calling script with an insert query when I enter this page is it automatically inserting this extra record before the form is filled out?

Hmm. The first line
if (isset($_POST['next_item'])){
should prevent that.

This will only return True if you hit the submitbutton.

On the page where you insert the product, doent you there also have an insert statement for the items? or do you have a submi-tbutton with the same name there ?


For debugging. Place
die('Form processed');
right under
if (isset($_POST['next_item'])){

and then load the page. It should normally only print the 'form processed' when you hit the submitbutton.

(you need to take it back out after debugging)

Also, take a look at the prod_items table after you inserted the product, to make sure there isn't inserted an item-record there right after that the product is inserted.

[percept]

Well I've spent quite a bit of time on this one... I've made sure my submit button names are different. I put the die('Form processed'); into my code and it comes up when I enter the page... so for some reason the empty record with prod_id is still being inserted.

However, because I added all products to the database through PHPMyAdmin I have to prioritize and finish the site so it can go live... tomorrow. Then I will come back to this one as it will be an important admin function for my client to add new products starting in a couple of weeks.

So thanks for all your help and I suspect this thread will still be here and I can come back to it if necessary and re-post my code including the script code that preceeds the page where the code is inserting the empty record.