Jump to content

inserting dynamic rows into database

MsKazza

By MsKazza
in MySQL Help

 Share

Recommended Posts

MsKazza Regular Member

MsKazza

Posted
Posted

Hi all :-)

I have a form which is using add row script to add more lines to the form.  They are all being passed to the process.php page correctly :

 

<?php 
// Database connect
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("avonbray", $con);

// Declare Variables
$rows  = $_POST['Rows'];

$firstname  = $_POST['firstname'];
$surname  = $_POST['surname'];
$phone  = $_POST['phone'];
$email  = $_POST['email'];
$address = $_POST['address'];
$comments = $_POST['comments'];

// Echo Form information for confirmation
echo 'Thank you ' . $firstname . ' for placing your order with us.</br></br>';
echo 'Please confirm the following details are correct :</br></br>';
echo 'Full name : ' . $firstname . '' . $surname . '</br>' ;
echo 'Address :  ' . $address . '</br>';
echo 'Phone :  ' . $phone . '</br>';
echo 'Email :  ' . $email . '</br></br>';


echo '<table cellpadding=\"0\" cellspacing=\"0\" border=\"1\" class=\"db-table\">';
echo '<tr><th>Product Code</th><th>Product Name</th><th>Brochure </th><th>Page No.</th><th>Qty.</th><th>Unit Price</th><th>Total Price</th></tr>';

for ($counter = 1; $counter <= $rows; $counter++) {
echo '<tr><td>' . $_POST['code' . $counter] . '</td>' ;
echo '<td>' . $_POST['desc' . $counter] . '</td>';
echo '<td>' . $_POST['br' . $counter] . '</td>';
echo '<td>' . $_POST['pg' . $counter] . '</td>';
echo '<td>' . $_POST['qty' . $counter] . '</td>';
echo '<td>' . $_POST['unitp' . $counter] . '</td>';
echo '<td>' . $_POST['tprice' . $counter] . '</td>';
}
echo '</tr></table><br>';

// insert into database
$sql="INSERT INTO details (orderid, first_name, sur_name, sex, age, town, bscf1, bscf_name1, andy, bscf2, bscf_name2, he_she, his_her, him_her, he_she_C, his_her_C, him_her_C, from_name, uploadedfile)
VALUES
('$orderid','$first_name','$sur_name','$sex','$age','$town','$bscf1','$bscf_name1','and','$bscf2','$bscf_name2','$he_she','$his_her','$him_her','$he_she_C','$his_her_C','$him_her_C','$from_name','$uploadedfile')";



?>

<br />
<span class="headings_text">If these details are correct please</span> <br>
<a href="confirm.php" class="headings_link">
click here to continue</a>

 

i just don't know how to insert the repeating rows into the database using mysql, any help much appreciated.

 

MsKazza

Link to comment
Share on other sites
MsKazza Regular Member

MsKazza

Posted
  • Author
Posted

the $rows when echo'd all post out correctly, in the database i have :

 

