RE: Working with FIND_IN_SET
You can do the same thing with like:
where concat(",",myField,",") like "%,$searchString,%"
this will of cause be somewhat slow, how about normalizing your database:
header(id,fields)
type(id,dataset)
header(1,)
type(1,black)
type(1,dark)
...
now you can select * from header,type where (id=id) and dataset
in("...","...") group by id
So long
Martin
-Original Message-
From: Thalis A. Kalfigopoulos [mailto:[EMAIL PROTECTED]]
Sent: Sunday, April 08, 2001 11:42 AM
To: John Hart
Cc: [EMAIL PROTECTED]
Subject: Re: Working with FIND_IN_SET
On Sun, 8 Apr 2001, John Hart wrote:
I am working on a rather large database project, in which I am making a
text column that contains different data, seperated by commas. What I need
to do is run a query where I will return only the datasets that contain any
of sets that partially, or fully match any data in the set...
For example, if the dataset contains:
dark, black, small
I want to be able to return this data if the query contains ran contains
any of the following matches:
dark
black
small
sma
bla
...etc...
I do not, however, want a full LIKE search with wildcards, because I do
not want to return the set if they were to search on 'all' (which would
match smALL).
Is there an easy way to do this, or am I forced into matching entire words
in a set?
I appreciate any help anyone could offer me... Thank you...
John
If I got you correctly, you might consider RLIKE where you define the
mathing string as a regular expressions. So in you case the regexp would be
something like:
RLIKE ".*, YOUR_QUERY_STRING_HERE.*, .*"
regards,
thalis
-
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: Mysql speed :)
But does that really change anything on the database server side ?
In other words is MySQL doing anything with respect to using prepared
statements ?
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 3:01 AM
To: Heikki Tuuri
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
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
Database Design Question
Hello,
I have a table design question relating to best practice and performance.
Monthy is somewhat of a SQL guru and so may be many others on this list,
this may actually help a lot of folks out there:
Example:
I want to get a listing of all applicable rates for a delivery:
a rate can be defined in three ways:
a. the entire state
b. for a certain area code
c. for a specific zip
to make things worth ideally I want to be able to say all of state="CA" is
$2, but areacode="714" is $1
and zip="92649" is $.50
the tables look like this right now
city(zip varchar(10),areacode varchar(10),state varchar(2),name varchar(30))
rate(zip varchar(10),areacode varchar(10),state varchar(2),decimal(10,2))
Data
rate('92647','','',.5)
rate('','714','',1)
rate('','','CA',2)
I was thinking of maybe using the same logic as for the permission system
inside of MySql with % or something as wildcards but left it empty sting for
now. Which also leads me to another question as I would like to setup
permissions for various users to be able to maintain only certain rates.
This can be done on the application level. I am wondering if I need to OR
should create a query in the application doing the various or conditions or
if there is a more elegant solution.
Note: I left out the NOT NULL and the index definition.
Martin Hubert
Freightgate - New Dimensions in e-Logistics (sm)
Visit us at http://www.freightgate.com
Email: [EMAIL PROTECTED]
Phone: +714.799.2833 Fax: +714.799.0100
-
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
Database Design Question
Design Question
Hello,
I have a table design question relating to best practice and performance.
Monthy is somewhat of a SQL guru and so may be many others on this list,
this may actually help a lot of folks out there:
Example:
I want to get a listing of all applicable rates for a delivery:
a rate can be defined in three ways:
a. the entire state
b. for a certain area code
c. for a specific zip
to make things worth ideally I want to be able to say
carrier1:
all of state="CA" is $2,
but areacode="714" is $1
and zip="92649" is $.50
carrier2:
but areacode="310" is $1
and zip="92647" is $.50
and zip="92648" is $.60
and zip="92649" is $.70
the tables look like this right now
city(zip varchar(10),areacode varchar(10),state varchar(2),name varchar(30))
rate(zip varchar(10),areacode varchar(10),state varchar(2),carrier
varchar(5),decimal(10,2))
Data
rate('92647','','','c1',.5)
rate('','714','','c1',1)
rate('','','CA','c1',2)
rate('92647','','','c2',.5)
rate('92648','','','c2',.5)
rate('92649','','','c2',.5)
rate('','310','','c2',1)
if I now query for all rates in CA it would select * from city where
city.state="CA"
but what's the most elegant way to do the join to get it back as one
resultSet ?
I was thinking of maybe using the same logic as for the permission system
inside of MySql with % or something as wildcards but left it empty sting for
now. Which also leads me to another question as I would like to setup
permissions for various users to be able to maintain only certain rates.
This can be done on the application level. I am wondering if I need to OR
should create a query in the application doing the various or conditions or
if there is a more elegant solution.
Note: I left out the NOT NULL and the index definition.
Thanks SO MUCH
Martin Hubert
Freightgate - New Dimensions in e-Logistics (sm)
Visit us at http://www.freightgate.com
Email: [EMAIL PROTECTED]
Phone: +714.799.2833 Fax: +714.799.0100
-
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
