Insert query into db on load page.

[william232]

hi all,i am wondering when a page loads can it automatically insert the query into the database of all the information the person has ordered. with them clicking a button but does it on load page

Can this be done in php?

[The Little Guy]

If what your saying is When a page loads, information is automatically inserted into a database. Then Yes.

How are you getting this information?

[william232]

from the previous page which is stored in the database,when they reach the invoice page it inserts all the information into the database table without the customer knowning?

[The Little Guy]

Somthing like this:

//insert to database
mysql_query("INSERT INTO database_name(`option1_column_name`,`option2_column_name`,`option3_column_name`)
VALUES('$_POST[option1]','$_POST[option1]','$_POST[option1]')")or die(mysql_error());


//update database
mysql_query("UPDATE database_name SET
option1_column_name='$_POST[option1]',
option2_column_name='$_POST[option2]',
option3_column_name='$_POST[option3]' WHERE
some_column='something'")or die(mysql_error());

[william232]

how do i go about inserting arrays in the database because i have a array called items and its not appearing so how can i insert arrays into the database? cuz when i lookup the arrays i get 'Array showing up how can i fix this?

[chazzy]

you shouldn't store an array in a database, you should retrieve each element from the array (using for or foreach) and insert each one individually or build a single insert to store them all - don't use arrays in a database, they're not searchable.

[william232]

in that case can they be in the same field in the database? like items or inventory?

[The Little Guy]

Yes. You will need to retrieve them from the database,
then probably do an explode() function on them.

[william232]

i am talking about inserting into database how can i go about that?

[chazzy]

typically, in an E-R diagram, multivalue fields are a table by themself, with a reference between the id of the main table and the current value being available.

[william232]

what is an er digram i have never heard of that before. are you able to write up an example of what you mean please.

Thanks

[pcthug]

Entity-relationship Diagram (http://www.umsl.edu/~sauter/analysis/er/er_intro.html)

In computer science, an entity-relationship model (ERM) is a model providing a high-level description of a conceptual data model. Data modeling provides a graphical notation for representing such data models in the form of entity-relationship diagrams (ERD). The first stage of information system design uses these models to describe information needs or the type of information that is to be stored in a database during the requirements analysis. The data modeling technique can be used to describe any ontology (i.e. an overview and classifications of used terms and their relationships) for a certain universe of discourse (i.e. area of interest). In the case of the design of an information system that is based on a database, the conceptual data model is, at a later stage (usually called logical design), mapped to a logical data model, such as the relational model; this in turn is mapped to a physical model during physical design. Note that sometimes, both of these phases are referred to as "physical design".

There are a number of conventions for entity-relationship diagrams (ERDs). The classical notation is described in the remainder of this article, and mainly relates to conceptual modelling. There are a range of notations more typically employed in logical and physical database design, including information engineering, IDEF1x (ICAM DEFinition Language) and dimensional modelling.

[william232]

what would be the best way to insert arrays in to the database i got told using a foreach loops would it be possible if somone could right and example for that please.

[pcthug]

Here you go:

$items = array('hat', 'shoe', 'apple', 'box');
$count = count($items);

$sql = 'INSERT INTO `items_tbl` (';
for($i = 1; $i <= $count; $i++) {
if($i != $count) {
$sql .= "item_$i, ";
}
else {
$sql .= "item_$i) VALUES(";
}
}

$i = 1;
foreach($items as $key => $item) {
if($i != $count) {
$sql .= "'$item', ";
}
else {
$sql .= "'$item')";
}
$i++;
}

@mysql_query($sql);
This code will produce the following sql:

INSERT INTO `items_tbl` (item_1, item_2, item_3, item_4) VALUES('hat', 'shoe', 'apple', 'box')

[william232]

Thank you pcthug :)

[chazzy]

that fails to fulfill a complete entity relationship. typically, in the situation you were describing above, you have a one to many (one master row, from the first table to many of the second table) relationship. you should not limit yourself based on table size or number of columns. A better solution, in my opinion would be:


//let's say the items you were picking were in a multiselect, which we can reference as an array.
//start the query
$sendData = "INSERT INTO `table`(`otherTableId`,`value`) VALUES";
$otherId = 1; //hypothetically, whatever the ID is from the other table.
$retrievedData = $_POST['selectName'];
$numElems = count($retrievedData);
$enum = 0;
if($numElems > 0){
foreach($retrievedData as $option){
$enum++;//increment the counter
$sendData .= "($otherId,'$option')";
if($enum < $numElems){
$sendData .= ",";//need a comma between each value-pair
}//if
}//foreach
}//if there's any elements in the array.
echo $sendData;
//produces output like INSERT INTO `table`(`otherTableId`,`value`) VALUES(1,'bob'),(1,'sam')....

[william232]

hey pcthug i did that code you gave me and i came back with

Parse Error:syntax error unexpected t_else on line 15

How can i fix that?

[pcthug]

It's woking for me - No errors. Could you please post the entire file with my code in it unmodified.

[william232]

This is what i have.


//session_start();
if (!isset($_SESSION['cart'])) {
$_SESSION['cart'] = array();
}
$items=array();
$prices=array();
include("dbconnect.php");
$count=count($items);
$sql="insert into customers (";
for($i=1;$i<=$count;$i++)
{
$sql.="item_$i,";
}
else
{
$sql.="item_$i) Values(";
}
}
$i=1;
foreach($items as $key=>$item)
{
if($i !=$count)
{
$sql.="'$item',";
}
else
{
$sql.="'$item')";
}
$i++;
}
$rs=mysqli_query($con,$sql);
?>
Welcome: <?php echo $_SESSION['user'];?>
<style type="text/css">
@import url(styles/crispyimportsInvoice.css);
</style>
<link href="styles/crispyimportsInvoice.css" rel="stylesheet" type="text/css">
<div id="container" align="center">
<div id="Header">
<H1>Crispy Imports</H1>
<?php include("includes/addy.inc.php"); ?>
<?php echo $today;?>
<h1>Order Information</h1>
<table border="1">
<tr>
<td>Reference Number::</td>
<td>34432</td>
</tr>
</table>
</div>
<div id="Box1">
Bill to:<br/>
<?php include("includes/customeraddy.inc.php"); ?>
</div><div id="Box2">
<table border="0">
<tr>
<td>Product #</td>
<td>Item</td>
<td colspan="2">Price</td>
</tr>
<?php
include("includes/cart2.inc.php");
?>
</table>
</div><div id="Box3">Ship to:<br/>
<?php include("includes/customeraddy.inc.php"); ?>
</div><div id="Footer" align="center">
<br/>Please Send the Money to this Bank Account:<br>
<?PHP include("includes/payment.inc.php");?>
or you can send it the payment to:<br/>
<?php include("includes/addy.inc.php"); ?>
</div>

[pcthug]

You removed an if statement, try changing to match:
//session_start();
if (!isset($_SESSION['cart'])) {
$_SESSION['cart'] = array();
}
$items=array();
$prices=array();
include("dbconnect.php");
$count=count($items);
$sql="insert into customers (";
for($i=1;$i<=$count;$i++)
if($i != $count)
{
$sql.="item_$i,";
}
else
{
$sql.="item_$i) Values(";
}
}
$i=1;
foreach($items as $key=>$item)
{
if($i !=$count)
{
$sql.="'$item',";
}
else
{
$sql.="'$item')";
}
$i++;
}
$rs=mysqli_query($con,$sql);
?>
Welcome: <?php echo $_SESSION['user'];?>
<style type="text/css">
@import url(styles/crispyimportsInvoice.css);
</style>
<link href="styles/crispyimportsInvoice.css" rel="stylesheet" type="text/css">
<div id="container" align="center">
<div id="Header">
<H1>Crispy Imports</H1>
<?php include("includes/addy.inc.php"); ?>
<?php echo $today;?>
<h1>Order Information</h1>
<table border="1">
<tr>
<td>Reference Number::</td>
<td>34432</td>
</tr>
</table>
</div>
<div id="Box1">
Bill to:<br/>
<?php include("includes/customeraddy.inc.php"); ?>
</div><div id="Box2">
<table border="0">
<tr>
<td>Product #</td>
<td>Item</td>
<td colspan="2">Price</td>
</tr>
<?php
include("includes/cart2.inc.php");
?>
</table>
</div><div id="Box3">Ship to:<br/>
<?php include("includes/customeraddy.inc.php"); ?>
</div><div id="Footer" align="center">
<br/>Please Send the Money to this Bank Account:<br>
<?PHP include("includes/payment.inc.php");?>
or you can send it the payment to:<br/>
<?php include("includes/addy.inc.php"); ?>
</div>

