SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 37

Thread: Loop INSERT

  1. #1
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Loop INSERT

    Can't seem to get this to work. Trying to get have multiple rows inserted. No error message, insert last record only. I have also tried foreach and do while, still only getting last record. BTW, the last values in the query insert but via some variable I did not show here, but still only the last record.

    The array
    PHP Code:
    <?php  $postarray=array("$_POST[qnt]","$_POST[type]","$_POST[mfg]","$_POST[model]","$_POST[model_num]","$_POST[descrip]","$_POST[msrp]");?>
    The loop with query

    PHP Code:

          
    for ($i=0$i count($postarray);$i++) {

    $query_Recordset3 "INSERT INTO temporder ( cust, store_num, qnt, type, mfg, model, model_num, descrip, price, po) VALUES ('$cust','$store_num','$_POST[qnt]','$_POST[type]','$mfg','$model','$model_num','$descrip','$price','$po') ";

    Anyone be able to see where I'm going wrong

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    for starters, it looks like you're issuing one INSERT statement per row to be inserted

    much better would be to issue a single INSERT statement for all the rows being inserted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,219
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Then for seconds, you are only building the query in your loop, you aren't executing it within your loop. So that means you are executing it outside of the loop, which would result in the last query to be executed only.

    Two ways to solve this, one move the execution of the query into the for loop (take the hit that each query is executed individually).
    Or change your for loop to build multiple INSERT commands like below and leave the execution of your query outside of the for loop (notice I change = to .=, and I added a semi-colon to the end of the query.
    PHP Code:
          $query_Recordset3 '';
          for (
    $i=0$i count($postarray);$i++) { 

    $query_Recordset3 .= "INSERT INTO temporder ( cust, store_num, qnt, type, mfg, model, model_num, descrip, price, po) VALUES ('$cust','$store_num','$_POST[qnt]','$_POST[type]','$mfg','$model','$model_num','$descrip','$price','$po'); "


  4. #4
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cpradio

    Thanks for your reply. I tried your edited version but received a syntax error. I tried a number of variations using your advice but was not able to get it to work.

    I'm not sure what is wrong with my logic. The loop works, the query works, missing something.

    r937

    Thanks for the reply, but have not found anything that would connect your reply to the solution. Could you be more specific?

    Gary

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gwpaul View Post
    Could you be more specific?
    sure

    generating one INSERT statement for each row will produce the following:
    Code:
    INSERT INTO table ( list,of,columns ) VALUES ( list,of,values );
    INSERT INTO table ( list,of,columns ) VALUES ( list,of,values );
    INSERT INTO table ( list,of,columns ) VALUES ( list,of,values );
    INSERT INTO table ( list,of,columns ) VALUES ( list,of,values );
    ...
    which is okay as far as it goes, but it's less efficient than this --
    Code:
    INSERT INTO table ( list,of,columns ) VALUES 
    ( list,of,values ) , ( list,of,values ) , ( list,of,values ) , ( list,of,values ) ...;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Then for seconds, you are only building the query in your loop, you aren't executing it within your loop. So that means you are executing it outside of the loop, which would result in the last query to be executed only.

    Two ways to solve this, one move the execution of the query into the for loop (take the hit that each query is executed individually).
    Or change your for loop to build multiple INSERT commands like below and leave the execution of your query outside of the for loop (notice I change = to .=, and I added a semi-colon to the end of the query.
    PHP Code:
          $query_Recordset3 '';
          for (
    $i=0$i count($postarray);$i++) { 

    $query_Recordset3 .= "INSERT INTO temporder ( cust, store_num, qnt, type, mfg, model, model_num, descrip, price, po) VALUES ('$cust','$store_num','$_POST[qnt]','$_POST[type]','$mfg','$model','$model_num','$descrip','$price','$po'); "

    er.. no. That's not the way to build a multiple-insert query.

    You're thinking more like this...
    PHP Code:
          $query_Recordset3 "INSERT INTO temporder ( cust, store_num, qnt, type, mfg, model, model_num, descrip, price, po) VALUES ";
       foreach(
    $_POST['entry'] AS $postvar) {
         
    $query_Recordset3 .= "('".$postvar['cust']."','".$postvar['store_num']."','".$postvar['qnt']."','".$postvar['type']."','".$postvar['mfg']."','".$postvar['model']."','".$postvar['model_num']."','".$postvar['descrip']."','".$postvar['price']."','".$postvar['po']."'),"
    }  
    $query_Recordset3 substr($query_Recordset3,0,-1); // get rid of trailing comma, since we cant use implode 
    Though from the look of your code, i think your form needs redesigning.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by r937 View Post
    sure

    generating one INSERT statement for each row will produce the following:
    Code:
    INSERT INTO table ( list,of,columns ) VALUES ( list,of,values );
    INSERT INTO table ( list,of,columns ) VALUES ( list,of,values );
    INSERT INTO table ( list,of,columns ) VALUES ( list,of,values );
    INSERT INTO table ( list,of,columns ) VALUES ( list,of,values );
    ...
    which is okay as far as it goes, but it's less efficient than this --
    Code:
    INSERT INTO table ( list,of,columns ) VALUES 
    ( list,of,values ) , ( list,of,values ) , ( list,of,values ) , ( list,of,values ) ...;
    Hi,

    Do you know a way to bind values to this way of doing things or do you just escape them yourself?

    Regards,
    Steve
    ictus==""

  8. #8
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Starlion

    Thank you for your detailed reply. After trying a number of different variations I am unable to get it to work. The results I'm sure are telling. In the first row, the result was '7', which is the quantity I entered for the last record. The next results were the first letter only of the records. This is the current variant of the code.

    PHP Code:

    $postvar
    =array("$cust""$store_num","$_POST[qnt]","$_POST[type]","$_POST[mfg]","$_POST[model]","$_POST[model_num]","$_POST[descrip]","$_POST[msrp]");

    $query_Recordset3 "INSERT INTO temporder ( cust, store_num, qnt, type, mfg, model, model_num, descrip, price, po) VALUES ";

       foreach(
    $_POST  as $postvar) {
         
    $query_Recordset3 .= "('".$postvar['cust']."','".$postvar['store_num']."','".$postvar['qnt']."','".$postvar['type']."','".$postvar['mfg']."','".$postvar['model']."','".$postvar['model_num']."','".$postvar['descrip']."','".$postvar['price']."','".$postvar['po']."'),"
    }  


    $query_Recordset3 substr($query_Recordset3,0,-1); // get rid of trailing comma, since we cant use implode  
    $Recordset3 mysql_query($query_Recordset3$szabo) or die(mysql_error());
    $row_Recordset3 mysql_fetch_assoc($Recordset3);
    $totalRows_Recordset3 mysql_num_rows($Recordset3); 
    Again I am still perplexed why my original logic did not work.

    Thank you

    Gary

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    Do you know a way to bind values to this way of doing things or do you just escape them yourself?
    nope, neither

    sorry, i don't do php

    i know what it should be generating, that's all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937

    I see your logic, it is the looping of the (list,of,values) that is eluding me.

    Gary

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    I need to see your form HTML before i can help further.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  12. #12
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the complete [sanitized] form page. The form is created dynamically. I will include the result page below.

    The form is created from a query selected rows from an individual customer table. When they execute a new table is created 'temporder', the information is inserted then queried back in the query that we have been working on.


    PHP Code:
    <?php session_start();?>
    <?php 
    require_once('Connections/*******.php'); ?>
    <?php 
    require_once( "WA_SecurityAssist/Helper_PHP.php" ); ?>
    <?php
    if (!WA_Auth_RulePasses("Logged in to company")){
        
    WA_Auth_RestrictAccess("company_LogIn.php");
    }

    if (!
    function_exists("GetSQLValueString")) {
    function 
    GetSQLValueString($theValue$theType$theDefinedValue ""$theNotDefinedValue ""
    {
      
    $theValue get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

      
    $theValue function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

      switch (
    $theType) {
        case 
    "text":
          
    $theValue = ($theValue != "") ? "'" $theValue "'" "NULL";
          break;    
        case 
    "long":
        case 
    "int":
          
    $theValue = ($theValue != "") ? intval($theValue) : "NULL";
          break;
        case 
    "double":
          
    $theValue = ($theValue != "") ? "'" doubleval($theValue) . "'" "NULL";
          break;
        case 
    "date":
          
    $theValue = ($theValue != "") ? "'" $theValue "'" "NULL";
          break;
        case 
    "defined":
          
    $theValue = ($theValue != "") ? $theDefinedValue $theNotDefinedValue;
          break;
      }
      return 
    $theValue;
    }
    }

    mysql_select_db($database_szabo$szabo);
    $query_Recordset11 "SELECT company.id, company.name FROM company WHERE id =$_SESSION[id]";
    $Recordset11 mysql_query($query_Recordset11$szabo) or die(mysql_error());
    $row_Recordset11 mysql_fetch_assoc($Recordset11);
    $totalRows_Recordset11 mysql_num_rows($Recordset11);


    $cust $row_Recordset11 ['name'];
    $cust=str_replace(' '''$cust);
    $cust strtolower($cust);
    mysql_select_db($database_szabo$szabo);
    $query_Recordset22 "SELECT * FROM $cust WHERE  model  IS NOT NULL";
    $Recordset22 mysql_query($query_Recordset22$szabo) or die(mysql_error());
    $row_Recordset22 mysql_fetch_assoc($Recordset22);
    $totalRows_Recordset22 mysql_num_rows($Recordset22);
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    <style type="text/css">

    body    {
        margin:0px;    
        }
    h1, h2, h3, h4,h5    {
        margin:0px;
        }
    th    {
        width:80px;}
    #orderform    {
        width:800px;
        margin:0px auto;}
    .center    {
        text-align:center;
        }
    </style>
    </head>

    <body>
    <?php
    $_SESSION
    ['type']= $row_Recordset22['type'];


    ?>

    <div id="orderform">
    <h2><?php echo $row_Recordset11['name']; ?>  Order Form</h2>
    <form action="szabo-confirmation.php" method="post" name="result" id="result">

    <?php 
    echo '<table>';
    echo 
    '<th>'.'Quantity'.'</td>'.  '<th>'.'Type'.'</td>'.  '<th>'.'Manufacturer'.'</td>'.  '<th>'.'Model'.'</td>'.  '<th>'.'Model Number'.'</td>'.  '<th>'.'Description'.'</td>''<th>'.'Price'.'</td>';
    do{

    echo 
    '<tr><td>'.'<input type="text" id="qnt" name="qnt" size="5" >''</td>';
    echo 
    '<td>'.'<input type="text" id="type" name="type" size="20"  readonly="readonly" value = '."$row_Recordset22[type]".' >'.'</td>';
    echo 
    '<td>'.'<input type="text" id="mfg" name ="mfg" size="20"  readonly="readonly" value = '."$row_Recordset22[mfg]".' >'.'</td>';
    echo 
    '<td>'.'<input type="text" id="model" name="model"  size="20"  readonly="readonly" value = '."$row_Recordset22[model]".' >'.'</td>';
    echo 
    '<td>'.'<input type="text" id="model_num" name="model_num" size="20"  readonly="readonly" value = '."$row_Recordset22[model_num]".' >'.'</td>';
    echo 
    '<td>'.'<input type="text" id="descrip" size="20" name="descrip"  readonly="readonly" value = '."$row_Recordset22[descrip]".' >'.'</td>';
    echo 
    '<td>'.'<input type="text" id="msrp" name="msrp" size="20"  readonly="readonly" value = '."$row_Recordset22[msrp]".' >'.'</td>';


    /*echo '<td>' '</td>';*/
    echo '</tr>';

    }

    while(
    $row_Recordset22 mysql_fetch_assoc($Recordset22));
    echo 
    '</table>';

    echo 
    '<h2>'."$row_Recordset22[type]".'</h2>';
    ?>
    <input name="submit" type="submit" value="Submit for Confirmation" />
    </form>
    </div>
    </body>
    </html>
    <?php
    mysql_free_result
    ($Recordset11);

    mysql_free_result($Recordset22);
    ?>
    The results page

    <?php session_start();?>
    <?php require_once('Connections/*******.php'); ?>

    <?php
    if (!function_exists("GetSQLValueString")) {
    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
    {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

    $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

    switch ($theType) {
    case "text":
    $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
    break;
    case "long":
    case "int":
    $theValue = ($theValue != "") ? intval($theValue) : "NULL";
    break;
    case "double":
    $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
    break;
    case "date":
    $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
    break;
    case "defined":
    $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
    break;
    }
    return $theValue;
    }
    }
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    <style type="text/css">

    body {
    margin:0px;
    }
    h1, h2, h3, h4,h5 {
    margin:0px;
    }
    td {
    text-align:center;}
    .center {
    text-align:center;
    }
    </style>
    </head>

    <body>
    <?php


    @$qnt = $_POST['qnt'];
    @$type = $_POST['type'];
    @$mfg = $_POST['mfg'];
    @$model = $_POST['model'];
    @$model_num = $_POST['model_num'];
    @$descrip = $_POST['descrip'];
    @$msrp = $_POST['msrp'];
    @$price = $qnt * $msrp;
    @$po=$_POST['po'];
    ?>
    <?php
    mysql_select_db($database_szabo, $szabo);
    $qry=mysql_query( "CREATE TABLE temporder LIKE orders ");




    mysql_select_db($database_szabo, $szabo);
    $query_Recordset1 = "SELECT company.name, company.store_num FROM company WHERE company.id =$_SESSION[id]";
    $Recordset1 = mysql_query($query_Recordset1, $szabo) or die(mysql_error());

    $totalRows_Recordset1 = mysql_num_rows($Recordset1);

    @$cust = "$row_Recordset1[name]";
    @$store_num = "$row_Recordset1[store_num]";

    ///////below are some of the variations of the loop I have been working on, currently i have the 2 loops and getting the same results////////////
    /*
    foreach($_POST as $key => $value)
    {*/
    mysql_select_db($database_szabo, $szabo);


    /* for ($i=1; $i < count($postarray);$i++) {*/
    /*$vd=var_dump($postarray);
    $postex = explode(' ',$vd);
    foreach ($postex as &$value){ */
    /* foreach($_POST as $postarray) {


    $query_Recordset3 = " INSERT INTO temporder ( qnt, type, mfg, model, model_num, descrip, price) VALUES ('$postarray[qnt]','$postarray[type]','$postarray[mfg]','$postarray[model]','$postarray[model_num]','$postarray[descrip]','$postarray[msrp]') ";

    $Recordset3 = mysql_query($query_Recordset3, $szabo) or die(mysql_error());

    }*/
    $postvar=array("$cust", "$store_num","$_POST[qnt]","$_POST[type]","$_POST[mfg]","$_POST[model]","$_POST[model_num]","$_POST[descrip]","$_POST[msrp]");

    $query_Recordset3 = "INSERT INTO temporder ( cust, store_num, qnt, type, mfg, model, model_num, descrip, price, po) VALUES ";

    for ($i=0; $i < count($postvar);$i++) {

    foreach($_POST as $postvar) {



    $query_Recordset3 .= "('".$postvar['cust']."','".$postvar['store_num']."','".$postvar['qnt']."','".$postvar['type']."','".$postvar['mfg']."','".$postvar['model']."','".$postvar['model_num']."','".$postvar['descrip']."','".$postvar['price']."','".$postvar['po']."'),";
    }

    }
    $query_Recordset3 = substr($query_Recordset3,0,-1); // get rid of trailing comma, since we cant use implode
    $Recordset3 = mysql_query($query_Recordset3, $szabo) or die(mysql_error());
    $row_Recordset3 = mysql_fetch_assoc($Recordset3);
    $totalRows_Recordset3 = mysql_num_rows($Recordset3);


    mysql_select_db($database_szabo, $szabo);
    $query_Recordset2 = "SELECT * FROM temporder WHERE qnt IS NOT NULL";
    $Recordset2 = mysql_query($query_Recordset2, $szabo) or die(mysql_error());
    $row_Recordset2 = mysql_fetch_assoc($Recordset2);
    $totalRows_Recordset2 = mysql_num_rows($Recordset2);





    echo '<table cellspacing ="6" align="center">';
    echo '<tr><th>'.'Quantity'.'</td>'. '<th>'.'Type'.'</td>'. '<th>'.'Manufacturer'.'</td>'. '<th>'.'Model'.'</td>'. '<th>'.'Model Number'.'</td>'. '<th>'.'Description'.'</td>'. '<th>'.'Price'.'</td></tr>';
    do {

    echo '<tr><td>'.'<input type="text" id="qnt" name="qnt" size="2" value = '."$row_Recordset2[qnt]".'>'. '</td>';
    echo '<td>'."$row_Recordset2[type]".'</td>';
    echo '<td>'."$row_Recordset2[mfg]".' </td>';
    echo '<td>'."$row_Recordset2[model]".'</td>';
    echo '<td>'."$row_Recordset2[model_num]".'</td>';
    echo '<td>'."$row_Recordset2[descrip]".'</td>';
    echo '<td>'."$row_Recordset2[price]".'</td>';


    echo '</tr>';

    }
    while($row_Recordset2 = mysql_fetch_assoc($Recordset2));



    echo '</table>';
    echo $cust;
    echo $qnt;
    echo $_SESSION['type'];

    ?>
    </body>
    </html>
    <?php
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    /*mysql_free_result($Recordset2);
    mysql_free_result($Recordset3);
    mysql_free_result($Recordset1);*/
    ?>

  13. #13
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    k, so here's why it doesnt work.
    echo '<td>'.'<input type="text" id="type" name="type" size="20" readonly="readonly" value = '."$row_Recordset22[type]".' >'.'</td>';

    This field has name "type".
    When the loop executes again, it creates another field.
    With name "type".
    Which... will overwrite the last one.

    So we need to tweak your form a bit to be able to handle multiple entries.

    What is the unique identifier for an item in your table? model_num?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  14. #14
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow...good eye. (I actually suffered from using 'desc' as a column in a table once)

    I have an ID field called 'id' which is the primary.

    Gary

  15. #15
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    mkay.

    I'm going to take a time out here and say this;
    We can make this work with what you've got. It'll take a bit of complex form creation, but it can be done.
    I personally would recommend that you take the time to pause working on this form and head to the mySQL/databases forum with your schema; your database design is screaming 'Normalize Me!' to me.

    Normalizing the database will make trying to do these inserts a LOT simpler.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  16. #16
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I read some articles on normalization, and on the face of it it would seem this suffers, however I have sep tables for much of the information (company, products etc). In this case I used a separate query instead of a join to insert the company name.

    The issue seems to be boiling down to the POST in that I can only get the last record. I have tried to insert and store in an array, but the same result of only the last result of the do while loop. I renamed "type" to "ptype" but that did not offer any different result.

    This can't be that unique of an application, but it still is eluding me.

    Any suggestions?

    Thanks for your help.

    Gary

  17. #17
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Your database screams normalize me because you're trying to insert product information into an order query; this would be redundant. The only thing an order needs to know is what was ordered (Your product's unique ID), how much was ordered, and by whom (a customer unique ID, presumably).

    What you're wanting to do is not so unique, no, but the only piece of information you should need to send via post is qty, attached to a key (the product's ID)
    <input type='text' name="qty[<productid>]">

    and then in the form-catcher...
    PHP Code:
    //Assumes $cust predefined (Session Value?)
    $query_Recordset3 "INSERT INTO temporder ( cust, qnt, prod_id) VALUES ";
    foreach(
    $_POST['qty'] AS $id => $qty) {
     
    //Sanitize both $id and $qty here     
          
    $query_Recordset3 .= "('".$cust."','".$qty."','".$id."'),"
    }
    $query_Recordset3 substr($query_Recordset3,0,-1); // get rid of trailing comma, since we cant use implode  
    $Recordset3 mysql_query($query_Recordset3$szabo) or die(mysql_error()); 
    Incidentally, see how much cleaner that query looks when normalized?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  18. #18
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again, however no luck. At this point I am getting a warning and error

    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\szabo\szabo-confirmation.php on line 93
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

    :: Line 93 foreach($_POST['qnt'] AS $id => $qnt) {

    I have added a prod_id column and <input>, works fine Each product has a unique id. Have varied your example numerous ways to use the prod_id instead of the qnt, at one point I was able to insert the last $_post.

    I appreciate your time and don't really expect you to write my code for me, the why this is not working is baffling.

    Below is the code for the two pages, maybe something will jump out.

    Input page

    PHP Code:
    <?php session_start();?>
    <?php 
    require_once('Connections/szabo.php'); ?>
    <?php 
    require_once( "WA_SecurityAssist/Helper_PHP.php" ); ?>
    <?php error_reporting
    (0);?>
    <?php
    if (!WA_Auth_RulePasses("Logged in to company")){
        
    WA_Auth_RestrictAccess("company_LogIn.php");
    }

    if (!
    function_exists("GetSQLValueString")) {
    function 
    GetSQLValueString($theValue$theType$theDefinedValue ""$theNotDefinedValue ""
    {
      
    $theValue post_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

      
    $theValue function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

      switch (
    $theType) {
        case 
    "text":
          
    $theValue = ($theValue != "") ? "'" $theValue "'" "NULL";
          break;    
        case 
    "long":
        case 
    "int":
          
    $theValue = ($theValue != "") ? intval($theValue) : "NULL";
          break;
        case 
    "double":
          
    $theValue = ($theValue != "") ? "'" doubleval($theValue) . "'" "NULL";
          break;
        case 
    "date":
          
    $theValue = ($theValue != "") ? "'" $theValue "'" "NULL";
          break;
        case 
    "defined":
          
    $theValue = ($theValue != "") ? $theDefinedValue $theNotDefinedValue;
          break;
      }
      return 
    $theValue;
    }
    }

    mysql_select_db($database_szabo$szabo);
    $query_Recordset11 "SELECT company.id, company.name FROM company WHERE id =$_SESSION[id]";
    $Recordset11 mysql_query($query_Recordset11$szabo) or die(mysql_error());
    $row_Recordset11 mysql_fetch_assoc($Recordset11);
    $totalRows_Recordset11 mysql_num_rows($Recordset11);


    $cust $row_Recordset11 ['name'];
    $cust=str_replace(' '''$cust);
    $cust strtolower($cust);
    mysql_select_db($database_szabo$szabo);
    $query_Recordset22 "SELECT * FROM $cust WHERE  model  IS NOT NULL";
    $Recordset22 mysql_query($query_Recordset22$szabo) or die(mysql_error());
    $row_Recordset22 mysql_fetch_assoc($Recordset22);
    $totalRows_Recordset22 mysql_num_rows($Recordset22);

    $query_Recordset222 "SELECT * FROM $cust WHERE  model  IS NOT NULL";
    $Recordset222 mysql_query($query_Recordset222$szabo) or die(mysql_error());
    $row_Recordset222 mysql_fetch_assoc($Recordset222);
    $totalRows_Recordset222 mysql_num_rows($Recordset222);
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    <style type="text/css">

    body    {
        margin:0px;    
        }
    h1, h2, h3, h4,h5    {
        margin:0px;
        }
    th    {
        width:80px;}
    #orderform    {
        width:800px;
        margin:0px auto;}
    .center    {
        text-align:center;
        }
    </style>
    <script type="text/javascript">
    <!--
    function MM_jumpMenu(targ,selObj,restore){ //v3.0
      eval(targ+".location='"+selObj.options[selObj.selectedIndex].value+"'");
      if (restore) selObj.selectedIndex=0;
    }
    //-->
    </script>
    </head>

    <body>

    <?php
    $_SESSION
    ['ptype']= $row_Recordset22['ptype'];


    ?>

    <div id="orderform">
    <h2><?php echo $row_Recordset11['name']; ?>  Order Form</h2>






    <table>
    <th>Quantity</th><th>Product Id</th><th>Type</th><th>Manufacturer</th><th>Model</th> <th>Model Number</th> <th>Description</th><th>Price</th></tr>
    <?php 

    do{

    ?>
    <form action="szabo-confirmation.php" method="post" name="result" id="result">
    <tr><td><select name ="qnt" id="qnt" ><option value="0">0</option><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option></select></td>
    <td><input type="text" id="prod_id" name="prod_id" size="4"  readonly="readonly" value = "<?php echo $row_Recordset22[prod_id?>"></td>
    <td><input type="text" id="ptype" name="ptype" size="10"  readonly="readonly" value = "<?php echo $row_Recordset22[ptype?>"></td>
    <td><input type="text" id="mfg" name ="mfg" size="10"  readonly="readonly" value = "<?php echo $row_Recordset22[mfg?>"></td>
    <td><input type="text" id="model" name="model"  size="20"  readonly="readonly" value = "<?php echo $row_Recordset22[model?>"></td>
    <td><input type="text" id="model_num" name="model_num" size="20"  readonly="readonly" value ="<?php echo $row_Recordset22[model_num?>"></td>
    <td><input type="text" id="descrip" size="20" name="descrip"  readonly="readonly" value ="<?php echo $row_Recordset22[descrip?>"></td>
    <td><input type="text" id="msrp" name="msrp" size="10"  readonly="readonly" value ="<?php echo $row_Recordset22[msrp?>"></td>




    </tr>

    <?php }

    while(
    $row_Recordset22 mysql_fetch_assoc($Recordset22));
    ?>
    </table>

    <input name="submit" type="submit" name="temporsub" value="Submit for Confirmation" />

    </form>

    <?php 

    /*$_SESSION['$row22']=mysql_fetch_assoc($Recordset22);*/
    ?>
    <?php 
    $myArr 
    = array();

    if(
    mysql_num_rows($Recordset222) > 0)//if it finds any row
    {
       while(
    $result mysql_fetch_array($Recordset222))
       {
          
    //adding data to the array
          
    $myArr[] = $result->$Recordset222['qnt'];
       }
    }
    /*print_r ($myArr);*/
    ?>
    </div>


    </body>
    </html>
    <?php
    mysql_free_result
    ($Recordset11);

    mysql_free_result($Recordset22);
    ?>
    Result Page (confirmation)

    PHP Code:
    <?php session_start();?>
    <?php 
    require_once('Connections/szabo.php'); ?>
    <?php error_reporting
    (0);?>
    <?php
    if (!function_exists("GetSQLValueString")) {
    function 
    GetSQLValueString($theValue$theType$theDefinedValue ""$theNotDefinedValue ""
    {
      
    $theValue get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

      
    $theValue function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

      switch (
    $theType) {
        case 
    "text":
          
    $theValue = ($theValue != "") ? "'" $theValue "'" "NULL";
          break;    
        case 
    "long":
        case 
    "int":
          
    $theValue = ($theValue != "") ? intval($theValue) : "NULL";
          break;
        case 
    "double":
          
    $theValue = ($theValue != "") ? "'" doubleval($theValue) . "'" "NULL";
          break;
        case 
    "date":
          
    $theValue = ($theValue != "") ? "'" $theValue "'" "NULL";
          break;
        case 
    "defined":
          
    $theValue = ($theValue != "") ? $theDefinedValue $theNotDefinedValue;
          break;
      }
      return 
    $theValue;
    }
    }
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    <style type="text/css">

    body    {
        margin:0px;    
        }
    h1, h2, h3, h4,h5    {
        margin:0px;
        }
    td    {
        text-align:center;}
    .center    {
        text-align:center;
        }
    </style>
    </head>

    <body>
    <?php 

    @$qnt $_POST['qnt'];
    @
    $qnta = array($_POST['qnt']);
    array_push($qnta"$qnt");
    @
    $ptype $_POST['ptype'];
    @
    $mfg $_POST['mfg'];
    @
    $model $_POST['model'];
    @
    $model_num $_POST['model_num'];
    @
    $descrip $_POST['descrip'];
    @
    $msrp $_POST['msrp'];
    @
    $price $qnt $msrp;
    @
    $po=$_POST['po'];
    $prod_id=$_POST['prod_id'];
    ?>
    <?php 
    mysql_select_db
    ($database_szabo$szabo);
    $qry=mysql_query(  "CREATE   TABLE temporder LIKE orders ");

    mysql_select_db($database_szabo$szabo);
    $query_Recordset1 "SELECT company.name, company.store_num  FROM company WHERE  company.id =$_SESSION[id]";
    $Recordset1 mysql_query($query_Recordset1$szabo) or die(mysql_error());

    $totalRows_Recordset1 mysql_num_rows($Recordset1);

    @
    $cust "$row_Recordset1[name]";
    @
    $store_num "$row_Recordset1[store_num]";


    mysql_select_db($database_szabo$szabo);    



    ///////////////////////////////////From Forum////////////////////////////////////////////

    //Assumes $cust predefined (Session Value?)
    $query_Recordset3 "INSERT INTO temporder ( cust, qnt, prod_id) VALUES ";
    foreach(
    $_POST['qnt'] AS $id => $qnt) {
     
    //Sanitize both $id and $qty here     
          
    $query_Recordset3 .= "('".$cust."','".$id."','".$prod_id."'),"
    }
    $query_Recordset3 substr($query_Recordset3,0,-1); // get rid of trailing comma, since we cant use implode  
    $Recordset3 mysql_query($query_Recordset3$szabo) or die(mysql_error());  


    /////////////////////////////////////////////////////////////////////////////////////////////



    $Recordset3 mysql_query($query_Recordset3$szabo) or die(mysql_error());
    @
    $row_Recordset3 mysql_fetch_array($Recordset3);
    @
    $totalRows_Recordset3 mysql_num_rows($Recordset3);

    mysql_select_db($database_szabo$szabo);
    $query_Recordset2 "SELECT * FROM temporder WHERE qnt >='1'";
    $Recordset2 mysql_query($query_Recordset2$szabo) or die(mysql_error());
    $row_Recordset2 mysql_fetch_array($Recordset2);
    $totalRows_Recordset2 mysql_num_rows($Recordset2);

    echo 
    '<table cellspacing ="6" align="center">';
    echo 
    '<tr><th>'.'Quantity'.'</td>'.  '<th>'.'Type'.'</td>'.  '<th>'.'Manufacturer'.'</td>'.  '<th>'.'Model'.'</td>'.  '<th>'.'Model Number'.'</td>'.  '<th>'.'Description'.'</td>''<th>'.'Price'.'</td></tr>';
    do    {

    echo 
    '<tr><td>'.'<input type="text" id="qnt" name="qnt" size="2" value = '."$row_Recordset2[qnt]".'>''</td>';
    echo 
    '<td>'."$row_Recordset2[ptype]".'</td>';
    echo 
    '<td>'."$row_Recordset2[mfg]".' </td>';
    echo 
    '<td>'."$row_Recordset2[model]".'</td>';
    echo 
    '<td>'."$row_Recordset2[model_num]".'</td>';
    echo 
    '<td>'."$row_Recordset2[descrip]".'</td>';
    echo 
    '<td>'."$row_Recordset2[price]".'</td>';


    echo 
    '</tr>';
    $row_Recordset2 mysql_fetch_array($Recordset2);
    $totalRows_Recordset2 mysql_num_rows($Recordset2);
    }
    while(
    $row_Recordset2 mysql_fetch_array($Recordset2));


    echo 
    '</table>';

    ?>
    </body>
    </html>
    <?php
    /*$row_Recordset1 = mysql_fetch_array($Recordset1);*/

    ?>
    Thanks again

    Gary

  19. #19
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    You havent keyed your form fields as an array.

    Code:
    <select name ="qnt" id="qnt" >
    ....
    PHP Code:
    <select name ="qnt[<?php echo $row_Recordset22['prod_id'?>]" id="qnt" >
    this will result in field names with array keys...
    IE:
    qnt[1]
    qnt[5]
    qnt[10]

    Which will be passed to PHP as $_POST['qnt'], an array of size 3, containing the key-value pairs {1 => somevalue,5 => somevalue,10 => somevalue}
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  20. #20
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, with your kind help it is almost working. I may have muddied the waters a bit when I added the prod_id as the key. The qnt is now inserting as I want, the prod_id is not. Here is what I have;
    Code:
    <tr><td><select name ="qnt[<?php echo $row_Recordset22['qnt'] ?>]" id="qnt" >
    
    <td><input type="text" id="prod_id" name="prod_id[<?php echo $row_Recordset22['prod_id'] ?>]" size="4"  readonly="readonly" value = "<?php echo $row_Recordset22['prod_id'] ?>"></td>
    What is working is this

    PHP Code:
    $query_Recordset3 "INSERT INTO temporder ( cust, qnt, prod_id) VALUES ";
    foreach(
    $_POST['qnt'] AS $id => $qnt) {
     
    //Sanitize both $id and $qty here     
          
    $query_Recordset3 .= "('".$cust."','".$qnt."','".$prod_id."'),"
    }

    $query_Recordset3 substr($query_Recordset3,0,-1); // get rid of trailing comma, since we cant use implode  
    $Recordset3 mysql_query($query_Recordset3$szabo) or die(mysql_error()); 
    However when I tried a 2nd foreach() on an update, I am getting a "columns dont match" error.

    PHP Code:
    $query_Recordset3 "INSERT INTO temporder ( cust, qnt, prod_id) VALUES ";
    foreach(
    $_POST['qnt'] AS $id => $qnt) {
     
    //Sanitize both $id and $qty here     
          
    $query_Recordset3 .= "('".$cust."','".$qnt."'),"
    }

    $query_Recordset3 substr($query_Recordset3,0,-1); // get rid of trailing comma, since we cant use implode  
    $Recordset3 mysql_query($query_Recordset3$szabo) or die(mysql_error());  

    $query_Recordset4 "UPDATE temporder SET prod_id";
    foreach(
    $_POST['prod_id'] AS $pid => $prod_id) {
     
    //Sanitize both $id and $qty here     
          
    $query_Recordset4 .= "('".$prod_id."'),"
    }
    $query_Recordset4 substr($query_Recordset4,0,-1); // get rid of trailing comma, since we cant use implode  
    $Recordset4 mysql_query($query_Recordset4$szabo) or die(mysql_error()); 
    Thanks so much for your patience and help. Hopefully it will finally be solved.

    Gary

  21. #21
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Mmh no.

    Do it all in one insert query. You have it almost perfect in your second codeblock, just need a very slight variable change...
    Notice that the keys of the $_POST['qnt'] array are already your product ID's. You dont need to send them seperately/insert them seperately.

    (Hint: foreach syntax might help you understand)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  22. #22
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried every iteration I can think of, swapped the prod_id with qnt, I've tried adding multiple foreach loops. Worse is that even when I have one working, either the prod_id or the qnt, it enters double into the table.

    I cant get it to work.

  23. #23
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Code:
    $query_Recordset3 = "INSERT INTO temporder ( cust, qnt, prod_id) VALUES ";
    foreach($_POST['qnt'] AS $id => $qnt) {
     //Sanitize both $id and $qty here     
          $query_Recordset3 .= "('".$cust."','".$qnt."','".$prod_id."'),"; 
    }
    
    $query_Recordset3 = substr($query_Recordset3,0,-1); // get rid of trailing comma, since we cant use implode  
    $Recordset3 = mysql_query($query_Recordset3, $szabo) or die(mysql_error());
    *sings* one of these things just doesnt belong here, one of these things just isnt the same...
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  24. #24
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.....i used to like that song.

  25. #25
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    I get to sing that one around here quite a bit

    Hope that solved your problem.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •