Re: Partial and inexact matches
SELECT FROM WHERE LIKE '%' the % is a wildcard and will match up anything after the chars. you can also put one infront to match ANYTHING with in it. ---8>< Many thanks for taking time to answer an "easy" question. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Partial and inexact matches
SELECT FROM WHERE LIKE '%' the % is a wildcard and will match up anything after the chars. you can also put one infront to match ANYTHING with in it. hth Tim "Computers are like Air-con, open windows and they stop working!" Robert S wrote: Probably a real n00b question. I am writing a simple contact management database with MySQL (version 3.23.49-8.9, Debian Woody). I'd like users to be able to enter the first few characters of a contact's name to do a search, and I'd like the option of doing inexact matches or phonetic matches. What sort of SELECT/WHERE statement do I need to do these things? I've combed the documentation and used Google but haven't found any such thing. -- signature.asc Description: OpenPGP digital signature
Partial and inexact matches
Probably a real n00b question. I am writing a simple contact management database with MySQL (version 3.23.49-8.9, Debian Woody). I'd like users to be able to enter the first few characters of a contact's name to do a search, and I'd like the option of doing inexact matches or phonetic matches. What sort of SELECT/WHERE statement do I need to do these things? I've combed the documentation and used Google but haven't found any such thing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hebrew
Using 4.1 Though I had it cracked ? I created a new table and set the Charset to Hebrew and the field Collation to hebrew_general_ci I then uploaded data to the database using MS Acess (with an ODBC connection) In MSAccess on my PC the Hebrew comes through fine. However when I open the newly created table using MSAccess or phpMyAdmin the Hebrew is represented with question marks. Pointers were I went wrong would be appreciated Regards John Berman -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.6.2 - Release Date: 04/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BINARY LIKE '..' just does not work
On Saturday, March 05, 2005 1:46 PM [GMT+1=CET], Gleb Paharenko <[EMAIL PROTECTED]> wrote: Hello. The problem I think is in indexes. After dropping the unique key the query gives the correct result. I've reported a bug: http://bugs.mysql.com/bug.php?id=8976 You may add your comments there. I have added my comment. TOO BAD that you specifed NON-CRITICAL. It is critical, becauase after priovider upgraded to 4.1 all our engines stopped searching for products and texts because dictionary table for word and term search id build on binary char. I can only guess how many sites are now broken in this manner. Artem "Artem Kuchin" <[EMAIL PROTECTED]> wrote: MySQL: 4.1.8a OS: FreeBSD 5.3 The problem is that BINARY LIKE '..' returns no rows no matter what specified in '..' however BINARY field_name LIKE '..' work, but I cannot use it because this does not use index (EXPLAINs are bellow). According to documentation BINARY LIKE shoud work. But it does not. Smells like a bug. Defaukt charset for mysql (it is specified at compile time) is cp1251 if it matters (test uses only latin chars). Below is the test case: (maybe someone else could run it on there server and tell me if it works and also report mysql server version where it was tested). CREATE TABLE voc2 ( id int(10) unsigned NOT NULL default '0', word char(32) binary NOT NULL default '', counter int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY i_vocabulary_word (word) ) TYPE=MyISAM; insert into voc2 values(1,'falama',1); insert into voc2 values(2,'lagraf',1); insert into voc2 values(3,'folka',1); insert into voc2 values(4,'pofik',1); mysql> select * from voc2 where word like 'f%'; Empty set (0.00 sec) mysql> select * from voc2 where word like '%f'; +++-+ id | word | counter | +++-+ 2 | lagraf | 1 | +++-+ 1 row in set (0.00 sec) mysql> select * from voc2 where word like '%f%'; +++-+ id | word | counter | +++-+ 1 | falama | 1 | 2 | lagraf | 1 | 3 | folka | 1 | 4 | pofik | 1 | +++-+ 4 rows in set (0.00 sec) mysql> select * from voc2 where word like BINARY 'f%'; Empty set (0.01 sec) mysql> select * from voc2 where word like BINARY '%f'; +++-+ id | word | counter | +++-+ 2 | lagraf | 1 | +++-+ 1 row in set (0.00 sec) mysql> select * from voc2 where word like BINARY '%f%'; +++-+ id | word | counter | +++-+ 1 | falama | 1 | 2 | lagraf | 1 | 3 | folka | 1 | 4 | pofik | 1 | +++-+ 4 rows in set (0.00 sec) mysql> select * from voc2 where binary word like 'f%'; +++-+ id | word | counter | +++-+ 1 | falama | 1 | 3 | folka | 1 | +++-+ 2 rows in set (0.00 sec) mysql> explain select * from voc2 where binary word like 'f%'; ++-+---+--+---+--+-+--+--+-+ id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+--+-+ 1 | SIMPLE | voc2 | ALL | NULL | NULL |NULL | NULL | 4 | Using where | ++-+---+--+---+--+-+--+--+-+ 1 row in set (0.00 sec) mysql> explain select * from voc2 where word like binary 'f%'; ++-+---+---+---+---+-+--+--+-+ id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+---+-+--+--+-+ 1 | SIMPLE | voc2 | range | i_vocabulary_word | i_vocabulary_word | 32 | NULL |1 | Using where | ++-+---+---+---+---+-+--+--+-+ 1 row in set (0.00 sec) This sucks. Regards, Artem -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which file to backup
Yes , I'm using innodb. Is it necesary to do a mysqladmin flush-logs before making a copy of ib_logfile* or must the server go down to do a safe copy of the ib_logfiles ? thanks Mauricio On Fri, 2004-09-17 at 09:08, Egor Egorov wrote: > Mauricio Pellegrini <[EMAIL PROTECTED]> wrote: > > > I'm trying to backup the 'binary log' and don't know which file is it. > > > > these are the files I see in my datadir > > > > -rw-rw 1 mysql users175K Sep 14 14:21 hrrgp01-bin.07 > > -rw-rw 1 mysql users345K Sep 14 15:03 hrrgp01-bin.08 > > -rw-rw 1 mysql users1.6M Sep 14 15:18 hrrgp01-bin.09 > > -rw-rw 1 mysql users3.9M Sep 15 09:06 hrrgp01-bin.10 > > -rw-rw 1 mysql users 29K Sep 15 09:11 hrrgp01-bin.11 > > -rw-rw 1 mysql users676K Sep 15 15:58 hrrgp01-bin.12 > > -rw-rw 1 mysql users4.4M Sep 16 11:10 hrrgp01-bin.16 > > -rw-rw 1 mysql mysql322M Sep 16 11:10 ibdata1 > > -rw-rw 1 mysql users 40M Sep 16 11:10 ib_logfile0 > > > > The thing is I intend to stop the server at a certain time and then > > make a backup copy of the actual binary log. > > > > But which is the name of the file I should use into my script? > > Backup *-bin.* and ib_logfile* if you use InnoDB. > > > > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations - new twist on a familiar problem...
Hello. Please, send us an output of the following statements: show variables like '%char%'; show variables like '%collation%'; "Stembridge, Michael" <[EMAIL PROTECTED]> wrote: > When running this simple query: > SELECT fileid FROM test WHERE ecn='0' > > > MySQL yields this error: > #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and > (utf8_general_ci,COERCIBLE) for operation '=' > > > The table collation is latin1_swedish_ci. > > I ran SHOW CREATE TABLE fileid and found this: DEFAULT CHARSET=latin1 at > the end. > > Here is the table layout: > > fileid int(10) > datereceiveddate > scn varchar(11) latin1_swedish_ci > ecn varchar(11) latin1_swedish_ci > > > Here is the problem: The table collation was once utf8_general_ci (due to > mistake), it was switched back to latin1_swedish_ci. Ever since then I've > had this error. > > Suggestions for getting the table back in line? > > Thanks, > Michael > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Logging failed attempts
Hello. You may use the General Query Log. See: http://dev.mysql.com/doc/mysql/en/query-log.html MrExecutive <[EMAIL PROTECTED]> wrote: > Hello Guys, > > I am using mySQL 4.1 for windows. How do i go about logging all failed > login attempts? > > Thanks! > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BINARY LIKE '..' just does not work
Hello. The problem I think is in indexes. After dropping the unique key the query gives the correct result. I've reported a bug: http://bugs.mysql.com/bug.php?id=8976 You may add your comments there. "Artem Kuchin" <[EMAIL PROTECTED]> wrote: > MySQL: 4.1.8a > OS: FreeBSD 5.3 > > The problem is that BINARY LIKE '..' returns no rows no > matter what specified in '..' however BINARY field_name LIKE '..' > work, but I cannot use it because this does not use index (EXPLAINs are > bellow). According to documentation BINARY LIKE shoud work. But it > does not. > > Smells like a bug. > > Defaukt charset for mysql (it is specified at compile time) is cp1251 if > it matters (test uses only latin chars). > > Below is the test case: > (maybe someone else could run it on there server and tell me if it works > and also report mysql server version where it was tested). > > CREATE TABLE voc2 ( > id int(10) unsigned NOT NULL default '0', > word char(32) binary NOT NULL default '', > counter int(10) unsigned NOT NULL default '0', > PRIMARY KEY (id), > UNIQUE KEY i_vocabulary_word (word) > ) TYPE=MyISAM; > > insert into voc2 values(1,'falama',1); > insert into voc2 values(2,'lagraf',1); > insert into voc2 values(3,'folka',1); > insert into voc2 values(4,'pofik',1); > > mysql> select * from voc2 where word like 'f%'; > Empty set (0.00 sec) > > mysql> select * from voc2 where word like '%f'; > +++-+ > | id | word | counter | > +++-+ > | 2 | lagraf | 1 | > +++-+ > 1 row in set (0.00 sec) > > mysql> select * from voc2 where word like '%f%'; > +++-+ > | id | word | counter | > +++-+ > | 1 | falama | 1 | > | 2 | lagraf | 1 | > | 3 | folka | 1 | > | 4 | pofik | 1 | > +++-+ > 4 rows in set (0.00 sec) > > mysql> select * from voc2 where word like BINARY 'f%'; > Empty set (0.01 sec) > > mysql> select * from voc2 where word like BINARY '%f'; > +++-+ > | id | word | counter | > +++-+ > | 2 | lagraf | 1 | > +++-+ > 1 row in set (0.00 sec) > > mysql> select * from voc2 where word like BINARY '%f%'; > +++-+ > | id | word | counter | > +++-+ > | 1 | falama | 1 | > | 2 | lagraf | 1 | > | 3 | folka | 1 | > | 4 | pofik | 1 | > +++-+ > 4 rows in set (0.00 sec) > > > mysql> select * from voc2 where binary word like 'f%'; > +++-+ > | id | word | counter | > +++-+ > | 1 | falama | 1 | > | 3 | folka | 1 | > +++-+ > 2 rows in set (0.00 sec) > > mysql> explain select * from voc2 where binary word like 'f%'; > ++-+---+--+---+--+-+--+--+-+ > | id | select_type | table | type | possible_keys | key | key_len | ref | > rows | Extra | > ++-+---+--+---+--+-+--+--+-+ > | 1 | SIMPLE | voc2 | ALL | NULL | NULL |NULL | NULL | > 4 | Using where | > ++-+---+--+---+--+-+--+--+-+ > 1 row in set (0.00 sec) > > > mysql> explain select * from voc2 where word like binary 'f%'; > ++-+---+---+---+---+-+--+--+-+ > | id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra | > ++-+---+---+---+---+-+--+--+-+ > | 1 | SIMPLE | voc2 | range | i_vocabulary_word | i_vocabulary_word | > 32 | NULL |1 | Using where | > ++-+---+---+---+---+-+--+--+-+ > 1 row in set (0.00 sec) > > > This sucks. > > Regards, > Artem > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert data
Michael, John, ALL;
Thank you! Thanks you! My errors of "local_host" and "if $dbh->err" fix it.
I am able to insert and select data.
Thank you ALL,
Jerry
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Friday, March 04, 2005 5:41 PM
To: John Trammell
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]; Gerald Preston
Subject: Re: insert data
Right. First, I think the logic is flawed. We should successfully
prepare() or die. Period. If the call to prepare() failed ($sth is undef),
we should not making dying conditional on yet another value.
More to the point, this line is actually the cause of the problem. (Sorry I
didn't see it earlier.) You've run into the precedence rules:
my $sth = $dbh->prepare( $sql ) or die $dbh->errstr if $dbh->err;
is read as
(my $sth = $dbh->prepare( $sql ) or die $dbh->errstr) if $dbh->err;
That is, it is equivalent to
if ($dbh->err)
{
$sth = $dbh->prepare( $sql ) or die $dbh->errstr;
}
Since the connect succeeded, $dbh->err is undef, so we never even call
prepare! Hence, $sth is undef when we get to execute, and you get the error
message. I expect this is what Joe (John Doe) was trying to tell us
earlier.
The simplest solution would be to drop the "if $dbh->err". That is, change
to
my $sth = $dbh->prepare( $sql ) or die $dbh->errstr;
John's suggestion (below) is better still, as it adds helpful detail to the
error message when there is one (though I don't see the need to make it a
separate line of code).
Michael
John Trammell wrote:
> Gerald Preston wrote:
> [snip]
>
>>my $sth = $dbh->prepare( $sql ) or die $dbh->errstr if $dbh->err;
>
> [snip]
>
> Regardless of other problems you may be having, I think you're not
> doing what you want to do here. How about instead:
>
> my $sth = $dbh->prepare($sql);
> $sth || die "Error preparing sth from '$sql': ", $dbh->errstr;
>
--
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]
