On Sep 29, 12:18 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> DiFFeReNT wrote:
> > The simple question is:
> > - How can INSERT data in an SQL query into MySQL in reverse order?
> > (not SORT BY, not ORDER BY -- when INSERTing, not when SELECTing)
> > or
> > - How can I INSERT data in an SQL query into MySQL with the ability to
> > INSERT based on SORT/ORDER BY?
>
> > In more detail:
>
> > I have been using a flat database (text file) to store entries from a
> > PHP journal. The PHP script was configured to add new entries to the
> > top of the text file. Each entry contained HTML code (sloppy).
>
> > In order to convert those entries to MySQL, I did some complicated
> > "Find and Replace" [with regular expressions] passes with the text
> > document so it looks like this: (with MySQL query added to top)
> > ----
> > INSERT INTO journal (journal_author, journal_date, journal_entry,
> > journal_author_email)
> > VALUES
> > ('author', 'date', 'entry', 'email'),
> > ('author', 'date', 'entry', 'email'),
> > ----
> > (The text document is currently 1,400 lines, 504 entries)
>
> > I have successfully inserted the entries into MySQL.
> > Unfortunately, because the PHP script that saved new entries at the
> > top of the document, the newest entry's id=1. It should be the other
> > way around, where the very first entry has an id of 1.
>
> > If I could insert that data in reverse order, it would solve my
> > problem.
> > Also, since each entry has a date, if I could sort the data by date
> > when inserting it would solve my problem.
>
> > Thanks in advance,
>
> Rows in this sort of database do not have an order when they are in the
> table. You use an ORDER BY clause, possibly in conjunction with an index to
> extract the records in whatever order you want at the time. If you will want
> to extract the records in date order when extracting them, just use ORDER BY
> date when extracting them.
>
> If you really want to have your autoincrement ID field work the way you are
> suggesting, just do a 2 stage insert.
>
> First insert to a temporary table, then use an INSERT ... SELECT statement
> to re-insert them into the final table using an ORDER BY id DESC clause on
> the SELECT

I'm having some trouble transferring it over to a temporary table...
The 'temp' table is setup exactly identical to the 'journal' table,
except auto_increment is not setup for journal_id.

I'm using the following query:
----
SELECT * FROM `journal`;
INSERT INTO `temp` (journal_id, journal_author, journal_author_email,
journal_date, journal_entry) VALUES ('journal_id', 'journal_author',
'journal_author_email', 'journal_date', 'journal_entry');
----

It says query successful, but then says:
----
SELECT *
FROM `journal` ;# Rows: 505
INSERT INTO `temp` ( journal_id, journal_author, journal_author_email,
journal_date, journal_entry )
VALUES (
'journal_id', 'journal_author', 'journal_author_email',
'journal_date', 'journal_entry'
);# Affected rows: 1
----

# Affected rows: 1
that one row looks like this:
0 journal_author journal_author_email journal_date
journal_entry

Based on the output, it looks like something stupid I should know....
I did try setting up the temp database with different field names with
no avail, but I could have done something different there too...

Thanks for your .