Need help on WHERE ... LIKE Query
Hi, List, I looked here: http://www.mysql.com/doc/en/String_comparison_functions.html But I am not seeing what I need. I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; This works in another RDBMS. It doesn't return a syntax error, either, but it returns no records. My guess is that MySQL is interpreting the whole thing literally, rather than looking for what I want. I need this to return all records where surname begins with the letters A through C (that is, all records with a surname which begins with A, B, or C). Anybody got a how-to? I'm sure there must be some way, other than to do this three times. Some of these can vary; that is, it may be 0-9, or 0-Z (show all), even, so I don't want to do a bunch of OR'ing, either. Thanks! --Scott Brown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help on WHERE ... LIKE Query
Thanks for all of the responses! Actually, Brent Baisley wins the syntax question of the day. The BETWEEN syntax is what I needed. REGEXP and RLIKE do not return any records, they return a count of the number of rows matching the expression. Thanks! --Scott Brown At 11:22 AM 10/30/2003, you wrote: Hi, List, I looked here: http://www.mysql.com/doc/en/String_comparison_functions.html But I am not seeing what I need. I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; This works in another RDBMS. It doesn't return a syntax error, either, but it returns no records. My guess is that MySQL is interpreting the whole thing literally, rather than looking for what I want. I need this to return all records where surname begins with the letters A through C (that is, all records with a surname which begins with A, B, or C). Anybody got a how-to? I'm sure there must be some way, other than to do this three times. Some of these can vary; that is, it may be 0-9, or 0-Z (show all), even, so I don't want to do a bunch of OR'ing, either. Thanks! --Scott Brown -- 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: Need help on WHERE ... LIKE Query
Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott At 11:35 AM 10/30/2003, you wrote: This should work for you: SELECT * FROM sometable WHERE surname BETWEEN 'A' AND 'D' ORDER BY surname In my quick test the first parameter is inclusive while the second is not, which is why it is D and not C. On Thursday, October 30, 2003, at 02:22 PM, Scott Brown wrote: I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Need help on WHERE ... LIKE Query
Oh, well, chalk it up to experience. RLIKE is what works the way I want. DOH! Thanks, --Scott Brown At 12:34 PM 10/30/2003, you wrote: Thanks so much Brent, this is what I was looking for. However, what do I do when I get to 'Z'? I looked here, and now I am really confused: http://www.mysql.com/doc/en/Comparison_Operators.html It seems to say that BETWEEN returns a rowcount as well? I am guessing that these all return records... So, what should I use, RLIKE, BETWEEN or??? Thanks, --Scott At 11:35 AM 10/30/2003, you wrote: This should work for you: SELECT * FROM sometable WHERE surname BETWEEN 'A' AND 'D' ORDER BY surname In my quick test the first parameter is inclusive while the second is not, which is why it is D and not C. On Thursday, October 30, 2003, at 02:22 PM, Scott Brown wrote: I want to do a string comparison like this: SELECT * FROM sometable WHERE surname LIKE '[A-C]%' ORDER BY surname; -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL max_connections on Windows
Hello, list, What is a good setting for max_connections on a Windows machine with 1GB RAM, Dual Athlons, RAID, etc... The default of 100 seems a bit lean, we would like to increase this number, but don't have any experience history with MySQL to draw upon. The docs say that 500-1000 is good for Solaris/Linux systems, but that is all I can find. Anyone have a good place to start for Windows, with the hardware mentioned? TIA, --Scott Brown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 and TIMESTAMP Column Types
I am running MySQL 4.1 in a sandbox, trying to test some code against it. No matter what, even when no value is explicitly applied, or the field is not explicitly named in a SQL statement, the TIMESTAMP column refuses to update. I have tested this with several programs written in 3 different languages. Is this a known issue? Anybody got any pointers to info on this? Thanks, --Scott Brown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 and TIMESTAMP Column Types
DROP TABLE IF EXISTS t; CREATE TABLE t (ts TIMESTAMP, i INT); INSERT INTO t (i) VALUES(0); SELECT * FROM t; At this point, the timestamp field is un-interpretable. SELECT returns -00-00 9:01 AM UPDATE t SET i = i + 1; SELECT * FROM t; Returns the same thing; -00-00 9:01 AM. JFYI, none of this is correct, not even the time. Finally, SELECT @@sql_mode; returns: ++ | @@sql_mode | ++ || ++ 1 row in set (0.01 sec) This is MySQL 4.1.0 alpha max nt, btw. Stranger and stranger... If I simply convert the column type to DATETIME, all of them are fine, and appear to have been properly stamped. So, my guess is, the error, or whatever it is that is happening, is occurring upon retrieval? Is this actually an ODBC driver issue? I am using the latest MyODBC, beta, I believe, 3.51.06.00 for Windows... Thanks! --Scott Brown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Currency
I am guessing that you are trying to find a MySQL data type of Currency, which does not exist. I generally use a column that is DECIMAL(150,2) for US currency. --Scott At 01:32 PM 10/2/2003, Fabio Bernardo wrote: I´m having some problems with currency´s fields. Actually I dont know what mysql´s field typeI have to choose..^ I wanna input this value: U$32.00 but, when I write the query: Select 'field' from table it returns.. '32' and not 32.00. Which select statement do i have to write? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL GUIs
I like PremiumSoft's MySQL Studio (now called Navicat, I believe). Very clean, easy to use interface. You can get a trial here: http://www.mysqlstudio.com/ --Scott Brown At 01:16 PM 6/11/2003 +0200, Rodolphe Toots wrote: hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- 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]
Storing queries
Hi, List, Just wondering... Anybody have a good method to store queries? I know MySQL doesn't support stored procedures at this time (will in 5.x, I guess). Obviously, I can just stash it in a varchar field in a table somewhere, then select it and execute it in a separate query, but is there a better, more efficient way? Any help greatly appreciated! TIA, --Scott Brown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random 32bit number for columns?
Hello, list, I am trying to find a means to create a column that self-populates with unique random 32bit integers in MySQL. Do I need to do this in code, or is there a column spec/extra that I can use to populate a column with random numbers on an insert? Ideally, the database itself would manage this column... Thanks! --Scott Brown - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query syntax help?
OK, I am having a bit of trouble designing a MySQL query that returns what I want. Here is the query as I have it thus far: SELECT DISTINCT regformfields.name AS thename, regformfields.label AS thelabel, regfields.name AS fieldsname FROM regformfields INNER JOIN regfields ON (regformfields.name = regfields.Name) WHERE regformfields.label != '' ORDER BY regfields.saveorder; In this particular query, there can be multiple occurrences of thename(can be filtered by DISTINCT), therefore multiple occurrences of thelabel (which can't be filtered by DISTINCT, as it is always different for the same thename), but fieldsname is always unique. I don't care which thename or which thelabel is returned, but I only want one (these two tables, together with some others, construct a schema for yet others...), i.e thename = 'email' may be returned twice in this result set, but I only want it to appear once. DISTINCT, as it is used here, does not return what I want, as thelabel will rarely, if ever, be distinct. The ideal query would force the DISTINCT to be related ONLY to thename, and return whatever thelabel it happens to grab, based on however it is indexing, which would be the first saveorder it stumbles upon. Any help would be appreciated! TIA, --Scott Brown - 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
MySQL table locking?
Hello, list, I'm having a weird issue with table locking. I'm running MySQL 3.23 on a Win2k server, used for eCommerce applications. All settings appear to be in order, that is, KeepAlive is 30, connection pooling is on, with a timeout of 30. The tables in any given database seem to remain locked for as long as 30 minutes after all clients have gone away, and all ODBC connections have completed. These are MyISAM tables. The long and short of it is that I am using PS MySQL Studio to remotely administer the db, and testing the software product concurrently. I need the software to lock the table, of course, but only for the amount of time I have specified, i.e. for 30 seconds after the client disappears, which should be 30 seconds after the KeepAlive times out, which is also 30 seconds. So, it shouldn't be any more than 1 minute after all clients go away, before I can commit a change to a table, right? I am also not quite sure if connection pooling even matters. Shouldn't ODBC just reuse the same connection if the KeepAlive times out before the ODBC pool, eh, so the 30 seconds for the inactive connection through the browser to time out is all that should matter? Am I missing something here? I am using iHTML for this particular eCommerce app, BTW. If anyone is familiar with this and knows if the issue is in iHTML, let me know that as well. TIA, --Scott Brown - 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
Building php4.0.4pl1 with mysql 3.23.32
I've got a real weird problem happening after building 4.0.4pl1 and mysql
3.23.32 on my test machine. I previously had 4.0.1 up and running with an
earlier version of mysql - I think it was 3.22.xx - and this combination
worked fine.
I *seem* to successfully connect to my mysql database... it's not returning
any error but it's not returning any data either.
PHP was configed with:
./configure' '--with-apache=/root/Apachetoolbox/apache_1.3.17'
'--enable-exif' '--enable-memory-limit=yes' '--enable-track-vars'
'--with-calendar=shared' '--enable-safe-mode' '--enable-magic-quotes'
'--enable-trans-sid' '--enable-sysvsem' '--enable-sysvshm' '--enable-wddx'
'--enable-yp' '--enable-sockets' '--with-gd=/usr/local'
'--enable-gd-imgstrttf' '--with-mysql' '--with-mysql' '--with-pgsql'
'--with-ldap'
And compiled and installed fine into 1.3.17 (as far as I could see)but
I'm not getting any results out of it.
I can force an error on the connect to the database by supplying a bad
userid - so it must be talking to mysqld fine... but I get no data out of
it, and no errors.
Here's an example of the code which is accessing the database - the database
open is handled by a separate function:
function ThePrice($prodid,$typ)
{
$inqSQL="select * from pricing where ProdCode ='" . $prodid . "' and
Price_effDate='".date("Y m d")."' order by price_effdate desc";
echo "!-- " . $inqSQL . " --\n";
$result = mysql_db_query("thedbname",$inqSQL);
if (!$result) {
echo mysql_error();
exit;
}
$rtnvalue = "!?";
if ($row = mysql_fetch_array($result)) {
if ($typ=="S")
$rtnvalue = $row["ProdSetup"];
else
$rtnvalue = $row["ProdMonthly"];
}
mysql_free_result($result);
echo "!-- product: $prodid Type: $typ Price: $rtnvalue . --\n";
return($rtnvalue);
}
$rtnvalue shows up as the default value !?. Sometimes. Sometimes they show
up blank. It's almost like the script is dying, but not telling me it's
dying.
The database fields are setup like this:
mysql describe pricing;
+---+--+--+-+++
| Field | Type | Null | Key | Default| Extra |
+---+--+--+-+++
| ID| int(11) | | PRI | NULL | auto_increment |
| ProdCode | varchar(50) | | |||
| Price_effdate | date | | | -00-00 ||
| ProdSetup | double(16,4) | | | 0. ||
| ProdMonthly | double(16,4) | | | 0. ||
+---+--+--+-+++
5 rows in set (0.00 sec)
So the field names do match... and mysql will return values when I select
the rows from it directly.
Does anyone have any suggestions??? As near as I can tell mysql_fetch_array
is failing - but there is a return result otherwise I'd be getting an error
message out of this routine...
I'm really confused here.
Is my build bad? Or is this 4.0.4pl1 version a little different?
-
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
