date:20091016

Performance tuning a group by with percentage

2009-10-16 Thread Scott Haneda

Running MySql 5.0.85, I need to be as efficient as possible about a  
few queries. If I could get a little review, I would appreciate it.


I collect data in the millions, and need the top 50 grouped by one  
field, with a percentage of how much those top 50 occupy.


Here is what I have come up with... 1) I have a feeling I can be more  
efficient, perhaps with a join 2) How can I get the percentage to be  
of precision in the hundredths, so * 100.00 ie: .07 becomes 7.00,  
getting SQL errors if I (percentage * 100)


SELECT user_agent_parsed, user_agent_original,  
COUNT( user_agent_parsed ) AS thecount,

COUNT( * ) / ( SELECT COUNT( * ) FROM agents ) AS percentage
FROM agents
GROUP BY user_agent_parsed
ORDER BY thecount DESC LIMIT 50;
Second issue, once a day I need to archive the result of the above.  
Any suggestions on how to best to do that? I can schedule with cron,  
or in my case, launchd, unless someone has a better suggestion.


Would you think that a simple 'SELECT (the above) INTO foo' would  
suffice? ( I will add a date stamp as well )




Thanks all.

--
Scott * If you contact me off list replace talklists@ with scott@ *


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Change clock/timezone time without restart mysql

2009-10-16 Thread Jonas Silveira

Hi,

 

I need to change the computer clock (changing the /etc/zoneinfo) but I would
not restart de MySQL service. The NOW() still returning the old time...

 

Thanks,
Jonas

Re: Change clock/timezone time without restart mysql

2009-10-16 Thread Joerg Bruehe

Jonas,

your information is somewhat incomplete, but still:

Jonas Silveira wrote:
> Hi,
> 
>  
> 
> I need to change the computer clock (changing the /etc/zoneinfo) but I would

From the file name, I assume it is some Unix platform.

> not restart de MySQL service. The NOW() still returning the old time...

"Works as designed":
A process inherits time zone information when it starts, from the parent
and the then valid environment.
This also holds for the MySQL server.

That is why time zone information contains the info about when daylight
saving time starts / ends:
A process starting before the change and still running after that will
use the correct time only because it got the information (about the
change point) already when it started.

HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
   (+49 30) 417 01 487
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

deleting the node, and child nodes/leafs for a db/tbl..

2009-10-16 Thread bruce

Hi.

I've got a situation where I have a couple of tables. The relationship
between the tables is one of parent/child. I'm trying to figure out the best
approach to being able to delete the associated children in the child tbls,
of a given parentID in the parentTBL...

I've checked into various sites/articles on the 'net.. but i'm not sure how
to accomplish this without getting into recursion...

I'm using python/php as the interface language to the test tbls..

Any pointers/articles/test code (code/schema) would be helpful...

Thanks




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

insert random number into table

2009-10-16 Thread Ray

Hello All,
I am trying to insert a unique random number* with each row when inserting 
into a table. I can do it in the application layer (php) but just wondering if 
there is an easier way in the database layer. 
Thanks
Ray

* for example, when registering a person for a contest, I want to give them a 
unique, random identifier.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Inserting an Image

2009-10-16 Thread Victor Subervi

Well, I've spent the last 2 days chasing my tail just to discover that there
was some screwy python deal going on. The following code works:

  sql = "update productsX set Name='%s', Title='%s', Description='%s',
Price='%s', Bedrooms='%s', Bathrooms='%s', Conditions='%s', Acreage='%s',
Construction='%s', Location='%s', Estate='%s', Address='%s', Furnished='%s'
where ID=%s;" % (name, title, description, price, bedrooms, bathrooms,
conditions, acreage, construction, location, estate, address, furnished, id)
  cursor.execute(sql)
  cursor.execute('update productsX set pic1=%s, pic2=%s, pic3=%s,
pic4=%s, pic5=%s, pic6=%s where ID=%s;', (pic1, pic2, pic3, pic4, pic5,
pic6, id))
Notice I have to use two separate sql calls and the only differences between
them are that the first have the strings in quotes AND there is a % sign
between the string widgets and the variables that replace them. That's it!
Wonder what the *#&^%( difference that makes.
V

On Thu, Oct 15, 2009 at 4:54 PM, Michael Dykman  wrote:

