Professional Web Applications Themes

php/mysql query insert values into enters the records in reverse order - MySQL

I'm loading tab delimited lines from a txt file using php and running a query for each line to enter the data into a mysql database. However the data appears in reverse order from the bottom line in the file upwards to the top instead of from the top downwards to the bottom. Which I would like Any help appreciated to accomplish this thanks code below. $fp = fopen('textfile.txt', 'r'); while(!feof($fp)) { $lines = fgets($fp); $line = explode("\t", $lines); //variables created for the database query here (code snipped to save space) //query here $sql = "INSERT INTO table_name (field_one, filed_two, ...

  1. #1

    Default php/mysql query insert values into enters the records in reverse order

    I'm loading tab delimited lines from a txt file using php and running
    a query for each line to enter the data into a mysql database.

    However the data appears in reverse order from the bottom line in the
    file upwards to the top instead of from the top downwards to the
    bottom. Which I would like

    Any help appreciated to accomplish this thanks code below.

    $fp = fopen('textfile.txt', 'r');

    while(!feof($fp))
    {
    $lines = fgets($fp);

    $line = explode("\t", $lines);

    //variables created for the database query here
    (code snipped to save space)

    //query here

    $sql = "INSERT INTO table_name (field_one, filed_two, field_three,
    filed_four, field_five, field_six)
    VALUES ('$field_
    one','$filed_two','$field_three','$field_four','$f ield_five','$field_six')";

    $result = mysql_query($sql);

    }


    Thank you
    Techie Guy Guest

  2. #2

    Default Re: php/mysql query insert values into enters the records in reverse order

    In article <g6tcm19burfg2v83m790krft6plot5bbj34ax.com>,
    Techie Guy <outthepub.com> wrote:
    >I'm loading tab delimited lines from a txt file using php and running
    >a query for each line to enter the data into a mysql database.
    >
    >However the data appears in reverse order from the bottom line in the
    >file upwards to the top instead of from the top downwards to the
    >bottom. Which I would like
    How are you determining the order the data appears in?

    What actions have you taken in your database design to ensure that the
    records come out in the order you think they should?

    In general, it's best to think of a database as containing a pile of
    records in no particular order, and any ordering you wish to bring to
    those records needs to be presented to the databse as part of the query.
    --
    Steve Watt KD6GGD PP-ASEL-IA ICBM: 121W 56' 57.8" / 37N 20' 14.9"
    Internet: steve Watt.COM Whois: SW32
    Free time? There's no such thing. It just comes in varying prices...
    Steve Watt Guest

  3. #3

    Default Re: php/mysql query insert values into enters the records in reverse order

    On Mon, 31 Oct 2005 20:26:22 +0000 (UTC), [email]steveWatt.COM[/email] (Steve Watt)
    wrote:
    >In article <g6tcm19burfg2v83m790krft6plot5bbj34ax.com>,
    >Techie Guy <outthepub.com> wrote:
    >>I'm loading tab delimited lines from a txt file using php and running
    >>a query for each line to enter the data into a mysql database.
    >>
    >>However the data appears in reverse order from the bottom line in the
    >>file upwards to the top instead of from the top downwards to the
    >>bottom. Which I would like
    >
    >How are you determining the order the data appears in?

    You are spot on I did a few tests and it appears that the order was
    by date and when sorting by ID DESC they appear correctly. Thank you
    for helping it's appreciated.


    >What actions have you taken in your database design to ensure that the
    >records come out in the order you think they should?
    >
    >In general, it's best to think of a database as containing a pile of
    >records in no particular order, and any ordering you wish to bring to
    >those records needs to be presented to the databse as part of the query.
    Techie Guy Guest

  4. #4

    Default Re: php/mysql query insert values into enters the records in reverse order

    On Mon, 31 Oct 2005 20:26:22 +0000 (UTC), [email]steveWatt.COM[/email] (Steve Watt)
    wrote:
    >In article <g6tcm19burfg2v83m790krft6plot5bbj34ax.com>,
    >Techie Guy <outthepub.com> wrote:
    >>I'm loading tab delimited lines from a txt file using php and running
    >>a query for each line to enter the data into a mysql database.
    >>
    >>However the data appears in reverse order from the bottom line in the
    >>file upwards to the top instead of from the top downwards to the
    >>bottom. Which I would like
    >
    >How are you determining the order the data appears in?
    The problem is not that the data is being entered in reverse order as
    I had originally thought.

    The issue I have is that the top line in the file is last line written
    to the file and the bottom line is first line written to the file when
    it is created.

    When the script reads the file it processes the last line written to
    the file first (the top) and enters that into the database first. Then
    it reads the second line and enters that into the database which means
    the data is not in the same order as the file.

    Any idea how to process the file from bottom line upwards so that the
    database contents end up in the same order as the file.

    The txt file contains a max of 30 - 40 lines but they are written in
    order of time received.

    Hope that makes sense
    >
    >What actions have you taken in your database design to ensure that the
    >records come out in the order you think they should?
    >
    >In general, it's best to think of a database as containing a pile of
    >records in no particular order, and any ordering you wish to bring to
    >those records needs to be presented to the databse as part of the query.
    Techie Guy Guest

  5. #5

    Default Re: php/mysql query insert values into enters the records in reverse order

    On Tue, 01 Nov 2005 07:20:08 -0500, Gary L. Burnore
    <gburnoredatabasix.com> wrote:
    >On Tue, 01 Nov 2005 10:13:46 +0000, Techie Guy <outthepub.com> wrote:
    >
    >>On Mon, 31 Oct 2005 20:26:22 +0000 (UTC), [email]steveWatt.COM[/email] (Steve Watt)
    >>wrote:
    >>
    >>>In article <g6tcm19burfg2v83m790krft6plot5bbj34ax.com>,
    >>>Techie Guy <outthepub.com> wrote:
    >>>>I'm loading tab delimited lines from a txt file using php and running
    >>>>a query for each line to enter the data into a mysql database.
    >>>>
    >>>>However the data appears in reverse order from the bottom line in the
    >>>>file upwards to the top instead of from the top downwards to the
    >>>>bottom. Which I would like
    >>>
    >>>How are you determining the order the data appears in?
    >>
    >>The problem is not that the data is being entered in reverse order as
    >>I had originally thought.
    >>
    >>The issue I have is that the top line in the file is last line written
    >>to the file and the bottom line is first line written to the file when
    >>it is created.
    >>
    >>When the script reads the file it processes the last line written to
    >>the file first (the top) and enters that into the database first. Then
    >>it reads the second line and enters that into the database which means
    >>the data is not in the same order as the file.
    >>
    >>Any idea how to process the file from bottom line upwards so that the
    >>database contents end up in the same order as the file.
    >>
    >>The txt file contains a max of 30 - 40 lines but they are written in
    >>order of time received.
    >>
    >>Hope that makes sense
    >>
    >
    >It does. But you apparently didn't read what Steve wrote. It
    >shouldn't matter what order they're inserted.
    Yes I read the post and clearly understand that it doesn't matter
    generally, what order data is entered into most databases.

    However in this case there are over 5000 records already in the
    correct order in the database and it would be nice to keep them that
    way. Certain fields also correspond numerically to the ID field and I
    was aiming for neatness. The ref# field for example is alphanumeric
    IN000000 and the numeric part happens to be the same # as the ID
    Entering the txt file in the correct order keeps these fields in line
    with the ID #.

    Thank you for your assistance
    >
    ># Variable Declaration
    >$i = 0;
    >
    ># Read file into temporary array
    >$temp = file($template_file);
    >
    ># Construct template output
    >for ($i=0; $i<count($temp); $i++) {
    >
    > // The current row is now $temp[$i]
    >
    >}
    >
    >
    >Of course, that's only one of many ways to do it.
    >
    >
    >>>What actions have you taken in your database design to ensure that the
    >>>records come out in the order you think they should?
    >>>
    >>>In general, it's best to think of a database as containing a pile of
    >>>records in no particular order, and any ordering you wish to bring to
    >>>those records needs to be presented to the databse as part of the query.
    Techie Guy Guest

  6. #6

    Default Re: php/mysql query insert values into enters the records in reverseorder

    Techie Guy wrote:
    >
    > Yes I read the post and clearly understand that it doesn't matter
    > generally, what order data is entered into most databases.
    >
    > However in this case there are over 5000 records already in the
    > correct order in the database and it would be nice to keep them that
    May be you *read* the posting, but then you refuse to *understand*.
    THERE IS NO ORDER in a MySQL database.
    > way.
    Since there is no order, you can't keep it.
    Christian Kirsch Guest

  7. #7

    Default Re: php/mysql query insert values into enters the records in reverse order

    >The issue I have is that the top line in the file is last line written
    >to the file and the bottom line is first line written to the file when
    >it is created.
    >
    >When the script reads the file it processes the last line written to
    >the file first (the top) and enters that into the database first. Then
    >it reads the second line and enters that into the database which means
    >the data is not in the same order as the file.
    >
    >Any idea how to process the file from bottom line upwards so that the
    >database contents end up in the same order as the file.
    The database contents *DOES NOT HAVE AN ORDER*, at least not one
    visible via SQL.

    If you don't specify ORDER BY on a query, you may get the rows
    in any order. And there's no guarantee that it will be consistent
    from one query to the next.
    >The txt file contains a max of 30 - 40 lines but they are written in
    >order of time received.
    In that case, it looks like ORDER BY time_received on your query
    would be a good idea.

    Gordon L. Burditt
    Gordon Burditt Guest

  8. #8

    Default Re: php/mysql query insert values into enters the records in reverse order

    On Tue, 01 Nov 2005 18:09:27 +0100, Christian Kirsch <ckbru6.de>
    wrote:
    >Techie Guy wrote:
    >
    >>
    >> Yes I read the post and clearly understand that it doesn't matter
    >> generally, what order data is entered into most databases.
    >>
    >> However in this case there are over 5000 records already in the
    >> correct order in the database and it would be nice to keep them that
    >
    >May be you *read* the posting, but then you refuse to *understand*.
    >THERE IS NO ORDER in a MySQL database.
    >
    >> way.
    >
    >Since there is no order, you can't keep it.
    Oh but yes you can

    Existing database contents over 5000 records order by ID DESC

    ID, REF, field3, field4, field5
    5067, IN005067, data3, data4, data5
    5066, IN005066, data3, data4, data5
    5065, IN005065, data3, data4, data5
    5064, IN005064, data3, data4, data5
    n
    1, IN000001, data3, data4, data5


    Txt file contents with no ID field
    IN005071, data3, data4, data5
    IN005070, data3, data4, data5
    IN005069, data3, data4, data5
    IN005068, data3, data4, data5


    Database after contents of txt file written by script
    ID and REF no longer correspond so when you query ID 5071 REF IN005068
    is returned as the record

    ID, REF, field3, field4, field5
    5071, IN005068, data3, data4, data5
    5070, IN005069, data3, data4, data5
    5069, IN005070, data3, data4, data5
    5068, IN005071, data3, data4, data5
    5067, IN005067, data3, data4, data5
    5066, IN005066, data3, data4, data5
    5065, IN005065, data3, data4, data5
    5064, IN005064, data3, data4, data5
    n
    1, IN000001, data3, data4, data5

    However if data was entered in reverse order from the txt file.
    the data would look like this
    ID, REF, field3, field4, field5
    5071, IN005071, data3, data4, data5
    5070, IN005070, data3, data4, data5
    5069, IN005069, data3, data4, data5
    5068, IN005068, data3, data4, data5
    5067, IN005067, data3, data4, data5
    5066, IN005066, data3, data4, data5
    5065, IN005065, data3, data4, data5
    5064, IN005064, data3, data4, data5
    n
    1, IN000001, data3, data4, data5

    If you now query ID 5071 it returns REF IN005071

    Therefore the order the data is written affect results of previously
    written scripts



    Techie Guy Guest

  9. #9

    Default Re: php/mysql query insert values into enters the records in reverseorder

    Techie Guy wrote:
    > On Tue, 01 Nov 2005 18:09:27 +0100, Christian Kirsch <ckbru6.de>
    > wrote:
    >
    >
    >>Techie Guy wrote:
    >>
    >>
    >>>Yes I read the post and clearly understand that it doesn't matter
    >>>generally, what order data is entered into most databases.
    >>>
    >>>However in this case there are over 5000 records already in the
    >>>correct order in the database and it would be nice to keep them that
    >>
    >>May be you *read* the posting, but then you refuse to *understand*.
    >>THERE IS NO ORDER in a MySQL database.
    >>
    >>
    >>>way.
    >>
    >>Since there is no order, you can't keep it.
    >
    >
    > Oh but yes you can
    >
    > Existing database contents over 5000 records order by ID DESC
    >
    Are you really so dense? This is NOT something inherent in the database.
    It's only the OUTPUT that's ordered. Hell.
    > Therefore the order the data is written affect results of previously
    > written scripts
    Nonsense.
    Christian Kirsch Guest

  10. #10

    Default Re: php/mysql query insert values into enters the records in reverse order

    In message <h03fm1t7lfo1v7c2gi320039r6ltigo4su4ax.com>, Techie Guy
    <outthepub.com> writes
    >>It does. But you apparently didn't read what Steve wrote. It
    >>shouldn't matter what order they're inserted.
    >
    >Yes I read the post and clearly understand that it doesn't matter
    >generally, what order data is entered into most databases.
    >
    >However in this case there are over 5000 records already in the correct
    >order in the database and it would be nice to keep them that way.
    >Certain fields also correspond numerically to the ID field and I was
    >aiming for neatness. The ref# field for example is alphanumeric
    >IN000000 and the numeric part happens to be the same # as the ID
    >Entering the txt file in the correct order keeps these fields in line
    >with the ID #.
    In general it's a bad idea to build a system that relies on a feature
    that you don't have direct control of. You are unlikely to have complete
    control of the sequence of records in a MySQL database because in a
    compliant relational database the sequence is undefined. You would have
    no cause for complaint if the next version of MySQL started returning
    records in some other order. If you want records in a specific order you
    should always specify it.

    So my advice is to accept the current behaviour which will break the
    apparent link between the ref# and the ID. Breaking that linkage is IMHO
    a good thing. It's probably going to break at some point in the future,
    best to get it over with. When I had to do something similar I carefully
    scrambled the sequence of ID numbers so that it was impossible to guess
    the sequence. Having some apparent relationship between the numbers
    encourages people to assume that this is always going to be so, and that
    isn't guaranteed. That assumption will bite you if you allow it to
    continue.

    However if you aren't convinced by this then you should be able to sort
    your text file in reverse order before loading it. Even Windows has a
    sort command.



    --
    Bernard Peek
    London, UK. DBA, Manager, Trainer & Author.

    Bernard Peek Guest

  11. #11

    Default Re: php/mysql query insert values into enters the records in reverse order

    On Wed, 02 Nov 2005 00:13:17 +0100, Christian Kirsch <ckbru6.de>
    wrote:
    >Techie Guy wrote:
    >> On Tue, 01 Nov 2005 18:09:27 +0100, Christian Kirsch <ckbru6.de>
    >> wrote:
    >>
    >>
    >>>Techie Guy wrote:
    >>>
    >>>
    >>>>Yes I read the post and clearly understand that it doesn't matter
    >>>>generally, what order data is entered into most databases.
    >>>>
    >>>>However in this case there are over 5000 records already in the
    >>>>correct order in the database and it would be nice to keep them that
    >>>
    >>>May be you *read* the posting, but then you refuse to *understand*.
    >>>THERE IS NO ORDER in a MySQL database.
    >>>
    >>>
    >>>>way.
    >>>
    >>>Since there is no order, you can't keep it.
    >>
    >>
    >> Oh but yes you can
    >>
    >> Existing database contents over 5000 records order by ID DESC
    >>
    >
    >Are you really so dense? This is NOT something inherent in the database.
    >It's only the OUTPUT that's ordered. Hell.
    It's just a shame you guys can't read between the lines of course it's
    the output.

    I assumed you guys were clever enough to work that out but apparently
    NOT.
    >> Therefore the order the data is written affect results of previously
    >> written scripts
    >
    >Nonsense.
    Techie Guy Guest

Similar Threads

  1. MYSQL query using GROUP BY and ORDER BY?
    By Bonge Boo! in forum PHP Development
    Replies: 3
    Last Post: August 15th, 06:12 PM
  2. insert mysql query into a history table... ?
    By Gawie Marais in forum PHP Development
    Replies: 2
    Last Post: June 23rd, 10:53 AM
  3. Reverse Order?
    By Mark Weisman in forum PERL Beginners
    Replies: 2
    Last Post: September 4th, 08:18 AM
  4. Sum a column of values from a MySQL query
    By Ben C. in forum PHP Development
    Replies: 3
    Last Post: August 5th, 03:37 PM
  5. [PHP] Sum a column of values from a MySQL query
    By Ben C. in forum PHP Development
    Replies: 8
    Last Post: August 5th, 01:50 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139