0

I do a transaction:

$dbh->beginTransaction();

$i = 0;
while ($i < $total_items_num) {
  $insert_data->execute(
    array( $item_data[ $i ],
           $category,
           $price,
    )
  );

  $i++;
}

$dbh->commit();

On my local machine, items get inserted in proper order, e.g. from 1st to 7th. But on production server it gets inserted in reverse order, e.g. from 7th to 1st.

What could be the possible reason for this, some setting I need to change?

EDIT: Here is the query:

    $insert_data = $dbh->prepare ("
    INSERT INTO goods (
    item_id,
    item_data,
    category,
    price
    )
    VALUES (NULL, ?, ?, ?);
    ");

and the order of items was supposed to be from 0 to whatever number. E.g. 0-6, 0-15...

So what I'd want (and what works for me locally) is that item_id (which is primary key) gets applied to items in that very order, e.g. if 1st item had item_id 1025, then the second one should have 1026.

But what happens is they get inserted in reverse order.

Thanks for suggestions and sorry if question was vague, hopefully better now.

CodeVirtuoso
  • 6,318
  • 12
  • 46
  • 62
  • You didn't say what is order and how it is defined in your case. And haven't shown **any** query in the question. Literally - I don't see **anything** related to mysql at all – zerkms Jan 19 '12 at 07:49
  • 1
    I don't know why this is but ultimately it doesn't matter. Row order in MySQL (as in most RDBMSes) is not guaranteed. If you want to retrieve the data in a certain order you should use an `ORDER BY` clause in your query using some column you have defined (e.g. `created_at` or `position`). More information in this question: http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order – Jordan Running Jan 19 '12 at 07:52
  • 1
    Mind that the rows in database are not ordered if you don't `ORDER BY` in query. – jkj Jan 19 '12 at 07:52
  • @Jordan: Thanks. I understand I need to ORDER BY when fetching data, but in the above loop I try to insert items 0 through 6 (for example) and I'm hoping that primary key will be given to them in that same, ascending order - is that something I can count on? – CodeVirtuoso Jan 19 '12 at 08:11
  • Can you clarify your issue? `item_id` is an auto_increment column, but when you loop through items, you expect to see `item_data[1]` in the database with a matching `item_id` of 1, etc? – Mike Purcell Jan 19 '12 at 08:15
  • @MikePurcell: Not exactly match it, I just expect that item_id of item_data[4] is higher than item_id of item_data[2]. Right now it's exactly the opposite. – CodeVirtuoso Jan 19 '12 at 08:18
  • @Freelancer: You accepted an answer which mentioned the possible disparity between localhost and production environments, but no mention was made of what the actual problem was. Were you missing an index? – Mike Purcell Jan 19 '12 at 17:34

2 Answers2

1

I'd suggest a couple of things to check out:

  1. Do your dev and production environment have the same indexes on the table that's concerned?

  2. If not, check if your verification query in both cases is exactly the same.

If your answer is yes in both cases, then the rule is the one the other comments point out: RDBMS' table records in resultsets are not necessarily "ordered" unless you make use of ORDER BY in your queries or you set up some indexing.

Federico Zancan
  • 4,846
  • 4
  • 44
  • 60
0

SQL databases do not have a concept of inherent order of rows. Order is applied only when you explicitly specify an ORDER BY clause in the query. In the absence of ORDER BY the database engine is allowed to return the rows in any order it chooses.

As to the sequence numbering, you haven't shown how the table is defined, or how $item_data is filled, so that question is unanswerable for now.

Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
  • Thanks. But how would I go about explicitly specifying an order in my above query? Basically I'm executing a prepared statement over and over in while loop, so primary key should auto-increment. But instead, it first inserts into database the last item I specified in loop and goes backwards. This makes me think it has something to do with transaction, but any clue is greatly appreciated. – CodeVirtuoso Jan 19 '12 at 08:24
  • There may be a difference in the production environment in the order in which the data is loaded into `$item_data`. Can you show how that happens? – Jim Garrison Jan 19 '12 at 08:28
  • Though of that, and tested, it loads in same order in both environments (data is read from csv file). – CodeVirtuoso Jan 19 '12 at 08:37
  • @Freelancer: perhaps it could be related to the transaction, but in this case I suggest not to think about how the transaction is internally performed; it is atomic, so for our degree of visibility in this context the order in which records get into the table within the same transaction could peacefully be irrelevant. – Federico Zancan Jan 19 '12 at 08:37