> Victor,
>
> again, your question has more to do with python usage than MySQL
> per-se..  you would be better off pursuing these questions in a python
> forum.
>
>  - michael dykman
>
> On Thu, Oct 15, 2009 at 3:28 PM, Gavin Towey  wrote:
> > "Image in string form"  sounds like you're not inserting binary data,
> rather some sort of encoded data.  Even if it is binary, you'll have to
> escape at least end quote characters, you can see clearly at the top of your
> data there is :
> > 'Ã¿ÃÃ¿Ã  JFIF Ã¿ÃC "" $(4,$&1'
> >
> >
> > Another thing to think about is storing image data directly in a database
> is often not a good idea. See these links for more info:
> >
> >
> http://mysqldump.azundris.com/archives/36-Serving-Images-From-A-Database.htmland
> http://hashmysql.org/index.php?title=Storing_files_in_the_database
> >
> > Regards,
> > Gavin Towey
> >
> > -Original Message-
> > From: Victor Subervi [mailto:victorsube...@gmail.com]
> > Sent: Thursday, October 15, 2009 12:05 PM
> > To: mysql@lists.mysql.com
> > Subject: Inserting an Image
> >
> > Hi;
> > I have successfully inserted images, like yesterday, before into MySQL
> with
> > the following code:
> >
> >  sql = 'update productsX set pic1="%s" where ID=2;' % pic1
> >  cursor.execute(sql)
> > where pic1 is simply an image uploaded through a form then sent over
> without
> > any alteration to another (python) script that uploads it.  Printing it
> out
> > looks like this (other variables included):
> >
> > update productsX set Name=%s, Title=%s, Description=%s, Price=%s,
> > Bedrooms=%s, Bathrooms=%s, Conditions=%s, Acreage=%s, Construction=%s,
> > Location=%s, Estate=%s, Address=%s, Furnished=%s, pic1=%s, pic2=%s,
> pic3=%s,
> > pic4=%s, pic5=%s, pic6=%s where ID=%s;', ('name1', 'title1', 'descr1',
> > '1.1', '2', '1', 'New', '1.5', 'New', 'arbor', 'abor', 'abor',
> 'Furnished',
> > 'Ã¿ÃÃ¿Ã  JFIF Ã¿ÃC "" $(4,$&1' -=-157:::#+?D?8C49:7Ã¿ÃC 7%
> > %77Ã¿Ã y| " Ã¿Ã Ã¿ÃM !1A Qa
> "qs
> > Â³ 27Bbcâ #46RrtâÂ¡Â±Â² $'3Câ%5DTUÆâÂ¢Â£ÃÃ±Ã¿Ã Ã¿Ã Ã¿Ã ?Å*â¹RPâÂ¥Â¨% dâpÂªÃcÂµkÃ¤Â«âÃ¸Z ÃÃ¡
> Ã®j
> > XÃªxÃ¶, `...@$Ã½ <%6...@$Ã½> Ã´Â¨2mÃÅ¾Ã Â¦ ÃRÃÂ¥Â¬Ã² guD|!%bO :Ã°Ã§â Zâ¦Ã¦ÂµÃÃvsMÂ¡Â·Â¢&T âÅÂ¬
> ÂºÃ¢
> > âÃ± Ã¢1ÃÂ¶Â¶KT[%Â¦-Â² {âÂ£6 ÃÃ·âÃ¢NI=Ã¤ÃfÃ Ã¸/RhÃÃ®RâÂ³q1 QÃ¼ÃâÆÅ¸ ÆE Å  Å ( (Â¢Å  Å ( (Â¢Å  Å (
> ÃÃª
> > Â´{ âmÃYÃ¼ VTÃ¢â pUÅ½I $Ã  QvÃ ÃÂ¼Ã/ZÃÃ¬{IrÃ,Â´Â£Ã x Ã #Â¸$Å Â·Ã·F_Ã Ã«}â¦â¦Ã¡RTdH Ã³
> > Ã#ÃUâÃ¶*DÃÃµÂ¨Yt]Å¾ Ã®ÃªÃ +p}5Ã¹ÃªÃ¿Â¹F â  vÃ£ ÃÃ¶y5Ãâ\ËÃ«O Å½c 'Ã¶(ÃÃµku-Â´^4ÃµÃÃ ÃÂ£8ÃÃ°%$
> Ã¬84
> > Ã*Ã¸ GÂ¥ÂÂ·T VÃ»#Âµc Ã¦ Â·uÃ; Ã"ÃusN\\Ãâ¹5{ÃÃÂ¸voÃ²ÃÃ»@ Ã© Â¼ÃCP U
> > Â¤Â¡%KPJR2I8T+Â´Â½Â¨Â®{Ã{:)kzDâ¡; &2~1' Â¤â Ã¯ÃÃAÂ¶ÃÂºÂ®ÃÂª5 ÃÃº AiMÃ§Ã¡ âO â8 â¢tÃpHÃ¢NÃ
> > UÂºlÂ¤ÃmÃ«Â¸Ã©Â«ÃÃ Ã¸Ã©Ã Ã»â7ï¿½...@guc q Ã Å hÃÂ¶ cFÃ Ã\Ã§Ã°Ã¤Ãâ¡Ã^8$} Ã¤ Ã=iÂ² Aj Â©Ã´Å Â«â°J_u
> > /Â¥<Æâ°%*Ã¡ÃËÃ;ËÂ¦ TÃÃ¶Â©6)WÃ»jÃZ`Ã¼ dÃsÃ¦âÃHQB E@ m5Ã QE EbÃÃ®
> > ÃP]âºqâÃxÃÂ§yn8pÃ¿Ãâ¡3IP5âÂ¨Â¿Â´Â©ÃºgL2Ã¥Â©K)aÃ©ÃÃ» Â¼ 5 Ã£âs 8Å¾T Ã¦ÃÃ¦*Ã½Âµ)QâÂÃ¤4Ã³pâº Â¦0
> ?ÃU]B
> > r + Ã§\Â»]_"Ã©Å¸ pTÅÅ¸XU] ÃÃ² Â âÂ¬gK Â®tTxÃ²^iâ° Ã 4â¡ RÃ¨ Â¤ Ã 8 YÂ«âº` Ã¼M ]ÃÅ Ã³ÅÂ¼â¬ÃK
> > ,Â¥IÃ¼29 ÃÆÅ¾6â¢iUâ¡]ÃÂ£ Ã/Â²G 5~xÃÂ£8Ã¶TÃÂ±ÃÂ¡Â§SÃ âºÂ«Â¿Ã©ËÃÃ Â¥ Ã[ ;Ã´â¡QÃÅ¸~4Â»vÃOÃÂ¬Ã°u âÃ«ÃÃ
> Ã
> > %Â®a dâÅ¸ g Â¨Ãe1. ÃµÃµÅÃ Ã¦Ã»2 Ã«ÃG$4>9' )$xÃ§ h3Ã¶â°Â´ÃÂ®Â Ã´HÃ®.%Å,â Ã£ Ã Âº x SÃ â¡Å½3NÃ»
> > ÃÃAÃ»ÃªÂº5ÅâË Â¨qÃÃ Â§ Ë ÃÃhÂ±Ã¬ÃOÃÃVÂ¢â° >`ÃTÃÂÃÃ Â«.Â¤Â¨Â¥GÅ¾Ã¨<â¬Ã£Å½gÂ½Ã£P<Ã¬MÂ£hÂ¸1 [
> ÃÃÂ®;J)mA-
> > ÃR8 :gâ¢ Ã£XjÃ»> ââ¢Wâ¡Ã ââ Xho8Ã© Ã·Gâ¡yÃÃ¢8Ã± â¡Â¤Ãµ5Ã«QÂ¥â°Å¸{Å¾Cjxo7"LÃÃÂ Ã  ÃG#ÃÅ¡â¡ âÅ¾Â¨ t
> > ââº-LjFÅ¾hÃËÃXyÂ¥(â¦ \8) Å½JÃ°} -ÃKpÂ¬â TâPÃu ÅÃ® â¬âÅ â Ã´TcÂ¢Ã¶â¦Â§ÃZ ÃÂ«ÃµÃ¹ÂµMÃ¬
> hâÂµ<Ã¶wÅ½7â¬Ã
> > 1ÃâÅ¡Ã2Â7 Ã³ ";= & b*SÃª e Ã7xÅ¾|p Ã¨:@Ã[YÃÃq.1 ÃsÂ¸'â¬b2 BOÃ_!Ã¨ > Â¡
> >

RE: insert random number into table

2009-10-16 Thread Jerry Schwartz

There is a RAND function in MySQL, but if you need to guarantee that the 
identifiers are unique you should consider using an auto-increment field.

If that isn't unique enough, you can use the UUID or UUID_SHORT functions. 
Theoretically, those should return values that are unique across the Internet 
(at least).

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com


>-Original Message-
>From: Ray [mailto:r...@stilltech.net]
>Sent: Friday, October 16, 2009 11:43 AM
>To: mysql@lists.mysql.com
>Subject: insert random number into table
>
>Hello All,
>I am trying to insert a unique random number* with each row when inserting
>into a table. I can do it in the application layer (php) but just wondering 
>if
>there is an easier way in the database layer.
>Thanks
>Ray
>
>* for example, when registering a person for a contest, I want to give them a
>unique, random identifier.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
>infoshop.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: insert random number into table

2009-10-16 Thread Ray

On October 16, 2009 10:57:48 am Jerry Schwartz wrote:
> There is a RAND function in MySQL, but if you need to guarantee that the
> identifiers are unique you should consider using an auto-increment field.
>
> If that isn't unique enough, you can use the UUID or UUID_SHORT functions.
> Theoretically, those should return values that are unique across the
> Internet (at least).
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
>
> >-Original Message-
> >From: Ray [mailto:r...@stilltech.net]
> >Sent: Friday, October 16, 2009 11:43 AM
> >To: mysql@lists.mysql.com
> >Subject: insert random number into table
> >
> >Hello All,
> >I am trying to insert a unique random number* with each row when inserting
> >into a table. I can do it in the application layer (php) but just
> > wondering if
> >there is an easier way in the database layer.
> >Thanks
> >Ray
> >
> >* for example, when registering a person for a contest, I want to give
> > them a unique, random identifier.
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
> >infoshop.com


Thanks for the replies. I think I'll use the php solution as it is the better 
match for what I am trying to do. I'l keep the UUID functions in mind for 
future use though.
Ray

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: insert random number into table

2009-10-16 Thread Gavin Towey

Don't try to give them a random number, instead use a table with a primary key 
that is AUTO_INCREMENT.  Then you just insert the record, and afterwards SELECT 
LAST_INSERT_ID(); to retrieve the id for the record created.

With random numbers, you're going to have more collisions when you add more 
records.

Regards,
Gavin Towey

-Original Message-
From: Ray [mailto:r...@stilltech.net]
Sent: Friday, October 16, 2009 8:43 AM
To: mysql@lists.mysql.com
Subject: insert random number into table

Hello All,
I am trying to insert a unique random number* with each row when inserting
into a table. I can do it in the application layer (php) but just wondering if
there is an easier way in the database layer.
Thanks
Ray

* for example, when registering a person for a contest, I want to give them a
unique, random identifier.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com


The information contained in this transmission may contain privileged and 
confidential information. It is intended only for the use of the person(s) 
named above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, distribution or duplication of this 
communication is strictly prohibited. If you are not the intended recipient, 
please contact the sender by reply email and destroy all copies of the original 
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: insert random number into table

2009-10-16 Thread Jerry Schwartz

>-Original Message-
>From: Ray [mailto:r...@stilltech.net]
>Sent: Friday, October 16, 2009 1:10 PM
>To: mysql@lists.mysql.com
>Subject: Re: insert random number into table
>
>On October 16, 2009 10:57:48 am Jerry Schwartz wrote:
>> There is a RAND function in MySQL, but if you need to guarantee that the
>> identifiers are unique you should consider using an auto-increment field.
>>
>> If that isn't unique enough, you can use the UUID or UUID_SHORT functions.
>> Theoretically, those should return values that are unique across the
>> Internet (at least).
>>
>> Regards,
>>
>> Jerry Schwartz
>> The Infoshop by Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> www.the-infoshop.com
>>
>> >-Original Message-
>> >From: Ray [mailto:r...@stilltech.net]
>> >Sent: Friday, October 16, 2009 11:43 AM
>> >To: mysql@lists.mysql.com
>> >Subject: insert random number into table
>> >
>> >Hello All,
>> >I am trying to insert a unique random number* with each row when inserting
>> >into a table. I can do it in the application layer (php) but just
>> > wondering if
>> >there is an easier way in the database layer.
>> >Thanks
>> >Ray
>> >
>> >* for example, when registering a person for a contest, I want to give
>> > them a unique, random identifier.
>> >
>> >--
>> >MySQL General Mailing List
>> >For list archives: http://lists.mysql.com/mysql
>> >To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
>> >infoshop.com
>
>
>Thanks for the replies. I think I'll use the php solution as it is the better
>match for what I am trying to do. I'l keep the UUID functions in mind for
>future use though.
>Ray
>
[JS] Just remember that the pseudo-random numbers that come out of a computer, 
regardless of where or how they are generated, are not guaranteed to be 
unique. Although the odds of a collision are slim, my philosophy has always 
been "if it's possible, it will happen - in the middle of your vacation".

If you want to generate UUIDs in PHP, take a look at 
http://www.shapeshifter.se/2008/09/29/uuid-generator-for-php/
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
>infoshop.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: insert random number into table

2009-10-16 Thread Scott Haneda

In addition to what Gavin said. You seem to want some form of key  
perhaps to be able to identify the authenticity of your contest  
winner. An auto increment ID won't be very secure for that, but you  
still need it.


Take a known combination of perhaps the key, name, email address, etc,  
and run a hashing function on it. Md5 will often suffice.


This will give you a unique hash that only you know how it was made.  
You can inject some 'salt' into a known location to add more  
difficulty in guessing your hashing input.


This hash will be asking too much of the user to hand type in, so I'm  
assuming you will accept it via a link in a URL.


Or ask for whatever you used to make the hash and rehash that and  
compare your internal stored result.


If not, for your needs you may be able to safely remove ambiguous  
characters, and take a substring of it to make it shorter.


It's also quite easy to create a dictionary of words and create a  
function that can make word1-xx-word2 and end up with red-56-car.


--
Scott
Iphone says hello.

On Oct 16, 2009, at 11:25 AM, Gavin Towey  wrote:

Don't try to give them a random number, instead use a table with a  
primary key that is AUTO_INCREMENT.  Then you just insert the  
record, and afterwards SELECT LAST_INSERT_ID(); to retrieve the id  
for the record created.


With random numbers, you're going to have more collisions when you  
add more records.


Regards,
Gavin Towey

-Original Message-
From: Ray [mailto:r...@stilltech.net]
Sent: Friday, October 16, 2009 8:43 AM
To: mysql@lists.mysql.com
Subject: insert random number into table

Hello All,
I am trying to insert a unique random number* with each row when  
inserting
into a table. I can do it in the application layer (php) but just  
wondering if

there is an easier way in the database layer.
Thanks
Ray

* for example, when registering a person for a contest, I want to  
give them a

unique, random identifier.

Re: insert random number into table

2009-10-16 Thread Scott Haneda

I always maintain a timestamp in my random numbers. As long as my  
precision is higher than my requests per second, wouldn't I be safe  
from collisions? Assuming a time machine is not invented.


--
Scott
Iphone says hello.

On Oct 16, 2009, at 11:29 AM, "Jerry Schwartz" > wrote:


JS] Just remember that the pseudo-random numbers that come out of a  
computer,
regardless of where or how they are generated, are not guaranteed to  
be
unique. Although the odds of a collision are slim, my philosophy has  
always
been "if it's possible, it will happen - in the middle of your  
vacation".


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

inserting csv - solved, but more to the puzzle

2009-10-16 Thread Patrice Olivier-Wilson

In case anyone might find this of the least interest, probably not, but 
I always hope to add to discussion just as part of the thank you for help.


Further work with same issues found that a file received from a PC based 
client, if opened in PC environment, didn't have the same problems (so 
far) as if opened in a Mac environment. The next file I had to work with 
in this particular project, I opened in PC, then uploaded using 
phpMyadmin with no issues. Same client, same type of file.


So, maybe there is a PC/Mac thing happening to cause the line 17, 
missing commas in csv?


Just a thought. If anyone has any insight, most appreciated.

--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: inserting csv - solved, but more to the puzzle

2009-10-16 Thread Andy Wallace


I've run into similar situations w/regard to Mac vs PC CSV files,
it usually has to do with the EOL character sequence. Macs use
LF (chr(10)), while PCs use CRLF (chr(13)chr(10)).

andy

Patrice Olivier-Wilson wrote:
In case anyone might find this of the least interest, probably not, but 
I always hope to add to discussion just as part of the thank you for help.


Further work with same issues found that a file received from a PC based 
client, if opened in PC environment, didn't have the same problems (so 
far) as if opened in a Mac environment. The next file I had to work with 
in this particular project, I opened in PC, then uploaded using 
phpMyadmin with no issues. Same client, same type of file.


So, maybe there is a PC/Mac thing happening to cause the line 17, 
missing commas in csv?


Just a thought. If anyone has any insight, most appreciated.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: inserting csv - solved, but more to the puzzle

2009-10-16 Thread Patrice Olivier-Wilson

Thank you for the confirmation, Andy. I appreciate your patience with 
such a newbie who is just trying to learn.



Andy Wallace wrote:

I've run into similar situations w/regard to Mac vs PC CSV files,
it usually has to do with the EOL character sequence. Macs use
LF (chr(10)), while PCs use CRLF (chr(13)chr(10)).

andy

Patrice Olivier-Wilson wrote:
In case anyone might find this of the least interest, probably not, 
but I always hope to add to discussion just as part of the thank you 
for help.


Further work with same issues found that a file received from a PC 
based client, if opened in PC environment, didn't have the same 
problems (so far) as if opened in a Mac environment. The next file I 
had to work with in this particular project, I opened in PC, then 
uploaded using phpMyadmin with no issues. Same client, same type of 
file.


So, maybe there is a PC/Mac thing happening to cause the line 17, 
missing commas in csv?


Just a thought. If anyone has any insight, most appreciated.






--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: insert random number into table

2009-10-16 Thread Ray

On October 16, 2009 12:29:42 pm Jerry Schwartz wrote:
> >-Original Message-
> >From: Ray [mailto:r...@stilltech.net]
> >Sent: Friday, October 16, 2009 1:10 PM
> >To: mysql@lists.mysql.com
> >Subject: Re: insert random number into table
> >
> >On October 16, 2009 10:57:48 am Jerry Schwartz wrote:
> >> There is a RAND function in MySQL, but if you need to guarantee that the
> >> identifiers are unique you should consider using an auto-increment
> >> field.
> >>
> >> If that isn't unique enough, you can use the UUID or UUID_SHORT
> >> functions. Theoretically, those should return values that are unique
> >> across the Internet (at least).
> >>
> >> Regards,
> >>
> >> Jerry Schwartz
> >> The Infoshop by Global Information Incorporated
> >> 195 Farmington Ave.
> >> Farmington, CT 06032
> >>
> >> 860.674.8796 / FAX: 860.674.8341
> >>
> >> www.the-infoshop.com
> >>
> >> >-Original Message-
> >> >From: Ray [mailto:r...@stilltech.net]
> >> >Sent: Friday, October 16, 2009 11:43 AM
> >> >To: mysql@lists.mysql.com
> >> >Subject: insert random number into table
> >> >
> >> >Hello All,
> >> >I am trying to insert a unique random number* with each row when
> >> > inserting into a table. I can do it in the application layer (php) but
> >> > just wondering if
> >> >there is an easier way in the database layer.
> >> >Thanks
> >> >Ray
> >> >
> >> >* for example, when registering a person for a contest, I want to give
> >> > them a unique, random identifier.
> >> >
> >> >--
> >> >MySQL General Mailing List
> >> >For list archives: http://lists.mysql.com/mysql
> >> >To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
> >> >infoshop.com
> >
> >Thanks for the replies. I think I'll use the php solution as it is the
> > better match for what I am trying to do. I'l keep the UUID functions in
> > mind for future use though.
> >Ray
>
> [JS] Just remember that the pseudo-random numbers that come out of a
> computer, regardless of where or how they are generated, are not guaranteed
> to be unique. Although the odds of a collision are slim, my philosophy has
> always been "if it's possible, it will happen - in the middle of your
> vacation".
>
> If you want to generate UUIDs in PHP, take a look at
> http://www.shapeshifter.se/2008/09/29/uuid-generator-for-php/
>
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
> >infoshop.com


Thanks for all the suggestions and points. There are definitely some I will 
keep in mind for future projects. auto increment won't work because I don't 
want the number to be based on the order of assignment.
I probably should have been more clear. What I am trying to do is randomly 
assign one of a fixed pool of numbers uniquely to each row. 
What I ended up doing is (in PHP): 
SELECT all used numbers and put them in an array.
create an array of all allowed numbers. 
combine the two arrays such that all available numbers are left in a third 
array with index numbers from 1 counting up sequentially. I then randomly 
choose one of those numbers and write it to the table.
Thanks again for all your suggestions.
Ray

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Performance tuning a group by with percentage

Change clock/timezone time without restart mysql

Re: Change clock/timezone time without restart mysql

deleting the node, and child nodes/leafs for a db/tbl..

insert random number into table

Re: Inserting an Image

RE: insert random number into table

Re: insert random number into table

RE: insert random number into table

RE: insert random number into table

Re: insert random number into table

Re: insert random number into table

inserting csv - solved, but more to the puzzle

Re: inserting csv - solved, but more to the puzzle

Re: inserting csv - solved, but more to the puzzle

Re: insert random number into table

16 matches

Site Navigation

Mail list logo

Footer information