php - Mysql varchar fields with number not sorting well -

-

i have little knowledge of mysql, importing csv file many numbers in db, developer changed type of fields int varchar , script sort 1 fields numeric descending no longer display number in descending order well, numbers mix up, if use phpmyadmin , click on column.

he mentioned me when changed numerical fields value varchar because data getting corrupted otherwise. succeeded in importing data display script not working anymore.

i way on head.

'avgsearches` varchar(11) collate utf8_bin default null, engine=innodb  default charset=utf8 collate=utf8_bin auto_increment=1406

here sorting order:

9900 90 880 8100 74000 720 70

select distinct keyword,avgsearches traffic status='y' order avgsearches desc

one way, make fixed length strings padded left leading zeroes. prevents use of index ordering job done. neater if converted stored function.

missing 'trim'.

select distinct keyword, avgsearches  traffic  status='y'  order right(concat('00000000000', trim(avgsearches)), 11)  desc

to confirm wrong please run query:

select distinct keyword, avgsearches,         concat('!', avgsearches, '!') raw_avgs,        length(avgsearches) avgs_raw_len,        concat('!', trim(avgsearches), '!') trim_avgs,        length(trim(avgsearches))      avgs_len,         char_length(trim(avgsearches)) avgs_char_len,         right(concat('00000000000', trim(avgsearches)), 11) with_trim traffic  status='y'  order right(concat('00000000000', trim(avgsearches)), 11)  desc

here sample output supplied op:

keyword                 avgsearches   no_trim with__trim  window valances         9900           009900     009900  wood blinds             9900           009900     009900  arch window shutters      90         00000090   00000090  arched window shutters    90         00000090   00000090

which cannot correct! 'no_trim' , 'with_trim' columns should 11 characters wide according query.

hmm, starting wonder in avgsearches column.

i have edited query tell me in column.


Comments

Post a Comment

Popular posts from this blog

How to access named pipes using JavaScript in Firefox add-on? -

-
i'm trying access named pipe in firefox add-on. code, based on solution 2 this codeproject question, is: var file = components.classes["@mozilla.org/file/local;1"].createinstance(components.interfaces.nsilocalfile); file.initwithpath("\\\\.\\pipe\\test"); var text = "some text written"; var writer = components.classes["@mozilla.org/network/file-output-stream;1"].createinstance(components.interfaces.nsifileoutputstream); // open file read/write access, , create if not exist. writer.init (file, 0x04 | 0x08, -1, 0); writer.write (text, text.length); writer.flush (); writer.close (); when run firefox scratchpad, get: /* exception: component returned failure code: 0x80520012 (ns_error_file_not_found) [nsifileoutputstream.init] @6 */ line 6 line call writer.init. i've played passing different flags writer.init, no luck. i'm able write normal file path code, not named pipe path. i've been searching more information of da...
Read more

multithreading - OPAL (Open Phone Abstraction Library) Transport not terminated when reattaching thread? -

-
i in process of writing application using opal makes h323 calls. have looked online number of working examples , have managed put resembles should like, @ present able call external ip via application when accept call craps out , dies. leaving me with: 0:12.949 setupcall:8752 sert.cxx(259) assertion fail: transport not terminated when reattaching thread , file d:\voip\software\opal\src\opal\transports.cxx, line 1021 passertfunc(0xde3f88, 0xffffffffd228226f, 0, 0) + 0x82 passertfunc(0x10d6e5f8, 0x3fd, 0, 0x10d6eb98) + 0x15b opaltransport::attachthread(0xde5870, 0xffffffffd22fc76b, 0, 0) + 0x96 h323connection::setupconnection(0xffffffffd22fc713, 0, 0, 0xde6ea8) + 0x196 asynchcallsetup(0x10d3572c, 0, 0, 0xdd0108) + 0x7c pthread1arg<psafeptr<opalconnection,psafeptrbase> >::main(0xffffffffd2282faf from have deduced, perhaps incorrectly if there thread lock issue caused (possibly arising fact application sending expected h323 call, throwing in c...
Read more

node.js - req param returns an empty array -

-
i using node.js , mongodb geolocation app , req param returns empty array exports.findpressure = function(req, res) { var queryobject = req.param('q'); console.log(queryobject); db.collection('pressure', function(err, collection) { collection.find( { loc: { $near :[ req.param('q') ] , $maxdistance : 5 }},{"value" : 1, _id : 0}) .sort({_id : -1}).limit(1).toarray(function(err, items) { res.send(items); }); }); }; the longitude , latitude values displayed in console listening on port 3000... connected 'weather' database 8.9068256,52.019347499999995 /pressure?q=8.9068256,52.019347499999995 200 27ms - 2b the url follows http://localhost:3000/pressure?q=8.9068256,52.019347499999995 if use values 8.9068256,52.019347499999995 getting value database if use req.param('q') returning empty array you seem passing values part of query string. req.query.q should contain comma-s...
Read more