Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 04:00, Hank hes...@gmail.com wrote:
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:
INSERT into table VALUES (,'');
which would cause a syntax error. If you include the single quotes, it
becomes:
INSERT into table VALUES ('','')
which won't cause a syntax error, but might cause some logic errors in the
database. The choice is yours.
Thanks, that is a good point. I would actually prefer errors to arise
on insert then a potentially inconsistent database or bad data. I
should definitely learn to use stored procedures, I know.
That said, I do go to great lengths to validate my data. What is an
alpha value? I do check is_numeric() and null, of course.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 07:47, Reindl Harald h.rei...@thelounge.net wrote: what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); all other things in the context of hand-written queries are all the nice one we read every day in the news and should NOT recommended because the next beginner reading this makes all the mistakes again Thanks, Reindi. I actually do something like this (simplified, in real code I use an array and a small custom function): $mysqlName=mysql_real_escape_string($name); Then, in the query I can see that all my variables start with $mysql* so I know that they have been sanitized. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here It's a matter of opinion. I never said the data wasn't sanitized (it is). But sometimes calculated values or bugs in PHP code end up with a null variable field. I was just suggesting the choice between two errors -- one syntax which will generate a hard failure of the query and likely whatever page, or a soft logical error, which won't. In either case, I have error trapping to catch both types of errors and alert me to them. I prefer the errors to be logical ones and not syntax errors. $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); I think what you posted is ugly style which makes reading the actual SQL in PHP code much harder to read and debug. The data validation should take place elsewhere long before it gets to constructing the SQL statement.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 19.09.2011 16:55, schrieb Hank: what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here It's a matter of opinion. I never said the data wasn't sanitized (it is). But sometimes calculated values or bugs in PHP code end up with a null variable field. I was just suggesting the choice between two errors -- one syntax which will generate a hard failure of the query and likely whatever page, or a soft logical error, which won't. In either case, I have error trapping to catch both types of errors and alert me to them. I prefer the errors to be logical ones and not syntax errors. $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); I think what you posted is ugly style which makes reading the actual SQL in PHP code much harder to read and debug. The data validation should take place elsewhere long before it gets to constructing the SQL statement. it is not because it is clear that it is sanitized instead hope and pray thousands of layers somewhere else did it - for a inline-query the best solution, if you are using a framework you will never have the insert into at this place! what i meant as ugly is that you are somewhere writing an inline-query and are not sure if it is a number or not - so it is NOT sanitized before because if you tell me it is you sanitze does not work if you get a non-integer at this point and you sanitze-method has to throw the error long before if it is really working signature.asc Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 18:11, Reindl Harald h.rei...@thelounge.net wrote:
it is not because it is clear that it is sanitized instead hope and pray
thousands of layers somewhere else did it - for a inline-query the best
solution, if you are using a framework you will never have the insert into
at this place!
what i meant as ugly is that you are somewhere writing an inline-query and
are not sure if it is a number or not - so it is NOT sanitized before
because if you tell me it is you sanitze does not work if you get a
non-integer
at this point and you sanitze-method has to throw the error long before
if it is really working
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
The resulting SQL query is easy to read, and I know that everything is
escaped. No operations are ever to be performed on $M. I need to look
into a way of making it immutable (add and read only). I could do it
with an object but I prefer an array. Actually, an array wrapped in an
object could perform the escaping itself, making me doubly sure that
some other dev didn't forget to escape while playing with the code.
By the way, I've never gotten a godd explanation about why to wrap the
variables in PHP MySQL queries with curly brackets. I don't even
remember where I picked up the habit. Does anybody here know?
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:11, Hank hes...@gmail.com wrote:
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
Furthermore, I don't want to clutter the query with
mysql_real_escape_string() all over the place. Therefore, I escape
everything before it goes into the array, so I know that all the data
in the array have been escaped. I can then use the array members in
the query.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 20.09.2011 00:39, schrieb Dotan Cohen:
On Tue, Sep 20, 2011 at 01:11, Hank hes...@gmail.com wrote:
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
Furthermore, I don't want to clutter the query with
mysql_real_escape_string() all over the place. Therefore, I escape
everything before it goes into the array, so I know that all the data
in the array have been escaped. I can then use the array members in
the query
i would use a samll class holding the db-connection with insert/update-methods
pass the whole record-array, lokk what field types are used in the table
and use intval(), doubleval() or mysql_real_escape-String
so you never write insert into inline and if the function is well desigend you
can throw the whole $_POST to it without thinikng about datatypes and ignore
automatically hidden-fields which are not used in the database
having as simple class with $db-fetch_all(), $db-insert, $db-update
has also the benefit that you can easy switch between mysql/mysqli
without the big overhead of a whole abstraction-layer and extend
this class with often used methods to make development faster
and much more stable as dealing the whole time with inline code
a basic class is written in few hours and can be extended whenever
needed - i wrote one ten years ago and heavily use it these days
as all the years
public function insert($table, array $data)
{
// so here you know where to look for fieldnames/fieldtypes
// prepare the data aray with escaping/intval()/doubleval()
// and generate finally the insert
//
// as return value use 0 on errors or the insert-id
}
signature.asc
Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
I want to be sure that all variables in the query are escaped. I don't trust myself or anyone else to do this to every variable right before the query: $someVar=mysql_real_escape_string($someVar); But you're doing exactly that right before the query anyway with: $M[username]=mysql_real_escape_string($username); You're just complicating things with the addition of an unneeded array. It seems much simpler and less cluttered to just do: $someVar=mysql_real_escape_string($someVar); before your insert. All you are doing is changing $someVar to $M[...] and then using $M[...] in the query. I really don't see the difference or benefit of using your array here. Both methods are doing exactly the same thing, except one is more convoluted. Now on the other hand, if you have several elements in the array $M to be inserted, and have a function like this to escape them all at once: for each ($M as $val) $val= mysql_real_escape_string($val); then your method starts to make more sense. -Hank
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 02:09, Hank hes...@gmail.com wrote:
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
But you're doing exactly that right before the query anyway with:
$M[username]=mysql_real_escape_string($username);
You're just complicating things with the addition of an unneeded array. It
seems much simpler and less cluttered to just do:
$someVar=mysql_real_escape_string($someVar);
before your insert. All you are doing is changing $someVar to $M[...]
and then using $M[...] in the query. I really don't see the difference or
benefit of using your array here. Both methods are doing exactly the same
thing, except one is more convoluted.
I know that this has been escaped:
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
This, I don't know if it has been escaped or not:
$query=INSERT INTO table (username) VALUES ('{$username}');
Now on the other hand, if you have several elements in the array $M to be
inserted, and have a function like this to escape them all at once:
for each ($M as $val) $val= mysql_real_escape_string($val);
then your method starts to make more sense.
I could foreach it. Or not. It doesn't matter. The point is having
known-safe variables being used in the query, which are also easy to
read.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote:
i would use a samll class holding the db-connection with insert/update-methods
pass the whole record-array, lokk what field types are used in the table
and use intval(), doubleval() or mysql_real_escape-String
so you never write insert into inline and if the function is well desigend
you
can throw the whole $_POST to it without thinikng about datatypes and ignore
automatically hidden-fields which are not used in the database
having as simple class with $db-fetch_all(), $db-insert, $db-update
has also the benefit that you can easy switch between mysql/mysqli
without the big overhead of a whole abstraction-layer and extend
this class with often used methods to make development faster
and much more stable as dealing the whole time with inline code
a basic class is written in few hours and can be extended whenever
needed - i wrote one ten years ago and heavily use it these days
as all the years
public function insert($table, array $data)
{
// so here you know where to look for fieldnames/fieldtypes
// prepare the data aray with escaping/intval()/doubleval()
// and generate finally the insert
//
// as return value use 0 on errors or the insert-id
}
You are right, using a class has many benefits. I might do that on a
future project. Thanks.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote: i would use a samll class holding the db-connection with insert/update-methods pass the whole record-array, lokk what field types are used in the table and use intval(), doubleval() or mysql_real_escape-String By the way, the database connection is include()ed from a file outside the webroot. This way if Apache is ever compromised or for whatever reason stops parsing the PHP, the resulting code returned to the browser won't have the daabase info (especially the password). -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 20.09.2011 01:23, schrieb Dotan Cohen: On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote: i would use a samll class holding the db-connection with insert/update-methods pass the whole record-array, lokk what field types are used in the table and use intval(), doubleval() or mysql_real_escape-String By the way, the database connection is include()ed from a file outside the webroot. This way if Apache is ever compromised or for whatever reason stops parsing the PHP, the resulting code returned to the browser won't have the daabase info (especially the password) if stops parsing - yes, but not relevant if it is in a include if the machine is compromised it does not matter someone could read your files can read also the include outside the docroot signature.asc Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Personally I don't use any quotes for the numeric types, and single quotes for
everything else. Ie:
UPDATE mytable SET int_field = 5 WHERE id = 3;
SELECT id FROM mytable WHERE int_field = 5;
UPDATE mytable SET varchar_field = 'Test' WHERE id = 3;
SELECT id FROM mytable WHERE varchar_field = 'Test';
UPDATE mytable SET datetime_field = '2011-09-18 00:00:00' WHERE id = 3;
If you are using PHP you may need to escape the single quotes if your php
string is in single quotes:
$query = 'UPDATE mytable SET varchar_field = \'Test\' WHERE id = 3'
But if you are doing interpolation and your string is in double quotes, you
should not need to escape:
$query = UPDATE $table_name SET varchar_field = 'Test' WHERE id = 3
Some people prefer to use back quotes on field names such as:
$query = UPDATE `mytable` SET `varchar_field` = 'Test' WHERE `id` = 3
And some people prefer to put numeric fields in quotes as well, although it is
not necessary:
UPDATE mytable SET int_field = '5' WHERE id = '3';
On 9/18/11 5:00 AM, Dotan Cohen wrote:
I am somewhat confused as to the proper way to place quotes around
arguments in INSERT and SELECT statements. I also don't see where this
is made explicit in the fine manual.
If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from PHP?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from PHP?
Is it the same for decimal and float?
If the column is type varchar, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from PHP?
Is it the same for text and blob?
Also, in PHP often I see code examples with the variable wrapped in
curly brackets, inside single quotes. What is the purpose of the curly
brackets? Here is such an example:
$query=INSERT INTO names (name) VALUE ('{$userName}');
If the column is type datetime, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from PHP?
What if I am using the NOW() function?
If the column is type set, is it preferable to use single or double
quotes on INSERT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on SELECT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on INSERT from PHP?
If the column is type set, is it preferable to use single or double
quotes on SELECT from PHP?
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com wrote: Personally I don't use any quotes for the numeric types, and single quotes for everything else. Ie: Thanks, Brandon. I understand then that quote type is a matter of taste. I always use double quotes in PHP and I've only recently started putting ticks around table and column names. I'll stick to your convention of no quotes around numerics and single quotes around everything else. Have a terrific week! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 12:28 PM, Dotan Cohen dotanco...@gmail.com wrote:
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com wrote:
Personally I don't use any quotes for the numeric types, and single
quotes
for everything else. Ie:
Thanks, Brandon. I understand then that quote type is a matter of
taste. I always use double quotes in PHP and I've only recently
started putting ticks around table and column names. I'll stick to
your convention of no quotes around numerics and single quotes around
everything else.
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:
INSERT into table VALUES (,'');
which would cause a syntax error. If you include the single quotes, it
becomes:
INSERT into table VALUES ('','')
which won't cause a syntax error, but might cause some logic errors in the
database. The choice is yours.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 19.09.2011 03:00, schrieb Hank:
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error
what ugly style - if it is not numeric and you throw it to the database
you are one of the many with a sql-injection because if you are get
ivalid values until there you have done no sanitize before and do not here
$sql=INSERT into table VALUES ( . (int)$id . ,' .
mysql_real_escape_string($val) . ');
or using a abstraction-layer (simple self written class)
$sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val)
. ');
all other things in the context of hand-written queries are all the nice one we
read every
day in the news and should NOT recommended because the next beginner reading
this makes all
the mistakes again
signature.asc
Description: OpenPGP digital signature
on select statements
what is the difference between this: SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';EXECUTE STMT USING @a; and this: 'SELECT * FROM tbl LIMIT 1 And then this: SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows; and this: 'SELECT * FROM tbl LIMIT 1,5
Re: on select statements
There is no logical difference. There's a tiny bit of overhead in preparing and executing the statements, but if you have a query where the limit is variable, a prepared statement might be better than coding within an application, because then when ANY user or application connects it can use that prepared statement. -Sheeri On 4/3/06, Yemi Obembe [EMAIL PROTECTED] wrote: what is the difference between this: SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';EXECUTE STMT USING @a; and this: 'SELECT * FROM tbl LIMIT 1 And then this: SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows; and this: 'SELECT * FROM tbl LIMIT 1,5 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: showing zero counts in select statements with group by
Sorry, two errors, should be ... ...) OR m.repyear IS NULL GROUP BY c.year PB - Chris Fonnesbeck wrote: On 2/17/06, Peter Brawley [EMAIL PROTECTED] wrote: Chris, Your WHERE clause is weeding out the NULL joined entries. Try something like... select m.repyear as repyear, count(*) as count from years y left join mortality m on y.year=m.repyear where ( m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') group by m.repyear; Thanks for the recommendation; unfortunately, it does not work: mysql select - m.repyear as repyear, - count(*) as count - from years y - left join mortality m on y.year=m.repyear - where ( - m.region like 'Southwest' - and m.repyear1985 - and m.dthcode!=4 - and (m.cause like '%red tide%' or m.remarks like '%red tide%') - ) OR m.repyear IS NULL - group by m.repyear; +-+---+ | repyear | count | +-+---+ |1994 | 1 | |1996 | 145 | |1997 |15 | |1999 |12 | |2000 |14 | |2001 |16 | |2002 |36 | |2003 |91 | |2004 | 5 | |2005 |52 | +-+---+ 10 rows in set (52.92 sec) Thanks, C. -- Chris Fonnesbeck + Atlanta, GA + http://trichech.us No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
showing zero counts in select statements with group by
I am trying to figure out how to get a comprehensive count of rows in a database by year. Unfortunately, when there are no rows for a particular year, the year is omitted from the result set, rather than showing up with a zero count: mysql select count(*) as count, repyear from mortality where region like 'Southwest' and repyear1985 and dthcode!=4 and (cause like '%red tide%' or remarks like '%red tide%') group by repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.09 sec) Looking at some online help, I understand that I am supposed to create a table with the years as entries, then run a left join with the same query in order to get my zeroes included. I went ahead and did this: mysql select * from years; +--+ | year | +--+ | 1986 | | 1987 | | 1988 | | 1989 | | 1990 | | 1991 | | 1992 | | 1993 | | 1994 | | 1995 | | 1996 | | 1997 | | 1998 | | 1999 | | 2000 | | 2001 | | 2002 | | 2003 | | 2004 | | 2005 | +--+ 20 rows in set (0.00 sec) However, running a left joined query using this table gives the same result: mysql select count(*) as count, m.repyear as repyear from years y left join mortality m on y.year=m.repyear where m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') group by m.repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.12 sec) So, I'm at a loss as to how to get a complete result set. I do find it a bit strange that sql returns an incomplete query as a default. Any ideas most welcome. -- Chris Fonnesbeck + Atlanta, GA + http://trichech.us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: showing zero counts in select statements with group by
Chris, Your WHERE clause is weeding out the NULL joined entries. Try something like... select m.repyear as repyear, count(*) as count from years y left join mortality m on y.year=m.repyear where ( m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') ) OR m.year IS NULL group by m.repyear; PB - Chris Fonnesbeck wrote: I am trying to figure out how to get a comprehensive count of rows in a database by year. Unfortunately, when there are no rows for a particular year, the year is omitted from the result set, rather than showing up with a zero count: mysql select count(*) as count, repyear from mortality where region like 'Southwest' and repyear1985 and dthcode!=4 and (cause like '%red tide%' or remarks like '%red tide%') group by repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.09 sec) Looking at some online help, I understand that I am supposed to create a table with the years as entries, then run a left join with the same query in order to get my zeroes included. I went ahead and did this: mysql select * from years; +--+ | year | +--+ | 1986 | | 1987 | | 1988 | | 1989 | | 1990 | | 1991 | | 1992 | | 1993 | | 1994 | | 1995 | | 1996 | | 1997 | | 1998 | | 1999 | | 2000 | | 2001 | | 2002 | | 2003 | | 2004 | | 2005 | +--+ 20 rows in set (0.00 sec) However, running a left joined query using this table gives the same result: mysql select count(*) as count, m.repyear as repyear from years y left join mortality m on y.year=m.repyear where m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') group by m.repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.12 sec) So, I'm at a loss as to how to get a complete result set. I do find it a bit strange that sql returns an incomplete query as a default. Any ideas most welcome. -- Chris Fonnesbeck + Atlanta, GA + http://trichech.us -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.10/263 - Release Date: 2/16/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: showing zero counts in select statements with group by
In the last episode (Feb 17), Chris Fonnesbeck said: I am trying to figure out how to get a comprehensive count of rows in a database by year. Unfortunately, when there are no rows for a particular year, the year is omitted from the result set, rather than showing up with a zero count: mysql select count(*) as count, repyear from mortality where region like 'Southwest' and repyear1985 and dthcode!=4 and (cause like '%red tide%' or remarks like '%red tide%') group by repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.09 sec) Looking at some online help, I understand that I am supposed to create a table with the years as entries, then run a left join with the same query in order to get my zeroes included. I went ahead and did this: [...] However, running a left joined query using this table gives the same result: mysql select count(*) as count, m.repyear as repyear from years y left join mortality m on y.year=m.repyear where m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') group by m.repyear; You probably want to group by y.year here, since for any year not in your mortality table, m.year will be null (thus grouping all your unused years together). Also, select count(m.repyear) (or any other field in m) instead of count(*), since * includes nulls (and would cause all your unused years to have a count of 1. If you remove the group by clause and look at the raw table generated by the left join it may make more sense. So, I'm at a loss as to how to get a complete result set. I do find it a bit strange that sql returns an incomplete query as a default. There's no way for mysql to have guessed that you wanted to see nonexistent values in your resultset. Since the query is only looking at a subset of the table, how would you even know which values were missing? Should it extend that number past 2005 to whatever the field's maximum value is? I assume it's just an integer field, so mysql couldn't have known it was a field storing a number known to be between 1986 and 2006. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not operator in Select statements
On 10/24/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: On Mon, 2005-10-24 at 06:09 +0200, Dotan Cohen wrote: How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; The above query is syntactically correct. If it didn't work for you, then your problem lies elsewhere. Perhaps post your error message? By the way, your question would have been answered by reading the manual... Jasper Bryant-Greene Thank you Jasper. I remember from the manual that this _should_be correct. But when it doesn't work I am inclinded to ask. I will go try to track down the cause of my disappointment with the query. Thank you. Dotan Cohen http://lyricslist.com/lyrics/artist_albums/332/mccartney_paul.php McCartney, Paul Song Lyrics
Not operator in Select statements
How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; $query = SELECT album, year FROM albums WHERE year NOT 1990 ORDER BY year ASC; Thanks in advance. Dotan Cohen http://technology-sleuth.com/question/what_is_a_cellphone.html
Re: Not operator in Select statements
On Mon, 2005-10-24 at 06:09 +0200, Dotan Cohen wrote: How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; The above query is syntactically correct. If it didn't work for you, then your problem lies elsewhere. Perhaps post your error message? By the way, your question would have been answered by reading the manual... -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server disconnects when executing simple Select statements++
At 11:07 AM 3/23/2005, V. M. Brasseur wrote: You say it usually crashes near the same record? Could you post the record information and also the query which is being run? Also, is there any information in your hostname.err file? If mysqld is bailing (and it appears that it is), it ought to be writing something useful to the error log. Cheers, --V mos wrote: I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike Thanks to everyone to posted to me publicly and privately on this problem. I finally traced the problem to a 3rd party set of MySQL components I was using for Delphi. It appears they have a bug in their MYSQL query component that caused a socket error if too many queries/second were executed and it momentarily caused the MySQL server to disconnect not only for my application, but all applications using the MySQL server. I was able to get around it by changing one of their properties. I can't believe I spent a day taking everything apart and twiddling with MYSQL settings and my program only to find the problem was with a 3rd party component. Sheesh! Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server disconnects when executing simple Select statements++
I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server disconnects when executing simple Select statements++
You say it usually crashes near the same record? Could you post the record information and also the query which is being run? Also, is there any information in your hostname.err file? If mysqld is bailing (and it appears that it is), it ought to be writing something useful to the error log. Cheers, --V mos wrote: I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server disconnects when executing simple Select statements++
mos [EMAIL PROTECTED] wrote on 03/23/2005 12:04:59 PM: I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike Please, PLEASE, tell me you are not creating and dropping a connection for each query within your loop. If you are, try rewriting your code create one connection early and drop it only when it's no longer useful. Always try to reuse an established connection as much as possible and always use the fewest number of connections you need to get the job done. Some statements (particularly extended INSERT statements) can exceed your server's max_allowed_packet size, which I have also seen cause that error. Also, check your MySQL server's error log to see if you are causing a condition you are not seeing reported back to your compiled code. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL server disconnects when executing simple Select statements++
Lost connection to MySQL server during query The last time I saw this mysqld was segfaulting on a specific query. The solution was to run an extended repair on the tables in question. http://dev.mysql.com/doc/mysql/en/repair-table.html I would reccommend against doing this until after hours as this will lock your MyISAM tables. Quoting mos [EMAIL PROTECTED]: I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with prepared select statements with parameters in where clause
Hi,
I'm having serious trouble getting prepared statements with bound parameters
in the where clause to work over the C API with MySQL 4.1.3. The Bugtracker on
mysql.com has similar bugs for 4.1.2, which are marked as closed and fixed in
4.1.3 so I wanted to make sure that I'm doing things correctly in my code
before fileing an official bug report. Operating system is Linux 2.6.
The query is declared as
SELECT UserID FROM users WHERE Login=? AND Password=?.
I should be getting exactly one row back from mysql, but mysql_stmt_fetch()
just returns MYSQL_NO_DATA.
The same prepared query but with explicitly hardcoded parameters works.
Thanks for any help.
The table in question looks like the following:
mysql show create table users\G
*** 1. row ***
Table: users
Create Table: CREATE TABLE `users` (
`UserID` bigint(20) unsigned NOT NULL auto_increment,
`Login` char(64) NOT NULL default '',
`Password` char(128) NOT NULL default '',
PRIMARY KEY (`UserID`,`Login`,`Password`),
UNIQUE KEY `Login` (`Login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And the code looks like this (altered from the example in section 21.2.7.5):
#include stdlib.h
#include stdio.h
#include string.h
#include mysql/mysql.h
#define STRING_SIZE 50
#define SELECT_SAMPLE SELECT UserID, Login, Password FROM users WHERE Login=? AND
Password=?
int main(void)
{
MYSQL *mysql;
MYSQL_STMT*stmt;
MYSQL_BINDbind[3]; /* results */
unsigned long length[3];
int row_count;
long long int int_data;
char login[STRING_SIZE];
char password[STRING_SIZE];
my_bool is_null[3];
MYSQL_BINDpbind[2]; /* parameters */
unsigned long plength[2];
char *pdata[2];
my_bool p_is_null[2];
mysql = mysql_init(NULL);
mysql_real_connect( mysql, localhost, myuser, mypassword,
mydb, 0, /tmp/mysql-4.1.sock, 0 );
/* Prepare a SELECT query to fetch data from test_table */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, mysql_stmt_init(), out of memory\n);
exit(0);
}
if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
{
fprintf(stderr, mysql_stmt_prepare(), SELECT failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
fprintf(stdout, prepare, SELECT successful\n);
plength[0] = plength[1] = STRING_SIZE * sizeof(char);
p_is_null[0] = p_is_null[1] = 0;
pdata[0] = (char*)malloc( STRING_SIZE * sizeof(char) );
pdata[1] = (char*)malloc( STRING_SIZE * sizeof(char) );
/* STRING PARAMETER */
pbind[0].buffer_type= MYSQL_TYPE_STRING;
pbind[0].buffer= (char *)pdata[0];
pbind[0].buffer_length=STRING_SIZE * sizeof(char);
pbind[0].is_null= p_is_null[0];
pbind[0].length= plength[0];
/* STRING PARAMETER */
pbind[1].buffer_type= MYSQL_TYPE_STRING;
pbind[1].buffer= (char *)pdata[1];
pbind[1].buffer_length= STRING_SIZE * sizeof(char);
pbind[1].is_null= p_is_null[1];
pbind[1].length= plength[1];
if( mysql_stmt_bind_param( stmt, pbind ) ) {
fprintf( stderr, mysql_stmt_bind_param() failed\n );
fprintf( stderr, %s\n, mysql_stmt_error( stmt ) );
exit(0);
}
mysql_real_escape_string( mysql, pdata[0], testuser1,
strlen(testuser1)*sizeof(char) );
plength[0] = strlen( pdata[0] ) + 1;
mysql_real_escape_string( mysql, pdata[1], bla, strlen(bla)*sizeof(char) );
plength[1] = strlen( pdata[1] ) + 1;
printf( Executing query with parameters %s and %s\n, pdata[0], pdata[1] );
/* Execute the SELECT query */
if (mysql_stmt_execute(stmt))
{
fprintf(stderr, mysql_stmt_execute(), failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
/* Bind the result buffers for all 4 columns before fetching them */
/* INTEGER COLUMN */
bind[0].buffer_type= MYSQL_TYPE_LONGLONG;
bind[0].buffer= (char *)int_data;
bind[0].is_null= is_null[0];
bind[0].length= length[0];
/* STRING COLUMN */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)login;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= is_null[1];
bind[1].length= length[1];
/* STRING COLUMN */
bind[2].buffer_type= MYSQL_TYPE_STRING;
bind[2].buffer= (char *)password;
bind[2].buffer_length= STRING_SIZE;
bind[2].is_null= is_null[2];
bind[2].length= length[2];
/* Bind the result buffers */
if (mysql_stmt_bind_result(stmt, bind))
{
fprintf(stderr, mysql_stmt_bind_result() failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
/* Now buffer all results to client */
if (mysql_stmt_store_result(stmt))
{
fprintf(stderr, mysql_stmt_store_result() failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
/* Fetch all rows */
row_count= 0;
fprintf(stdout, Fetching results ...\n);
while (!mysql_stmt_fetch(stmt))
{
row_count++;
fprintf(stdout, row %d\n, row_count);
/* column 1 */
fprintf(stdout,column1 (bigint) : );
if (is_null[0])
fprintf(stdout, NULL\n);
else
fprintf(stdout, %lld(%ld)\n, int_data, length[0]);
/* column 2 */
fprintf(stdout,
select statements inside functions?
Hi, I would like to know if functions will ever be able to issue SELECT statements, like procedures can. I want a function which can select multiple rows from another table, process those rows into a single scalar, and return that as its result. I want a function to do this so that I can use it in the column list of SELECT queries, rather than calling it like a procedure. A trivial example, showing the error saying I cant use SELECT in a function is below. - Will this be included in future, and is just missing now as functions are so new? I am using mysql 5.0.1-alpha (win32) Thanks, Mike. mysql delimiter | mysql create function test() - returns int - select 1234; - end - | ERROR 1301 (0A000): Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strings and variables in select statements
Hi, I would appreciate help with the following. I have a table where each of the values in a column consists of a combination of letters and numbers followed by a decimal then a single digit. Examples of the two types of entries that could be found in this column(accession_num) are: BQ877252.1 AA3588976.2 and possibly T84780 The entries above are found in a table called hs_identifiers. The hs_identifiers table has two columns accession_num and hs_id_num. Given an accession_num I want to be able to look up in the hs_identifiers table to look up for the corresponding hs_id_num. This would be a simple select statement for example Select hs_identifiers.hs_id_num from hs_identifiers, where (accession_num = (result of another select statement)._) or (accession_num=(result of another select statement)); The only thing is I am not sure what the syntax is for the part (accession_num = (result of another select statement)._) In the section (result of another select statement)._) I am trying to say the result concatenated with a period concatenated with one wild card character. For example: result of another select statement = BQ877252 period is '.' and wild card character 1,2,3, etc. The whole string concatenated together would be BQ877252.1 thanks, Annie. - Post your free ad now! Yahoo! Canada Personals
Re: Strings and variables in select statements
I'm a beginner so don't quote me. I beleive you need something like: Select hs_identifiers.hs_id_num from hs_identifiers, where (accession_num LIKE (result of another select statement)%); I beleive that should give you the first part and if there is anything after it. Respectfully, Ligaya Turmelle Annie Law [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, I would appreciate help with the following. I have a table where each of the values in a column consists of a combination of letters and numbers followed by a decimal then a single digit. Examples of the two types of entries that could be found in this column(accession_num) are: BQ877252.1 AA3588976.2 and possibly T84780 The entries above are found in a table called hs_identifiers. The hs_identifiers table has two columns accession_num and hs_id_num. Given an accession_num I want to be able to look up in the hs_identifiers table to look up for the corresponding hs_id_num. This would be a simple select statement for example Select hs_identifiers.hs_id_num from hs_identifiers, where (accession_num = (result of another select statement)._) or (accession_num=(result of another select statement)); The only thing is I am not sure what the syntax is for the part (accession_num = (result of another select statement)._) In the section (result of another select statement)._) I am trying to say the result concatenated with a period concatenated with one wild card character. For example: result of another select statement = BQ877252 period is '.' and wild card character 1,2,3, etc. The whole string concatenated together would be BQ877252.1 thanks, Annie. - Post your free ad now! Yahoo! Canada Personals -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strings and variables in select statements
To date, my understanding is that mysql do not support sub-query. My suggestion will be by using 2 sql statement. The first statement will select value from the reference table. The value retrieve from first statement can be used to construct the second statement. Your first sql will be like : a) Select a_col, b_col from table_a Your second sql will be like b) select * from table_b where table_b.fk_col in ( value_a_col1, value_a_col2, value_a_col3,...) hope above help. -Original Message- From: Ligaya Turmelle [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 09:25 To: [EMAIL PROTECTED] Subject: Re: Strings and variables in select statements I'm a beginner so don't quote me. I beleive you need something like: Select hs_identifiers.hs_id_num from hs_identifiers, where (accession_num LIKE (result of another select statement)%); I beleive that should give you the first part and if there is anything after it. Respectfully, Ligaya Turmelle Annie Law [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, I would appreciate help with the following. I have a table where each of the values in a column consists of a combination of letters and numbers followed by a decimal then a single digit. Examples of the two types of entries that could be found in this column(accession_num) are: BQ877252.1 AA3588976.2 and possibly T84780 The entries above are found in a table called hs_identifiers. The hs_identifiers table has two columns accession_num and hs_id_num. Given an accession_num I want to be able to look up in the hs_identifiers table to look up for the corresponding hs_id_num. This would be a simple select statement for example Select hs_identifiers.hs_id_num from hs_identifiers, where (accession_num = (result of another select statement)._) or (accession_num=(result of another select statement)); The only thing is I am not sure what the syntax is for the part (accession_num = (result of another select statement)._) In the section (result of another select statement)._) I am trying to say the result concatenated with a period concatenated with one wild card character. For example: result of another select statement = BQ877252 period is '.' and wild card character 1,2,3, etc. The whole string concatenated together would be BQ877252.1 thanks, Annie. - Post your free ad now! Yahoo! Canada Personals -- 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: complicated select statements and out of memory errors
Bernd Jagla wrote: Hi there, I have some rather complicated sql statements that seem to eat up all resources from mysqld. Once I have two of them running at the same time no one can log on any more. Simple queries involving distinct get a out of memory error. We have tried changing the index buffer size from 256M to 2G, no change. We have 7G of memory on an IRIX system with 8 cpus. No replication. Do you have any idea what is happening? Thanks a lot Bernd Bernd: The queries that are giving you trouble are not able to use a key, and I do not see a way to re-write them without re-organizing your data in some way. Perhaps you could add columns sum1 that will store substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) and sum2 that will store substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) + substring(replaced,10,1) + substring(replaced,11,1) + substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) and make a key on them. There might be a better way, but it is hard to tell without seeing your application. the sql statements look like this: insert into rule2 select id from text_uniq_bin where (substring(rev_rep,1,1)=1) AND ( substring(replaced,1,1) + substring(replaced,2,1) + substring(replaced,3,1) + substring(replaced,4,1) + substring(replaced,5,1) + substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) ) 2 AND (substring(replaced,3,1)=0) AND ( substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) ) 5 ; AND insert into rule3 select id from text_uniq_bin where (substring(rev_rep,1,1)=1) AND ( substring(replaced,1,1) + substring(replaced,2,1) + substring(replaced,3,1) + substring(replaced,4,1) + substring(replaced,5,1) + substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) ) 2 AND (substring(rev_rep,3,1)=0) AND ( substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) ) =5 AND (substring(rev_rep,2,1)=0) AND ( substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) + substring(replaced,10,1) + substring(replaced,11,1) + substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) ) 7 -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
complicated select statements and out of memory errors
Hi there, I have some rather complicated sql statements that seem to eat up all resources from mysqld. Once I have two of them running at the same time no one can log on any more. Simple queries involving distinct get a out of memory error. We have tried changing the index buffer size from 256M to 2G, no change. We have 7G of memory on an IRIX system with 8 cpus. No replication. Do you have any idea what is happening? Thanks a lot Bernd the sql statements look like this: insert into rule2 select id from text_uniq_bin where (substring(rev_rep,1,1)=1) AND ( substring(replaced,1,1) + substring(replaced,2,1) + substring(replaced,3,1) + substring(replaced,4,1) + substring(replaced,5,1) + substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) ) 2 AND (substring(replaced,3,1)=0) AND ( substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) ) 5 ; AND insert into rule3 select id from text_uniq_bin where (substring(rev_rep,1,1)=1) AND ( substring(replaced,1,1) + substring(replaced,2,1) + substring(replaced,3,1) + substring(replaced,4,1) + substring(replaced,5,1) + substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) ) 2 AND (substring(rev_rep,3,1)=0) AND ( substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) ) =5 AND (substring(rev_rep,2,1)=0) AND ( substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) + substring(replaced,10,1) + substring(replaced,11,1) + substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) ) 7
Re: Nested SELECT statements problem
Thanks for your input! First, I removed the quotation marks into the sub-query in order to fix syntax. Then, I tried to use different alliases from the main query. However, this doesn't work. I mean that mySQL return a syntax error. I checked the server version and it is 3.23.56. As someone else noticed, nested queries are not supported in mySQL until version 4 or so. Therefore, I assume that it would not work for me. Since I'm not the administrator of the mySQL server, I'm not able to upgrade it. So, I'm stucked with version 3.23.56. Now, someone suggested me to replace subqueries with something like this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html . However, I'm not able to figure out how to change my queries. Any idea? Le dimanche, 3 aoû 2003, à 01:07 Canada/Eastern, Lin Yu a écrit : One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur aut.IDAuteur AND book.IDLivre ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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: Nested SELECT statements problem
Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Le dimanche, 3 aoû 2003, à 12:41 Canada/Eastern, Pascal Délisle a écrit : Thanks for your input! First, I removed the quotation marks into the sub-query in order to fix syntax. Then, I tried to use different alliases from the main query. However, this doesn't work. I mean that mySQL return a syntax error. I checked the server version and it is 3.23.56. As someone else noticed, nested queries are not supported in mySQL until version 4 or so. Therefore, I assume that it would not work for me. Since I'm not the administrator of the mySQL server, I'm not able to upgrade it. So, I'm stucked with version 3.23.56. Now, someone suggested me to replace subqueries with something like this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html . However, I'm not able to figure out how to change my queries. Any idea? Le dimanche, 3 aoû 2003, à 01:07 Canada/Eastern, Lin Yu a écrit : One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur aut.IDAuteur AND book.IDLivre ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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: Nested SELECT statements problem
- Original Message - From: Pascal Délisle [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 1:02 PM Subject: Re: Nested SELECT statements problem Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Can you dynamically generate a unique name for your temporary table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
Since I'm new to mySQL, could you point me how to create temporary tables dynamically? Le dimanche, 3 aoû 2003, à 15:32 Canada/Eastern, Jim McAtee a écrit : - Original Message - From: Pascal Délisle [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 1:02 PM Subject: Re: Nested SELECT statements problem Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Can you dynamically generate a unique name for your temporary table? -- 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: Nested SELECT statements problem
| Matthew | Gold | ++-+--+ 3 rows in set (0.02 sec) - Original Message - From: Pascal Délisle [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 8:02 PM Subject: Re: Nested SELECT statements problem Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Le dimanche, 3 aoû 2003, à 12:41 Canada/Eastern, Pascal Délisle a écrit : Thanks for your input! First, I removed the quotation marks into the sub-query in order to fix syntax. Then, I tried to use different alliases from the main query. However, this doesn't work. I mean that mySQL return a syntax error. I checked the server version and it is 3.23.56. As someone else noticed, nested queries are not supported in mySQL until version 4 or so. Therefore, I assume that it would not work for me. Since I'm not the administrator of the mySQL server, I'm not able to upgrade it. So, I'm stucked with version 3.23.56. Now, someone suggested me to replace subqueries with something like this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html . However, I'm not able to figure out how to change my queries. Any idea? Le dimanche, 3 aoû 2003, à 01:07 Canada/Eastern, Lin Yu a écrit : One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur aut.IDAuteur AND book.IDLivre ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.506 / Virus Database: 303 - Release Date: 01/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
In the last episode (Aug 03), Pascal Dlisle said: Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: ... The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Use the CREATE TEMPORARY TABLE command, which creates tables which are invisible to other client connections and are automatically dropped when the client exits. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
What version of MySQL are you using? Regards, A$ On Saturday, August 2, 2003, at 11:45 PM, Pascal Délisle wrote: Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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]
Nested SELECT statements problem
Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
In the last episode (Aug 02), Pascal Dlisle said: I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. I doubt think it would work under Oracle either, since you quoted your subselect. Unless book.IDLivre is a varchar field with one of the records containing the string SELECT book.IDLivre ... = ecr.IDLivre, of course. Also make sure you're using MySQL 4.1.0, since that's the first version that supports subselects. If you're running something older, take a look at http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html which shows you how to rewrite most (not all) subqueries as joins. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
Dan Nelson wrote: In the last episode (Aug 02), Pascal Dlisle said: I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. I doubt think it would work under Oracle either, since you quoted your subselect. Unless book.IDLivre is a varchar field with one of the records containing the string SELECT book.IDLivre ... = ecr.IDLivre, of course. Also make sure you're using MySQL 4.1.0, since that's the first version that supports subselects. If you're running something older, take a look at http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html which shows you how to rewrite most (not all) subqueries as joins. How about if you try this: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM auteur aut INNER JOIN (livreEcritPar ecr INNER JOIN livre book ON ecr.IDLivre = book.IDLivre) ON aut.IDAuteur = ecr.IDAuteur WHERE book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); -- Peter K. Aganyo Eternal Designs Inc., +1(617)344-8023 (fax voicemail)
RE: Nested SELECT statements problem
One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up simple SELECT statements with the C api?
Hello. On Thu 2003-02-27 at 14:52:56 -0800, [EMAIL PROTECTED] wrote: [...] Anyways, I'm running into a little bit of a performance issue as the old database report writer had direct access to the database through a c library (no sql interface). On some reports there can be as many as 100,000 select statements. With mysql this is ending up with a performance penalty of about 3x the existing time that the current report writer takes. Running Intel's vtune I can see that the select statements (through mysql_query)are taking up around 90% of the run time. [...] Anyways, I'm not sure if there is any kind of change I can make to reduce this sql statement penalty and was hoping someone here could possibly help reduce it. First, let me clarify, that the perceived speed loss is less with the SQL statements per se, but with: build query - send - task switching to server process - read query - parse - optimizer - read data and build result - send result - task switching to client process - read result. That's of course not complete, but you get the idea. (You can avoid some of the latency by running queries in parallel.) IMHO, there is not much you can do about it. You switched from a specialized database interface to one that is intended for general, rational storage. A general approach is always slower than a specialized one, if both are of the same quality. There are some things you can try to get more speed, but when have implemented so much of them that you are at the old speed, you will have a similar specialized solution as you had before. If you say you are fine with the specialized solution, I wonder why you changed to an RDBMS to begin with (you could have taken, e.g. BDB). If you are not, I fear you have to live with some speed loss. That said, I suggest you take a look at (and benchmark for your application): - UNIX sockets, make sure to use them if you can, - your queries: can you combine some of the 100.000 statements? (oh, see you answered that below), - Using MySQL 4.0 to take advantage of the new query cache (you have to enable it explicitly), - threading (client-side), maybe you can run stuff in parallel, - the HANDLER commands which bypass some abstraction layers and - libmysqld, the embedded library, which bypasses the connection overhead. I am sure there is more, but that is what came to mind currently. [...] I have a feeling it's the overhead with every query that's really the problem here and that there really is no fix. That's right. I also can't really combine the sql statements and save the data for later due to the unique format of the reports. But perhaps there are some optimizations I can make to help. HTH, Benjamin. -- [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
speeding up simple SELECT statements with the C api?
I'm in the process of writing a report writer for mysql which is necessary for the migration from the current database of my company's product to mysql (due to the amount of reports dependent on the current database report writer). Anyways, I'm running into a little bit of a performance issue as the old database report writer had direct access to the database through a c library (no sql interface). On some reports there can be as many as 100,000 select statements. With mysql this is ending up with a performance penalty of about 3x the existing time that the current report writer takes. Running Intel's vtune I can see that the select statements (through mysql_query)are taking up around 90% of the run time. I was originally using the C++ library but changed it to the C api after seeing that the C++ api was giving a much larger performance penalty likely due to it duplicating the row data (vtune said about 3x penalty but it was more like a 1.5x penalty). Anyways, I'm not sure if there is any kind of change I can make to reduce this sql statement penalty and was hoping someone here could possibly help reduce it. The select statements are very simple and usually take a form like select field1, field2, field3 from table where field4 = (some value) order by field1 and sometimes like this: select field1, field2, field3 from table where field4 = (some value) LIMIT 1 Most often there will only be one result returned and only one table is ever queried at a time. I have a feeling it's the overhead with every query that's really the problem here and that there really is no fix. I also can't really combine the sql statements and save the data for later due to the unique format of the reports. But perhaps there are some optimizations I can make to help. Thanks, Gary Hertel - 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: speeding up simple SELECT statements with the C api?
Oh, and just as a sidenote all the queries have indexes that match the order by and where conditions (they're the same indexes as the original database). -Gary Hertel - Original Message - From: Gary Hertel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 27, 2003 2:52 PM Subject: speeding up simple SELECT statements with the C api? I'm in the process of writing a report writer for mysql which is necessary for the migration from the current database of my company's product to mysql (due to the amount of reports dependent on the current database report writer). Anyways, I'm running into a little bit of a performance issue as the old database report writer had direct access to the database through a c library (no sql interface). On some reports there can be as many as 100,000 select statements. With mysql this is ending up with a performance penalty of about 3x the existing time that the current report writer takes. Running Intel's vtune I can see that the select statements (through mysql_query)are taking up around 90% of the run time. I was originally using the C++ library but changed it to the C api after seeing that the C++ api was giving a much larger performance penalty likely due to it duplicating the row data (vtune said about 3x penalty but it was more like a 1.5x penalty). Anyways, I'm not sure if there is any kind of change I can make to reduce this sql statement penalty and was hoping someone here could possibly help reduce it. The select statements are very simple and usually take a form like select field1, field2, field3 from table where field4 = (some value) order by field1 and sometimes like this: select field1, field2, field3 from table where field4 = (some value) LIMIT 1 Most often there will only be one result returned and only one table is ever queried at a time. I have a feeling it's the overhead with every query that's really the problem here and that there really is no fix. I also can't really combine the sql statements and save the data for later due to the unique format of the reports. But perhaps there are some optimizations I can make to help. Thanks, Gary Hertel - 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: speeding up simple SELECT statements with the C api?
Hello Gary, The MySQL server uses the myisam libraries to access MyISAM tables at the lower level. It works similarly to and just as quickly as C-ISAM, though the API is fairly different (IMHO). A myisam user manual is available, but is not complete (I know, since I wrote it). You could use that method for much faster navigation when your application reads just a row at a time. MySQL (4.0?) also provides a HANDLER syntax for doing row at a time IO but more at the SQL level. It is slower than myisam, but probably much faster than normal SQL for your purposes. I recommend you try (order of preference): - Rewrite the reports to be set based not row based. Or - See if HANDLER syntax is quick enough. I think it will be. Or - See if you can cope with the myisam libraries. Or - Beef up your hardware and tweak everything. Good luck, Stephen Brownlow, Sydney, Australia. - 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
$Variables and Select statements
Hi,
From the code below $wk gets the values (w1, w2, w3, etc.) as it goes
through the 'for' loop. w1, w2, etc. are also colums in the table manager.
What I can't figure out is why the select statement fails (no error, just no
data) when I use $wk as apposed to hardcoding w1, w2, etc. Is this possible
or am I missing something?
Note: the colums w1, w2, etc contain numbers which I am trying to select.
TIA
$db = mysql_connect(xx,xx,xx);
mysql_select_db(database);
echo TABLE ALIGN=center WIDTH=200 BORDER=0TR\n;
for ($i = 1; $i 26; $i++) {
$wk = w$i;
echo TR Width=200TD ALIGN=center COLSPAN=2BWeek
.$i./B/TD/TR\n;
echo TR Width=200TD COLSPAN=2HR SIZE=5/TD/TRTR\n;
echo TR WIDTH=100TD ALIGN=leftBName/B/TDTD
ALIGN=rightBPoints/B/TD;
echo TR Width=100TD/TD/TR\n;
$query = select name, $wk from manager order by $wk desc;
$result = mysql_query($query) or die(Query failed);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo TR WIDTH=150TD ALIGN=left.$line['name']./TDTD
ALIGN=right.$line['$wk']./TD;
}
echo TR Width=200TD COLSPAN=2HR SIZE=5/TD/TR\n;
}
-
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: $Variables and Select statements
Hi,
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo TR WIDTH=150TD
ALIGN=left.$line['name']./TDTD
ALIGN=right.$line['$wk']./TD;
}
Here is your problem. Your are indeed substituting $wk into the query
correctly, but when you go to output the result rows you are not.
You should simply remove the quote marks from:
$line['$wk']
so that it is:
$line[$wk]
PHP does not interpret variable names in single-quoted strings - only in
double-quoted strings. With your current code, PHP is only looking for an
array element called '$wk', which doesn't exist!
In future, may I suggest you that post questions like this to the PHP
mailing list, as this is nothing to do with MySQL.
Regards,
Basil Hussain
---
Internet Developer, Kodak Weddings
E-Mail: [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: $Variables and Select statements
Thanks for the information, I will try that in this situation, however, I
have done this in other select statements and it has worked fine. I will
follow-up in the PHP list if I still have problems.
Please note also that since I assumed this was a MySQL problem I posted it
to this group. Since these two programs are so closely interlinked, it is
hard for us newbies to tell where the problem may lie. I have in the past
posted a question to both groups as I wasn't sure of it either. These lists
are for those that know and those that don't and both have to be tolerant of
the other.
Beauford
- Original Message -
From: Basil Hussain [EMAIL PROTECTED]
To: C. Reeve [EMAIL PROTECTED]; MySQL List
[EMAIL PROTECTED]
Sent: Wednesday, December 18, 2002 11:57 AM
Subject: RE: $Variables and Select statements
Hi,
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo TR WIDTH=150TD
ALIGN=left.$line['name']./TDTD
ALIGN=right.$line['$wk']./TD;
}
Here is your problem. Your are indeed substituting $wk into the query
correctly, but when you go to output the result rows you are not.
You should simply remove the quote marks from:
$line['$wk']
so that it is:
$line[$wk]
PHP does not interpret variable names in single-quoted strings - only in
double-quoted strings. With your current code, PHP is only looking for an
array element called '$wk', which doesn't exist!
In future, may I suggest you that post questions like this to the PHP
mailing list, as this is nothing to do with MySQL.
Regards,
Basil Hussain
---
Internet Developer, Kodak Weddings
E-Mail: [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
-
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: Executing concurrent select statements from perl on MySQL ISAM tables(fwd)
Sorry, Geetika. It looks like I lost this thread in the mix.
Have you tried some Perl like this:
# concurrent.pl
# Check start time.
for my $n (1..$DO_TRIES) {
print qq{Doing # [$n] PID [$$]\n};
my $pid = fork();
if ($pid == 0) {
print qq{Child [$$] running command.\n};
exec(qq{echo '$query' | $MYSQL $DATABASE /dev/null});
}
}
# Check end time
# Do some math and make a report.
---
Rodney Broom
President, R.Broom Consulting
http://www.rbroom.com/
sql
-
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
Executing concurrent select statements from perl on MySQL ISAM tables(fwd)
I am trying to make my perl script fork several processes that all execute 'select' statements on MySQL. I want the statements to happen concurrently (as I want to monitor the behaviour of MySQL with native ISAM tables under this concurrent Read access). So I am using 'fork'. However, I think my 'select' statements are not happening concurrently. Is there something I am missing? Thanks. Does anyone please have any sample code on how to execute *concurrent* MySQL select statements on MyISAM tables? I am looking for perl scripts, bash scripts, whatever. - 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: Executing concurrent select statements from perl on MySQL ISAM tables (fwd)
In the last episode (Nov 25), Geetika Tewari said: I am trying to make my perl script fork several processes that all execute 'select' statements on MySQL. I want the statements to happen concurrently (as I want to monitor the behaviour of MySQL with native ISAM tables under this concurrent Read access). So I am using 'fork'. However, I think my 'select' statements are not happening concurrently. Is there something I am missing? Thanks. That should work. As a test, run two complicated selects, preferably ones that take a couple seconds to run, then run show processlist from a mysql prompt. -- Dan Nelson [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: Executing concurrent select statements from perl on MySQL ISAM tables(fwd)
From: Geetika Tewari [EMAIL PROTECTED] ...perl script fork several processes... ...I think my 'select' statements are not happening concurrently. Why do you think that? --- Rodney Broom President, R.Broom Consulting http://www.rbroom.com/ sql - 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
Executing concurrent select statements from perl on MySQL ISAM tables(fwd)
My previous question raised some follow up questions, so I am going to try to clarify now by explaining the root of my problems: My original (bash) script is the following: ./getTime.pl result.txt echo started: concurrent. # doing level of concurrency $CL let I=1 while [ $I -le $CL ]; do ./my_read.pl let I=$I+1 done ./getTime.pl result.txt My_read.pl just executes a single select statement on a database of 10^4 tuples in a MySQL version 3.23.53 MyISAM table. One such select takes 1.75 seconds to run serially. The getTime.pl script gives me the time in microseconds before and after the transaction using gettimeofday(). By running many my_real.pl with the sign, I am trying to generate several simultaneous concurrent READ transactions on the database. I want to measure the change in the timing as I raise the concurrency level ($CL). Note I am using MyISAM tables. While they do not have transactional support, they should cater to concurrent Read-only transactions. However the time results I have obtained thus far indicate that the my_read.pl script is running sequentially. The TPS (transactions per seconds) ratio remains constant. I am using Debian with 256MB of RAM, and 4 GB of hard disk. Each tuple in my database has roughly 4 doubles. Also, I am pretty sure that the transactions are long enough to be simultaneous. -- Forwarded message -- Date: Mon, 25 Nov 2002 12:38:45 -0500 (EST) To: [EMAIL PROTECTED] Subject: Executing concurrent select statements from perl on MySQL ISAM tables (fwd) I am trying to make my perl script fork several processes that all execute 'select' statements on MySQL. I want the statements to happen concurrently (as I want to monitor the behaviour of MySQL with native ISAM tables under this concurrent Read access). So I am using 'fork'. However, I think my 'select' statements are not happening concurrently. Is there something I am missing? Thanks. Does anyone please have any sample code on how to execute *concurrent* MySQL select statements on MyISAM tables? I am looking for perl scripts, bash scripts, whatever. - 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: select statements
Thanks very much Jed,
I did as you suggested but theres a problem somewhere
I implode like so
$toString = implode($toArray, ,);
then perform query...
$result = mysql_query(SELECT emailAddress FROM subscribers_html WHERE
mailingGroup IN ('$toString'));
I have checked that $toString has the values that I expect, and it does (a
list of words seperated by commas).
this returns 0 rows
If I use NOT IN instead of IN it returns all rows. Theres obviously a catch
somewhere. I have scoured my books and the web, and everything looks correct
to me? Obviuosly it isn't though. Can anyone see where i'm going wrong.
I don't like asking the list for help unless I have exhausted my own reading
supplies, but today i'm stuck
What should I be looking for on the web? I can't find specific details of
WHERE value IN on the web. Searches for such phrases seem pretty useless!
Thanks for your time
Jules
- Original Message -
From: Jed Verity [EMAIL PROTECTED]
To: julian haffegee [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, September 06, 2002 3:42 AM
Subject: Re: select statements
Hello, Jules,
You can use the value in (a,b,c) format. If necessary, implode your
array
and do something like this:
SELECT emailAddress FROM subscribers_html WHERE mailingGroup value in
($toText);
Check the documentation, if necessary, for usage of implode and value
in.
HTH!
Jed
On the threshold of genius, julian haffegee wrote:
Hi all,
I have a database of names and email addresses, that I want to email.
$result = mysql_query(SELECT emailAddress FROM subscribers_html WHERE
mailingGroup='$toText');
The select statement needs to return a row when mailingGroup='$toText',
but
$toText is an array. How should I write this select to say where
mailingGroup is an element of the array $toText. Currently it works for
arrays with 1 element only.
I could turn the array into a string, but I wondered if there was a
clever
MySQL way of doing it all as a select
Thanks for your help!
Jules
-
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: select statements
Perhaps your array items don't have quotes? For example...
Is this what your array looks like: $toTxt = array(a,b,c)
Or is it this: $toTxt = array(\'a\',\'b\',\'c\')
For this to work, it needs to be the second one. If necessary, you can just
walk through the array, adding the quotes:
?
$toTxt = array(a,b,c);
function AddQuotes ($arrItem,$key) {
$arrItem = '$arrItem';
}
array_walk($toTxt,'AddQuotes');
$toTxt = implode($toTxt,,);
$sql = select * from table where field value in ($toTxt);
?
Does that work? HTH!
Jed
On the threshold of genius, Ian Osterberg wrote:
Thanks Jed,
I'm still stuck though
I did as you suggested
- Original Message -
From: Jed Verity [EMAIL PROTECTED]
To: julian haffegee [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, September 06, 2002 3:42 AM
Subject: Re: select statements
Hello, Jules,
You can use the value in (a,b,c) format. If necessary, implode your
array
and do something like this:
SELECT emailAddress FROM subscribers_html WHERE mailingGroup value in
($toText);
Check the documentation, if necessary, for usage of implode and value
in.
HTH!
Jed
On the threshold of genius, julian haffegee wrote:
Hi all,
I have a database of names and email addresses, that I want to email.
$result = mysql_query(SELECT emailAddress FROM subscribers_html WHERE
mailingGroup='$toText');
The select statement needs to return a row when mailingGroup='$toText',
but
$toText is an array. How should I write this select to say where
mailingGroup is an element of the array $toText. Currently it works for
arrays with 1 element only.
I could turn the array into a string, but I wondered if there was a
clever
MySQL way of doing it all as a select
Thanks for your help!
Jules
-
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
select statements
Hi all, I have a database of names and email addresses, that I want to email. $result = mysql_query(SELECT emailAddress FROM subscribers_html WHERE mailingGroup='$toText'); The select statement needs to return a row when mailingGroup='$toText', but $toText is an array. How should I write this select to say where mailingGroup is an element of the array $toText. Currently it works for arrays with 1 element only. I could turn the array into a string, but I wondered if there was a clever MySQL way of doing it all as a select Thanks for your help! Jules - 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: select statements
Hello, Jules, You can use the value in (a,b,c) format. If necessary, implode your array and do something like this: SELECT emailAddress FROM subscribers_html WHERE mailingGroup value in ($toText); Check the documentation, if necessary, for usage of implode and value in. HTH! Jed On the threshold of genius, julian haffegee wrote: Hi all, I have a database of names and email addresses, that I want to email. $result = mysql_query(SELECT emailAddress FROM subscribers_html WHERE mailingGroup='$toText'); The select statement needs to return a row when mailingGroup='$toText', but $toText is an array. How should I write this select to say where mailingGroup is an element of the array $toText. Currently it works for arrays with 1 element only. I could turn the array into a string, but I wondered if there was a clever MySQL way of doing it all as a select Thanks for your help! Jules - 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
Internal Select statements using IN and NOT IN
I've noticed that MySQL doesn't support the use of internal selects with the in and not in methods such as in this example from a where statement. and shift_id not in (select shift_assoc from facdba.staff_schedule_temp where status 'EXL') This functionality is available in Oracle but I'm trying to use a program I wrote on a sever running MySQL. I'm able to do everything else with some changes to my sql statements, but I need the ability to do an internal select on the fly in my sql statements so that I can compare a value from one table to a list of results from another select. In this particular example, I do not have the ability to call the internal select first and then insert it into the next database call. It needs to be done on the fly because unlike the simple example above, this call is very complex and involves may variables and unioned selects. Joins don't work especially since I'm using the NOT IN clause. I find it really hard to believe that MySQL doesn't have support for this or some other functionality to replace it. I mean it has everything else and then some. But I've searched the documentation and haven't found anything. If anyone knows a way to perform an internal select similar to what is shown above, please let me know. --- Alan Coleman [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: Internal Select statements using IN and NOT IN
Never mind, I figured it out by reading a previous question and I understand how to use Left join to do this. -Original Message- From: Alan Coleman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 5:22 AM To: [EMAIL PROTECTED] Subject: Internal Select statements using IN and NOT IN I've noticed that MySQL doesn't support the use of internal selects with the in and not in methods such as in this example from a where statement. and shift_id not in (select shift_assoc from facdba.staff_schedule_temp where status 'EXL') This functionality is available in Oracle but I'm trying to use a program I wrote on a sever running MySQL. I'm able to do everything else with some changes to my sql statements, but I need the ability to do an internal select on the fly in my sql statements so that I can compare a value from one table to a list of results from another select. In this particular example, I do not have the ability to call the internal select first and then insert it into the next database call. It needs to be done on the fly because unlike the simple example above, this call is very complex and involves may variables and unioned selects. Joins don't work especially since I'm using the NOT IN clause. I find it really hard to believe that MySQL doesn't have support for this or some other functionality to replace it. I mean it has everything else and then some. But I've searched the documentation and haven't found anything. If anyone knows a way to perform an internal select similar to what is shown above, please let me know. --- Alan Coleman [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 - 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
Problem with select statements in Visual Basic
I have a problem in VB trying to use select statements with MySQL. My problem does not occur only with the example below but with ANY select statement I try to make against my MySQL database. Much thanks in advance to anyone that can help me out with this :) This is my code: Private Sub optNoTech_Click() Adodc1.RecordSource = Select * from usermanager where techstatus = 'U' Adodc1.Refresh DataGrid1.Refresh End Sub I keep getting this error: [MySQL][ODBC 3.51 Driver][mysqld-3.23.49.nt]You have an error in your SQL syntax near Select * from usermanager where techstatus = 'U' at line 1. Thanks :) -Trevor - 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
Speed problem with sorted select statements
Description: I try to get the last 5 different times and the first 5 different times. There is a difference of aproximately 1 minute and 40 seconds between this to queries. I do not see reason for the difference in the execution times. I am the only one who works on the database. Nobody else has acces to it. The times shown in the examples does not vary. There are always the same. There are no other programs aktiv on my machine. It is not a problem of workload or network traffic. I have looked on the mailing list but I could not find something helpfull. There was no mysqlbug script in the mysql directories. So I tried to collect all the necessary information as described in the documentation. I tried to reproduce the behaviour with smaller tables but it was not possible. I know that I do not need the Time index but I tried it to see if the timing changes. It does not. There is no other reason for this index. -- MySQL dump 8.17 -- -- Host: localhostDatabase: snoopertest7 - -- Server version 4.0.0-alpha-nt -- -- Table structure for table 'eventlist' -- CREATE TABLE eventlist ( ID double default NULL, eventTime char(25) default NULL, sameTimeCounter char(3) default NULL, interface char(10) default NULL, telegramType char(30) default NULL, telegramID double default NULL, KEY TelegramTypeIndex (telegramType), KEY TelegramIDIndex (telegramID), KEY eventTimeIndex (eventTime,sameTimeCounter,ID), KEY Time (eventTime) ) TYPE=MyISAM; How-To-Repeat: mysql select distinct eventTime from eventList where eventTime '99' order by eventTime desc limit 0,5; +--+ | eventTime| +--+ | 000617153001 030 | | 000617153000 970 | | 000617153000 910 | | 000617153000 880 | | 000617153000 850 | +--+ 5 rows in set (1 min 39.89 sec) mysql explain select distinct eventTime from eventList where eventTime '99' order by eventTime desc limit 0,5; +---+---+-+--+-+ --+-+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra| +---+---+-+--+-+ --+-+--+ | eventList | range | eventTimeIndex,Time | Time | 26 | NULL | 3418451 | where used; Using index; Using temporary | +---+---+-+--+-+ --+-+--+ 1 row in set (0.00 sec) mysql select distinct eventTime from eventList where eventTime '0' order by eventTime asc limit 0,5; +--+ | eventTime| +--+ | 000617102959 970 | | 000617103000 030 | | 000617103000 100 | | 000617103000 160 | | 000617103000 220 | +--+ 5 rows in set (0.00 sec) mysql explain select distinct eventTime from eventList where eventTime '0' order by eventTime asc limit 0,5; +---+---+-+--+-+ --+-+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra | +---+---+-+--+-+ --+-+--+ | eventList | range | eventTimeIndex,Time | Time | 26 | NULL | 3418459 | where used; Using index; Using temporary; Using filesort | +---+---+-+--+-+ --+-+--+ 1 row in set (0.00 sec) Fix: how to correct or work around the problem, if known (multiple lines) Synopsis: Speed problem with sorted select statements Submitter-Id: submitter ID Originator: Denny Fritzsche Organization: Alcatel Canada MySQL support: none Severity: serious Priority: high Category: mysqld Class:sw-bug Release:mysql-4.0.0-alpha-nt Exectutable: mysqld-nt Environment: Compaq Deskpro EN, x86 Family 6 Model 8 Stepping 3, 256 MB RAM, i815 Chip Set System:NT4 SP6 Compiler: Architecture: i - 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
Crash of mysqld on large SELECT statements
Description: We are running about 100 MySQL-database servers on a single Machine. The databases are usually accessed via a web-interface. For some batch processing we tried to do basically 'SELECT *' on some tables. This caused the mysqld-Processed to fail. 'safe_mysql' restarted the processes a limited number of times. Eventually all but three deamons died. This is the stack trace of one of the deamons: 0x807b75f handle_segfault__Fi + 383 0x812bcaa pthread_sighandler + 154 0x8150974 chunk_realloc + 56 0x8150907 realloc + 787 0x811594e my_realloc + 46 0x80fae62 _mi_read_pack_info + 1026 0x80f1c11 mi_open + 3789 0x80c68ed open__9ha_myisamPCciUi + 25 0x80c2d11 ha_open__7handlerPCcii + 33 0x80975c0 openfrm__FPCcT0UiUiUiP8st_table + 5008 0x8093572 open_unireg_entry__FP3THDP8st_tablePCcN22b + 70 0x8092bca open_table__FP3THDPCcN21Pb + 978 0x809379a open_tables__FP3THDP13st_table_list + 86 0x80957c2 open_and_lock_tables__FP3THDP13st_table_list + 18 0x8081e58 mysql_execute_command__Fv + 604 0x8085ce6 mysql_parse__FP3THDPcUi + 210 0x808130d do_command__FP3THD + 1261 0x808080c handle_one_connection__FPv + 548 How-To-Repeat: One reexecuting the batch-script, the same problem occured. Fix: None known. Submitter-Id: ?? Originator: Organization: Biomax AG MySQL support: none Synopsis: Segmentation fault Severity: serious Priority: high, of course Category: mysql Class: sw-bug Release: mysql-3.23.43 (Official MySQL binary) Environment: machine, os, target, libraries (multiple lines) System: Linux pseudo 2.2.18 #1 Tue Jan 22 15:55:44 CET 2002 i686 unknown The same error occured also with Kernel version 2.4.6 Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static' LIBC: -rwxr-xr-x1 root root 1382179 Jan 19 2001 /lib/libc.so.6 -rw-r--r--1 root root 2585872 Jan 19 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Jan 19 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared - 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: Crash of mysqld on large SELECT statements
On Tuesday 22 January 2002 10:21 am, Bernhard Schmalhofer wrote: x807b75f handle_segfault__Fi + 383 ? ? ? ? ?0x812bcaa pthread_sighandler + 154 ? ? ? ? ?0x8150974 chunk_realloc + 56 ? ? ? ? ?0x8150907 realloc + 787 ? ? ? ? ?0x811594e my_realloc + 46 ? ? ? ? ?0x80fae62 _mi_read_pack_info + 1026 ? ? ? ? ?0x80f1c11 mi_open + 3789 ? ? ? ? ?0x80c68ed open__9ha_myisamPCciUi + 25 ? ? ? ? ?0x80c2d11 ha_open__7handlerPCcii + 33 ? ? ? ? ?0x80975c0 openfrm__FPCcT0UiUiUiP8st_table + 5008 ? ? ? ? ?0x8093572 open_unireg_entry__FP3THDP8st_tablePCcN22b + 70 ? ? ? ? ?0x8092bca open_table__FP3THDPCcN21Pb + 978 ? ? ? ? ?0x809379a open_tables__FP3THDP13st_table_list + 86 ? ? ? ? ?0x80957c2 open_and_lock_tables__FP3THDP13st_table_list + 18 ? ? ? ? ?0x8081e58 mysql_execute_command__Fv + 604 ? ? ? ? ?0x8085ce6 mysql_parse__FP3THDPcUi + 210 ? ? ? ? ?0x808130d do_command__FP3THD + 1261 ? ? ? ? ?0x808080c handle_one_connection__FPv + 548 This does look like your table is corrupted very badly or otherwise we have some ugly bug in mi_open(). Please upload the table on which this is happening (in binary format, .frm, .MYD and .MYI files to ftp://support.mysql.com/pub/mysql/secret/ Monty - I sent you a copy so you'd be prepared to start working on this once we have a test case... -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - 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: need help with select statements
On Thursday 30 August 2001 15:28, Andre Konopka wrote: Hi, I have, three tables (one,two, three) with the rows id/value. id is the key for all the tables. with select value from one where id=xx; select value from two where id=xx; select value from three where id=xx; I can select all values from the tables with the id=xx. How can I do this with one select statement. Notice, it's possible that there is an entry for id=xx in table one, but not in table two, or three. Of course ist's possible that there is only a value in table three. The output could be id one.value two.value three.value x1 233 2342 NULL x2 NULL 998NULL x3 33 232 23 Clear enough? A couple of LEFT JOINs should probably do the trick HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Limitations on select statements?
Hello everyone, I have a few questions that I hope some of you might be able to answer. Is there a MAX_SIZE on a select statements and when does MySQL lose performance when the statement becomes very large? When using the 'IN' clause, how does MySQL parse the data? Does it do an inline search or a binary tree? Thanks for your help... --Dave J. --- Dave W. Juntgen [EMAIL PROTECTED] Medical Informatics Engineering, Inc. http://www.mieweb.com/ 4101 W. Jefferson Blvd. Phone: 219-459-6270 Fort Wayne, IN 46804 Fax: 219-459-6271 - 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
Missing rows from select statements on heap table type
I upgraded from 3.23.27-beta to 3.23.32 after seeing bugfixes on intermediate versions. However the problems are continuing: Mod_perl DBI/DBD::mysql queries are failing to return rows that I know exist. It could be DBI/DBD::Mysql, but just in case I have some questions: - Are there known problems with the heap table type? - Are there better ways of programming, say using bindings rather than using '?' placeholders, that could help? - Could updates interfere with my queries to produce this? - What speed penalty would I incur by going to MyISAM tables, given enough RAM to keep the data I need in the filesystem cache? I don't think my server is particularly stressed, the load numbers are typically less than .3, .1, .05 and CPU 95% or better free. TIA for you help. -- Christopher Everett - 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
