2. PHPRunner General questions
  3. add records to 2 tables from session variable
This topic is locked

add records to 2 tables from session variable

 		12/19/2007 7:39:06 PM
PHPRunner General questions
J
 jetacera author

URGENT help needed - must have this project finished by this Friday for client!
I'm using a MySQL database. The tables I'm working with are

_customers

_order_details (child table)

_order_summary (master table)
I've used an AfterSuccessfulLogin event to create session variables as follows:
[codebox]global $strTableName, $conn;

$strSQLExists = "select from _customers where customer_id='".$_SESSION["UserID"]."'";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$_SESSION["customer_id"] = $data["customer_id"];

$_SESSION["email"] = $data["email"];

$_SESSION["firstname"] = $data["firstname"];

$_SESSION["lastname"] = $data["lastname"];

$_SESSION["address"] = $data["address"];

$_SESSION["city"] = $data["city"];

$_SESSION["state"] = $data["state"];

$_SESSION["zip"] = $data["zip"];

$_SESSION["phone"] = $data["phone"];

}[/codebox]
I have another session variable called "len" created on a page external to my PHPRunner pages. It contains 1 or more detail records with a primary key called item_key. I need to explode this session variable to obtain these records, turn them into individual session variables, add each exploded record as a new record into the _order_details table and add a parent record to the _order_summary table.
Here is my code for exploding the session variable:

[codebox]$len = $_SESSION['len'];

if ($len) {

$items = explode(',',$len);

$contents = array();

foreach ($items as $item) {

$contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;

}

foreach ($contents as $item_key=>$qty) {

$sql = 'SELECT FROM _items WHERE item_key = '.$item_key;

$result = $db->query($sql);

$row = $result->fetch();

extract($row);

}[/codebox]
Here is where I'm stuck in the coding: Now how can I create the new records for each exploded row and for the related record in the parent table? Will the above syntax work in an event?
Any suggestions or assistance would be greatly appreciated.
Thanks,

Janet
J
 		jetacera author 12/19/2007

OK, I've gotten a little further in pulling the data from the SESSION variable.
Here is the code I used in a BeforeShowList event:
[codebox]$query = "insert into _order_summary (customer_id, email, phone, firstname, lastname, address, city, state, zip) values ($_SESSION["customer_id"], $_SESSION["email"], $_SESSION["phone"], $_SESSION["firstname"], $_SESSION["lastname"], $_SESSION["address"], $_SESSION["city"], $_SESSION["state"], $_SESSION["zip"])";

$result = mysqli_query($dbc, $query);

if (mysqli_affected_rows($dbc) == 1) {
//insert order ID

$order_no = mysqli_insert_id($dbc);
//insert order detail records

$query = "insert into _order_details (order_no, customer_id, item_key, item_id, product_name, details, quantity, price) values ";

for each ($_SESSION['len] as $orderdetail_id => $value) {

$query .= "($order_no, $orderdetail_id, $_SESSION["customer_id"], {$value['item_key']}, {$value['item_id']}, {$value['product_name']}, {$value['details']}, {$value['quantity']}, {$value['price']}), ";

}
$query = substr($query, 0, -2); //Chop off last two characters.

$result = mysqli_query($dbc, $query);
if (mysqli_affected_rows($dbc) == count($_SESSION['len'])) {
mysqli_commit($dbc);

mysqli_close($dbc);
//Clear the session.

unset($_SESSION['len']);
} [/codebox]
Any comments or suggestions still welcome. I would still like confirmation of this syntax in PHPRunner however.
Also, FYI, I'm using PHPRunner 4.0 on this project (started this project a few months before 4.1 came out and didn't want to switch over in the middle of this project).
Thanks,
Janet
J
 		jetacera author 12/20/2007

OK, making progress... now I'm trying to figure out a syntax error in my code.
Here's the code:

[codebox]{

//** Custom code ****

// put your custom code here
//add order to order summary table

$query = "insert into _order_summary (customer_id, email, phone, firstname, lastname, address, city, state, zip) values ({$_SESSION['customer_id']}, {$_SESSION['email']}, {$_SESSION['phone']}, {$_SESSION['firstname']}, {$_SESSION['lastname']}, {$_SESSION['address']}, {$_SESSION['city']}, {$_SESSION['state']}, {$_SESSION['zip']})";

$result = mysqli_query($dbc, $query);

if (mysqli_affected_rows($dbc) == 1) {
//insert order ID

$order_no = mysqli_insert_id($dbc);
//insert order detail records

$query = "insert into _order_details (order_no, customer_id, item_key, item_id, product_name, details, quantity, price, item_subtotal) values ";

for each ($_SESSION['len'] as $orderdetail_id => $value) {

$query .= "($order_no, $orderdetail_id, $_SESSION["customer_id"], {$value['item_key']}, {$value['item_id']}, {$value['product_name']}, {$value['details']}, {$value['quantity']}, {$value['price']}), {$value['quantity']$value['price']})";}

$item_subtotal += $price $quantity;
$query = substr($query, 0, -2); //Chop off last two characters.

$result = mysqli_query($dbc, $query);
if (mysqli_affected_rows($dbc) == count($_SESSION['len'])) {
mysqli_commit($dbc);

mysqli_close($dbc);
//Clear the session.

unset($_SESSION['len']);
}

} // function BeforeShowList

[/codebox]
Here's the error:
[codebox]error: syntax error, unexpected T_STRING, expecting '(' in /home/lenstudi/public_html/ch/include/len_events.php on line 17

[/codebox]
Here is line 17 of the code:

[codebox]for each ($_SESSION['len'] as $orderdetail_id => $value) {[/codebox]
what is my error?
Thanks!
Janet
J
 		Jane 12/20/2007

Hi,
here is the correct code:

foreach ($_SESSION['len'] as $orderdetail_id => $value) {


More about foreach operator:

http://php.net/manual/en/control-structures.foreach.php