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,

> If I could insert that data in reverse order, it would solve my
> problem.
You could use the journal table has a temporary table, and work your
way around with that.

> Also, since each entry has a date, if I could sort the data by date
> when inserting it would solve my problem.
Read what I said above.
With this temporary table you can select each item by date, and insert
them into a new table in the order you like.

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

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 .

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.

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!

DiFFeReNT wrote:
> 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!

That's why reading the manual is such a good idea. How do you think I
learned?

On Sep 29, 3:49 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> DiFFeReNT wrote:
> > 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!
>
> That's why reading the manual is such a good idea. How do you think I
> learned?

And I always do - but this is one of those cases where you can't get
any results by searching Google and the manual.
You try it - I spent hours doing that.
I only found it because you told me exactly what to search for.
And in this case, I'm too short on time to dedicate weeks of reading
through the manual - I'm doing this in my extra time (volunteering)
for a good cause.


Anyways, thanks for your .
It worked great.

Here's the process I did for those who find this thread:

**** MOVE TO TEMP from current DB ****

INSERT INTO temp (journal_id, journal_author, journal_author_email,
journal_date, journal_entry)
SELECT * FROM journal


**** MOVE TO JOURNAL_NEW from TEMP ****

INSERT INTO journal_new (journal_id, journal_author,
journal_author_email, journal_date, journal_entry)
SELECT *
FROM temp
WHERE journal_id IS NULL
ORDER BY journal_id DESC