0o0o0 Posted October 10, 2008 Share Posted October 10, 2008 I need to take 12/07/2008 in the date column... importing to table and make it 12072008 trying to figure the substring useage for this in a ... SELECT DISTINCT MIN( substring (stripslashes (date) ) ) as date that was an attempt lol not much of a good one. ive tried several all night.. its now 7am I can figure it out. The select distinct min(date) as date .... wont work cause the dates not a stamp on the table but a 12/07/2008. what im doing is going from one table to a temp table.. and trying to figure out how to strip the /'s off the dates in the date column on the fly.. before it goes into the temp table. is it possible thanks? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 11, 2008 Share Posted October 11, 2008 You should be converting your dates to proper SQL-99 format: YYYY-MM-DD. STR_TO_DATE() can help you. Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted October 16, 2008 Author Share Posted October 16, 2008 I am using that.. but also with distinct.. and MIN or MAX.. not sure if I completely understand DISTINCT or not.. heres an example.. I want or group by manufacturer... and have the most current date shown but distinct by dates.. which the dates come in from a csv file as text.. then converted how I show it below.. it does convert and the column is entrydate, which is set in sql to DATE $sql = "INSERT INTO TMP SELECT DISTINCT Bike_manufacture, MAX(STR_TO_DATE(entrydate, '%m/%d/%Y')) as entrydate, blah, blah_blah, bleh_bleh_bleh from STREETB group by (Bike_manufacture)"; mysql_query($sql); it converts the date coming in as 08/08/2008 to 2008-08-08 fine.. But the MIN or MAX is only grabbing the lower of the two Bike_manufacture's that are the same cept for the changes in the newer entry... like yamaha 2008-08-08 and yamaha 2008-10-10 it grabs 2008-08-08 everytime.. is it cause sql is not converting the date properly into the Table so sql is grabbing the older one all the time? MAX(STR_TO_DATE(entrydate, '%m/%d/%Y')) cant the above be used on the fly when transfering into the TMP table? to anyone who can help.. its now a 3 week trial and error tedious process. thanks to anyone who can suggest anything to move me on from being stuck this long lol. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 16, 2008 Share Posted October 16, 2008 Well, you don't need distinct in this case, since there will only be one manufacturer/date pair in any case. But yes, maybe it's not correctly converting all of them? Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted October 16, 2008 Author Share Posted October 16, 2008 IF I made a 2nd tmp table.. so now I have.. cvs into first table... then converts dates in the first TMP table.. then the 2 TMP table have MIN MAX sort the dates.. what would I put for this to work... without the MIN MAX... cause when I just drop the min or max.. I get NULL dates. But with the MIN (or max) it actually converts the dates. ???? $sql = "INSERT INTO TMP SELECT DISTINCT Bike_manufacture, (STR_TO_DATE(entrydate, '%m/%d/%Y')) as entrydate, blah, blah_blah, bleh_bleh_bleh from STREETB group by (Bike_manufacture)"; mysql_query($sql); any ideas? drop the brackets? or is there code im missing now?? since I took the min or max out on the FIRST TEMP table. ?? thanks. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 16, 2008 Share Posted October 16, 2008 Well, a column list for the TMP table would help.... Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted October 17, 2008 Author Share Posted October 17, 2008 The thing... converts perfectly.. and does min max properly.. but when new data is imported with the already converted dates.. the system looks at the first one that does not need to be converted and stops. 2008-10-08 09/10/2008 etc etc it stops and does not convert the newer dates added in the other rows. anyone run into this problem?? how to make it ignore already converted formats.. and skip down the list to non converted and convert them? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 17, 2008 Share Posted October 17, 2008 I'm very confused.. .you're using GROUP BY (again, no point for DISTINCT) -- so what do you think it's going do? Import all the data first, then aggregate it! Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted October 17, 2008 Author Share Posted October 17, 2008 dude... its one thing to the next.. if I take either one of those off... it shows all dates NULL.. if I leavem on it works but doesnt select the current one .. if I dont use MAX or MIN.. NULL.. losin my mind. Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted October 17, 2008 Author Share Posted October 17, 2008 Start over.. have two tables .. ones full of data.. and the other empty.. I want to move all from TABLE_A to TABLE_B.. while converting a text date to real DATE.. my guess... $sql = "INSERT INTO TABLE_B SELECT col1, (STR_TO_DATE(col2, '%m/%d/%Y')) as col2, col3,col4,col5,col6 from TABLE_A "; this does not work for me.. and the date does not convert. see any errors? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 18, 2008 Share Posted October 18, 2008 Other than the missing column list for TABLE_B, not really... could you post some sample data & table structures? Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted October 18, 2008 Author Share Posted October 18, 2008 oh! I need a column list for b as well?? maybe thats it? ( is that what you meant?) Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted October 18, 2008 Author Share Posted October 18, 2008 WOW fenway .... havent figured it out yet.. but now im really understanding whats DISTINCT AND GROUP BY AND MAX means.. all I wants to do was grab the two line identical but with different dates.. and make it grab the newer full line with the newer date. I did not realize what I was doing is using something that pulls a max date.. and meshes it in with an older line.. making a whole new line.. no wonder I was so completely screwed up!!!!!!!! ok so this is what I want to do.. yamaha | todays date | red | 600cc | fast | 25 sold yamaha | yesterdays date | red | 600cc | fast | 10 sold Kawasaki | yesterdays date | red | 600cc | fast | 14 sold so it would use the new yamaha coming in from the cvs file... and would just re add kawasaki cause theirs no change. And kick out the old yamaha with the old sold qty. What it was doing and confused me was... took todays date but still posted yesterdays only 10 sold... like so. yamaha | todays date | red | 600cc | fast | 10 sold didnt realize it meshed the lines together and only took the MAX date and put it in yesterdays entry. wow .. duh! ok so.. all I want to do is... get the new listing in the db.. and kick out the out listing by date. but also keep any non changed entries regardless of date. Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted October 19, 2008 Author Share Posted October 19, 2008 Learning about indexing now.. seems like that is what I really need. find an index and delete the rows with older dates. hey im learning self teaching can be long and grueling by it should be oh about 2054 when ive learn 1/5th of php. lol Quote Link to comment Share on other sites More sharing options...
fenway Posted October 20, 2008 Share Posted October 20, 2008 I think I understand now... if you have a unique index on your destination table, then an INSERT INTO... ON DUPLICATE KEY UPDATE... will allow you "overwrite" all the entires. Of course, you'll have to use a derived table to get the most recent first. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.