Link to home
Start Free TrialLog in
Avatar of BenthamLtd
BenthamLtd

asked on

MySQL Insert - Repeating rows not matching position

Dear EE,

This is an odd one, hence the question. Here is a simple loop that extracts form variable inputs 12 times over and inserts them into the relevant SQL tables as "repeating rows". Works fine, apart from the positioning of the entries when pulling them back.

<?php

$i = 1;

while ($i <= 12) {

$fGoodsRet = $post['fGoodsRet'.$i.''];
$fGoodsRetQty = $post['fGoodsRetQty'.$i.''];

$fGoodsShip = $post['fGoodsShip'.$i.''];
$fGoodsShipQty = $post['fGoodsShipQty'.$i.''];

$fGoodsReceipt = $post['fGoodsReceipt'.$i.''];
$fGoodsReceiptQty = $post['fGoodsReceiptQty'.$i.''];

$fGARCode = $post['fGARCode'.$i.''];
$fGARQty = $post['fGARQty'.$i.''];
$fGARQuality = $post['fGARQuality'.$i.''];
$fGARType = $post['fGARType'.$i.''];
$fGARSupplier = $post['fGARSupplier'.$i.''];

$newsheet_goodstobereturned_query = "INSERT INTO goods_to_be_returned (Sheet_No, GoodsToBeReturned, GoodsToBeReturned_qty) VALUES ('$post[fSheetNo]', '$fGoodsRet', '$fGoodsRetQty') ";
$newsheet_goodstobereturned_sql = mysql_query($newsheet_goodstobereturned_query) or die(mysql_error());

$newsheet_goodstobeshipped_query = "INSERT INTO goods_to_be_shipped (Sheet_No, GoodsToBeShipped, GoodsToBeShipped_qty) VALUES ('$post[fSheetNo]', '$fGoodsShip', '$fGoodsShipQty') ";
$newsheet_goodstobeshipped_sql = mysql_query($newsheet_goodstobeshipped_query) or die(mysql_error());

$newsheet_goodstobesentonreceipt_query = "INSERT INTO goods_to_be_sent_on_receipt (Sheet_No, GoodsToBeSentOnReceipt, GoodsToBeSentOnReceipt_qty) VALUES ('$post[fSheetNo]', '$fGoodsReceipt', '$fGoodsReceiptQty') ";
$newsheet_goodstobesentonreceipt_sql = mysql_query($newsheet_goodstobesentonreceipt_query) or die(mysql_error());

$newsheet_goodsactuallyreturned_query = "INSERT INTO goods_actually_returned (Sheet_No, GoodsActuallyReturned, GoodsActuallyReturned_qty, GoodsActuallyReturned_quality, GoodsActuallyReturned_type, GoodsActuallyReturned_supplier) VALUES ('$post[fSheetNo]', '$fGARCode', '$fGARQty', '$fGARQuality', '$fGARType', '$fGARSupplier') ";
$newsheet_goodsactuallyreturned_sql = mysql_query($newsheet_goodsactuallyreturned_query) or die(mysql_error());

$i++;

}

?>

Open in new window


Weirdly, when viewing them on the edit.php page or in the MySQL tables, the order is reversed in one or more tables.

The goods_to_be_returned table, for example, looks like this:

+----------+-------------------+-----------------------+
| Sheet_No | GoodsToBeReturned | GoodsToBeReturned_qty |
+----------+-------------------+-----------------------+
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 |                   |                       | 
|   433303 | testA             | 1                     | 
+----------+-------------------+-----------------------+

Open in new window


Despite the entry being the first one entered in the loop.

I've also tried reversing the order with $i--; it still does the same thing. And tried putting it through an array beforehand, again, same problem.

Any ideas peeps? I'd rather use loops than revert to coding everything long hand.

Many thanks.
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

A database table doesn't store any ordering info per se. You order your data by adding an ORDER BY clause to your query. Of course, you need something to order by (like a timestamp maybe, or Sheet No.). That will depend on your data design
Can you post the code that displays the edit page, or at least your sql statement that selects what to display?
Avatar of BenthamLtd
BenthamLtd

ASKER

Yeah, sure. It shouldn't make a difference though as the other tables are 99% identical to each other.

<?php

// code extract from edit.php


$sheet = $_GET['sheet'];  // there is cleaning and string matching here, omitted for example

function mysql_grab_array($query, $col_name) {
	$query_sql = mysql_query($query);
    $values = array(1);
    for ($i=0; $i<mysql_num_rows($query_sql); ++$i)
        array_push($values, mysql_result($query_sql,$i,$col_name));
    return $values;
}

// GOODS TO BE RETURNED
$goodstobereturned_query = "SELECT GoodsToBeReturned AS GoodsToBeReturned, GoodsToBeReturned_qty AS GoodsToBeReturned_qty FROM goods_to_be_returned WHERE Sheet_No = '{$sheet}'";

$goodstobereturned_row = mysql_grab_array($goodstobereturned_query, 'GoodsToBeReturned');
$goodstobereturned_qty_row = mysql_grab_array($goodstobereturned_query, 'GoodsToBeReturned_qty');

// GOODS TO BE SHIPPED
$goodstobeshipped_query = "SELECT GoodsToBeShipped AS GoodsToBeShipped, GoodsToBeShipped_qty AS GoodsToBeShipped_qty FROM goods_to_be_shipped WHERE Sheet_No = '{$sheet}'";

$goodstobeshipped_row = mysql_grab_array($goodstobeshipped_query, 'GoodsToBeShipped');
$goodstobeshipped_qty_row = mysql_grab_array($goodstobeshipped_query, 'GoodsToBeShipped_qty');

// GOODS TO BE SENT ON RECEIPT
$goodstobesentonreceipt_query = "SELECT GoodsToBeSentOnReceipt AS GoodsToBeSentOnReceipt, GoodsToBeSentOnReceipt_qty FROM goods_to_be_sent_on_receipt WHERE Sheet_No = '{$sheet}'";

$goodstobesentonreceipt_row = mysql_grab_array($goodstobesentonreceipt_query, 'GoodsToBeSentOnReceipt');
$goodstobesentonreceipt_qty_row = mysql_grab_array($goodstobesentonreceipt_query, 'GoodsToBeSentOnReceipt_qty');

?>

Open in new window


And then the rows are simply echoed back into <form> inputs thus:

<input name="fGoodsRet1" size="10" value="<?php echo $goodstobereturned_row[1]; ?>" />

etc etc etc
To order your results you need to add an ORDER BY clause to your SELECT statements.
$goodstobereturned_query = "SELECT GoodsToBeReturned AS GoodsToBeReturned, GoodsToBeReturned_qty AS GoodsToBeReturned_qty FROM goods_to_be_returned WHERE Sheet_No = '{$sheet}' ORDER BY Sheet_No";

Open in new window

Of course, this will only make sense if your Sheet_No is stored in a particular order. Looking at your original post, it looks like it's not a unique value, so you may need to order by more than one column:

$goodstobereturned_query = "SELECT GoodsToBeReturned AS GoodsToBeReturned, GoodsToBeReturned_qty AS GoodsToBeReturned_qty FROM goods_to_be_returned WHERE Sheet_No = '{$sheet}' ORDER BY Sheet_No, ID";

Open in new window

I agree with ChrisStanyon which is why I wanted to see the code that was retrieving the data. Do you have primary keys for the tables, possibly an auto-incrementing id field? You could sort by that to see the data in the order it was entered.
ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial