1064 errors
Hi, I have several procedures that I have taken from an old Microsoft database, and I have tired to use them in a MySQL 5.1.32 database, but I am getting errors when trying to input them. There are three in total that I am struggling with and would appreciate some guidance... This is a cross selling query: CREATE PROCEDURE 'x'.'CrossSelling' () BEGIN SELECT TOP 5 OrderDetails.ProductID, OrderDetails.ProductName, Count(OrderDetails.ProductID) AS CountOfProductID FROM OrderDetails WHERE (((OrderDetails.OrderID) In (select OrderID from OrderDetails where ProductID=[pid]))) GROUP BY OrderDetails.ProductID, OrderDetails.ProductName HAVING (((OrderDetails.ProductID)[pid])) ORDER BY Count(OrderDetails.ProductID) DESC; END Error is: 1064 '5 OrderDetails.ProductID, OrderDetails.ProductName, Count(OrderDetails.ProductID' at line 3 If somebody could give me an idea of what is wrong here with regards to it working with MySQL, I might be able to make the other two problem functions work with out too many tears. Thanks. Mat
my final 1064 error
Here is my final problem that I am struggling to overcome... SELECT Vouchers.VoucherID, Vouchers.VoucherCode, Vouchers.StartDate, Vouchers.EndDate, Vouchers.Discount, Vouchers.VoucherTypeID FROM Vouchers WHERE (((DateDiff('d',[StartDate],Date()))=0) AND ((DateDiff('d', [EndDate],Date()))=0)); Basically, the error is on the WHERE line of the query, and I assume it is something to do with the DateDiff, but I don't know if there is a MySQL equivalent. Also, is Date() valid MySQL? Thanks. Mat
From MS Access to MySQL
I have taken a Microsoft Access database and have basically copied the structure of it and rebuilt it in MySQL, however, I have encountered some problems with formatting of data. I need to ask what is the most suitable field type to use to retain the content from Access field types The MS Access fields are: Currency (formatted to £ with two decimal places e.g. £10.00) Currency (formatted to £#,##0.00;(£#,##0.00) Date/Time (with a Now() default) Yes/No Number (as a percentage) Number (with field size of Double and Decimal Places of 2) What would be the equivalent of the above for MySQL 5.1.35? If there isn't an equivalent for some, how can I work around it considering that the Access version of the database is already used in an ASP website? Thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Does MySQL 5.1 store queries?
Until recently I have been using 4.0.25 and have just upgraded to 5.1 and just wondered if MySQL now enabled me to store queries in the database rather than have to put them all on my pages. Basically, I want to be able to write some select statements and save them in the database. Thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Securing MySQL on Mac OSX
I am in the process of installing MySQL and PHP on my Mac following instruction in a book written by a chap called David Powers. I have come to a section that is called 'Securing MySQL on Mac OSX'. Basically it states that MySQL is up and running with a default account of 'root' and it's not password protected and so I need to plug that security gap. I've always used root as my account when working on PC's but having read this in David's book, I am now concerned; does this mean that I am open to attack from a potential hacker if I don't password protect MySQL? It mentions that root in MySQL has nothing to do with the root of Mac OSX, but I need to know if MySQL being unprotected in this way has opened a door for hackers. Thanks
Upgrading
Hi all, I am on... wait for it... version 4.0.25 and I want to upgrade to MySQL 5.x Is there anything special I should do in order to upgrade? Do I need to uninstall v4 or can I just download the most current version and double click to upgrade? I am not particularly hardcore mysql minded, and quite honestly, if it ain't broke don't fix it is a good mantra of mine... but I need to move on now as I have received an sql file that is v5 compatible but not v4 compatible. Once I have it up and running I'll be fine, it's just that I am nervous about upgrading and consequently breaking it, and at that point, I'll be struggling to put it right. Any advice on how I can best do this / best practices etc will be very much appreciated. Many thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Connection failed!!
I have just loaded MySQL 5 and MySQL Front on to a new computer, and I am now getting a MySQL-Error which is: Connection failed: 1045 - Access denied for user 'root'@'localhost' (using password: NO) My Old pc has exactly the same settings but that uses MySQL 4 I have done a system restore to the point before I installed MySQL 5, and then installed MySQL 4 and that went on with no problem, and I am able to make a connection with MySQL Front! So, what's different in the processes of installation between v4 and v5 that stops me making a connection with v5? What have I done wrong. In both instances, I have used 'localhost' as my Hostname and 'root' as my username, and 'root' as a password for v4 and 'NO' password for v5 (because I didn't see anywhere where it asked for it - I think). I am using MySQL Front because I'm not Neo and I don't see the world in zero's and one's ;) Mat
Query problem
I am trying to display results from one or the other part of the query, however, at the moment it is showing results from both parts. The Replace part of the query works fine in that it chooses the correct data to display, but the content relevant to /8/ always displays even when I select, say, /13/ or /22/. I need to hide results for /8/ until it is selected. rsChannelArticles.Source = SELECT * FROM bunker01db1.tbl_allarticles WHERE (fld_category LIKE '%/ + Replace(rsChannelArticles__channel, ', '') + /%' AND fld_show = 1 AND fld_reldate =NOW()) OR (fld_category LIKE '%/8/%' AND fld_reldate =NOW()) ORDER BY fld_reldate DESC The reason for this is that the webpage displays content when a release date/time (or embargo) has passed, however, the section /8/ needs content to disappear once that date/time has passed. Any ideas? Thanks
I need to add to content somehow
I have a DB that has a field in it that currently just holds single or double numbers - these numbers are basically a reference to a category in which the particular record should be displayed. However I have now been asked if I can make it so that a particular record can be displayed in more than one category. All I need to do is somehow ask the field to add a forward slash to the front and end of the data, so the data will go from this: 1 33 21 9 11 to this: /1/ /33/ /21/ /9/ /11/ How do I get MySQL to do this? I guess I might have to do it in two steps by firstly adding the slash to the front and then lastly to the back. But I have no idea on how to do it. My reason for doing this is so that I get the webpage to look for numbers that are between the slashes. this will enable me to have numbers like this: /1/33/9/ enabling me to have a record in more than one category. Any help would be appreciated. Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL query problem
Hi, I have built a site with Dreamweaver and I have a problem with a query. I am trying to pass a parameter from one page to another to drill down. Basically, I have one product entry that is in multiple categories on my website. So, say it's a dress, it is therefore related to category 1 which is 'Girls', but it is also more specifically related to category 2 which is 'Girls Dresses'. The way I have set this up is to have a column called MultiCategoryID that holds both the number 1 and 2 like this: /1/2/ When a user clicks a link to look at dresses, the parameter 2 is passed, but my query on the result page is wrong in some way because no records are displaying even though there is content to display. This is what I have so far: SELECT * FROM Products WHERE MultiCategoryID LIKE '/catdrill/' ORDER BY ProductID DESC The parameter settings are: Name: catdrill Type: Numeric Value: Request(MCID) MCID is the url parameter being passed Default value: 2 Only when I test the Default value with an exact match of /1/2/ does any product display. What have I done wrong here? Is there a way to get it to recognise that I want it to pick specific numbers between the slashes rather than the whole lot? I have tried to change the slashes to full stops just in case they are causing problems, but it's still giving the same problem. Thanks. Mat
Select question
I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Thanks Mat
Is this a permissions problem?
Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [MySQL][ODBC 3.51 Driver]Access denied for user: '@localhost' to database 'client_db1' I am having trouble getting any MySQL site to work on my local PC, but they work fine remotely. I have just changed PC and now have IIS whereas before I used to have Personal Web Server. I have just managed to stop this happening with any microsoft access database site by changing permissions, but I am not having the same kind of result with the MySQL sites. Do I need to change permissions for the MySQL databases? If so, up on which folder do I change permissions. My websites are located in C:\inetpub\wwwroot, and MySQL is located at C:\mysql. The location of these folders didn't have any adverse effect with personal web server, does it with IIS? Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I am stupid
I had managed to land a job that required me to learn MySQL quickly. I was used to working with access though, so what I did was find a utility for access called MyAccess which enables you to work in access but you are actually affecting a MySQL db - it has a few funny things that could put you off, but it is an exteremly good way off getting data from Access to MySQL if you are inexperienced - I think it is meant for Access 97, but I do have it working with Access 2003. However, one thing that I did find extremely useful was an application called MySQLFront. It works very much like access. Search on the MySQL site for these, but I can remeber if they are something I had to pay for or not. I think they might be shareware which is usually a small fee. --- Aman Raheja [EMAIL PROTECTED] wrote: Hey there It's alright - everyone starts someday. Go on to start with the docs on mysql's website. If there's something you don't get, come back here. This is a great list to help newbies. For your qns Log in to mysql; mysql create database db; mysql source db.sql; (this considers your db.sql in in the same dir) the alternative is, on command line $ mysql -p -uuser db db.sql The -p option makes you prompt for a password, so don't use it if you don't have one yet for this user. Also to know how only specific user may be configured to use this database, look at GRANT in mysql docs. HTH Aman Raheja http://www.techquotes.com On Sat, 2 Apr 2005 19:20:46 +0100 (BST), Niki Lampropoulou [EMAIL PROTECTED] wrote : I dont know how to do this very basuc, just atrated working with MySQL and not sure about what I am supposed to do.. Please help!! 2. Create a database for the program to use in MySQL. 3. Create the tables in the new database using db.sql which is in the sql directory. niki Send instant messages to your online friends http://uk.messenger.yahoo.com -- 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]
Timestamp plus 365 days
Am I able to add 365 days to an already existing TIMESTAMP on a subscription service? If so I was going to use a form on a web page to update it and in the insert statement use Now() + INTERVAL 365 DAY but after some consideration, this would be wrong. This would cause a problem if a current subscriber updated their subscription for another year before their present subscription ran out. In such a case, how do I take a date, eg: 1st March 2004 and add 365 days to it so that no matter when I edit it, it returns the date 1st March 2005? Do I have to change the column type in the database or is it some clever sql - or both? Thanks Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create a date value 7 days from now
I am trying to get MySQL to automatically create a date seven days from the date a new record is created. The new records will be inserted through an ASP, VBScript website. I have managed to get this to work in MS Access by typing in to its default field in the database Now()+7. As you would expect, it gives me a date 7 days from now. I have tried the same with MySQL and it keeps throwing the default I type out and replacing it with -00-00 00:00:00 Is it because MySQL doesn't store procedures or something? How do I get this to work? Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create a date value 7 days from now
I assume the INSERT statement you have suggested goes on the webpage that has the form from which the new record will be created, Is that correct? Thanks. Mat On Saturday, February 7, 2004, at 02:24 PM, Roger Baklund wrote: * Matthew Stuart I am trying to get MySQL to automatically create a date seven days from the date a new record is created. The new records will be inserted through an ASP, VBScript website. I have managed to get this to work in MS Access by typing in to its default field in the database Now()+7. As you would expect, it gives me a date 7 days from now. I have tried the same with MySQL and it keeps throwing the default I type out and replacing it with -00-00 00:00:00 You can't use a function as a default value in mysql. The special data type TIMESTAMP will use NOW() as a default value, but you can't use NOW()+7 or any other expression involving a function as a default value. What you need to do is include your expression in the INSERT statement: INSERT mytable SET mycol1=something,starttime=NOW(),endtime=NOW()+7; Now, this will set the endtime 7 _seconds_ into the future. If you want it to be 7 _days_, you could use something like this: INSERT mytable SET mycol1=something,starttime=NOW(), endtime=NOW()+INTERVAL 7 DAY; ...or for older versions, before release 3.23.4 (28 Sep 1999): INSERT mytable SET mycol1=something,starttime=NOW(), endtime=DATE_ADD(NOW(),INTERVAL 7 DAY); URL: http://www.mysql.com/doc/en/DATETIME.html URL: http://www.mysql.com/doc/en/Date_and_time_functions.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need DSN less help
I am having problems with making a connection between my website and the MySQL db on a different server. I have tried both hand coding and using the MyOLEDB application for MySQL. What am I doing wrong? If you think this looks a little strange, it is because it is a dreamweaver connection file. This is an error that I get when I try to browser the site: Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified /csi/index.asp, line 8 This is the code on index.asp, line 8: Recordset.ActiveConnection = MM_connection_STRING and here is the code from the connection file: % ' FileName=Connection_odbc_conn_dsn.htm ' Type=ADO ' DesigntimeType=ADO ' HTTP=false ' Catalog= ' Schema= Dim MM_connection_STRING MM_connection_STRING = Driver={ mySQL }; _ Server =servername.com; _ Port =3306; _ Option =131072; _ Stmt=; _ Database=databasename_db; _ Uid=database_user; _ Pwd=database_pass % Can somebody please tell me what is wrong with this? My ISP is a reseller who has no experience of MySQL and because he is the customer (not me) they will only talk to him. Besides that they are quite useless when it comes to dreamweaver. Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need DSN less help
The ISP do have the driver installed and they also know what they are talking about. The problem is they won't talk to me because I am not the reseller (ie their customer). The reseller I have used knows nothing about MySQL - he knows everything else but MySQL. Mat On Thursday, February 5, 2004, at 02:34 PM, [EMAIL PROTECTED] wrote: Matt, Are you sure that they have the MySQL driver installed on their webserver? Since you say that they have no experience with MySQL, they probably do not, and that would be your problem. Regards, Gary H. I am having problems with making a connection between my website and the MySQL db on a different server. I have tried both hand coding and using the MyOLEDB application for MySQL. What am I doing wrong? If you think this looks a little strange, it is because it is a dreamweaver connection file. This is an error that I get when I try to browser the site: Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified /csi/index.asp, line 8 This is the code on index.asp, line 8: Recordset.ActiveConnection = MM_connection_STRING and here is the code from the connection file: % ' FileName=Connection_odbc_conn_dsn.htm ' Type=ADO ' DesigntimeType=ADO ' HTTP=false ' Catalog= ' Schema= Dim MM_connection_STRING MM_connection_STRING = Driver={ mySQL }; _ Server =servername.com; _ Port =3306; _ Option =131072; _ Stmt=; _ Database=databasename_db; _ Uid=database_user; _ Pwd=database_pass % Can somebody please tell me what is wrong with this? My ISP is a reseller who has no experience of MySQL and because he is the customer (not me) they will only talk to him. Besides that they are quite useless when it comes to dreamweaver. Thanks Mat -- 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]
Uploading database content with PHPMyAdmin
I have just started to use PHPMy Admin in the last hour or so. How do I upload the data and structure to a remote server. I have done a mysqldump and have tried to upload it but I keep getting an error. I create a statement in a window, browse to a file to upload it and submit the form. I wait a few minutes and I then get an error message saying I am using the wrong syntax. How do I instruct it to accept a mysqldump file? If it doesn't accept dumped files, then what do I have to do to get the data up to a remote server? I have searched for PHPmyAdmin but didn't get any reasonable results. So I don't even know what I am looking for. Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL select help required please
I am trying to create a html search results page with the following: SELECT * FROM tbl_allarticles WHERE (fld_headline LIKE'%userinput%' OR fld_summary LIKE'%userinput%' OR fld_body LIKE'%userinput%') AND fld_category LIKE 'catvalue' The above works fine, but the below code is giving me some jip (It is simply a command to look for dates between user inputted start and end dates, but it is not working) it just follows on from the above code: AND fld_reldate BETWEEN 'startdatevalue' AND 'enddatevalue' What problem are you having exactly? An error, or just not getting the results you expected? Maybe you need to format the date in mmdd format before giving it to MySQL. The problem I am having is that I am getting a Data type mismatch in criteria expression error. The way I understand this to work is the quotes around the start and end date values make these values variables which will be issued at runtime by the user when submitting the form on the previous page. Is that right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIMIT not working problem
This query is working fine apart from the LIMIT part. I cant see what is wrong with it, I have tried it in different places and still no luck. Does anybody know why its not working? WHERE (fld_headline LIKE'%userinput%' OR fld_summary LIKE'%userinput%' OR fld_body LIKE'%userinput%') AND fld_category LIKE 'catvalue' AND fld_reldate BETWEEN 'startdatevalue' AND 'enddatevalue' AND fld_show = 1 AND fld_reldate = NOW() LIMIT 50 ORDER BY fld_reldate Error is: You have an arror in your SQL syntax. Check the manual blah, blah for the right syntax to use near limit 100 at line 4. Where limit 100 has come from I dont understand, but what ever I put as a LIMIT value it always states limit 100. TIA Mat
SQL select help required please
I am trying to create a html search results page with the following: SELECT * FROM tbl_allarticles WHERE (fld_headline LIKE'%userinput%' OR fld_summary LIKE'%userinput%' OR fld_body LIKE'%userinput%') AND fld_category LIKE 'catvalue' The above works fine, but the below code is giving me some jip (It is simply a command to look for dates between user inputted start and end dates, but it is not working) it just follows on from the above code: AND fld_reldate BETWEEN 'startdatevalue' AND 'enddatevalue' I have tried to enclose it with brackets in several different places, without brackets too. Any help would be appreciated. TIA Mat
Performance problems
I have built a web site and I am testing it locally on my PC. Testing through Internet Explorer is awfully slow and most of the time I am getting error 'ASP 0113' script timed out. The table I am calling records from is quite text heavy (a few hundred to a 1,000+ words per field in some places). I have built a search facility too and in doing so I have added indexes to the table to try to make the search results appear quicker. I have tried selects within the MySQL command window and they are much quicker. For example I have selected all from the table in question and it returned every record (2,000 of them) in 8.9 seconds. Selecting specific records takes fractions of a second. The web pages are taking minutes before timing out. My system is 1ghz processor, 650ram, Windows ME running Personal Web Server and MySQL 4.0.15. Any advice on how I might be able to improve performance with what I have would be appreciated. TIA Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convert MS Access to MYSQL
On Friday, January 30, 2004, at 03:12 AM, [EMAIL PROTECTED] wrote: There was some discussion in December of converting MS Access databases to Mysql and the DBManager program was recommended. I have recently converted access to mysql. I used access to import a CSV file and ensured all data was in the right place. I then built a table in mysql ensuring that all columns were in the same order as the access table (and of the correct format) I then used a plugin for access called MyAccess (shareware available via mysql.com), it enables you to use access as your GUI to mysql. I then simply used access to copy from one table to the other. It was a very text heavy import to deal with, with hundreds if not thousands of illegal characters that would corrupt a mysql db. Myaccess escaped them all for me. It was dead simple and I'm no techie. Job done - in about an hour. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Time out problem
I have developed a website on my PC and I am testing there too. I keep getting script time out errors, can I stop this from happening? I am basically having to build an identical site and test it using access. Double the work!! I can't believe that access is responding tens if not hundreds of times faster and without timing out once! What could I have done wrong? I use a PC with 1ghz processor, 650ram, Windows ME running personal web server (not IIS), Mysql 4.0.15, I am also using MySQL Front. TIA Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem searching table
ASP and VB I have created a search web page searching a MySQL table with the following code: CREATE TABLE tbl_allarticles ( fld_ID mediumint(8) unsigned NOT NULL auto_increment, fld_category tinyint(2) unsigned NOT NULL default '0', fld_updateddate timestamp(8) NOT NULL, fld_createddate timestamp(8) NOT NULL, fld_mainstory char(1) NOT NULL default '', fld_reldate date NOT NULL default '-00-00', fld_headline varchar(255) default NULL, fld_summary text, fld_body mediumtext, fld_displayname tinyint(1) unsigned default NULL, fld_show tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (fld_ID), KEY fld_category (fld_category,fld_updateddate,fld_createddate,fld_mainstory,fld_reldate, fld_headline,fld_displayname,fld_show), FULLTEXT KEY fld_body (fld_body), FULLTEXT KEY fld_summary (fld_summary) ) TYPE=MyISAM; SELECT * FROM tbl_allarticles WHERE fld_headline OR fld_summary OR fld_body LIKE '%userinput%' AND fld_show = 1 with the variable of: userinput'Request(qstextfield) 'qstextfield' is the name of the text area in which the user inputs their search criteria. It is working fine as a search but it seems only to be looking in one field, that being fld_body. I have done exact searches to match content in fld_headline and I am getting zero results. I have also tried AND instead of OR in the sql, but that returned no results on any search... I suspect it is something to do with KEY, I created the table with MySQL-Front and expected it to create INDEX's, but it has created KEY, something I have never come across before. Any help in how I might make this work will be appreciated, thanks. Mat
Selecting by date
I have created a sql statement that enables me to select all entries in to MySQL that are dated today, I am trying to do the same for all items that are now one day old and also two days old etc I am doing this: SELECT * FROM table WHERE category = 3 AND show = 1 AND date = NOW()-1 I have it working for todays records, it filters out any that don't match todays date or NOW(), but it still returns the same records for NOW()-1, or NOW()-2. I have tried DATE() but this gives me an error. What is the correct syntax for what I am trying to achieve? Thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with a dump
I have tried two ways of dumping data but it doesn't seem to be working. One using the admin window with mysqldump dbname dumptest.sql but I don't know if it has done anything because all it did was return to a new blank line. I can't find anywhere a file named dumptest.sql The other way was to use MySQL-front by way of export, this did produce a file but when I come to import it again to ensure that the file is OK I got this error: 1064 - You have an error... ...near'()' at line 1. I have looked on the MySQL site and it is a ER_PARSE_ERROR. I don't know what this means. When I exported and imported I just used the default settings that MySQL-front has to offer, I didn't select any other options that were available. Line one of the sql document when opened in notepad is '# MySQL-Front Dump 2.5'. The first line of what I call sql is 'CREATE TABLE all_articles (' and the following is the first line of data to import: INSERT INTO all_articles VALUES(1, 3, 2003-03-14 14:21:00, 2003-03-14 14:21:00, N, 2003-03-14 00:00:00, UNITED STATES: ALPA Applauds Senate Action, NULL, The following statement was issued bAir Line Pilots Association (ALPA), International/b, \'We applaud the action taken. (Tel: +0 000 000 000; web site: http://www.site.org/), 1, 1); I have removed what is basically a lot of text in one of the fields and left any bits that might be causing my problem. Any help/suggestions is greatly appreciated. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help with syntax for mysqldump
I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql If I can't get the dump file to work, how do I get the database on my local machine up to the remote server. I am using version 4.0.15. Are there any bugs? Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
mysqldump is run at the system command-line, not within the mysql client environment. This is most likely to be my problem then. I assumed that what is called the system command line to be the mysql client environment. The tutorial book that I have been going through instructs me to issue this command in the start menus run dialogue box: C:\Windows\Desktop cd C:\mysql\bin and from there I have issued all commands in the black window. Is this not the command line, and if not, what is? Sorry for my ignorance, I am still a beginner. Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing a dumpfile
Right having just got to grips with the mysqldump command, I would like to be able to know how to import the database back in to MySQL should anything happen to my PC. Does mysqlimport have to be done in the command line window like mysqldump, and if so, how? It's just that I tried to import stating terminated, enclosed, escaped, etc and by the time I had come to list the db name to import in to and the path to the file I wish to import, the window wouldn't let me type anymore. Why? Did it get as bored as I did? What syntax do you people out there use? Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I'm using 4, host using 3!!!
I am using version 4 of MySQL and the people who I am told to use to host the site are running 3 on the server I will be using. I am not using any real complex table structures eg: autoincrement, date, timestamp, medium int, small int, indexes. I have to do a dump of data to install on remote server. Will this be backwards compatible? Should I just install version 3 and be done with it? Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Passwords query
I have been given these passwords for a MySQL db on a remote server. I have to dump the records to this volume on the remote and I am not sure what these passwords mean to me. SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER username_master password SELECT,INSERT,UPDATE,DELETE username_runtime password SELECT username_read password Do these relate to the connection between the database the website calling on it, or is it for editing directly in MySQL, such as the admin window? Do I have to state these usernames and passwords in the website? I am trying to find info on it, but am not doing too well at present. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: apostrophe error
Man I have been banging away at this for a week and I am only just getting to a point where I am finding a resolution. There seems to be a lot of info out there for this kind of thing, but very little on how to implement it. I use ASP and VBScript with Macromedia Dreamweaver so with asp I can use the replace() function (PHP has many variations apparently). I have found that Dreamweaver during construction of the sql insert statement adds some code to supposedly escape quotes - it doesn't, but with a bit of jiggery-pokery I have managed to get it to insert the slash. This is the code in the insert statement which sits above the html headers etc: If (MM_altVal ) Then MM_formVal = MM_altVal ElseIf (MM_delim = ') Then ' escape quotes MM_formVal = ' Replace(MM_formVal,',\'') ' Else MM_formVal = MM_delim + MM_formVal + MM_delim End If The problem with this is I have also added it to an update page and when the update is submitted, another slash is added - this undoes the initial escape of the quote and escapes the first escape... if you see what I mean. O\'Brien becomes O\\'Brien. I have added this directly below the above code in the same sql insert statement to try to prevent the second escape but it's not working: If (MM_altVal ) Then MM_formVal = MM_altVal ElseIf (MM_delim = ') Then ' escape quotes MM_formVal = ' Replace(MM_formVal,\\',\'') ' Else MM_formVal = MM_delim + MM_formVal + MM_delim End If I get an error code of: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''O\'''Brien'' where id = 27'. /quotetest_site/TMP29oavr7xvf.asp, line 123 Line 123 is the execute command I have tried two identical replace values in the one sql insert statement and the insert works but gives results of \'O\\'\'Brien\' for some reason. I think I am on the right track but can't quite figure it out. I don't know if there should be two separate insert statements, or two replace() values in one insert statement. For the time being, I have managed to over come it (I think) by using replace values of ',acute; but this isn't ideal. If any asp and vb gurus could help me with this I think it would also help a few others too. I have heard of 'Magic Quotes', but got no results on a search of mysql.com and I also had a look at the mysql_real_escape_quotes function, but I could make head nor tail of it. Mat
Escaping single quotes
I am on my first MySQL DB and it is very text heavy because it is a news site, therefore there is a great deal of use of the apostrophe or as MySQL would see it the single quote. I was hoping to be able to use double quotes to overcome the need to constantly have to escape the apostrophe/single quote, and where speech marks or a double quote is required, I was going to suggest that the editors of the site use two single quotes. Is this going to cause me problems? However... I have seen a message in the mailing lists that stated the use of double quotes locks me into MySQL for ever as most other DB packages use only single quotes to enclose data. I am concerned about this just incase my client decides that for some reason in the future they want me to use Oracle or MS SQL etc. On top of this is have also just seen messages about 'magic quotes', what is this then? A reply to the message also mentions the use of 'mysql_real_escape_string'!! Does this mean that when a user submits a html form with a name such as 'O'Brien' the name is automatically escaped to 'O\'Brien'? Would I be safer to use double quotes to enclose data, eg. O'Brien? I obviously can't ask all visitors to the site who enter their name to escape each quote. Are my prayers answered? I have been a bit concerned that I might not be able to completely fulfill my clients needs because of them being restricted to the use of single quotes. Thanks in advance Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie FTP question
I have my website and MySQL db sitting locally on my PC. I need to get it to the remote server, I know how to get the website pages and images there, but am not sure how to transfer the data, tables and all from the database. Can somebody help me or point me to a tutorial? Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
struggling newbie - datetime or timestamp problem
I have been sent a url for datetime explanations in the MySQL manual. I have learnt a few things, but I am also more confused than ever. I am trying to create a couple of columns (one createddate and one updateddate) in a MySQL table that are DATETIME or TIMESTAMP values, but I am having trouble understanding how it works. I want both columns to auto add a date and time when a record is first inserted, but only the updateddate column to update when the record is updated. Could someone give me the code for these two columns please? This is what I have: CREATE TABLE all_articles ( id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, createddate DATETIME DEFAULT -00-00 00:00:00 updateddate DATETIME DEFAULT -00-00 00:00:00 article TEXT ); Obviously these aren't going to auto add/update because I am not stating a NOW() or NULL value anywhere, but I thought this would be the cleanest way to give it to you. The way the zeros, hyphens and colons are typed is how I would like to store my dates if at all possible. TIA Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing dates from access
I have an access db with populated fields, some of them are date fields. One is a created date and another is a updated date. I haven't tried it yet, so I am not sure what I will get but I assume that if I am asking MySQL to automatically update these fields rather than manually have to fill them in, I would lose the correct dates? Will MySQL replace the original dates with that of the current date? If so, how can I overcome this? The Access db I have doesn't have any automatic formatting for the date fields at the moment, but I want to introduce it in the MySQL version. Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I use TAB delimiters for importing CSV
Am I able to specify to somebody who is dumping from Oracle to use TAB as the delimiter? Does MySQL enable me to specify during import that the CSV is a TAB delimited file? What do most of you use that would not cause problems with text heavy dumps? Double quotes and comas etc are used through out the data in the db. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relationships
How do I create relationships between tables? I have tried but have no way of being sure that I am successful just by looking at the db. I am not sure how to relate one record in one table to it corresponding record in another. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Escaping single quotes
I am going to take over an existing website and in its present format it is a site powered by an Oracle DB. I will be migrating to MySQL. The site is a news based site and has the use of the single quote or apostrophe (') through most of it's articles. I think that each article at present is an external .txt file that is pulled in to Oracle. If I carried on this method of having an external .txt file would that over come the necessity to escape (\') every single quote in each article? If so, how might I be able to pull that data through so that it loads into the web browser. I asume it is some sort of http:// based link as it would be for an image. Is there any special kind of formatting I have to do to the text file for it to show as html? TIA Mat Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 --
MySQL beginner - upload to remote confusion
I have just moved from MS Access to MySQL, I haven't really got going yet, and I am a little confused at how I interact with a db created in MySQL. In the past I have always kept my database in its associated website folder within wwwroot. However, I have been advised to keep it in C:\mysql\data. Does it need to be here, or can I locate it in wwwroot? I use Dreamweaver and I am also a little confused as to how I can upload the db using the ftp/site manager if it isn't in the wwwroot site folder from where dreamweaver manges the site. I have looked at a test db I have created in the data directory - Access creates just one file which is the whole database, however I can't seem to locate one particular file, there seems to be many. If this is the case, what do I need to upload to ensure all of the database is uploaded and where do I upload to on the remote site? Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]