SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post MySql mulitple row PHP update

    Hi all,

    I'm creating an update page that will update all the rows in a mysql db. At the moment it only updates the last row of the table. Any help is greatly received.

    Code:

    if (isset($_POST['submit']))
    {
    for ( $i=0; $_POST["id"] ; $i++)
    {
    $theid = $_POST["id"][$i];
    $qry = mysql_query("UPDATE products SET
    products_kelkoo_date = '$_REQUEST[products_kelkoo_date]',
    products_kelkoo_price = '$_REQUEST[products_kelkoo_price]',
    products_price = '$_REQUEST[products_price]',
    products_best_date = '$_REQUEST[products_best_date]',
    products_best_price = '$_REQUEST[products_best_price]',
    products_best_name = '$_REQUEST[products_best_name]',
    products_notes = '$_REQUEST[products_notes]' WHERE products_id = $theid");
    mysql_query($qry);
    }
    }
    else
    {

    // read data from database
    $result = mysql_query("select * from products order by products_model limit $rows")
    or die ("Could not read data because ".mysql_error());

    // print the data in a table
    if (mysql_num_rows($result)) {
    print "<div id=Main>";
    print "<table cellpadding=2 cellspacing=0 border=1 width=\"1032\" bgcolor=#FFFBF0 style=border-color:#000066>\n";
    print "<tr class=style4>

    <td width=27 bgcolor=#3399FF>ID</td>
    <td width=117 bgcolor=#3399FF>Make</td>
    <td width=128 bgcolor=#3399FF>Model</td>
    <td width=74 bgcolor=#3399FF>Kelkoo Price as of*</td>
    <td width=74 bgcolor=#3399FF>Kelkoo Lowest Price</td>
    <td width=55 bgcolor=#3399FF>Sell Price Incl VAT</td>
    <td width=55 bgcolor=#3399FF>Special Price</td>
    <td width=55 bgcolor=#3399FF>Kelkoo Price Diff</td>
    <td width=74 bgcolor=#3399FF>Sell Price Ex VAT</td>
    <td width=74 bgcolor=#3399FF>Buy Price as of*</td>
    <td width=74 bgcolor=#3399FF>Best Buy Price</td>
    <td width=85 bgcolor=#3399FF>Best Buy Company</td>
    <td width=165 bgcolor=#3399FF>Product Notes</td>
    </tr>";
    while ($qry = mysql_fetch_array($result)) {

    $id=$qry[manufacturers_id];

    $manufactdetails = mysql_query("SELECT manufacturers_name FROM manufacturers WHERE manufacturers_id=$id");
    $manufact = mysql_fetch_array($manufactdetails);
    $manufacturers_name = $manufact[manufacturers_name];


    $specialdetails = mysql_query("SELECT specials_new_products_price FROM specials WHERE products_id=$qry[products_id]");
    $special = mysql_fetch_array($specialdetails);
    $specials_price = $special[specials_new_products_price];

    // define what % vat is
    $vat = 17.5;

    // work out the amount of vat
    $price_with_vat = $qry[products_price] + ($vat*($qry[products_price]/100));

    // Kelkoo difference sum
    $products_diff = ($qry[products_kelkoo_price] - $price_with_vat);

    // strings used in decimal point routine
    $vatprice = $price_with_vat;
    $price = $qry[products_price];
    $numberkelkoo = $qry[products_kelkoo_price];

    // Kelkoo difference to two decimal places
    $kelkoo_diff = number_format($products_diff, 2, '.', '');

    // VAT price to two decimal places
    $products_price = number_format($price, 2, '.', '');

    // Kelkoo price to two decimal places
    $kelkooprice = number_format($numberkelkoo, 2, '.', '');

    // VAT price to two decimal places
    $products_vatprice = number_format($vatprice, 2, '.', '');


    $specials_pricedec = number_format($specials_price, 2, '.', '');

    // Display results
    print "<form name=opscreen method=post action=$_SERVER[PHP_SELF]>";
    print "<tr class=style3>
    <td>$qry[products_id] <input name=id type=hidden value=$qry[products_id]></td>
    <td>$manufacturers_name </td>
    <td>$qry[products_model] </td>
    <td><input name=products_kelkoo_date type=text value= $qry[products_kelkoo_date] size=6 ></td>
    <td><input name=products_kelkoo_price type=text value= $kelkooprice size=6 ></td>
    <td>$products_vatprice </td>
    <td>$specials_pricedec </td>
    <td>$kelkoo_diff </td>
    <td><input name=products_price type=text value= $products_price size=5 ></td>
    <td><input name=products_best_date type=text value= $qry[products_best_date] size=5 ></td>
    <td><input name=products_best_price type=text value= $qry[products_best_price] size=5 ></td>
    <td><input name=products_best_name type=text value= $qry[products_best_name] size=6 ></td>
    <td><textarea rows=6 name=products_notes cols=12 >$qry[products_notes] </textarea></td>";
    print "</td></tr>\n";
    }
    print "</table></div>\n";
    print "<input type=submit name=submit value=Update />";
    print "</form>";
    }
    }
    ?>

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    wow, that's a lot of php code for a mysql forum

    could this be the reason you're updating only one row --

    ... WHERE products_id = $theid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    removed it and still does not work.

    How do i use an array for this or a loop

  4. #4
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i still need help please

  5. #5
    Internet Business Optimizer KCgame's Avatar
    Join Date
    Mar 2005
    Location
    Singapore
    Posts
    931
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi norwichdesign,

    I might have some misunderstanding in your for loop condition..

    PHP Code:
    for ( $i=0$_POST["id"] ; $i++) 
    When will $_POST["id"] return FALSE to stop the loop?

  6. #6
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've changed my code and i think it's nearly there, but now when i press update i'm just getting 'array' placed into the table on all rows

    if (isset($_POST['submit']))

    {
    for ($i = 0; $i < count($_REQUEST['rowid'])+1; $i++) {
    $rowid = $_REQUEST['rowid'][$i];
    $products_best_date = $_REQUEST['products_best_date'][$i];
    $products_best_price = $_REQUEST['products_best_price'][$i];
    $products_best_name = $_REQUEST['products_best_name'][$i];
    $query = mysql_query("UPDATE products SET
    products_kelkoo_date = '$_REQUEST[products_kelkoo_date]',
    products_kelkoo_price = '$_REQUEST[products_kelkoo_price]',
    products_price = '$_REQUEST[products_price]',
    products_best_date = '".$products_best_date."',
    products_best_price = '".$products_best_price."',
    products_best_name = '".$products_best_name."',
    products_notes = '".$products_notes."' where rowid ='".$rowid."'");
    $result = mysql_query($query);
    or die ("Couldn’t execute query" .mysql_error());}
    }
    else
    {

    // print the data in a table


    $qry = mysql_query("select * from products order by products_model")
    or die ("Could not read data because ".mysql_error());
    $nrows = mysql_num_rows($qry);

    echo "<table cellpadding=2 cellspacing=0 border=1 width=\"1032\" bgcolor=#FFFBF0 style=border-color:#000066>";


    echo "\t<tr class=style4>";
    echo" <td width=27 bgcolor=#3399FF>ID</td>
    <td width=117 bgcolor=#3399FF>Make</td>
    <td width=128 bgcolor=#3399FF>Model</td>
    <td width=74 bgcolor=#3399FF>Kelkoo Price as of*</td>
    <td width=74 bgcolor=#3399FF>Kelkoo Lowest Price</td>
    <td width=55 bgcolor=#3399FF>Sell Price Incl VAT</td>
    <td width=55 bgcolor=#3399FF>Special Price</td>
    <td width=55 bgcolor=#3399FF>Kelkoo Price Diff</td>
    <td width=74 bgcolor=#3399FF>Sell Price Ex VAT</td>
    <td width=74 bgcolor=#3399FF>Buy Price as of*</td>
    <td width=74 bgcolor=#3399FF>Best Buy Price</td>
    <td width=85 bgcolor=#3399FF>Best Buy Company</td>
    <td width=165 bgcolor=#3399FF>Product Notes</td>
    </tr>";

    for ($i=0; $i < $nrows; $i++) {
    WHILE($row = mysql_fetch_array($qry)){
    $i++;

    $id=$row[manufacturers_id];

    $manufactdetails = mysql_query("SELECT manufacturers_name FROM manufacturers WHERE manufacturers_id=$id");
    $manufact = mysql_fetch_array($manufactdetails);
    $manufacturers_name = $manufact[manufacturers_name];


    $specialdetails = mysql_query("SELECT specials_new_products_price FROM specials WHERE products_id=$row[products_id]");
    $special = mysql_fetch_array($specialdetails);
    $specials_price = $special[specials_new_products_price];


    // define what % vat is
    $vat = 17.5;

    // work out the amount of vat
    $price_with_vat = $row[products_price] + ($vat*($row[products_price]/100));

    // Kelkoo difference sum
    $products_diff = ($row[products_kelkoo_price] - $price_with_vat);

    // strings used in decimal point routine
    $vatprice = $price_with_vat;
    $price = $row[products_price];
    $numberkelkoo = $row[products_kelkoo_price];

    // Kelkoo difference to two decimal places
    $kelkoo_diff = number_format($products_diff, 2, '.', '');

    // VAT price to two decimal places
    $products_price = number_format($price, 2, '.', '');

    // Kelkoo price to two decimal places
    $kelkooprice = number_format($numberkelkoo, 2, '.', '');

    // VAT price to two decimal places
    $products_vatprice = number_format($vatprice, 2, '.', '');


    $specials_pricedec = number_format($specials_price, 2, '.', '');

    // Display results
    echo "<form name=opscreen method=post action=$_SERVER[PHP_SELF]>";

    $bgcolor = ($i&1) ? "#EBEBEB" : "#D7D7D7" ;
    echo "<tr class=style3 bgcolor = '$bgcolor'>\n";
    echo "\t<td>$row[products_id]&nbsp;<input name=rowid[".$i."] type=hidden value='".$row[products_id]."'></td>
    <td>$manufacturers_name&nbsp;</td>
    <td>$row[products_model]&nbsp;</td>
    <td><input name=products_kelkoo_date[$i] type=text value= $row[products_kelkoo_date] size=6 ></td>
    <td><input name=products_kelkoo_price[$i] type=text value= $kelkooprice size=6 ></td>
    <td>$products_vatprice&nbsp;</td>
    <td>".$specials_pricedec."&nbsp;</td>
    <td>".$kelkoo_diff."&nbsp;</td>
    <td><input name=products_price[$i] type=text value= $products_price size=5 ></td>
    <td><input name=products_best_date[$i] type=text value= $row[products_best_date] size=5 ></td>
    <td><input name=products_best_price[$i] type=text value= $row[products_best_price] size=5 ></td>
    <td><input name=products_best_name[".$i."] type=text value= '".$row[products_best_name]."' size=6 ></td>
    <td><textarea rows=6 name=products_notes[".$i."] cols=12 >'".$row[products_notes]."' </textarea></td></td></tr>";
    }
    echo "</table>";
    }
    echo "<input type=submit name=submit value=Update /></form>";
    }
    ?>

  7. #7
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm loosing hair over this, it's got to be something simple

  8. #8
    Internet Business Optimizer KCgame's Avatar
    Join Date
    Mar 2005
    Location
    Singapore
    Posts
    931
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are updating with array reference values...sql does not know which element in your array that you want it to match to..
    You should indicate the index of the array that you want the rows to update like:
    PHP Code:
    /*etc..*/ where rowid ='".$rowid[0]."' //the first element 
    instead of
    PHP Code:
    /*etc..*/ where rowid ='".$rowid."' 
    Hope it helps

  9. #9
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    now i'm getting

    Couldn’t execute queryQuery was empty

  10. #10
    SitePoint Addict
    Join Date
    Apr 2005
    Location
    San Diego, CA
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, congrats. Just a tip, in the future post just the relevant code instead of the entire script. You might get better responses.
    I study speed waiting. I can wait an entire hour in 10 minutes.

  11. #11
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I posted it all as i'm not sure where my error is.

    It's still not working


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
  •