RE: Can we crypt passwords on MySQL

[csebe]

There is a PASSWORD('your_clear_text_password_here') function you can use
wherever you define a new password. See the manual for more.

Lian


> -Original Message-
> From: Grégoire Dubois [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 19, 2003 12:55 AM
> Cc: 'Mysql'
> Subject: Can we crypt passwords on MySQL
>
>
> Hi all,
>
> Is it possible to crypt the passwords on MySQL. If yes, how does it
> work, and how is it to be implemented.
> Any link would be great.
>
> Thank you.
> Grégoire Dubois.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Can we crypt passwords on MySQL

[csebe]

Strange...

This is what the online manual says in "4.3.7 Setting up passwords":

>>>
Passwords must be encrypted when they are inserted in the user table, so the
INSERT statement should have been specified like this instead:

mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey',PASSWORD('biscuit'));

You must also use the PASSWORD() function when you use SET PASSWORD
statements:

mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');
<<<

However in "6.3.6.2 Miscellaneous Functions" it says indeed:


PASSWORD(str)
OLD_PASSWORD(str)
[...]
Note: The PASSWORD() function is used by the authentication system in MySQL
Server, you should NOT use it in your own applications. For that purpose,
use MD5() or SHA1() instead. Also see RFC-2195 for more information about
handling passwords and authentication securely in your application.


Whom to believe?

Lian


> -Original Message-
> From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 27, 2003 1:19 AM
> To: [EMAIL PROTECTED]; Grégoire Dubois
> Cc: 'Mysql'
> Subject: Re: Can we crypt passwords on MySQL
>
>
> Hi,
>
> Using PASSWORD function to crypt password is not recommended, it's mainly
> used for internal mysql password encryption.
> Prefer using MD5/SHA1 functions to encrypt a password.
>
> Regards,
>   Jocelyn
>
>
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: "Grégoire Dubois" <[EMAIL PROTECTED]>
> Cc: "'Mysql'" <[EMAIL PROTECTED]>
> Sent: Thursday, June 19, 2003 12:14 AM
> Subject: RE: Can we crypt passwords on MySQL
>
>
> > There is a PASSWORD('your_clear_text_password_here') function
> you can use
> > wherever you define a new password. See the manual for more.
> >
> > Lian
> >
> >
> > > -Original Message-
> > > From: Grégoire Dubois [mailto:[EMAIL PROTECTED]
> > > Sent: Thursday, June 19, 2003 12:55 AM
> > > Cc: 'Mysql'
> > > Subject: Can we crypt passwords on MySQL
> > >
> > >
> > > Hi all,
> > >
> > > Is it possible to crypt the passwords on MySQL. If yes, how does it
> > > work, and how is it to be implemented.
> > > Any link would be great.
> > >
> > > Thank you.
> > > Grégoire Dubois.
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Can we crypt passwords on MySQL

[csebe]

So it's a future thing since I can see MySQL is only at 4.0.13 now.
Anyway, I do prefer (hopefully there are others too ;) inserting directly
into user, db, host,... tables instead of using GRANT, so for this case I
(still) can use PASSWORD(), right ?!

BTW, maybe I'm missing something but what could be the use for a password
function returning different results with the same input? I mean, can one
using the encrypted result do anything with it after ? A comparison between
encrypted results looks in this case unlikely to yeld a result like: "yes
the password is good".

Thanks,
Lian

> -Original Message-
> From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 27, 2003 1:41 AM
> To: [EMAIL PROTECTED]
> Cc: 'Mysql'
> Subject: Re: Can we crypt passwords on MySQL
>
>
> It's not recommended because in MySQL 4.1, it returns a different result
> each time PASSWORD() is used, with the same input string.
> For MD5/SHA1 function, take a look here :
>
> http://www.mysql.com/doc/en/Miscellaneous_functions.html
>
>   Jocelyn
>
>
>
>
> - Original Message -
> From: "development" <[EMAIL PROTECTED]>
> To: "'Jocelyn Fournier'" <[EMAIL PROTECTED]>
> Cc: "'Mysql'" <[EMAIL PROTECTED]>
> Sent: Thursday, June 19, 2003 12:38 AM
> Subject: RE: Can we crypt passwords on MySQL
>
>
> Jocelyn
>
> Why is that not recommended ?
>
> Do you have any links for how to encrypt the password with MD5 ?
>
> Freddie
>
>
> -Original Message-
> From: Jocelyn Fournier [mailto:[EMAIL PROTECTED]
> Sent: Freitag, 27. Juni 2003 00:19
> To: [EMAIL PROTECTED]; Grégoire Dubois
> Cc: 'Mysql'
>
> Hi,
>
> Using PASSWORD function to crypt password is not recommended, it's mainly
> used for internal mysql password encryption.
> Prefer using MD5/SHA1 functions to encrypt a password.
>
> Regards,
>   Jocelyn
>
>
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: "Grégoire Dubois" <[EMAIL PROTECTED]>
> Cc: "'Mysql'" <[EMAIL PROTECTED]>
> Sent: Thursday, June 19, 2003 12:14 AM
> Subject: RE: Can we crypt passwords on MySQL
>
>
> > There is a PASSWORD('your_clear_text_password_here') function
> you can use
> > wherever you define a new password. See the manual for more.
> >
> > Lian
> >
> >
> > > -Original Message-
> > > From: Grégoire Dubois [mailto:[EMAIL PROTECTED]
> > > Sent: Thursday, June 19, 2003 12:55 AM
> > > Cc: 'Mysql'
> > > Subject: Can we crypt passwords on MySQL
> > >
> > >
> > > Hi all,
> > >
> > > Is it possible to crypt the passwords on MySQL. If yes, how does it
> > > work, and how is it to be implemented.
> > > Any link would be great.
> > >
> > > Thank you.
> > > Grégoire Dubois.
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: reinstall mysql

[csebe]

Please specify OS, and previous install type (package, rpm, dist, compiled,
etc.)

And by the way, when/why you do "make distclean" during the new install?

Lian

> -Original Message-
> From: saad al-hajeri [mailto:[EMAIL PROTECTED]
> Sent: Sunday, June 22, 2003 11:34 AM
> To: [EMAIL PROTECTED]
> Subject: reinstall mysql
>
>
> I would like to remove old mysql ,and install it again.
>
> I remove the old directory,Then I tryed to install it again .When
> I reach step make it give this error.
>
> [EMAIL PROTECTED] mysql-standard-4.0.13-pc-linux-i686]# make distclean
> make: *** No rule to make target `distclean'.  Stop.
>
> My Question.
> 1.How can I remove old mysql.
> 2.I need clear steps to install it again.
>
> Regard
> Saad al-Hajeri
>
>
>
> _
> Get an email address your friends will never forget... FREE!
> Become [EMAIL PROTECTED] at http://www.emailaccount.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Textfile to a 2 column mysql database

[csebe]

To pinpoint the solution here it is an one liner ;-)

perl -n -e 'chomp; print "INSERT INTO YourTable (question,answer) VALUES
(\"$tmp\",\"$_\");\n" if ($|--); $tmp=$_' test.txt > test.sql

It's a "hybrid" perl solution which requires feeding in MySQL afterwards and
it disregards special characters.

Lian


> -Original Message-
> From: gerald_clark [mailto:[EMAIL PROTECTED]
> Sent: Monday, June 23, 2003 4:42 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: Textfile to a 2 column mysql database
>
>
> Unless, of course, you have quotes in your data.
> With perl you can use the quote() function to ensure the whole
> line gets in.
>
> Christopher Knight wrote:
>
> >OR, if you are good at vi,
> >
> >you can insert a ' at the begining and end of every line (if you
> dont have
> >any 's in the file)
> >then put a , at the end of every odd line
> >then join every other line
> >
> >the put a   "insert into blah (question, answer) values
> ( "   at the
> >begin of every line
> >and then a ");" at the end of every line
> >
> >Then you hopefully have a file full of insert statements and you can just
> >feed it into a mysql client.
> >If I missed a step or added one by accident or even got one
> wrong, go ahead
> >and fix it and pretend
> >I told you correctly. ;-)
> >
> >The problem with my solution, is that if you have any wierd
> characters, they
> >arent escaped.
> >
> >or you could just write a perl program
> >
> >chris
> >
> >-Original Message-
> >From: gerald_clark [mailto:[EMAIL PROTECTED]
> >Sent: Monday, June 23, 2003 8:21 AM
> >To: O.S. Bos
> >Cc: [EMAIL PROTECTED]
> >Subject: Re: Textfile to a 2 column mysql database
> >
> >
> >Write a quick perl program.
> >
> >O.S. Bos wrote:
> >
> >
> >
> >>Hi there,
> >>
> >>I have a textfile that I want to get inserted into a database. The
> >>textfile consists of Questions and Answers. 1st line is a question. 2nd
> >>line of the textfile is the answer. And so on...
> >>
> >>What is the best way to import these lines into the database with 2
> >>columns. 1 column for the questions and one for the answers?
> >>
> >>Thx!
> >>Unox
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Getting the unique auto-increment primary key after an insert

[csebe]

The good ol' LAST_INSERT_ID() function is what you probably need. Check it
out in manual.

Lian

> -Original Message-
> From: Matt Hyne [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 24, 2003 5:58 AM
> To: [EMAIL PROTECTED]
> Subject: Getting the unique auto-increment primary key after an insert
>
>
>
> Hi Folks, needs a little advice.
>
> I have a table that contains a unique auto-incrementing primary key
> called 'id' (eg 1,2,3,4...).   When inserting data, I let mySQL allocate
> the value that goes into this field.
>
> Now I want to insert a new row into this table and get the 'id' value
> that mySQL has allocated (so I can then do some more work such as
> attaching a filename called 'id'.xxx).
>
> Can someone suggest a good way to do this ?  I thought of maybe doing a
> "SELECT MAX(id) FROM table" first and then using id+1 as the new 'id'
> for the next entry but this is frought with danger as someone could come
> along and insert a new entry between my SELECT and INSERT.
>
> Because 'id' is the only guaranteed unique column, I cannot think of a
> way to do a SELECT after an INSERT to get back the 'id' of the last
> thing inserted.
>
> Can anyone help ?
>
> Matt
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Getting the unique auto-increment primary key after an insert

[csebe]

As stated only few days ago on this list, the LAST_INSERT_ID() is relevant
per connection, i.e. it returns the correct last inserted id of the current
connection, disregarding other possible inserts done through other parallel
connections. So each connection has its own counter for this.

HTH,
Lian
www.programEz.net

> -Original Message-
> From: Matt Hyne [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 24, 2003 9:07 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: Getting the unique auto-increment primary key after an
> insert
>
>
>
> Thanks,
>
> I found it but I wasn't sure if it could be changed between my INSERT
> and the SELECT LAST..() by another instance of my application.
>
> I'll suck-it-and-see !
>
> Matt
>
> [EMAIL PROTECTED] wrote:
>
> > The good ol' LAST_INSERT_ID() function is what you probably need.
> Check it
> > out in manual.
> >
> > Lian
> >
> >> -Original Message-
> >> From: Matt Hyne [mailto:[EMAIL PROTECTED]
> >> Sent: Tuesday, June 24, 2003 5:58 AM
> >> To: [EMAIL PROTECTED]
> >> Subject: Getting the unique auto-increment primary key after an
> insert
> >>
> >>
> >>
> >> Hi Folks, needs a little advice.
> >>
> >> I have a table that contains a unique auto-incrementing primary key
> >> called 'id' (eg 1,2,3,4...).   When inserting data, I let mySQL
> allocate
> >> the value that goes into this field.
> >>
> >> Now I want to insert a new row into this table and get the 'id' value
> >> that mySQL has allocated (so I can then do some more work such as
> >> attaching a filename called 'id'.xxx).
> >>
> >> Can someone suggest a good way to do this ?  I thought of maybe doing
> a
> >> "SELECT MAX(id) FROM table" first and then using id+1 as the new 'id'
> >> for the next entry but this is frought with danger as someone could
> come
> >> along and insert a new entry between my SELECT and INSERT.
> >>
> >> Because 'id' is the only guaranteed unique column, I cannot think of
> a
> >> way to do a SELECT after an INSERT to get back the 'id' of the last
> thing inserted.
> >>
> >> Can anyone help ?
> >>
> >> Matt
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Problem setting/activating password

[csebe]

I don't know if this apply when using GRANT but have you tried afterwards:
> flush privileges;

HTH,
Lian




> -Original Message-
> From: Riaan Oberholzer [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 25, 2003 9:19 AM
> To: [EMAIL PROTECTED]
> Subject: Problem setting/activating password
> 
> 
> Hi,
> 
> I am created a user by using:
> 
> GRANT ALL ON *.* TO username@"%" IDENTIFIED BY
> "password"
> 
> I did this from the mysql command line tool, logged in
> as root. The command succeeds and the new user is
> created in the mysql.user table, but the password does
> not work. I can only use this new user if I use a
> empty string password (no password).
> 
> I also tried to do the SET PASSWORD afterwords, no
> luck. I then tried the Windows GUI / Administration to
> grant access and then set the password, but again,
> only an empty string password is accepted.
> 
> I am using mysql 4.0.13.
> 
> How do I actually get the password validation to be
> activated and instruct the server that the new user
> must provide his password?
> 
> __
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Problem setting/activating password

[csebe]

What about using the username only instead of username@"%" ?
According to manual (section 7.34) it should be identic in behaviour: 
[...]
The simple form user is a synonym for user@"%". 
[...]

Does it do the same?

Lian
P.S. Andy, sorry for posting by mistake to you. ;-|


> -Original Message-
> From: Riaan Oberholzer [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 25, 2003 9:19 AM
> To: [EMAIL PROTECTED]
> Subject: Problem setting/activating password
> 
> 
> Hi,
> 
> I am created a user by using:
> 
> GRANT ALL ON *.* TO username@"%" IDENTIFIED BY
> "password"
> 
> I did this from the mysql command line tool, logged in
> as root. The command succeeds and the new user is
> created in the mysql.user table, but the password does
> not work. I can only use this new user if I use a
> empty string password (no password).
> 
> I also tried to do the SET PASSWORD afterwords, no
> luck. I then tried the Windows GUI / Administration to
> grant access and then set the password, but again,
> only an empty string password is accepted.
> 
> I am using mysql 4.0.13.
> 
> How do I actually get the password validation to be
> activated and instruct the server that the new user
> must provide his password?
> 
> __
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL 4.0.13 GRANT syntax

[csebe]

update is a reserved word in MySQL. Choose another username.

Lian Sebe
Freelance Analyst-Programmer
www.programEz.net

> -Original Message-
> From: Adam Lawrence [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 25, 2003 5:10 PM
> To: [EMAIL PROTECTED]
> Subject: MySQL 4.0.13 GRANT syntax
>
>
> I am attempting to modify the GRANT table using the syntax
> specified in the
> MySQL 4.0.13 documentation, and am getting error messages claiming the
> syntax is incorrect. (I'm running MySQL on Windows 98, by the way.) I used
> mysqlc with root access.
>
> mysql> USE mysql;
> Database changed
> mysql> GRANT SELECT ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'update';
> ERROR 1064: You have an error in your SQL syntax. Check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> '[EMAIL PROTECTED] IDENTIFIED BY 'update'' at line 1
> mysql>
>
> >From the manual, the syntax:
>
> GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
> ON {tbl_name | * | *.* | db_name.*}
> TO user_name [IDENTIFIED BY [PASSWORD] 'password']
> [, user_name [IDENTIFIED BY 'password'] ...]
> [REQUIRE
> NONE |
>   [{SSL| X509}]
>   [CIPHER cipher [AND]]
>   [ISSUER issuer [AND]]
>   [SUBJECT subject]]
> [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
>   MAX_UPDATES_PER_HOUR # |
>   MAX_CONNECTIONS_PER_HOUR #]]
>
> ... and the following examples:
>
> mysql> GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED]
> -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
> mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
> -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
> mysql> GRANT RELOAD,PROCESS ON *.* TO [EMAIL PROTECTED];
> mysql> GRANT USAGE ON *.* TO [EMAIL PROTECTED];
>
> It appears that my syntax is consistant with the examples provided in the
> 4.0.13 documentation. The root account, of course, has full
> privileges with
> GRANT.
>
> Any ideas?
>
> --
> Adam Lawrence
> Sustaining Engineering
>  Tectrol Inc.
> --
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: user privileges question

[csebe]

> -Original Message-
> From: Paul DuBois [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 26, 2003 1:26 AM
> To: michael young; [EMAIL PROTECTED]
> Subject: Re: user privileges question
>
>
> At 18:02 -0400 6/25/03, michael young wrote:
> >Hi,
> >   I am creating a small web based program for users to access
> >the data in the datbases. New databases will be added in later. I
> >want to create a user to access these databases with certain rights,
> >not all rights. Will I have to grant this user right to each
> >database as I add them in or is there a way to give this user rights
> >to all databases (EXCEPT for mysql DB) including DB's I'll add
> >later. Other than the mysql database there is no database on this
> >server that this user should be elcluded from. What is the best way
> >to handle this?
>
> Grant the user privileges for each database.  There is no syntax for
> "all except".
>
> >
> >Thanks,
> > Michael
>
>
> --
> Paul DuBois, Senior Technical Writer
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
> Are you MySQL certified?  http://www.mysql.com/certification/
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

Victoria Reznichenko said few days ago, MySQL reads the privileges table
sequentially and bails out as soon as it finds a match.
(http://www.mysql.com/doc/en/Connection_access.html,
and for our case: http://www.mysql.com/doc/en/Request_access.html)

Therefore, I reckon the logical thing would be first to deny that user's
access to the "mysql" db, then to allow access for the same user, to
everything else.

So my soultion would be:
In the user table give TheUser "N" on every field.
In the db table you should have 2 rules similar to:

Host | Db  | User | Select_priv | Insert_priv |...
--
%| mysql   | TheUser |N|N|
%| %   | TheUser |Y|N|
localhost | mysql   | TheUser |N|N|
localhost | %   | TheUser |Y|N|
...

The point is to have 2 records for each host (be it % or a specific one),
for the same user.
According to the sorting rules defined in docs, this should work.

Any validation/comments of this logic are highly welcomed ;-)

Bests,
Lian Sebe
Freelance Analyst-Programmer
www.programEz.net




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: incorrect SUM() results

[csebe]

Hi all,

> -Original Message-
> From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 27, 2003 3:18 PM
> To: [EMAIL PROTECTED]
> Subject: Re: incorrect SUM() results
>
>
> Shaun Callender <[EMAIL PROTECTED]> wrote:
> >
> > I'm using mySQL 4.0 trying to solve what I think is a simple problem.
> >
> > to calculate the balance by account #
> > can this be done without the use of temporary tables ?
> >
> > TABLE 1 contains
> > ACCOUNT #, INVOICE #, INV TOTAL
> >
> > TABLE 2 contains
> > there can be multiple payment records associated with an invoice
> > ACCOUNT #, INVOICE #, PAYMENT, PAYMENT DATE
> >
> > example
> > TABLE 1 : ABCCORP, 1000, 500$
> > TABLE 1 : ABCCORP, 1001, 300$
> >
> > TABLE 2 : ABCCORP, 1000, 150$, 2003-01-01
> > TABLE 2 : ABCCORP, 1000,  50$, 2003,02-01
> > TABLE 2 : ABCCORP, 1000,  50$, 2003,03-01
> >
> > SQL STMT
> > select TABLE1.accountno,
> >   SUM(TABLE1.invtotal) as INVTOTAL,
> >   SUM(TABLE2.PAYMENT) as PAYMENT
> >   SUM(TABLE1.invtotal ) - SUM( TABLE2.PAYMENT) as BALANCE
> > LEFT JOIN TABLE2 ON TABLE2.invoiceno = TABLE1.invoiceno
> > GROUP BY TABLE1.accountno
> > ORDER BY TABLE1.accountno
> >
> > the result set will show which is wrong.
> > ABCCORP,1800$,200$,1600$
> >
> > It should be
> > ABCCORP,800$,200$,300$
>
> It's not correct, because 800-200<>300, isn't it?

Moreover, 150+50+50 is 250 not 200.

>
> >
> > How can this be done correctly ?
>
> Here is what I got with your data:
>
> mysql> select table1.*, table2.* from table1 left join table2 on
> table2.invoice=table1.invoice;
> +-+-+---+-+-+-++
> | account | invoice | total | account | invoice | payment | pdate  |
> +-+-+---+-+-+-++
> | ABCCORP |1000 |   500 | ABCCORP |1000 | 150 | 2003-06-27 |
> | ABCCORP |1000 |   500 | ABCCORP |1000 |  50 | 2003-06-27 |
> | ABCCORP |1000 |   500 | ABCCORP |1000 |  50 | 2003-06-27 |
> | ABCCORP |1001 |   300 | NULL|NULL |NULL | NULL   |
> +-+-+---+-+-+-++
> 4 rows in set (0.00 sec)
>
> If I add SUM() functions and GROUP BY clause to the SELECT
> statement I should get SUM(table1.total)=500+500+500+300=1800,
> SUM(table2.payment)=150+50+50=250, 1800-250=1550.
>
> mysql> select table1.account, sum(table1.total) as total,
> sum(table2.payment) as payment,
> sum(table1.total)-sum(table2.payment) as balance from table1 left
> join table2 on table2.invoice=table1.invoice group by
> table1.account order by table1.account;
> +-+---+-+-+
> | account | total | payment | balance |
> +-+---+-+-+
> | ABCCORP |  1800 | 250 |1550 |
> +-+---+-+-+
> 1 row in set (0.00 sec)
>
> It's correct.

>From the SQL Cartesian point of view ;-) However it's unusable for the
business, isn't it?

>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
><___/   www.mysql.com

I took it as a personal challenge in my "trying to master the JOIN
statements". Until now, all what I've got is however a "per account_no & per
invoice" situation, like this:

SELECT table1.accountno,table1.invoiceno,invtotal AS Invoiced,sum(payment)
AS Paid, invtotal-sum(payment) AS Balance
FROM table1 LEFT JOIN table2
USING (invoiceno)
GROUP BY accountno,invoiceno;

Which displays (for the supplied data):
+---+---+--+--+-+
| accountno | invoiceno | Invoiced | Paid | Balance |
+---+---+--+--+-+
| ABC   |  1000 |  500 |  250 | 250 |
| ABC   |  1001 |  300 |0 | 300 |
+---+---+--+--+-+
And that's a good report for the business.
I'm somehow pesimistic about doing the requested totals in only one
statement, but I'm trying to do it without temporary tables at least...

Bests,
Lian


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: perl error when execute mysqlaccess

[csebe]

This more a perl question.
You need to install CGI.pm for perl.
The CGI module should be available from RPMs.

HTH,
Lian



> -Original Message-
> From: Iago Sineiro [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 27, 2003 1:32 PM
> To: MySql Mail List
> Subject: perl error when execute mysqlaccess
>
>
> Hi.
>
> When I execute the perl script mysqlaccess I receive the following error
>
> Can't locate CGI.pm in @INC (@INC contains:
> /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0
> /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi
> /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl
> /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi
> /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl
> /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 .) at
> ./mysqlaccess line 530.
> BEGIN failed--compilation aborted at ./mysqlaccess line 530.
>
> Any idea.
>
> The Linux box is a RedHat 9 in where I installed mysql-4.0.13 using binary
> tar.gz package.
>
> Iago Sineiro
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Problem using TEMPORARY TABLE

[csebe]

It's probably a matter of granting CREATE TEMPORARY TABLE privilege for the
"hardware" user.

HTH,
Lian

> -Original Message-
> From: Andrey Mishenin [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 03, 2003 8:26 PM
> To: [EMAIL PROTECTED]
> Subject: Problem using TEMPORARY TABLE
>
>
>
> I've got a problem using temporary tables. Tring to make one
> (mysql> CREATE TEMPORARY TABLE ... ) I recieve an error message:
>
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to
> database 'hardware'
> At the mean time all other sql requests work correctly. How can I
> handle this? Please help.
>
> ___
> йНППЕЯОНМДЕМр фСПМЮК МНБНИ ЩОНУХ сЙПЮХМШ
> http://www.korrespondent.net/zhurnal
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Delete user/no access to mysql db

[csebe]

The REVOKE command should do most of the job for you, which is removing the
privileges for a specific user. See the manual for more.

However, the user remains defined in the "user" table (while having no
privileges), until one with access, deletes him/her manually.

HTH,
Lian

> -Original Message-
> From: Stephan Samuel [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 07, 2003 5:39 PM
> To: [EMAIL PROTECTED]
> Subject: Delete user/no access to mysql db
>
>
> Hi.
>
> I'm using MySQL on my web host. I'm a dba of the db's that I create, but I
> have no access to the mysql db. I can create users (GRANT USAGE
> ON my_db ...
> works), but I don't know how to get rid of them.
>
> I searched the manual, and the only reference to removing users
> that I could
> find was deleting records from mysql.user. Is there another way?
>
> Thanks,
> Stephan Samuel
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: access denied

[csebe]

What's user are you logged with when you're trying these commands? It looks
like you specified no user when launching the mysql console. Try with user
root if you have access to it.
You don't have enough rights to perform your commands without a user
specified.

Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

> -Original Message-
> From: Helen [mailto:[EMAIL PROTECTED]
> Sent: Friday, July 18, 2003 4:57 AM
> To: [EMAIL PROTECTED]
> Subject: access denied
>
>
> mysql> CREATE DATABASE csttrainrep
>  -> ;
> ERROR 1044: Access denied for user: '@localhost' to database
> 'csttrainrep'
> mysql> GRANT ALL ON csttrainrep. TO helen
>  -> ;
> ERROR 1064: You have an error in your SQL syntax.  Check the manual
> that corresponds to your MySQL server version for the right syntax to
> use near 'TO helen' at line 1
> mysql> GRANT ALL ON csttrainrep.* TO helen;
> ERROR 1044: Access denied for user: '@localhost' to database
> 'csttrainrep'
> mysql>
>
>
> how do i change this?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Design decision

[csebe]

Hi everyone,

Just wanted your expert opinion on the following:

I'm implementing an authorization system with user/group permissions stored
in a database. I have a Users table and a Group table, identical in
structure:
mysql> desc users;
mysql> desc groups;
+---+-+
| Field | Type|
+---+-+
| id| int(11) |
| name  | varchar(30) |
+---+-+

Now, my question is "How to store BEST the relations between users and
groups?".

Solution 1. I use a separate table with this structure:
mysql> desc users2groups;
+-+-+
| Field   | Type|
+-+-+
| idUser  | int(11) |
| idGroup | int(11) |
+-+-+
and I add one record for each user <--> group mapping. So a SELECT will
return potentially many rows for one group or one user.

Solution 2. I construct and maintain a string separated by colons (let's
say) for each group. So in the users2groups I'd have for example:
| idGroup | idUser   |
| 123 | 2:3:4:8:9:10 |

Similary, since I need also user-to-group lookups I construct a string for
the "group membership of a user" so I can have in the same table:
| idGroup | idUser   |
| 123 | 2:3:4:8:9:10 |
| 123:456 | 4|

Solution 3. Similary to Solution 2 but using the initial tables extended
with one more field to accomodate the membership constructed string like:
+---+-+
| Field | Type|
+---+-+
| id| int(11) |
| name  | varchar(30) |
| member_of | text|
+---+-+

In Solution 1 I have multiple rows returned. In solution 2,3 I have only
one.
Solution 1 is scalable however Solution 2,3 can reach (potentially) the
limits of the column specification (unlikely though).

Assuming I'm interested in maximum speed at the authorization moment (and
not at administrative moment), and that I'll have a big number of users and
groups, and I access the database via Perl (so no problem to
construct/deconstruct strings), what do you think is the best solution?

Thank you for your time,

Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

"I'm not mad. I've been in bad mood for the last 30 years..."


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysql queries with numbers

[csebe]

Do it in MySQL if you can. "Use the force" ;-)
Besides MIN() and MAX() there are also statistical functions implemented as:
AVG(), STDDEV() etc.

See the manual for all functions.


Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

> -Original Message-
> From: Taylor Lewick [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 28, 2003 6:00 PM
> To: [EMAIL PROTECTED]
> Subject: mysql queries with numbers
>
>
> Question for everyone, I have a database with about 30-60 days
> worth of information.  Much of it numerical.
> Can and should I use SQL to run quereis that will return me the
> high/low and average for the time frame, as well as standard deviation,
> or should I just get all of the info into an array via perl and
> let it do the crunching?
>
> Thanks,
> Taylor
>
> 
>   Please Note
> The information in this E-mail message is legally privileged
> and confidential information intended only for the use of the
> individual(s) named above. If you, the reader of this message,
> are not the intended recipient, you are hereby notified that
> you should not further disseminate, distribute, or forward this
> E-mail message. If you have received this E-mail in error,
> please notify the sender. Thank you
> *
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: rights to create table, select, then drop table..

[csebe]

Hi there,

I tested your setup and wors fine for me. I issued this commands as mysql's
root:

grant usage on *.* to [EMAIL PROTECTED] identified by 'nelu';
grant select,insert,update,create,drop on test.* to nelu@'%';

Then I logged in as nelu with:

mysql -u nelu -p test

create table test1 (id int, nume text);
insert into test1 values (1,"cico");
select * from test1;
+--+--+
| id   | nume |
+--+--+
|1 | cico |
+--+--+
drop table test1;

So it's clearly working.

Therefore I recommend you to do a
select * from mysql.user;
to see if you have other "rules" that cancel the good ones somehow.

In addition, this is from manual and maybe useful for future security:
"You cannot specify that a user has privileges to create or drop tables in a
database but not to create or drop the database itself."

HTH,

Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

> -Original Message-
> From: Jeff McKeon [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 28, 2003 10:43 PM
> To: [EMAIL PROTECTED]
> Subject: FW: rights to create table, select, then drop table..
>
>
> Nobody has any ideas on this one?
>
> Jeff
>
> -Original Message-
> From: Jeff McKeon
> Sent: Friday, July 25, 2003 3:23 PM
> To: [EMAIL PROTECTED]
> Subject: rights to create table, select, then drop table..
>
>
> I have a need to get data from the db that requires me to
>
> 1) do a select and create a new table with the results
> 2) run a query against that new table
> 3) drop the new table
>
> I have a script on my server that does this using the root account that
> has all on *.* for the db. It works fine.
>
> I now want to get these results on a web page.
> I want to create a new db user for my .php web page to use to connect to
> the db that only has the needed priviledges on that specific db to get
> the job done.
>
> what priviledges do I need to give that user?
>
> currently I have the following but the user can't even log into the db
> from the command line..
>
> mysql> show grants for user;
> +---
> -+
> | Grants for [EMAIL PROTECTED] |
> +---
> -+
> | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD
> '6fe4c0ab2cf30ae3' |
> | GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' |
> +---
> -+
> 2 rows in set (0.00 sec)
>
> when I do a "show grants for user", what should I see to allow what I
> want?
>
> Thanks,
>
> Jeff McKeon
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Design decision

[csebe]

Lin,
thanks for your input.
Indeed I forgot to mention there is a many-to-many relation between users
and groups.

I'm inclined though to use Solution 3. My main concern with 2 and 3 was not
to exceed the column allocated space for the concatenated string, when it
grows with the number of users in a group. I calculated the space
requirement for storing a string resulted by 1 milion user IDs each
separated by a character. It's taken about 8 milion bytes to store the whole
string. A MediumText gives me 16 MB so I think I'm pretty much covered.

As I said my main requirements would be speed when a Select is performed. So
I prefer to do some additional logic in perl and to retrieve faster results
in one Select returning only one row. Hope I'm not wrong ;-)

Thanks again,

Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

> -Original Message-
> From: Lin Yu [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 28, 2003 9:33 PM
> To: [EMAIL PROTECTED]; 'mysqllist'
> Subject: RE: Design decision
>
>
> Lian,
>
> Between your design solutions (1) and (3), you need to decide,
> from the logical
> business requirement, whether the nature of the relationship
> between user and
> group is one-to-many (a group may have many users, and each user
> may belong to
> exactly one group) or many-to-many (a group may have many users,
> and each user
> may belong to multiple groups). For the former, use Solution (3), for the
> latter, use Solution (1). Granted, Solution (3) is a subset of
> Solution (1), but
> requires more resources which might be a waste if you only need
> represent a
> one-to-many relationship.
>
> Your solution (2) has no restriction on the granularity of the
> relationship
> i.e., it can support both; it all depends on your implementation
> outside SQL,
> thus is not really a DB schematic means. In this case, the relationship is
> actually interpreted and maintained by your application program,
> not by DBMS.
>
> In making a choice between Solution (2) and the other two you
> need to consider
> the performance difference and code maintenance.
>
> Best regards,
> 
> Lin
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 28, 2003 10:22 AM
> To: mysqllist
> Subject: Design decision
>
> Hi everyone,
>
> Just wanted your expert opinion on the following:
>
> I'm implementing an authorization system with user/group
> permissions stored
> in a database. I have a Users table and a Group table, identical in
> structure:
> mysql> desc users;
> mysql> desc groups;
> +---+-+
> | Field | Type|
> +---+-+
> | id| int(11) |
> | name  | varchar(30) |
> +---+-+
>
> Now, my question is "How to store BEST the relations between users and
> groups?".
>
> Solution 1. I use a separate table with this structure:
> mysql> desc users2groups;
> +-+-+
> | Field   | Type|
> +-+-+
> | idUser  | int(11) |
> | idGroup | int(11) |
> +-+-+
> and I add one record for each user <--> group mapping. So a SELECT will
> return potentially many rows for one group or one user.
>
> Solution 2. I construct and maintain a string separated by colons (let's
> say) for each group. So in the users2groups I'd have for example:
> | idGroup | idUser   |
> | 123 | 2:3:4:8:9:10 |
>
> Similary, since I need also user-to-group lookups I construct a string for
> the "group membership of a user" so I can have in the same table:
> | idGroup | idUser   |
> | 123 | 2:3:4:8:9:10 |
> | 123:456 | 4|
>
> Solution 3. Similary to Solution 2 but using the initial tables extended
> with one more field to accomodate the membership constructed string like:
> +---+-+
> | Field | Type|
> +---+-+
> | id| int(11) |
> | name  | varchar(30) |
> | member_of | text|
> +---+-+
>
> In Solution 1 I have multiple rows returned. In solution 2,3 I have only
> one.
> Solution 1 is scalable however Solution 2,3 can reach (potentially) the
> limits of the column specification (unlikely though).
>
> Assuming I'm interested in maximum speed at the authorization moment (and
> not at administrative moment), and that I'll have a big number of
> users and
> groups, and I access the database via Perl (so no problem to
> construct/deconstruct strings), what do you think is the best solution?
>
> Thank you for your time,
>
> Lian Sebe, M.Sc.
> Freelance Analyst-Programmer
> www.programEz.net
>
> "I'm not mad. I've been in bad mood for the last 30 years..."
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: GRANT command question

[csebe]

Hi there,

If you go to the MySQL manual (chapter 4.3.1) you'll see that GRANT ALL...
does not include the granting of privileges to others. So you must use
something like:

GRANT ALL [...] WITH GRANT OPTION;

The manual has also downloadable versions.

Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

> -Original Message-
> From: Charles Cantrell [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 28, 2003 11:55 PM
> To: '[EMAIL PROTECTED]'
> Subject: GRANT command question
>
>
> I have recently set up mySQL on a Mandrake release of Linux (Version 7 of
> Mandrake, I believe), using the binary 4.0.13 standard release.
>
> The set up and start up all were normal, as far as I could tell, with no
> warnings or error messages.
>
> In nearly all respects, the database appears to be running as expected. I
> have the book "PHP and mySQL Web Development" by Luke Welling and Laura
> Thomson, and have been working through the examples there.
>
> The question I have is about the GRANT command, and the apparent results
> there. I don't know if my problem is one of understanding what the results
> should be, or if I have another problem.
>
> I am attempting to set up an administrative user, that has the same
> privileges as root. I have been able to set up a user that appears to have
> all privileges, with the exception of GRANT privileges to other
> users. This
> user can create new database tables, insert records, modify them, delete
> them and so on.
>
> But, when this user tries to GRANT privileges on any database,
> even ones it
> has created, an 'access denied' error is generated.
>
> I am obviously missing some part of the process, but I am not sure what it
> is. Can you explain the piece I am missing? I would appreciate it
> very much.
> Thanks.
>
> Charles Cantrell
>
> PS: I submitted a much more detailed message showing the GRANT
> table set up
> and so on, but it was rejected by the filters. If this information would
> help someone answer my question, let me know and I can send it directly,
> maybe.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Message

[csebe]

It's a perl DBI question not a MySQL one but anyways...

$sth is just a statement handle and if you print it it gives you a hash ref,
so is not an error.

After preparing you should do an execute on the statement than fetch the
results. Better, use a prepare-execute-fetch all-in-one command like:

selectall_arrayref("select * from ...")
As its name says, it returns an array ref with your results.

"man DBI" will give you every detail on this however you should get familiar
with perl data types and references manipulation.

Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

> -Original Message-
> From: upscope [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 05, 2003 12:41 AM
> To: [EMAIL PROTECTED]
> Subject: Message
>
>
> I am just learning mysql and I'm trying to write a cgi to update the
> database from a form. Also just learning  Perl. I have the
> following script
> started that is called by an HTML form. I get the follwing message when I
> execute it. Where can I find the message descriptions.
>
> DBI::st=HASH(0x1b31f28)
>
> this is my script so far:
> #!c:\perl\bin\perl
>
> use DBI();
> print "Content-type:text/html\n\n";
>
> #Connect to database members.
>
> $database = "members";
> $table = "members";
>
> $dbh = DBI->connect("DBI:mysql:$database")or
> dienice("Can't connect:$DBI::errstr");
>
> $sth = $dbh->prepare("select
> membername,address,city,state,zipcode,phonenumber from members")or
> dienice("Can't prepare statement: ",$dbh->errstr);
>
> print $sth;
>
> exit;
>
> upscope
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Scripts with arguments

[csebe]

If it's going to be a simple batch file then use %1, %2, ... inside the
script to get the parameters passed to it.

Or better install & use a real scripting language like perl. ;-)

Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

> -Original Message-
> From: Enrique Andreu [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 29, 2003 12:36 PM
> To: [EMAIL PROTECTED]
> Subject: Scripts with arguments
>
>
> Hello.
> I have to do an script to create MySQL users, the name
> of the new user have to be a parameter of the script.
> I have no idea, someone can helps me.
> I'm an NT user.
>
>
> Thanks:
>Enrique Andreu
>
> ___
> Yahoo! Messenger - Nueva versión GRATIS
> Super Webcam, voz, caritas animadas, y más...
> http://messenger.yahoo.es
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: PHP Search + MySQL Query

[csebe]

Did you ORed or ANDed the LIKE clauses?
Please provide the combined statement that didn't return results, the one
with more than one LIKE clauses.


Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

> -Original Message-
> From: Brown, Chris [mailto:[EMAIL PROTECTED]
> Sent: Saturday, August 30, 2003 1:28 PM
> To: [EMAIL PROTECTED]
> Subject: PHP Search + MySQL Query
>
>
> Hi all,
>
> In learning PHP and expanding my (seriously limited) SQL knowledge, I
> need to write a query that will look for one or more (can be any number)
> of the following fields held in the table:
>
> +---+-+
> | Field | Type|
> +---+-+
> | id| int(6)  |
> | svc   | varchar(20) |
> | sev   | tinyint(1)  |
> | dsc   | varchar(60) |
> | inits | varchar(20) |
> | dst   | date|
> | tst   | time|
> | den   | date|
> | ten   | time|
> | res   | tinyint(1)  |
> +---+-+
>
> I was attempting this using a SELECT command, a WHERE clause, and lots
> of LIKE statements.  If search criteria had been entered, the LIKE
> statement went along the lines of LIKE('%criteria%'), and if it was
> empty, was left with a LIKE('%').
>
> Yes, it's tragic SQL (one day they may send me on a proper SQL
> course...) and naturally, it doesn't work.  The result given is "Empty
> Set", even if I only put in one part of search criteria, and if I run a
> manual SELECT id,dsc FROM problems WHERE $field
> LIKE('%this_criteria_exists%'); it works fine.
>
> I went back and made the query up manually field by field, one field at
> a time, adding each LIKE statement in query after query, and found that
> after field DSC it failed, with the empty set result.
>
> Can anyone point me into the direction of a) Why it fails and b) A
> proper query that'll make this work??
>
> All help very gratefully received.
>
> Many thanks in advance
>
> --
> Chris Brown
> e: ckb@:nospam:.maxnet.eu.org, m: 
>
> Never argue with an idiot, he'll just lower you to his level and beat
> you with experience...
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]