Programmer's Corner Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Programmer's Corner - Forums


control order of insertion

 
Post new topic   Reply to topic    Programmer's Corner Forum Index -> MySQL
Author Message
Hrqls
De Blonde Puffin


Joined: 22 Oct 2004
Posts: 558
Location: Alkmaar, The Netherlands
PostPosted: Wed May 07, 2008 5:29 am    Post subject: control order of insertion Reply with quote
i am using some sql files to insert some data into a mysql database
i am using the following command no the command line (linux shell)
Code:

mysql database_name < myfile.sql


this works fine except that sometimes when i insert more than 1 file sequentially the data of a later file is inserted into the tables before the data of an earlier file

for example 2 sql files :
Code:

LOCK TABLES MY_TABLE_NAME WRITE;
INSERT INTO MY_TABLE_NAME (11,'abc');
UNLOCK TABLES;

Code:

LOCK TABLES MY_TABLE_NAME WRITE;
INSERT INTO MY_TABLE_NAME (12,'def');
UNLOCK TABLES;

wen i first load the file with 'abc' and then the file with 'def' then my tables shows the line with 'def' above the line with 'abc' when i view them in mysql with
Code:

select * from MY_TABLE_NAME;


is there a way to make sure that the file which i insert first is first in the table as well ?

(i am using a database and tables which was created by a former colleague so i am not sure if i can change the tables properties at will)
Back to top
Hrqls
De Blonde Puffin


Joined: 22 Oct 2004
Posts: 558
Location: Alkmaar, The Netherlands
PostPosted: Wed May 07, 2008 7:47 am    Post subject: Reply with quote
i have the idea that its caused because i submit a lot of data into a large database quite fast

maybe it doesnt have time to finish the sorting process

is there a way to force a sync or update or something like that on a table to force the sorting to finish before anything else will be done ?
Back to top
Ankou
Spam Mod


Joined: 22 Oct 2004
Posts: 1201
Location: Wisconsin
PostPosted: Wed May 07, 2008 7:00 pm    Post subject: Reply with quote
I'm not aware of a way to make that happen. I guess my question is, does it really matter? I mean if the order of display matters when you view the data just use the 'order by'

Code:
SELECT fieldOne, fieldTwo from MY_TABLE_NAME ORDER BY fieldTwo;


(Where fieldOne and fieldTwo are whatever you table fields are)
Back to top
Hrqls
De Blonde Puffin


Joined: 22 Oct 2004
Posts: 558
Location: Alkmaar, The Netherlands
PostPosted: Fri May 09, 2008 10:32 am    Post subject: Reply with quote
true .. thats what i did in some listsbox on the php pages .. i added the 'order'
there is a table though in which i dont know how to apply the 'order' to that data

its a table of 7 rows (each day), and 11 columns, which i want to be 'order'ed by the 1 column

i submitted the same sql files really slow and 1 part of them is in order in the database itself now .. but not the part for the php table Sad

Smile

is there a way via an msql command by which i read the whole table and 'order' it and write it to a new table ?
Back to top
Ankou
Spam Mod


Joined: 22 Oct 2004
Posts: 1201
Location: Wisconsin
PostPosted: Tue May 13, 2008 7:58 pm    Post subject: Reply with quote
You can INSERT data with SELECT... actually I'll just post a link because it's just easier than me typing everything out.

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
Back to top
Hrqls
De Blonde Puffin


Joined: 22 Oct 2004
Posts: 558
Location: Alkmaar, The Netherlands
PostPosted: Wed May 14, 2008 8:43 am    Post subject: Reply with quote
ah! i didnt know that .. thats nice Smile
i can create a new table from the current table using this select and probably also 'order by' to create the new table in the correct order

i might then drop the original table and rename the new table to the name of the original table

that way the original table would be sorted wouldnt it ?

is there a way to order by 2 fields ? for example i want to sort by date and by time (which are 2 different fields in the table) ... i want first sort everything by date .. and entries with the same date should be sorted by time
Back to top
Ankou
Spam Mod


Joined: 22 Oct 2004
Posts: 1201
Location: Wisconsin
PostPosted: Mon May 19, 2008 9:50 pm    Post subject: Reply with quote
Hrqls wrote:
that way the original table would be sorted wouldnt it ?


Yeah I think that should get you what you want.


Hrqls wrote:
is there a way to order by 2 fields ?


When you use ORDER BY just list how you want it and separate the fields with a comma...

SELECT * FROM tableName ORDER BY dateField ASC, timeField DESC

In reality you'll probably want both your dateField and timeField order by ascending order. The example above is just to show you that you can order the fields in different ways (ASC - ascending, DESC - descending). If both are going to be in ascending order then you can just put

SELECT * FROM tableName ORDER BY dateField, timeField
Back to top
Hrqls
De Blonde Puffin


Joined: 22 Oct 2004
Posts: 558
Location: Alkmaar, The Netherlands
PostPosted: Fri May 23, 2008 6:33 am    Post subject: Reply with quote
thanks!!!

now i have to find some time again to work on that project and test it all Smile
(i have stored your posts to be sure Smile)
Back to top
Display posts from previous:   
Post new topic   Reply to topic    Programmer's Corner Forum Index -> MySQL All times are GMT - 5 Hours
Page 1 of 1

 


Powered by phpBB © 2001, 2002 phpBB Group