[william232]

I did that and all i get back is insertin into customers ( and thats all,doesnt insert the array into the database at all it is the exactly the same thing as pcthug showed me.

How can i fix this?

The items are coming from the database and are shown in an array.

[pcthug]

Currently, $items is an empty array (see line 5 of previous script). Can you please show us the SQL Query used to retrieve the items?

[william232]

This is the sql function i get the item name from database.


$cQuery="SELECT * FROM products";
$result=mysqli_query($con,$cQuery);;
if(!$result)
{
echo "Error:".mysqli_error($con);
}
else
{
$count=$result->num_rows;
if($count>0)
{
while($data=$result->fetch_assoc())
{
//echo " here is &nbsp;".$data['productName'];
$items[]=$data['productName'];
$prices[]=$data['ProductCost'];
//$data['prices']//$prices =mysqli_fetch_assoc();
}
}
else
{
echo "There is no products in the catalog,Please contact the webmaster of this store";
}
}

[pcthug]

<?php
//session_start();
if (!isset($_SESSION['cart'])) {
$_SESSION['cart'] = array();
}
include("dbconnect.php");
$cQuery="SELECT * FROM products";
$result=mysqli_query($con,$cQuery);;
if(!$result)
{
echo "Error:".mysqli_error($con);
}
else
{
$count=$result->num_rows;
if($count>0)
{
while($data=$result->fetch_assoc())
{
//echo " here is &nbsp;".$data['productName'];
$items[]=$data['productName'];
$prices[]=$data['ProductCost'];
//$data['prices']//$prices =mysqli_fetch_assoc();
}
}
else
{
echo "There is no products in the catalog,Please contact the webmaster of this store";
}
}
$sql="insert into customers (";
for($i=1;$i<=$count;$i++)
if($i != $count)
{
$sql.="item_$i,";
}
else
{
$sql.="item_$i) Values(";
}
}
$i=1;
foreach($items as $key=>$item)
{
if($i !=$count)
{
$sql.="'$item',";
}
else
{
$sql.="'$item')";
}
$i++;
}
$rs=mysqli_query($con,$sql);
?>
Welcome: <?php echo $_SESSION['user'];?>
<style type="text/css">
@import url(styles/crispyimportsInvoice.css);
</style>
<link href="styles/crispyimportsInvoice.css" rel="stylesheet" type="text/css">
<div id="container" align="center">
<div id="Header">
<H1>Crispy Imports</H1>
<?php include("includes/addy.inc.php"); ?>
<?php echo $today;?>
<h1>Order Information</h1>
<table border="1">
<tr>
<td>Reference Number::</td>
<td>34432</td>
</tr>
</table>
</div>
<div id="Box1">
Bill to:<br/>
<?php include("includes/customeraddy.inc.php"); ?>
</div><div id="Box2">
<table border="0">
<tr>
<td>Product #</td>
<td>Item</td>
<td colspan="2">Price</td>
</tr>
<?php
include("includes/cart2.inc.php");
?>
</table>
</div><div id="Box3">Ship to:<br/>
<?php include("includes/customeraddy.inc.php"); ?>
</div><div id="Footer" align="center">
<br/>Please Send the Money to this Bank Account:<br>
<?PHP include("includes/payment.inc.php");?>
or you can send it the payment to:<br/>
<?php include("includes/addy.inc.php"); ?>
</div>

[william232]

How can i get it so i inserts into the the column items?