On Sep 29, 2:28 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> DiFFeReNT wrote:
> > 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 .
>
> Err, I said "an INSERT ... SELECT statement"
>
> I did not say "use a SELECT statement and an INSERT statement!
>
> You will find it in the manual.

Oh. That's why I was confused; I've never heard of that before.
I'll give it a try.

For future Google visitors:
http://dev.mysql.com/doc/refman/5.0/...rt-select.html

Thanks!