Re: Someone PLEASE Help Me!!! This should be really easy for you folks!
At 05:16 PM 10/17/03 -0700, you wrote: I desperately need everyone's help. I normally build database driven websites on NT Servers with an MS Access database, using perl 5 to read/write the database. Now I need to build a website that can handle a database with almost a gigabyte worth of images to catalog (no it's not porn). Ideally I would like to use the same method BUT the only hosts i can find that will offer that much disk space only use Unix and MySQL. I, However, have never used MySQL. I have used sql statements in the perl script to read/write the MS Access database, but i have never used MySQL. I need someone to tell me would I have to buy some software to build a MySQL database? Is there a free download that i can use to build the database? Where would I find it to download/purchase it? Is there an easy example of what statements I could use from a perl script to add, delete and read a single entry or a whole range of entries from the database? Can someone please help me? I'm desperate! Alternatively, if someone knows of a host that offers that much space and MS Access capatibility for around $10 a month that would certainly work too. Thank you everyone for your help and time in this. Sincerely, Mike Tiny Bailey Author of What Have We Done? The Dragon Chronicles Mike, I'm learning MySQL also. Should be really easy with your background Try this. 30 days free: http://www.webyog.com/sqlyog You can change database structure or data values in the GUI, and read the resulting MySQL statements in the other window. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: msaccess frontend
On Sat, 18 Oct 2003 09:42:30 -0600 Colleen Dick said: CD- Have anybody ever set up MS Access as a frontend to mysql? CD- CD- the dB is an already established mysql db running on linux CD- I don't want to convert it, just use access to get to it. CD- CD- I have followed the recipe at CD- http://www.washington.edu/computing/web/publishing/mysql-access.html CD- CD- I can see the tables so I know I'm connecting and the linkage CD- is working. I understand that access has issues with CD- enums, so I made a really simple test table with just an auto_inc CD- key and a VARCHAR. When I double click that table icon CD- (it's a globe icon) I get Reserve Error -1104. CD- CD- Would it be better to just redo all the tables from access CD- in the first place? This database isn't huge. Thanks... CD- CD- CD- Reserved Error 1104 is a Jet engine error, and means (IIRC), Invalid Session Handle. Recommended fix is to create a new access mdb and try again. Try another ODBC driver (I have both the 2.5 and 3.51 MySQL drivers - sometimes one will refuse to work correctly, but then changing the driver to the other (and then relinking the tables in access) fixes the problems. There is no regularity / pattern to this. S'pose it just depends on the mood that windoze is in at the time. :-( (In the following, I declare an interest as a satisfied Navicat user - I have nothing to do with the company that has developed Navicat). Finally, I do wonder if you really _need_ access as a frontend? Have you tried, for example, MySqlFront or Navicat? I have Access as a front end for two particular MS diehard employees, whilst the rest of the group use Navicat. We have loads more problems with the Access front end than we do with the Navicat. (Lost connections and corruption of data on the link from the db to access being the main ones.) HTH Martin -- -- [EMAIL PROTECTED] -- This email, and any attachments, has been scanned for virus contamination using Norton Anti-Virus 2002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing LAST_INSERT_ID()/AUTO_INCREMENT()
I have a ticketing system that sets the ID of the ticket as the LAST_INSERT_ID. By default, it was counting up from 0. I updated the ID of the last ticket to reflect a different numbering scheme (MM). I would like to automate this but I don't want to change the ID of a current ticket. I was hoping that there was a way to update the LAST_INSERT_ID. Is this possible? If so, what is the query that should be run? Thank You, Jason Williard Janix
Test
Sorry folks, just wondering about my email provider -Jakob -- To Unix or not to Unix. That is the question whether 'tis nobler in the mind to suffer slings and arrows of vast documentation or to take arms against a sea of buggy OS and by raping the support lines end then? ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Partnership Required!
Attention Folks! These letters are trap for you. If you vanna loose some money and maybee Your life, try it! On Sat, 18 Oct 2003 19:46:29 GMT, chrislor100 wrote Mr.Christopher Tayelor Brait Merchant Bank. 9 Fricker Road, Illovo Boulevard Illovo Sandton Republic of South Africa. Email: [EMAIL PROTECTED] Dear Sir/Madam, I am Christopher Tayelor, Bank Manager of Brait Merchant Bank,South Africa.This is an urgent and very confidential business proposition. On June 6, 2000,a Foreign Oil consultant/contractor with the South African Institute of Mining and Metallurgy, Mr.Steve Warren made a numbered time(Fixed) Deposit for twelve calendar months, valued at US$26,500, 000.00,(Twenty-six Million, five hundred thousand Dollars) in my branch. Upon maturity,I sent a routine notification to his forwarding address but got no reply. After a month,we sent a reminder and finally we discovered from his contract employers, the National Petroleum Corporation that Mr.Steve Warren died from an automobile accident. On further investigation,I found out that he died without making a WILL,and all attempts to trace his next of kin was fruitless. I therefore made further investigation and discovered that Mr.Steve Warren did not declare any kin or relations in all his official documents,including his Bank Deposit paperwork in my Bank. This sum of US$26,500,000.00 has carefully been fixed in my bank for safekeeping. No one will ever come forward to claim it.According to South African Law, at the expiration of 5 (five) years, the money will revert to the ownership of the Government if nobody applies to claim the fund. Consequently, my proposal is that I will like you as a Foreigner to stand in as the owner of the money which was fixed deposited in my bank. I am writing you because I as a public servant,i cannot operate a foreign account. I want to present you as the owner of the funds so you can be able to claim them with the help of my attorney. This is simple.I will like you to provide immediately your full names and address so that the Attorney will prepare the necessary documents which will put you in place as the beneficiary of the funds. The money will be moved out for us to share in the ratio of 80% for me and 20% for you. The paperwork for this transaction will be done by the Attorney and this will guarantees the successful execution of this transaction. If you are interested, please reply immediately via my email address and Upon your response,I shall then provide you with more details and relevant documents that will help you understand the transaction. Please observe utmost confidentiality, and be rest assured that this transaction would be most profitable for both of us because I shall require your assistance to invest my share in real estate within your country. Due to the nature of confidentiality in this Transaction our communication can only be via email and fax mostly. Awaiting your urgent reply via my email. Thanks and my regards. Mr.Christpher Tayelor, Manager, Brait Merchant Bank. Laszlo Illyes Teaching-assistant Sapientia University (Csikszereda) Miercurea-Ciuc Tel:+40266317310 Fax:+40266317310/+40266371121 Mobil:+40740055706 E-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: msaccess frontend
We use MS Access with ODBC links to MySQL, but prefer a commercial product called MyManager. It cost about $199.00 US. for the professional version. looks and works much like MS Access. There are several on the market, we chose this one as the best for our purposes. Here's the link: http://ems-hitech.com/mymanager/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
might be a cilly question to u -execuse me.
Hello Friends, i am new to mysql and plz don't treat my question as a cilly as i am just getting into mysql. my problem is: when i am executing the following query i am getting the error. whats wrong with the query? desc of table accounting and user and contents of them are also listed below. mysql version is : mysql Ver 11.18 Distrib 3.23.54, for redhat-linux-gnu (i386) mysql select *from accounting where uid = (select uid from user wher gid=apswan); ERROR 1064: You have an error in your SQL syntax near 'select uid from user wher gid=apswan)' at line 1 mysql mysql desc user; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | uid | varchar(20) | | PRI | | | | gid | varchar(20) | YES | | NULL| | | comment | text| YES | | NULL| | | password | varchar(35) | YES | | NULL| | | enable| varchar(35) | YES | | NULL| | | gpassword | varchar(35) | YES | | NULL| | | arap | varchar(35) | YES | | NULL| | | pap | varchar(35) | YES | | NULL| | | chap | varchar(35) | YES | | NULL| | | mschap| varchar(35) | YES | | NULL| | | expires | datetime| YES | | NULL| | | b_author | varchar(20) | YES | | NULL| | | a_author | varchar(20) | YES | | NULL| | | svc_dflt | int(4) | YES | | NULL| | | maxsess | int(4) | YES | | NULL| | | user | int(1) | YES | | NULL| | | acl_id| int(4) | YES | | NULL| | | sess | int(4) | YES | | NULL| | +---+-+--+-+-+---+ 18 rows in set (0.05 sec) mysql desc accounting; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | date | datetime | | MUL | -00-00 00:00:00 | | | nas | varchar(16) | | MUL | | | | uid | varchar(20) | | MUL | | | | terminal | varchar(20) | YES | | NULL | | | client_ip| varchar(16) | | MUL | | | | type | varchar(20) | YES | | NULL | | | service | varchar(20) | YES | | NULL | | | priv_lvl | int(2) | YES | | NULL | | | cmd | varchar(255) | YES | | NULL | | | elapsed_time | int(6) | YES | | NULL | | | bytes_in | int(10) | YES | | NULL | | | bytes_out| int(10) | YES | | NULL | | +--+--+--+-+-+---+ 12 rows in set (0.00 sec) mysql mysql select * from user; +--+++++---+--+--+--++-+--+--+--+-+--++--+ | uid | gid| comment| password | enable | gpassword | arap | pap | chap | mschap | expires | b_author | a_author | svc_dflt | maxsess | user | acl_id | sess | +--+++++---+--+--+--++-+--+--+--+-+--++--+ | apswan || network admins | $1$.bJrxNDv$1JDyhV/kOwglyU7wVB1MH1 || NULL | | | || 2003-10-26 18:36:44 | | |0 | 0 |2 | 0 | NULL | | manoj| apswan | DNC Chittoor | $1$F6D75fPB$Mgu8OzHUZqzxzWsDSPoAT1 || NULL | | | || 2003-10-25 18:43:22 | | |0 | 0 |1 | 0 | NULL | | dncchitt | apswan | DNC Chittoo| $1$2pAHLzKV$qakvoD.PpZjWZh5u9TMiu. || NULL | | | || 2003-10-25 19:00:46 | welcome | bye bye |0 | 0 |1 | 0 | NULL | | snc | apswan | SNC Engineer | $1$rqG.1CUi$mkPT6tEMMtRrCcXbMH7Sp1 || NULL | | | || 2003-10-25 19:01:28 | | |0 | 0 |1 | 0 | NULL | +--+++++---+--+--+--++-+--+--+--+-+--++--+ 4 rows in set (0.00 sec) mysql select * from accounting;
Re: might be a cilly question to u -execuse me.
Probably there's nothing wrong with the query. The problem seems to be that MySQL you're using (v3) does NOT support subqueries. Note that MySQL v4.0, the actual production release, does NOT support subqueries either. Subqueries are supported on MySQL v4.1, which is still in ALPHA stages and is strongly NOT recommended for production servers. http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#ANSI_diff_Subqueries Sincerily, Leonardo Rodrigues - Original Message - From: Manoj Kumar Neelapareddy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, October 19, 2003 11:46 AM Subject: might be a cilly question to u -execuse me. Hello Friends, i am new to mysql and plz don't treat my question as a cilly as i am just getting into mysql. my problem is: when i am executing the following query i am getting the error. whats wrong with the query? desc of table accounting and user and contents of them are also listed below. mysql version is : mysql Ver 11.18 Distrib 3.23.54, for redhat-linux-gnu (i386) mysql select *from accounting where uid = (select uid from user wher gid=apswan); ERROR 1064: You have an error in your SQL syntax near 'select uid from user wher gid=apswan)' at line 1 mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: might be a cilly question to u -execuse me.
Hello, mysql select *from accounting where uid = (select uid from user wher gid=apswan); ERROR 1064: You have an error in your SQL syntax near 'select uid from user wher gid=apswan)' at line 1 mysql Take care, you wrote wher instead of where. And as Leonardo pointed out, the version you're using does probably not support subqueries. You might try to write your query as follows: select accounting.* from accounting left join user on accounting.uid = user.uid where user.gid=apswan; Jean-Pierre -- Powered by Linux From Scratch - http://schwicky.net/ PGP Key ID: 0xEE6F49B4 - AIM/Jabber: Schwicky - ICQ: 4690141 Nothing is impossible... Everything is relative! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is there no REGEXP()?
Hi
I can write a query such
where left(t1.txtDevPostCode,5) in
(#QuotedValueList(qryRadius.postcode)#)
but unfortunately UK postcodes are variable length
SO I really want something like
where REGEXP(t1.txtDevPostCode,^[a-z]{1,2}[0-9]0,2}) in
(#QuotedValueList(qryRadius.postcode)#)
This use of REGEXP doesnt exist AFAIK , what work arounds exist, or
how would you do it?
BTW the QuotedValueList contains Short Postcodes of type AA11 or A11
or AA1
zzapper
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing LAST_INSERT_ID()/AUTO_INCREMENT()
Hi Jason,
For MyISAM tables only, you can manually set the auto
increment counter using the syntax:
ALTER TABLE table_name AUTO_INCREMENT = 1000
Keep in mind, however, that this does not change the
value of the LAST_INSERT_ID() since it still
represents the value last inserted. On a freshly
created table, that is 0. So if you create a table,
alter the AUTO_INCREMENT value to 1000, until you
insert a record, LAST_INSERT_ID() will return 0. Once
you insert a record, LAST_INSERT_ID() will then return
1000 (or whatever value you used in the ALTER
statement). (See below for an example session).
Another major caveat (also illustrated below) is that
the value returned by LAST_INSERT_ID() is
session/connection specific and therefore cannot be
trusted in the manner you are thinking of, assuming
you plan to create your ticket number using a syntax
similar to:
INSERT INTO tickets SET ticket_num =
CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y%m'),
LAST_INSERT_ID());
Lastly, you will not be able to use the ALTER TABLE
table_name AUTO_INCREMENT = 1000 command to reset the
ticket number to a starting value each month (if that
was your intent).
There are a lot of little gotchas when using
AUTO_INCFREMENT and especially LAST_INSERT_ID() for
anything other than a simple incremental counter. You
may have to get rather sophisticated in the way you
solve your problem; someone else on the form may have
some ideas from experience. Nevertheless, whatever
your solution ends up being, I highly recommend you
test the heck out it, and make sure you test with
concurrent connections/sessions.
Hope that helps :)
Good Luck,
Mark
===START EXAMPLE===
~From Session 1~
mysql CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
mysql use testdb;
Database changed
mysql CREATE TABLE table01(id SMALLINT(4) UNSIGNED
ZEROFILL NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),
data VARCHAR(10));
Query OK, 0 rows affected (0.03 sec)
mysql SELECT LAST_INSERT_ID();
+--+
| last_insert_id() |
+--+
|0 |
+--+
1 row in set (0.00 sec)
mysql ALTER TABLE table01 AUTO_INCREMENT = 1000;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql SELECT LAST_INSERT_ID();
+--+
| last_insert_id() |
+--+
|0 |
+--+
1 row in set (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM table01;
+--+---+
| id | data |
+--+---+
| 1000 | last=0|
| 1001 | last=1000 |
+--+---+
2 rows in set (0.00 sec)
~From Session 2~
mysql use testdb;
Database changed
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM table01;
+--+---+
| id | data |
+--+---+
| 1000 | last=0|
| 1001 | last=1000 |
| 1002 | last=0|
| 1003 | last=1002 |
+--+---+
4 rows in set (0.00 sec)
~Back to Session 1~
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM table01;
+--+---+
| id | data |
+--+---+
| 1000 | last=0|
| 1001 | last=1000 |
| 1002 | last=0|
| 1003 | last=1002 |
| 1004 | last=1001 |
| 1005 | last=1004 |
| 1006 | last=1005 |
+--+---+
7 rows in set (0.00 sec)
Notice the flux in the last value due to the reasons
described above.
===END EXAMPLE===
--- Jason Williard [EMAIL PROTECTED] wrote:
I have a ticketing system that sets the ID of the
ticket as the
LAST_INSERT_ID. By default, it was counting up from
0. I updated the
ID of the last ticket to reflect a different
numbering scheme
(MM). I would like to automate this but I
don't want to change
the ID of a current ticket. I was hoping that there
was a way to update
the LAST_INSERT_ID. Is this possible? If so, what
is the query that
should be run?
Thank You,
Jason Williard
Janix
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing LAST_INSERT_ID()/AUTO_INCREMENT()
Thanks, Mark, for the syntax. I had actually played around enough to
figure it out already and got it to work the way that I wanted to. I
guess when I asked the question, I wasn't fully aware of what I was
wanting. The LAST_INSERT_ID wasn't a major issue. I ended up using the
following code:
$new_increment = date('Ym') * 1 + 1;
$query = mysql_query( ALTER TABLE `$calls` AUTO_INCREMENT =
$new_increment );
if ( !$query ) {
die( Couldn't Alter Table! );
}
else{
echo Successfully updated AUTO_INCREMENT: $new_increment\n;
}
After a few tests, it seems to be working properly. I've gone ahead and
setup a cron job to take care of this once a month.
Thanks again,
Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedded MySql with Borland BCC32
Hi experts, I need to create a static library for an embedded application with a 4-Byte alignment with BCC32 version 5.0. I prefer using the command line version. Using the files from the current distribution .\mysql\libmysqld results in early compile errors already in config-win.h, line 93, obviously with a mis-interpretation of __int64. Is there any experience about this, possibly a makefile available for that purpose or is another Borland-Compiler option or version more suitable? Kind regards in advance. Dieter W. Kuske I.C.S. Industrielle Automatisierungssysteme GmbH IT SOLUTIONS FOR INDUSTRIAL PRODUCTION ENVIRONMENTS SIGNATURE We are located at: N 52°19'22.3 E010°40'20.8 You may reach us by Phone: +49-5308-961000 Fax : +49-5308-961001 Mobile.: +49-171-81 81 007 UMS :+49-1212-66-81 81 007 Office hours from 09:00 AM through 11:00 PM UTC mailto:[EMAIL PROTECTED] For confidential messages use PGP. Get my PGP-Pub-Key from our homepage http://www.ics-consult.de ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting remote connected machine as value in a select.
Hello, I'm curious as to if something is possible - I don't know if its possible to explain it right. I have a table which contains a bunch machine specific information, file paths, names etc. What I would like to try and accomplish is when I query this table for the information I need, I also receive back a value (that I can define) that is determined by the identity of the machine making the query - either by IP address or whatever. For an example say I have 3 machines total. 1 mySQL DB Server 2 web servers When I connect to mySQL from either of the 2 web servers and say something like Select * from table, one of the values returned would be something like web1 as that is what I defined to be the value for when machine 192.168.0.4 connected to it. It doesn't have to be IP, I'm just using that for a example. Any ideas on how to accomplish something like this? mySql 4.0 RH 9 Thanks -Chris
Help needed
I have a problem with this query Version MySQL 4.0.15-nt The query gets all articles written by author but excludes those articles which are related to products SELECT DISTINCT post.post_id, post.title FROM c_posts post, c_post_product prod, authors_relation rel, c_post_properties prop WHERE rel.post_id != '$nPostId' // This is the post_id of the post which is currently being viewed ( works ) AND rel.author_id = '$nAuthorId' // The author_id of the Author AND post.post_id = rel.post_id // To connect the two tables AND post.post_id = prop.post_id // The properties table AND post.post_id prod.post_id // This is where the problem is. I've also tried != . Details below** AND prop.state != 3 ** I don't get any errors but the query just doesn't Exclude those post_id's which are in the c_post_product table which should be the case. ** I've also tried the NOT IN function, but I get errors when trying that. Seems like it's not supported by my version ?? The tables are basically as follows c_posts post_id title content c_post_product post_id product_id authors_relation author_id post_id c_post_properties post_id And some other fields Suggestions and help - very much appriciated !! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Selective' joins
Hello, Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 23:40: ok, then something is obviously wrong. :) Try selecting some columns from each table, to see what is going on: SELECT a.ID,b.EAN,b.ISRC,c.StationID,d.status,c2.StationID,d2.status ... I found the problem: Normally, the same row could be found in Trackdata and EncodingData. The problem is, that we don't have a unique identifyer, so there are multiple items with the same StationID and ProdID. If I use a JOIN, MySQL seems to multiply the rows, therefore I know where the high load and the high processing time cames from :( I have to wait and see, if the guys decide to redesign the database, there are too much of flaws. Thanks for your help. Regards, Andreas -- Eine Ferengi-Erwerbsregel besagt: Pass auf, was du verkaufst; es könnte genau das tun, was der Kunde erwartet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Records w/in 1 week Time Frame
How can I pull all records within a 1 week time frame? So if I enter 2003-10-19, how can I write a query that will get all records from 2003-10-12 - 2003-10-19. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Records w/in 1 week Time Frame
Off the top of my head, I'd say use the
DATE_SUB(date,INTERVAL expression type) Function (see
manual page
http://www.mysql.com/doc/en/Date_and_time_functions.html
for details)
For example:
SELECT * FROM myTable WHERE date_field =
DATE_SUB('2003-10-19', INTERVAL 7 DAY);
To get the records for the past 7 days from the
current day, use the NOW() function:
SELECT * FROM myTable WHERE date_field =
DATE_SUB(NOW(), INTERVAL 7 DAY)
This assumes your date_field is of type DATE. If it is
of type DATETIME, you will need to consider if you
want records that are exactly within a 1 week time
frame (i.e. to the minute) or within a 1 week time
frame based on the day.
For example, if the current time is 2003-10-19 19:25
and you use this syntax:
SELECT * FROM myTable WHERE date_field =
DATE_SUB(NOW(), INTERVAL 7 DAY)
A record with a datetime of 2003-10-12 19:00:00 is
NOT returned since it is older (by 25 minutes) than 7
days. However, if you change the syntax to limit the
calculation to year, month and day, such as:
SELECT * FROM myTable WHERE date_field =
DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY),
'%Y-%m-%d')
All records from 2003-10-12 00:00:00, including the
one from 2003-10-12 19:00:00 is returned.
I haven't used date time functions extensively so
there may be a more elegant way that I am not familiar
with. If so, someone else, please chime in.
-Mark
--- Developer [EMAIL PROTECTED] wrote:
How can I pull all records within a 1 week time
frame? So if I enter
2003-10-19, how can I write a query that will get
all records from
2003-10-12 - 2003-10-19.
Thanks in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL's 'myslq' database
When you make changes to the mysql database (the database which controls how MySQL works), does MySQL automatically load the updates? When adding users or changing security permissions for hosts or users or databases, it seems like some updates take affect immediately and some don't. Are you supposed to restart the server after modifying the settings or is it supposed to automatically reflect the changes? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL's 'myslq' database
Hi Dan, If you manually modify the mysql database without using GRANT, then you need to FLUSH PRIVILEGES afterwards to get MySQL to reload the permissions. Of course, restarting the server has the same effect. Using GRANT takes care of everything automatically, though. Hope that helps. Matt - Original Message - From: Dan Jones [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Sent: Sunday, October 19, 2003 7:02 PM Subject: MySQL's 'myslq' database When you make changes to the mysql database (the database which controls how MySQL works), does MySQL automatically load the updates? When adding users or changing security permissions for hosts or users or databases, it seems like some updates take affect immediately and some don't. Are you supposed to restart the server after modifying the settings or is it supposed to automatically reflect the changes? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL's 'myslq' database
After modifying user privileges, the new privileges do not take immediate effect. You need to load the changes by issuing the following command: FLUSH PRIVILEGES; See the MySQL manual page http://www.mysql.com/doc/en/FLUSH.html#IDX601 for more detail. --- Dan Jones [EMAIL PROTECTED] wrote: When you make changes to the mysql database (the database which controls how MySQL works), does MySQL automatically load the updates? When adding users or changing security permissions for hosts or users or databases, it seems like some updates take affect immediately and some don't. Are you supposed to restart the server after modifying the settings or is it supposed to automatically reflect the changes? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL Connection String error
Pls help me, I want to know if this connection string can be used for multi user, coz
when I change Server=localhost with server=127.0.0.1 it can not work. thanks
Connect_Str = Driver={MySQL ODBC 3.51 Driver}; _
SERVER=127.0.0.1; _
Database=sekolah; _
UID=root;PWD=tim; OPTION= 1 + 2 + 8 + 32 + 2048 + 16384
-
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
Re: Help needed
* Unnar I have a problem with this query Version MySQL 4.0.15-nt The query gets all articles written by author but excludes those articles which are related to products SELECT DISTINCT post.post_id, post.title FROM c_posts post, c_post_product prod, authors_relation rel, c_post_properties prop WHERE rel.post_id != '$nPostId' // This is the post_id of the post which is currently being viewed ( works ) AND rel.author_id = '$nAuthorId' // The author_id of the Author AND post.post_id = rel.post_id // To connect the two tables AND post.post_id = prop.post_id // The properties table AND post.post_id prod.post_id // This is where the problem is. I've also tried != . Details below** AND prop.state != 3 ** I don't get any errors but the query just doesn't Exclude those post_id's which are in the c_post_product table which should be the case. Try a LEFT JOIN, and check if prod.post_id is NULL: SELECT post.post_id, post.title FROM c_posts post authors_relation rel, c_post_properties prop LEFT JOIN c_post_product prod ON post.post_id = prod.post_id WHERE rel.post_id != '$nPostId' // This is the post_id of the post which is currently being viewed ( works ) AND rel.author_id = '$nAuthorId' // The author_id of the Author AND post.post_id = rel.post_id // To connect the two tables AND post.post_id = prop.post_id // The properties table AND prod.post_id IS NULL AND prop.state != 3 URL: http://www.mysql.com/doc/en/JOIN.html ** I've also tried the NOT IN function, but I get errors when trying that. Seems like it's not supported by my version ?? Well... the IN and NOT IN operators are available, but the operand is a list, you can't use sub-queries in 4.0: URL: http://www.mysql.com/doc/en/Comparison_Operators.html#IDX1177 URL: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is there no REGEXP()?
I'm not sure what you're using to query with, but with the REGEXP expression
you can OR(|) regular expression out the wazoo, so the capablility is built
in MySQL.
and I would consider it to be a pretty straightforward solution, you can do
something like
WHERE lookup RLIKE this|that|other|^starts like this|ends like this$|it
contains.*something.*like.*this
and on and on...
Kelley
zzapper wrote:
Hi
I can write a query such
where left(t1.txtDevPostCode,5) in
(#QuotedValueList(qryRadius.postcode)#)
but unfortunately UK postcodes are variable length
SO I really want something like
where REGEXP(t1.txtDevPostCode,^[a-z]{1,2}[0-9]0,2}) in
(#QuotedValueList(qryRadius.postcode)#)
This use of REGEXP doesnt exist AFAIK , what work arounds exist, or
how would you do it?
BTW the QuotedValueList contains Short Postcodes of type AA11 or A11
or AA1
zzapper
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
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]
Quotes and loading
Hello all I have a bit of a problem with some characters I'm loading from a Filemaker Pro database. The single quotes are stored in MySQL as ASCII character 155 (an 'O' with a tilde over it). I have tried everything I can think of to replace this with the PHP I use to query the db but I have failed miserably. I have similar problems with all single quotes and double quotes. I could probably process the text file before loading it into MySQL (replacing the quotes with their HTML special characters) but I'm wondering if there is anything more fundamental I should be doing. Cheers and any assistance appreciated kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
