mysql export with a sort/filter feature

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

mysql export with a sort/filter feature

Post by robster »

Hi all,

I know these are php forums but there are generally some mysql gurus in here too... so I thought i'd ask :)

I have a large table in my database called 'archives'.
This table has monthly archives of data from my site.
I have archives from Jan 2001 all the way to now.
I also have a way to display this data in a month by month way, using PHP.
The monthly display has a meny on the left which shows the month names, these are displayed by parsing the database and when it finds a new month (ie: when 1 turns to 2, or Jan to Feb) it adds that month to the menu. I do this as there are some months where there is no data and I don't want to have say, March, in my menu when march doesn't exist.

So that's how this part of my site works. I hope you've followed me so far :)


The problem is, when I entered this data into the database, it wasn't done in a linear fashion from the first month to the last. An example is Jan 2002 was added WAY later than April 2002.

What this does, is when the menu is created, it adds the months in the order they are in the database. That leaves things like Jan, Feb, March, June, Sep, Oct, Nov, Dec, April, May, July.

The months aren't in order.


The way I envision fixing this, is exporting each month with the phpmyadmin application and then importing them in correct order.
I don't seem to find this functionality in phpmyadmin. I'm wondering if somebody here has some way of doing this?

ie: doing an export from a table in my database where a rule exists (ie: export only entries in 2002 with month 1).



You can't imagine how helpful this would be to me. I have WAY to much data to manually edit each entry.


Thanks so much, I really look forward to seeing if anyone can imagine a way to get mysql or php/mysql to do this.


Rob
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post by m3rajk »

well... i hope you're using date or something else that has both the month and year.

if so, what does it matter? just sort by that feild ASC

ie: SELECT * FROM archives ORDER BY month_year
or you can explicitly calll it in ascending order: SELECT * FROM archives ORDER BY month_year ASC
Last edited by m3rajk on Sun Sep 07, 2003 2:42 pm, edited 1 time in total.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

not that I seen

Post by phpScott »

phpMyAdmin doen't do this as far as I have seen.

But of course you could alway write your own script to export the data.
Just do a dump of the table that you want and then use the example as a way to create your own file using php.

It probably won't be as effiecient as a propber dump but you could create a whole series of files organized by month then empty the table and start reinserting the data from the files all in one big click of a button.

phpScott
Post Reply