CREATE TABLE IF NOT EXISTS `orders` (
  `orderid` int( NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `surname` varchar(30) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `email` varchar(70) NOT NULL,
  `address` blob NOT NULL,
  `comments` blob,
  `code1` varchar(15) NOT NULL,
  `desc1` varchar(155) NOT NULL,
  `br1` int(5) NOT NULL,
  `pg1` int(5) NOT NULL,
  `qty1` int(3) NOT NULL,
  `unitp1` varchar(6) NOT NULL,
  `tprice1` varchar(6) NOT NULL,
  `code2` varchar(15) DEFAULT NULL,
  `desc2` varchar(155) DEFAULT NULL,
  `br2` int(5) DEFAULT NULL,
  `pg2` int(5) DEFAULT NULL,
  `qty2` int(3) DEFAULT NULL,
  `unitp2` varchar(6) DEFAULT NULL,
  `tprice2` varchar(6) DEFAULT NULL,
  `code3` varchar(15) DEFAULT NULL,
  `desc3` varchar(155) DEFAULT NULL,
  `br3` int(5) DEFAULT NULL,
  `pg3` int(5) DEFAULT NULL,
  `qty3` int(3) DEFAULT NULL,
  `unitp3` varchar(6) DEFAULT NULL,
  `tprice3` varchar(6) DEFAULT NULL,
  `code4` varchar(15) DEFAULT NULL,
  `desc4` varchar(155) DEFAULT NULL,
  `br4` int(5) DEFAULT NULL,
  `pg4` int(5) DEFAULT NULL,
  `qty4` int(3) DEFAULT NULL,
  `unitp4` varchar(6) DEFAULT NULL,
  `tprice4` varchar(6) DEFAULT NULL,
  `code5` varchar(15) DEFAULT NULL,
  `desc5` varchar(155) DEFAULT NULL,
  `br5` int(5) DEFAULT NULL,
  `pg5` int(5) DEFAULT NULL,
  `qty5` int(3) DEFAULT NULL,
  `unitp5` varchar(6) DEFAULT NULL,
  `tprice5` varchar(6) DEFAULT NULL,
  `code6` varchar(15) DEFAULT NULL,
  `desc6` varchar(155) DEFAULT NULL,
  `br6` int(5) DEFAULT NULL,
  `pg6` int(5) DEFAULT NULL,
  `qty6` int(3) DEFAULT NULL,
  `unitp6` varchar(6) DEFAULT NULL,
  `tprice6` varchar(6) DEFAULT NULL,
  `code7` varchar(15) DEFAULT NULL,
  `desc7` varchar(155) DEFAULT NULL,
  `br7` int(5) DEFAULT NULL,
  `pg7` int(5) DEFAULT NULL,
  `qty7` int(3) DEFAULT NULL,
  `unitp7` varchar(6) DEFAULT NULL,
  `tprice7` varchar(6) DEFAULT NULL,
  `code8` varchar(15) DEFAULT NULL,
  `desc8` varchar(155) DEFAULT NULL,
  `br8` int(5) DEFAULT NULL,
  `pg8` int(5) DEFAULT NULL,
  `qty8` int(3) DEFAULT NULL,
  `unitp8` varchar(6) DEFAULT NULL,
  `tprice8` varchar(6) DEFAULT NULL,
  `code9` varchar(15) DEFAULT NULL,
  `desc9` varchar(155) DEFAULT NULL,
  `br9` int(5) DEFAULT NULL,
  `pg9` int(5) DEFAULT NULL,
  `qty9` int(3) DEFAULT NULL,
  `unitp9` varchar(6) DEFAULT NULL,
  `tprice9` varchar(6) DEFAULT NULL,
  `code10` varchar(15) DEFAULT NULL,
  `desc10` varchar(155) DEFAULT NULL,
  `br10` int(5) DEFAULT NULL,
  `pg10` int(5) DEFAULT NULL,
  `qty10` int(3) DEFAULT NULL,
  `unitp10` varchar(6) DEFAULT NULL,
  `tprice10` varchar(6) DEFAULT NULL,
  `code11` varchar(15) DEFAULT NULL,
  `desc11` varchar(155) DEFAULT NULL,
  `br11` int(5) DEFAULT NULL,
  `pg11` int(5) DEFAULT NULL,
  `qty11` int(3) DEFAULT NULL,
  `unitp11` varchar(6) DEFAULT NULL,
  `tprice11` varchar(6) DEFAULT NULL,
  `code12` varchar(15) DEFAULT NULL,
  `desc12` varchar(155) DEFAULT NULL,
  `br12` int(5) DEFAULT NULL,
  `pg12` int(5) DEFAULT NULL,
  `qty12` int(3) DEFAULT NULL,
  `unitp12` varchar(6) DEFAULT NULL,
  `tprice12` varchar(6) DEFAULT NULL,
  `code13` varchar(15) DEFAULT NULL,
  `desc13` varchar(155) DEFAULT NULL,
  `br13` int(5) DEFAULT NULL,
  `pg13` int(5) DEFAULT NULL,
  `qty13` int(3) DEFAULT NULL,
  `unitp13` varchar(6) DEFAULT NULL,
  `tprice13` varchar(6) DEFAULT NULL,
  `code14` varchar(15) DEFAULT NULL,
  `desc14` varchar(155) DEFAULT NULL,
  `br14` int(5) DEFAULT NULL,
  `pg14` int(5) DEFAULT NULL,
  `qty14` int(3) DEFAULT NULL,
  `unitp14` varchar(6) DEFAULT NULL,
  `tprice14` varchar(6) DEFAULT NULL,
  `code15` varchar(15) DEFAULT NULL,
  `desc15` varchar(155) DEFAULT NULL,
  `br15` int(5) DEFAULT NULL,
  `pg15` int(5) DEFAULT NULL,
  `qty15` int(3) DEFAULT NULL,
  `unitp15` varchar(6) DEFAULT NULL,
  `tprice15` varchar(6) DEFAULT NULL,
  `code16` varchar(15) DEFAULT NULL,
  `desc16` varchar(155) DEFAULT NULL,
  `br16` int(5) DEFAULT NULL,
  `pg16` int(5) DEFAULT NULL,
  `qty16` int(3) DEFAULT NULL,
  `unitp16` varchar(6) DEFAULT NULL,
  `tprice16` varchar(6) DEFAULT NULL,
  `code17` varchar(15) DEFAULT NULL,
  `desc17` varchar(155) DEFAULT NULL,
  `br17` int(5) DEFAULT NULL,
  `pg17` int(5) DEFAULT NULL,
  `qty17` int(3) DEFAULT NULL,
  `unitp17` varchar(6) DEFAULT NULL,
  `tprice17` varchar(6) DEFAULT NULL,
  `code18` varchar(15) DEFAULT NULL,
  `desc18` varchar(155) DEFAULT NULL,
  `br18` int(5) DEFAULT NULL,
  `pg18` int(5) DEFAULT NULL,
  `qty18` int(3) DEFAULT NULL,
  `unitp18` varchar(6) DEFAULT NULL,
  `tprice18` varchar(6) DEFAULT NULL,
  `code19` varchar(15) DEFAULT NULL,
  `desc19` varchar(155) DEFAULT NULL,
  `br19` int(5) DEFAULT NULL,
  `pg19` int(5) DEFAULT NULL,
  `qty19` int(3) DEFAULT NULL,
  `unitp19` varchar(6) DEFAULT NULL,
  `tprice19` varchar(6) DEFAULT NULL,
  `code20` varchar(15) DEFAULT NULL,
  `desc20` varchar(155) DEFAULT NULL,
  `br20` int(5) DEFAULT NULL,
  `pg20` int(5) DEFAULT NULL,
  `qty20` int(3) DEFAULT NULL,
  `unitp20` varchar(6) DEFAULT NULL,
  `tprice20` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`orderid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

i don't know how to get the $rows to the insert statement as they are not all listed seperately. As in :

 


$sql="INSERT INTO orders (orderid, firstname, surname, email, phone, address, comments, code1, desc1, br1, pg1, qty1, unitp1, tprice1, code2, desc2, br2, pg2, qty2, unitp2, tprice2,...... etc.)
VALUES
('$orderid','$firstname','$surname','$email','$phone','$address','$comments','??????????????????????)";

 

i can get the first ones in (name, surname, etc.) but no idea how to do the actual products

Link to comment
Share on other sites
mikosiko Newbie

mikosiko

Posted
Posted

anyone please?

 

The best you can do for a start is to modify the design of your table "orders" taking all those 20 repeating elements out off  there into other table ("orders_detail" maybe).

Per example:

ORDERS

  `orderid`,

  `firstname`,

  `surname`,

  `phone`,

  `email`,

  `address`,    // address and comments shouldn't be BLOB type

  `comments`

 

ORDERS_DETAIL

  `id`  --> Auto-Increment field

  `orderid`  // This will allow you to associate the details to the order

  `code` varchar(15) NOT NULL, ---> Is this the "Product" code?... if so.. the next field shouldn't be here

  `desc` varchar(155) NOT NULL,  /// see previous note

  `br` int(5) NOT NULL,

  `pg` int(5) NOT NULL,

  `qty` int(3) NOT NULL,

  `unitp` varchar(6) NOT NULL,  // unitp and tprice should be numeric and tprice could be calculated on usage.. no necessarily you must include it on the table.

  `tprice` varchar(6) NOT NULL

 

after the re-design your form should be modified to: (simple description)

- Capture the Order data

- Capture the array of detail lines

- Validate/Sanitize your data

- Insert your Order record capturing and storing the LAST_RECORD_ID() of your inserted order.

- Process your orders detail array, and insert them in your "order_detail" table using the LAST_RECORD_ID() stored before.

Link to comment
Share on other sites
MsKazza Regular Member

MsKazza

Posted
  • Author
Posted

Thank you for all that i shall certainly be taking it all into account, however the question is and was how do i take the information from the dynamically created rows (form shows 7 rows by default and the user can click add new row, to a max of 20) and put them into the insert statement.

 

These are the variables passed to process.php

$rows  = $_POST['Rows'];

$firstname  = $_POST['firstname'];
$surname  = $_POST['surname'];
$phone  = $_POST['phone'];
$email  = $_POST['email'];
$address = $_POST['address'];
$comments = $_POST['comments'];

 

all of the above works fine when echo'd to the page.  However one client might order 3 things whereas another might order 15.  How do i take the info from $rows and put it into the insert statement.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.