?
Solved

MySQL Insert - Repeating rows not matching position

Posted on 2012-08-20
6
Medium Priority
?
360 Views
Last Modified: 2012-11-01
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.
0
Comment
Question by:BenthamLtd
6 Comments
 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 38312187
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
0
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 38312266
Can you post the code that displays the edit page, or at least your sql statement that selects what to display?
0
 

Author Comment

by:BenthamLtd
ID: 38312304
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
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 45

Expert Comment

by:Chris Stanyon
ID: 38312360
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

0
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 38312438
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.
0
 
LVL 60

Accepted Solution

by:
Julian Hansen earned 1500 total points
ID: 38313021
You don't have a unique field defined in the database so sql is going to pull them out based on the character set and the data that is in the fields - irrespective of the order you put them in

run this against your table
alter table ` goods_to_be_returned table` 
   add column `id` int NOT NULL AUTO_INCREMENT first,
   add primary key(`id`)

Open in new window


You don't need to change the way you are inserting the records and you won't need an order by clause either.

Bear in mind although you can add this to your existing table the result will be for the data to be ordered as it is currently. So you have two options

1. Add the id field but don't set it to auto number and manually type in the integer values for each record in the order you want them to come out. When complete enable autonumber on that column

2. Truncate the table - add the column with the autonumber and recapture the data.

NB: Line 3 is optional - if you have a primary key already which - if the data you posted is complete - you don't seem to have.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 1 hour left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question