Updating Random Records
Is there a way to update random records? I have a database of 5.6 million records and I want to pull out 5000 records and mark a field in the database that I've pulled these so that I don't pull the same 5000 again. I know I can select the records by doing: SELECT * FROM 5600k WHERE picked IS NULL ORDER BY rand() LIMIT 5000; But what I really want to do is: UPDATE 5600k SET picked=1 WHERE picked IS NULL ORDER BY rand() LIMIT 5000; Then doing: SELECT * FROM 5600k WHERE picked=1; Then resetting the table so I can pick a random selection again later: UPDATE 5600k SET picked=0 WHERE picked=1; So can the update statement be done to update random records? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RPM or Source or Binary
That's exactly what I do with MySQL. For PHP I go with DSO module. - Original Message - From: "sysadmin" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Friday, July 06, 2001 10:20 PM Subject: Re: RPM or Source or Binary > this way works perfectly for us. > > http://www.mysql.com/articles/ddws/4.html > > - Original Message - > From: "Roger Ramirez" <[EMAIL PROTECTED]> > To: "MySQL" <[EMAIL PROTECTED]> > Sent: Saturday, July 07, 2001 10:02 AM > Subject: RPM or Source or Binary > > > > I'm curious as to how people are installing MySQL on Linux. I've been > > learning linux on Red Hat and more recently I've been using Trustix Secure > > Linux (which also uses RPMs) but I've never actually installed MySQL > through > > the RPM's. The person who gave me my first basic lessons in Linux had an > > aversion to package managers and liked to install everything from source. > So > > my questions is, on a RPM-based distro of Linux how would you install > MySQL > > and why do you think your way is the best. If you version of Linux is not > > RPM-based what version do you prefer, Source or Binary and why? > > > > Thanks, > > Roger > > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RPM or Source or Binary
I'm curious as to how people are installing MySQL on Linux. I've been learning linux on Red Hat and more recently I've been using Trustix Secure Linux (which also uses RPMs) but I've never actually installed MySQL through the RPM's. The person who gave me my first basic lessons in Linux had an aversion to package managers and liked to install everything from source. So my questions is, on a RPM-based distro of Linux how would you install MySQL and why do you think your way is the best. If you version of Linux is not RPM-based what version do you prefer, Source or Binary and why? Thanks, Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: db design questions
Pretty much was Siomara says is the way I think, except that I would have made the extra table for the two addresses. For the kids field it depends on what you want. If you want to have information for each child then you would create a separate table, but if you just need to record the number of kids then I think one field in the main table would be fine. Also if you have information like you mentioned, gender and marital status, instead of making it a char or varchar field with the words Male and Female or Married and Single just use a tinyint and make the values 0 or 1. You should either comment somewhere what the 0 and 1 means or actually create another table for sex that just has two fields and two records. 0-male, and 1-female and same with marital status. Another example of using a second table to store information would be the interest checkboxes you'll see on most web pages registration form. Its a list of check boxes that you are supposed to check what interests you have, movies, stocks, computers and so on. For this you would have your main table with no interest field, just the rest of your information plus your unique id. A second table (interest_definitions) would have two columns, interest_id and interest_text. and the third table (interests) would have user_id and interest_id. The interest_definitions would have all the possible interests someone can choose and the third table would have a row for each interest the user is into. Hope that wasn't too long winded. :) Roger - Original Message - From: "Siomara Pantarotto" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, July 06, 2001 5:07 PM Subject: Re: db design questions > Hi, > > Try to keep the simple attributes in one table and the repetitions in > separated tables> > > For example: > > A person has only one gender, one name, one marital stauts , etc > > however a person can have none,one or more than one kid. Create another > table and create a relationship between both. > > I really don't know how many addresses you want to store for each person but > if it's not that many. keep them in the same table. > > Create fields for streetwork, citywork, ststework, etc... > and streethome, cityhome, etc... > > This allows you to store only one professional and one home adresses. > > If you want more than that ...then put them in a separated table as I > suggested to do for kids > > I don't have an example similar to your need but you can take a look at my > website at: > > www.geocities.com > > and maybe get some ideas from the example I posted there. > > click tutorials and team5.com ecommerce sample. > > good luck > > Siomara > > > > >From: "Ed Peddycoart" <[EMAIL PROTECTED]> > >To: "MySQL" <[EMAIL PROTECTED]> > >Subject: db design questions > >Date: Fri, 6 Jul 2001 10:25:42 -0700 > > > >I am working on a website which will make use of a MySQL database. I know > >little if anything about the best way to design my db for efficiency, or > >anything else for that matter. The information which I need to put in the > >database will be contact/profile information on various people: Name, > >Address, Phone, Email, Marital Status, Spouse, kids, things like that and a > >field for comments submitted from the various people. Right now I have a > >single table with a record for each item I want to store. Is that an > >acceptable way? > > > >What are some websites which contain some basic information on DB design > >etc. Things such as the best way to structure the db for effiency, > >searches, what do all the terms mean (keys, index etc) etc. > > > >Ed > > > > > >- > >Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > >To request this thread, e-mail <[EMAIL PROTECTED]> > >To unsubscribe, e-mail > ><[EMAIL PROTECTED]> > >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > _ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Random Selection
I have a database of 5.6 million records and I need to choose 5000 random records. What would that query look like? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
indexing
Hi, I'm using MySQL 3.23.39 under Red Hat 7.1 on a Pentium III 600mhz with 640MB of Ram. I only 1 table in a database with 5.6 million records and I'm currently trying to index a varchar(100) field. I started the query "alter table mytable add index myfield (myfield)" at 10am EST this morning and 5:45 hours later its still running. If I look in my mysql/var directory I have the following. -rw-rw1 root root Jul 5 09:44 mytable.frm -rw-rw1 root root 689493112 Jul 5 09:58 mytable.MYD -rw-rw1 root root 114336768 Jul 5 09:58 mytable.MYI -rw-rw1 root root Jul 5 09:59 #sql-312_9.frm -rw-rw1 root root 616317108 Jul 5 11:21 #sql-312_9.MYD -rw-rw1 root root 136826880 Jul 5 14:30 #sql-312_9.MYI The very last file #sql-312_9.MYI has been growing at a rate of about 40-50MB/hour. Is there any way for me to tell how much longer this will go on? I did a couple of indexes on two other fields in this table the day before yesterday but I issued the commands and walked away from the computer. It was a rough day so i don't remember how long it took. The other two fields are varchar(5) and varchar(10) and I know for a fact that it didn't take more then 24 hours, and I'm pretty sure it was less than 6 hours. Thanks, Roger Ramirez - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems with MOD
Why do I get the following results? mysql> select (1008306000-988344000)/86400/7, mod((1008306000-988344000)/86400,7.); ++-- + | (1008306000-988344000)/86400/7 | mod((1008306000-988344000)/86400,7.) | ++-- + |33.0060 |0 | ++-- + 1 row in set (0.00 sec) Shouldn't the value in Column 2 be some number other then 0? I'm running MySQL 3.23.27. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: COUNT(DISTINCT )
You can use: SELECT , count() FROM GROUP BY > -Original Message- > From: Franz, Fa. PostDirekt MA [mailto:[EMAIL PROTECTED]] > Sent: Friday, February 16, 2001 3:02 AM > To: [EMAIL PROTECTED] > Subject: COUNT(DISTINCT ) > > > > Hi Everybody , > > I am using MySql 3.22.32 on LINUX. > My problem is , that 'SELECT COUNT(DISTINCT ) FROM > > doesn't work > like described in the HTML-manual (7.4.13) . > It even doesn't work at all. > Is tthat a bug , am I stupid , or is there any workaround. > > Greetings > Klaus > > Besuchen Sie uns auch im Internet: http://www.postdirekt.de > > Diese Mail ist von: > Deutsche Post Direkt GmbH > Beleglese Center Mannheim > > Klaus Franz > Manager Abgleichsysteme > > Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 > 68161 Mannheim > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query/Left Join problem
Duh why didn't I think of that. Thank you graciously. :) > -Original Message- > From: Quentin Bennett [mailto:[EMAIL PROTECTED]] > Sent: Thursday, February 15, 2001 4:26 PM > To: 'Roger Ramirez'; MySQL List > Subject: RE: Query/Left Join problem > > > Hi, > > Your left join tried to find members in newsletter_subscriptions > that don't > exist in perms. > > How about they exist, but are 'No'?: > > SELECT newsletter_subscription.member_id > FROM newsletter_subscription LEFT JOIN perms ON > newsletter_subscription.member_id=perms.id > WHERE newsletter_subscription.member_id<>0 AND (perms.id IS NULL OR > mass_mail='No'); > > Does that give you what you need? > > Regards > > Quentin > > > -Original Message- > From: Roger Ramirez [mailto:[EMAIL PROTECTED]] > Sent: Friday, 16 February 2001 08:34 > To: MySQL List > Subject: Query/Left Join problem > > > Hi there. > > I'm having a problem with a couple of tables I'm using and finding some > missing records and I was hoping someone could help me. > > Here is the setup. > > I have a table that is called "perms". Within here I have a bunch of > columns, only 2 of which are important. > ID which is an auto increment field and is my primary key for this table. > mass_mail which has a value of 'Si' or 'No'. > +---+--+ > | mass_mail | count(mass_mail) | > +---+--+ > | No| 2871 | > | Si| 8854 | > +---+--+ > > The second table I have is called "newsletter_subscription". This table > also has a bunch of columns member_id which is the only important one. > There is one record in newsletter_subscription for each record > within perms > where mass_mail='Si', plus a few records (almost 100) which have > a member_id > of 0. > > When I do this: > select count(*) from newsletter_subscription where member_id<>0; > > I get: > +--+ > | count(*) | > +--+ > | 8857 | > +--+ > > As you can see there are three more records in the newsletter_subscription > table that should not be (8854/8857). So to find those three > records I try > this: > > SELECT newsletter_subscription.member_id > FROM newsletter_subscription LEFT JOIN perms ON > newsletter_subscription.member_id=perms.id > WHERE newsletter_subscription.member_id<>0 AND perms.id IS NULL; > > I get: > Empty set (0.11 sec) > > I need to find the three extra records that are in newsletter_subscription > and remove them. How would I go about finding them. > > I also checked the newsletter_subscription table to make sure that it did > not have any duplicate member_id's other then the few records with a > member_id of 0. The way I did it was to do this: > SELECT member_id, count(member_id) FROM newsletter_subscription GROUP BY > member_id ORDER BY 2 ASC > > Then I look at the last couple of lines and see this: > | 11922 |1 | > | 0 | 73 | > +---+--+ > 8859 rows in set (0.10 sec) > > Is there a faster/easier way to do this? something like WHERE > count(member_id) > 1 (although I know this does not work in that query) > > > Thanks, > > Roger Ramirez > Senior Developer > > TodoBebe.com - Todo lo que quiere saber de su bebé. > TodoBebe.com - The best baby website in Spanish! > www.todobebe.com > > > > [EMAIL PROTECTED] > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > The information contained in this email is privileged and confidential > and intended for the addressee only. If you are not the intended > recipient, you are asked to respect that confidentiality and not > disclose, copy or make use of its contents. If received in error > you are asked to destroy this email and contact the sender immediately. > Your assistance is appreciated. > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query/Left Join problem
Hi there. I'm having a problem with a couple of tables I'm using and finding some missing records and I was hoping someone could help me. Here is the setup. I have a table that is called "perms". Within here I have a bunch of columns, only 2 of which are important. ID which is an auto increment field and is my primary key for this table. mass_mail which has a value of 'Si' or 'No'. +---+--+ | mass_mail | count(mass_mail) | +---+--+ | No| 2871 | | Si| 8854 | +---+--+ The second table I have is called "newsletter_subscription". This table also has a bunch of columns member_id which is the only important one. There is one record in newsletter_subscription for each record within perms where mass_mail='Si', plus a few records (almost 100) which have a member_id of 0. When I do this: select count(*) from newsletter_subscription where member_id<>0; I get: +--+ | count(*) | +--+ | 8857 | +--+ As you can see there are three more records in the newsletter_subscription table that should not be (8854/8857). So to find those three records I try this: SELECT newsletter_subscription.member_id FROM newsletter_subscription LEFT JOIN perms ON newsletter_subscription.member_id=perms.id WHERE newsletter_subscription.member_id<>0 AND perms.id IS NULL; I get: Empty set (0.11 sec) I need to find the three extra records that are in newsletter_subscription and remove them. How would I go about finding them. I also checked the newsletter_subscription table to make sure that it did not have any duplicate member_id's other then the few records with a member_id of 0. The way I did it was to do this: SELECT member_id, count(member_id) FROM newsletter_subscription GROUP BY member_id ORDER BY 2 ASC Then I look at the last couple of lines and see this: | 11922 |1 | | 0 | 73 | +---+--+ 8859 rows in set (0.10 sec) Is there a faster/easier way to do this? something like WHERE count(member_id) > 1 (although I know this does not work in that query) Thanks, Roger Ramirez Senior Developer TodoBebe.com - Todo lo que quiere saber de su bebé. TodoBebe.com - The best baby website in Spanish! www.todobebe.com [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: creating tables from the shell
ok it looks like your funneling whatever query you have in create_member.sql into mysql and executing it on the samp_db database. From your filename I'm assuming that you have a "create table" query within create_member.sql. Is the table not being created within samp_db? Try this: ./bin/mysql samp_db show tables; and see if this doesn't output the member table that you are trying to create from withing the .sql file. > -Original Message- > From: Michael Tuminello [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, February 06, 2001 4:05 PM > To: [EMAIL PROTECTED] > Subject: creating tables from the shell > > > Hey - > > sorry for being too much of a pinhead to figure this out, but the > server is really not giving me too many clues to work with. > > I'm trying to execute commands from the shell to create new tables > (I'm walking through the new riders book). > > the book says % mysql samp_db < create_member.sql > > for me, for whatever reason, mysql will not execute except from its > root directory. so I'm doing > % ./bin/mysql samp_db < ./var/mysql/create_member.sql > (the book also doesn't tell you where to put the executible scripts, > so I stuck them in the data directory) > > can anyone tell me why this isn't working? it seems to execute (no > error), but nothing happens. Also, can anyone tell me if there is a > way to get the server to tell you a little more about why things > don't work? I could use some help debugging, and the log entries are > pithy, to say the least. > > thanks in advance- > > MT > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Boolean Datatype
Couldn't you just do:
boolVarsENUM ('True', 'False') DEFAULT 'False'
-or-
boolVarsENUM ('TRUE', 'FALSE') DEFAULT 'FALSE'
unless I'm misunderstanding what you are saying about ASP.
> -Original Message-
> From: Russ Davies [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 06, 2001 2:44 PM
> To: Oson, Chris M.; [EMAIL PROTECTED]
> Subject: Re: Boolean Datatype
>
>
> Thanks for the suggestion, but this would still mean converting the data
> type from within ASP (the 'True' type) down to a char ('T') would it not?
>
> It is more sensical than using 1/0 but still involves the same number of
> processes?
>
> Russ Davies
>
>
>
> - Original Message -
> From: "Oson, Chris M." <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Tuesday, February 06, 2001 6:36 PM
> Subject: RE: Boolean Datatype
>
>
> > Why not try using ENUM?
> >
> > http://www.mysql.com/doc/E/N/ENUM.html
> >
> > You could setup your table like this:
> >
> > CREATE TABLE logInfo2
> > (
> > logID INT NOT NULL UNIQUE,
> > ipAddress VARCHAR(20) NOT NULL DEFAULT 'None',
> > boolVarsENUM ('T', 'F') DEFAULT 'F'
> > )
> >
> > SELECT ipAddress FROM logInfo2 WHERE boolVars = 'F';
> >
> > -Original Message-
> > From: russ [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, February 06, 2001 9:10 AM
> > To: [EMAIL PROTECTED]
> > Subject: Boolean Datatype
> >
> >
> > Im new to the list, apologies if this has been asked before.
> >
> > Im developing a backend for a personal site (www.russd.com) using mySQL.
> > The site is hosted on NT4 and has myODBC installed, I have some database
> > access working, but I'm looking for a way to implement boolean types.
> >
> > Using Access/SQL server I can simple do an insert using SQL like the
> > following (from within ASP):
> >
> > INSERT INTO tblMyTable (booleanField) VALUES (" &
> myBooleanVariant & ");"
> >
> > Unfortunately, mySQL doesn't appear to have a boolean datatype
> and myODBC
> > doesn't seem to convert True/False to 1/0.
> > The best solution I have so far is to use a TINYINT and change the
> > True/False values within my ASP code to 1/0. This isn't perfect howevere
> as
> > its more code, more work for the server and far less readable.
> I also have
> > to cast to boolean when I SELECT data from the mySQL database.
> >
> > Could anyone suggest a better way of implementing this, or am I missing
> > something?
> > I have done a Google search and searched the mySQL manual, but got
> nothing.
> >
> > Many thanks in advance of your efforts.
> > Russ Davies
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php (the manual)
> >http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Insert into blob
Try using this function on your data before inserting into the database. http://www.php.net/manual/en/function.addslashes.php If that doesn't work show us what error you are getting. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Monday, February 05, 2001 6:15 PM > To: [EMAIL PROTECTED] > Subject: Insert into blob > > > Hi, > > I am having some problems inserting data into a blob field, > everything works > fine but if I use any HTML and try to insert it, it doesn`t work. > So in short > is there something I should be doing to allow HTML to go into a > text blob? I > am using PHP if that helps. > > TIA > Ade > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Error creating tables
I copied and pasted your create table query into mysql and it created the table without a problem. I'm using 3.32.32. mysql> describe top -> ; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | Id | int(11) | | PRI | NULL| auto_increment | | nadal | decimal(10,0) | YES | | NULL|| | kuupaev | date | YES | | NULL|| | usa1| text | YES | | NULL|| | usa2| text | YES | | NULL|| | usa3| text | YES | | NULL|| | usa4| text | YES | | NULL|| | usa5| text | YES | | NULL|| | usa6| text | YES | | NULL|| | usa7| text | YES | | NULL|| | usa8| text | YES | | NULL|| | usa9| text | YES | | NULL|| | usa10 | text | YES | | NULL|| | eesti1 | text | YES | | NULL|| | eesti2 | text | YES | | NULL|| | eesti3 | text | YES | | NULL|| | eesti4 | text | YES | | NULL|| | eesti5 | text | YES | | NULL|| | tekst | longtext | YES | | NULL|| +-+---+--+-+-++ 19 rows in set (0.04 sec) > -Original Message- > From: Esko Lehtme [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, February 06, 2001 8:36 AM > To: [EMAIL PROTECTED] > Subject: Error creating tables > > > Can anyone help? > In this script is one bug but i can't find it :( > > create table top( > Id int(11) DEFAULT '0' NOT NULL auto_increment, > nadalnumeric, > kuupaev DATE, > usa1 text, > usa2 text, > usa3 text, > usa4 text, > usa5 text, > usa6 text, > usa7 text, > usa8 text, > usa9 text, > usa10text, > eesti1 text, > eesti2 text, > eesti3 text, > eesti4 text, > eesti5 text, > tekstlongtext, > PRIMARY KEY (id) > ); > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Max value
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Gro up_by_functions max(x) works with the group by clause. > -Original Message- > From: Roman Arefiev [mailto:[EMAIL PROTECTED]] > Sent: Friday, February 02, 2001 10:25 AM > To: [EMAIL PROTECTED] > Subject: Max value > > > Hello, I'm a newbie in MySql, it would be great if you can help me .. > the problem is : > I have a Mysql table like: > > create table users (user varchar(20), debt int(10)); > > How can I select user with the greatest value of debt ? > I have this function (max) in MS Access database and it works perfectly. > I've read all docs about MySql, but I couldn't find the same function in > MySql. > If it is difficult to you, can you supply me with the exact SQL-query (for > this example). > > Thanx a lot, > sorry for my English, > Roman Arefiev, > Sochi. Russia. > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Auto-Increment - how can I avoid repeating IDs?
Use your own id's. I personally hate auto increment. I do all my work in php so I make my id's like this. srand(time()); $id = md5(uniqid(rand())); This will make a 32 character id. > -Original Message- > From: Viken Nokhoudian [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 30, 2001 3:58 PM > To: [EMAIL PROTECTED] > Subject: Auto-Increment - how can I avoid repeating IDs? > > > I am using MySQL ver. 3.22.32 > > When a table has an auto-incrementing ID field and the last inserted > record had an ID of, say, 100, then I delete that record, the database > engine will re-use the 100 value. How can I get the engine to never > repeat an ID value to avoid corrupted data correlations? > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Insert Problem
Hmmm I don't believe you can have a where in an insert statement. You
either do or you don't. Perhaps you are thinking of update?
- Original Message -
From: "Toby Miller" <[EMAIL PROTECTED]>
To: "Liste mysql" <[EMAIL PROTECTED]>
Sent: Tuesday, January 30, 2001 6:28 AM
Subject: Insert Problem
I have a record being inserted and I don't see what the problem is. This is
the insert statement:
insert into auth_users (username, password, firstname, lastname, address1,
address2, city, state, zip, phone, fax, email, hint) values ("username",
"password", "firstname", "lastname", "Address1", "", "City", "ST", "12345",
"444-555-", "", "[EMAIL PROTECTED]", "password hint") where username =
"username" and password = "cd6c8f619fe02d9ea5d283cea1dfdefc"
Here is the error that I am getting:
You have an error in your SQL syntax near 'where username = "tobymiller" and
password = "cd6c8f619fe02d9ea5d283cea1dfdefc"' at line 1
I am using a MD5 encrypted string for the password, that's what that is.
Here is the table that this record is being inserted into:
CREATE TABLE auth_users (
username varchar(20) NOT NULL,
password varchar(120) NOT NULL,
firstname varchar(36) NOT NULL,
lastname varchar(36) NOT NULL,
address1 varchar(60),
address2 varchar(60),
city varchar(60),
state char(2),
zip varchar(10),
phone varchar(12),
fax varchar(12),
email varchar(120) NOT NULL,
hint varchar(120),
ipaddress varchar(15),
hash varchar(120),
created timestamp(14),
createdby varchar(20) DEFAULT 'byhand' NOT NULL,
loggedin timestamp(14),
UNIQUE username (username)
);
Any ideas?
Thanks,
Toby
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with Query Syntax?
Hmmm... Looks like you over did the query to me. This should work. SELECT o.OrderID, c.CustomerFirst, c.CustomerLast, s.SalesRepFirst, s.SalesRepLast FROM Orders as o, Customers as C, SalesReps as s WHERE O.CustomerPhone=c.CustomerPhone AND o.SalesRepID=s.SalesRepID of course you don't need the o.OrderID but I usually like selecting the ID from the main table being queried. - Original Message - From: "Lee Jenkins" <[EMAIL PROTECTED]> To: "MySQL Mail list" <[EMAIL PROTECTED]> Sent: Monday, January 29, 2001 10:18 PM Subject: Help with Query Syntax? > > > Hi all. I'm having a little trouble with this query. > > **Table 1 (Orders) ** > > OrderID SmallInt > OrderStatus VarChar(20) > CustomerPhone VarChar(10) > SalesRepID SmallInt(11) > > **Table 2 (Customers)** > > CustomerFirst VarChar(20) > CustomerLastVarChar(20) > CustomerPhone VarChar(10) > > **Table 3 (SalesReps)** > SalesRepID SmallInt(11) > SalesRepFirst VarChar(20) > SalesRepLast VarChar(20) > > > I'm trying to retrieve all ORDERS with the CustomerFirst and CustomerLast > and the SalesRepFirst and SalesRepLast from these respective tables. There > are other fields and they are listed in the SQL statement below. The ODBC > driver informs me that there is a problem with the JOINS. I'm very new to > MySQL syntax. Can you join more than one table? > > > SELECT DISTINCT deliverycustomers.CustomerFirst, > deliverycustomers.CustomerLast, > deliverycustomers.CustomerAddress1, > deliverycustomers.HouseNumber, orders.OrderID, > servers.ServerFirst, > servers.ServerLast, deliverycustomers.CustomerPhone, > orders.OrderTime > FROM > servers INNER JOIN deliverycustomers INNER JOIN orders ON > deliverycustomers.CustomerPhone = orders.CustomerPhone ON > servers.ServerID = > orders.DriverID GROUP BY deliverycustomers.CustomerFirst, > deliverycustomers.CustomerLast, > deliverycustomers.CustomerAddress1, > deliverycustomers.HouseNumber, orders.OrderID, > servers.ServerFirst, > servers.ServerLast, deliverycustomers.CustomerPhone, > orders.OrderTime > WHERE > Orders.OrderType = 'Delivery' AND (Orders.OrderStatus = 'Open' > OR Orders.OrderStatus = 'PreAuth') ORDER BY OrderTime; > > > > Any help would be greatly appreciated. > Lee Jenkins > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
