Fulltext index -first query slow, subsequent queries fast
Hi List
I have a table with a fulltext index across five fields, with about 2.2
million records and a data size of about 5.6 GB (index another 3.5 GB).
When I test a query that uses fulltext matching, the first run takes
about 15-16 seconds to complete. The second run takes about 0.1 sec and
subsequent runs will all all ge around the 0.1 sec time.
The query that I use has a call to NOW() as one of the criteria, so i
know that the query results will not be cached. Yet, the times would
suggest that some sort of caching effect is being observed.
Is there something I can do that can return more consistent query
performance - hopefully with a time somewhere between the two extremes?
The structure of the table as well as the query are below.
All help will be appreciated.
CREATE TABLE `article` (
`article_id` int(11) NOT NULL auto_increment,
`site_id` int(11) NOT NULL default '0',
`article_code` varchar(80) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`publication_id` int(11) default '0',
`rating_id` int(11) NOT NULL default '0',
`status_id` int(11) NOT NULL default '0',
`section_id` int(11) NOT NULL default '-1',
`template_id` int(11) default NULL,
`headline1` varchar(255) default NULL,
`headline2` varchar(255) default NULL,
`headline3` varchar(255) default NULL,
`live` enum('Y','N') NOT NULL default 'N',
`modified_date` datetime NOT NULL default '-00-00 00:00:00',
`original_date` datetime NOT NULL default '-00-00 00:00:00',
`flash` enum('Y','N') default NULL,
`expiry_date` datetime default NULL,
`embargo_date` datetime default NULL,
`embargo_hour` int(11) default NULL,
`embargo_day` int(11) default NULL,
`message` varchar(255) default NULL,
`section_front` enum('Y','N') NOT NULL default 'N',
`front_page` enum('Y','N') NOT NULL default 'N',
`author_id` int(11) default '0',
`is_urgent` enum('Y','N') NOT NULL default 'N',
`live_date` datetime default NULL,
`page_number` int(11) NOT NULL default '0',
`is_free` enum('Y','N') NOT NULL default 'N',
`source_id` int(11) default NULL,
`edition` int(11) default NULL,
`master_article` int(11) default NULL,
`newspapersection_id` int(11) default NULL,
`blurb` text NOT NULL,
`body` text NOT NULL,
`is_indexed` enum('Y','N') NOT NULL default 'N',
`zone` varchar(255) NOT NULL default '',
`warning` varchar(255) NOT NULL default '',
`blurb_is_intro` enum('Y','N') default 'N',
PRIMARY KEY (`article_id`),
KEY `site_id` (`site_id`),
KEY `article_code` (`article_code`),
KEY `name` (`name`),
KEY `publication_id` (`publication_id`),
KEY `rating_id` (`rating_id`),
KEY `status_id` (`status_id`),
KEY `section_id` (`section_id`),
KEY `live` (`live`),
KEY `modified_date` (`modified_date`),
KEY `original_date` (`original_date`),
KEY `expiry_date` (`expiry_date`),
KEY `section_front` (`section_front`),
KEY `front_page` (`front_page`),
KEY `live_date` (`live_date`),
KEY `is_urgent` (`is_urgent`),
KEY `page_number` (`page_number`),
KEY `author_id` (`author_id`),
KEY `embargo_date` (`embargo_date`),
KEY `master_article` (`master_article`),
KEY `newspapersection_id` (`newspapersection_id`),
KEY `site_status` (`site_id`,`status_id`),
KEY `flash` (`flash`),
FULLTEXT KEY `blurb`
(`blurb`,`body`,`headline1`,`headline2`,`headline3`)) ENGINE=MyISAM
DEFAULT CHARSET=latin1 MAX_ROWS=1000 AVG_ROW_LENGTH=2586;
SELECT SQL_CALC_FOUND_ROWS article_id AS iArticleId, MATCH(blurb, body,
headline1, headline2, headline3) AGAINST (microsoft) AS dRelevance
FROM article WHERE embargo_date = NOW() AND status_id IN (-1, -6, -10)
AND site_id = 45 AND MATCH(blurb, body, headline1, headline2, headline3)
AGAINST (microsoft) ORDER BY embargo_date DESC LIMIT 0, 25
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
Jerry Schwartz wrote: File system, or disk caching, uses some kind of algorithm to hold chunks of files in system RAM. That way a program can get to it more quickly than if it had to go out to the disk. The algorithms vary, depending upon the smarts of the program and the smarts of the file system. The system might keep the most recently used stuff, the most frequently used stuff, even the stuff it thinks you will need based upon the pattern of use. Regards, snip Hi Jerry Thanks for the explanation. So, in short, I am most likely hitting a wall with the fulltext index, and I am just getting lucky cos of the disk caching, but I am not going to be able to get away from that initial slow load...rats. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike snip I have read about sphinx and the good performance boost it provides - unfortunately there is a lot of legacy code reading off the db, so I will need to get all sorts of stuff signed off, before I can make any major changes ;). But I will definitely look into it so that I can offer it as a possible solution. Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Passing db names to a stored proc in MySQL 5
Hello List I have tried dynamically assigning a database name to a stored proc via its parameter list: CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR) BEGIN SELECT * FROM test_db_name.test_table; END; MySQL does not resolve test_db_name to the value passed in the parameter, and the query fails because there is no database with that name. Is there any way around this? Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passing db names to a stored proc in MySQL 5
[EMAIL PROTECTED] wrote: Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 07:37:17 AM: Hello List I have tried dynamically assigning a database name to a stored proc via its parameter list: snip snip Use a prepared statement. Build your SQL statement as a string, prepare it, and execute it. It's all right there in the manual. snip I am afraid that it doesn't work - unless I am being thick (yes, I know, not like it hasn't happened before). Used a test statement such as this: PREPARE query_statement FROM SELECT * FROM ?.sites Which causes a syntax error right at the ? Whereas the following statement works fine, but doesn't help me : SELECT * FROM sites WHERE site_name = ? While RTFM, I came across the following: Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth. Which, if combined with my test results leaves me with Martijn's original answer of it not being possible after all :(. Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passing db names to a stored proc in MySQL 5
[EMAIL PROTECTED] wrote:
snip
You were so close! Try it more like this:
SET sSQL=CONCAT('SELECT * FROM ', passed_in_db_name, '.sites WHERE
site_name=?');
PREPARE query_statement from sSQL
snip
Doh!
I cannot believe that I didn't think of that - seems I was being dense
after all!
Thanks Shawn will give it a try
Rory
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Passing db names to a stored proc in MySQL 5
snip
You were so close! Try it more like this:
SET sSQL=CONCAT('SELECT * FROM ', passed_in_db_name, '.sites WHERE
site_name=?');
PREPARE query_statement from sSQL
snip
Yup - got it to work - but it is so much hassle that I might seriously
reconsider having queries run across databases, as it works fine for one
or two stored procs, but I really cannot imagine doing hundreds this
way! Only change I had to make was to put the @ in front of the variable
name otherwise the prepare statement bombs!
Thanks Shawn and Martijn for the help
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN combined with JOIN
Hello List I am running a query to find accounts that not represented in an invoice: Table structure is as follows: Invoice -- invoice_line_number account_number Account_Parameters - account_id parameter_id parameter_value Parameter_Library --- parameter_id parameter_description The account parameters table stores a number of parameters for each account entry including the account number used in the invoice. Therefore to match account_id to account_number I have to use the relevant parameter_value to match the account_number. I also want the query to return 0 if I found an account number in the invoice that I do not have an entry for in account_parameters. I use the invoice_line_number field to limit the number of records I pull from Invoice The query I am trying is as follows: SELECT IFNULL(AP.account_id, 0), I.account_number FROM Invoice I LEFT JOIN Account_Parameters AP ON AP.parameter_value = I.account_number JOIN Parameter_Library PL ON AP.parameter_id = PL.parameter_id AND PL.parameter_description = Account_Number WHERE I.invoice_line_number 10 However, all that is returned is those records that match and not those from Invoice that didn't match. Meaning that somehow my LEFT JOIN is incorrect - can anybody help? Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
Jason Ferguson wrote: The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). Jason snippety-snip Hi Jason If it's not too late (aren't timezones wonderful?) ;). Have you considered using an interim table into which you load your file in its entirety? Load all fields and have each field set to something like CHAR or VARCHAR big enough to accommodate the fields in the file. Then just pick the columns that you are interested in: Then you can do a : INSERT INTO final_table (col_1, col_2..col_n) SELECT col_1, IF(col_3=unknown, 0, col_3) AS col_2 FROM interim_table Also means that you don't have to necessarily upgrade to 5.x (as per your previous problem) - unless you want to, of course ;) Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1064 error
Schalk Neethling wrote: Greetings What might be causing the 1064 error in the following query? SELECT mem_number, first_name, last_name, area_represented, joining_points + E-Model Challenge + SA Pro Model + Star Model Challenge + Eastern Cape Classic + SA Model Super Star + KZN Model GP + Mpumalanga GP + Glam Slam Model Challenge + Model Man Woman + SA Look of the Year + SA Face of the Year + KZN Classic + Eastern Cape GP + Western Cape Classic + Free State Classic + North West GP + Northern Province Classic + SA Model Open + Cover Search + Champion of Champions + Northern Cape Classic + Goldfields Model GP + Limpopo Classic + SA Model Portfolio + Top Model Challenge + Gauteng Model Classic + Year of the Model AS total_points FROM modelcup.ab_leader_board All of the rows does exist in the table and all row names are correct. TYIA! Wow, a select statement like that is always going to cause you problems - I know if I had to do it I would be sitting with typos from now until Christmas. If I may suggest an alternative design, which will be a bot more normalised and perhaps easier to work with ... making a suppostion based on the above I think a table structure such as the following may make lfe easier... Table: models ___ |mem_number |first_name |last_name |area_respresented | | Table: events ___ |event_id |event_name Table: events_points |event_id |mem_number |points Would result in a query that looks like this: SELECT a.mem_number, a.first_name, a.last_name, a.area_represented, IFNULL(SUM(b.points), 0) AS total_points FROM models LEFT JOIN events_points ON a.mem_number = b.mem_number GROUP BY a.mem_number, a.first_name, a.last_name, a.area_represented Makes for a design that is easier to maintain (you could also treat your joining points as another event). New events just get added as an entry in the events table and are then referenced in the events_points table rather than having to add a whole stack of columns ;). Sorry, I know, answering an unasked question but I hope it helps Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_safe just ends
Cam wrote: So I'm a little confused here I've installed mysql-standard-4.0.20-pc-linux-i686.tar to /usr/local/mysql and then ran the scripts/mysql_install_db with seemingly no errors. After reading section 5.1 'the MySQL Server and Server Startup Scripts' I figured that cd /usr/local/mysql bin/mysqld_safe would simply work but no. I get [EMAIL PROTECTED] mysql]# bin/mysqld_safe [2] 11616 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040728 10:50:16 mysqld ended [2]+ Donebin/mysqld_safe [EMAIL PROTECTED] mysql]# snip I don't know why, but something at the back of my mind is shouting 'Check Permissions', I think you need to check the OS file permissions for the folder in which you have stored the data for mySql. If memory serves, part of the installation process is to change file permissions and /or groups and ownership - methinks this is where your problem lies -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391 - mobile www.nebula.co.za This e-mail is intended only for the person to whom it is addressed and may contain confidential information which may be legally privileged. Nebula Solutions accepts no liability for any loss, expense or damage arising from this e-mail and/or any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL book
Gerald Taylor wrote: I totally recommend Paul Dubois's book. Excellent book I own the first edition. I bet the second edition is just as good and more up to date. Paul DuBois wrote: At 1:55 +0200 7/26/04, Schalk Neethling wrote: Can anyone suggest o great book to learn MySQL inside out? I am thinking of getting: *MySQL By* Paul DuBois http://www.informit.com/safari/author_bio.asp?ISBN=0735709211 - New Riders Publishing That's the first edition. I would suggest getting the second edition instead. :-) (http://www.kitebird.com/mysql-book/) I have both and the second edition is even better than the first. Highly recommended. -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391 - mobile www.nebula.co.za This e-mail is intended only for the person to whom it is addressed and may contain confidential information which may be legally privileged. Nebula Solutions accepts no liability for any loss, expense or damage arising from this e-mail and/or any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy the database
mysql wrote: Hello, I wants to copy a full database with all its information to a database on another computer. Is it really that simple that I just create a new db on the new system and then copy the files from the old db directory into the newly created one? cheers Alex A safer solution is to use mysqldump - this dumps the contents of your database and the data structure as SQL queries, and then you can just treat the dumped file as a batch file when recreating. -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391 - mobile www.nebula.co.za This e-mail is intended only for the person to whom it is addressed and may contain confidential information which may be legally privileged. Nebula Solutions accepts no liability for any loss, expense or damage arising from this e-mail and/or any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding the records in one table that are not in another table
Jeff Gannaway wrote: I have 2 tables - ProductsOLD and ProductsNEW. I need to find the records that are in the ProductsOLD table and are NOT in ProductsNEW (this will tell me which products have been discontinued). Here's some sample data: +==+ | ProductsOLD | +==+ + Vendor | ID | ++-+ | AAD| 1 | | AAD| 2 | | AAD| 3 |* | BBD| 1 |* | BBD| 2 | | BBD| 3 |* ++-+ (* = these are the products that are NOT in the ProductsNEW table) +==+ | ProductsNEW | +==+ + Vendor | ID | ++-+ | AAD| 1 | | AAD| 2 | | AAD| 5 | | BBD| 2 | | BBD| 7 | | BBD| 10 | ++-+ snip Hi Jeff What you are looking for is a LEFT JOIN - it would look something like this: SELECT a.* FROM ProductsOLD a LEFT JOIN ProductsNEW b ON a.Vendor = b.Vendor AND a.ID = b.ID WHERE b.ID IS NULL HTH Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure Database Systems
Sarah Tanembaum wrote: snip We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I maintained. The data should be synchronize/replicate between those computers. Well, so far it is easy, isn't it? Here's my question: a) How can I make sure that it secure so only authorized person can modify/add/delete the information? Beside transaction logs, are there any other method to trace any transaction(kind of paper trail)? Assuming there are 3 step process to one enter the info e.g: - One who enter the info (me) - One who verify the info(the owner of info) - One who verify and then commit the change! How can I implement such a process in MySQL and/or PHP or any other web language? b) How can I make sure that no one can tap the info while we are entering the data in the computer? (our family are scattered within US and Canada) c) Is it possible to securely synchronize/replicate between our computers using VPN? Does MySQL has this functionality by default? d) Other secure method that I have not yet mentioned. Anyone has good ideas on how to implement such a systems? Thanks Hi Sarah This is more of a PHP question than a MySQL question as to my mind while it is all possible, the bulk of the work would need to be done on the PHP side. Assuming that you don't have the time to write all the necessary code from scratch, you might want to look for a content-management system (CMS) written in PHP and using MySQL that has some (ideally, all) the functionality that you require. This can serve as a good foundation and can be tweaked to your relevant requriements. A good place to start looking for this would be Sourceforge or google. I also agree with Shawn, that your best bet is to use a single server, ideally hosted by a hosting company that provides MySQL/PHP. This removes the need for replication to multiple machines, and you can just keep a regular backup copy on a local machine - also reduces some of your security concerns. Regards Rory McKinley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using BETWEEN or = =
On 13 Jan 2004 at 10:11, Eve Atley wrote: I am attempting to construct a select statement in which I can find values between two fields: start, and end. I have tried using BETWEEN and comparing with = and =, but neither meet success. Can someone please set me straight? This is meant to be in a PHP page, but I'm assuming the syntax is similar if not the same. I understand there is also a min/max, but I'm not sure it would work in this instance. $sql = SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Table: federal-married (finds federal tax rate based on marital status) start = min field, ie. 804 end = max field, ie. 2801 $_POST['salary'] = salary of individual posted from a form -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi Eve If I assume that you are looking for a salary that lies between the values stored in the start and end field? In this case your query won't work cos your greater and less thans are a little bit confused. This query (compare with yours above) should work: $sql = SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
Re: Convert query from v4 syntax to v3
On 7 Jan 2004 at 11:04, Odhiambo Washington wrote: Hello, I have a query that executes well when run on MySQL-4.x, but not 3.23.x: SELECT popbox.local_part, popbox.password_hash, popbox.domain_name, CONCAT(domain.path,'/',popbox.mbox_name) AS path FROM popbox JOIN domain USING (domain_name) I would like to make this query run on a 3.23.58 server. I have tried SELECT popbox.local_part, popbox.password_hash, popbox.domain_name, CONCAT(domain.path,'/',popbox.mbox_name) AS path FROM popbox JOIN domain ON popbox.domain_name=domain.domain_name ..but I still end up with error. Of course I am lost about the syntax now ;) Basically, the problem begings with the USING... -Wash -- +== + |\ _,,,---,,_ | Odhiambo Washington[EMAIL PROTECTED] Zzz /,`.-'`'-. ;-;;,_ | Wananchi Online Ltd. www.wananchi.com |,4- ) )-,_. ,\ ( `'-'| Tel: +254 20 313985-9 +254 20 313922 '---''(_/--' `-'\_) | GSM: +254 722 743223 +254 733 744121 +== + Whatever the missing mass of the universe is, I hope it's not cockroaches! -- Mom Hi Wash Can you post the error message? Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convert query from v4 syntax to v3
On 9 Jan 2004 at 16:48, Odhiambo Washington wrote: * Rory McKinley [EMAIL PROTECTED] [20040109 13:21]: wrote: On 7 Jan 2004 at 11:04, Odhiambo Washington wrote: Hello, I have a query that executes well when run on MySQL-4.x, but not 3.23.x: SELECT popbox.local_part, popbox.password_hash, popbox.domain_name, CONCAT(domain.path,'/',popbox.mbox_name) AS path FROM popbox JOIN domain USING (domain_name) I would like to make this query run on a 3.23.58 server. I have tried SELECT popbox.local_part, popbox.password_hash, popbox.domain_name, CONCAT(domain.path,'/',popbox.mbox_name) AS path FROM popbox JOIN domain ON popbox.domain_name=domain.domain_name ..but I still end up with error. Of course I am lost about the syntax now ;) Basically, the problem begings with the USING... [snip] Hi Wash Can you post the error message? The line is part of a small script. The error is: You have an error in your SQL syntax near 'USING (domain_name)' Hi Wash I can't see what is throwing the syntax error..p'raps I am just being dense. Try this query and see if it still throws the same error: SELECT popbox.local_part, popbox.password_hash, popbox.domain_name, CONCAT(domain.path,'/',popbox.mbox_name) AS path FROM popbox, domain WHERE popbox.domain_name = domain.domain_name Regards Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
Re: Convert query from v4 syntax to v3
On 9 Jan 2004 at 15:33, Roger Baklund wrote: * Rory McKinley [...] * Odhiambo Washington I have a query that executes well when run on MySQL-4.x, but not 3.23.x: [...] I can't see what is throwing the syntax error..p'raps I am just being dense. Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11. URL: http://www.mysql.com/doc/en/JOIN.html Try this query and see if it still throws the same error: SELECT popbox.local_part, popbox.password_hash, popbox.domain_name, CONCAT(domain.path,'/',popbox.mbox_name) AS path FROM popbox, domain WHERE popbox.domain_name = domain.domain_name That should do it. :) -- Roger Oh well of course..if you're actually going to refer to the fine manual instead of blundering around in the dark...then you will get an answer a lot more quicklybut is it as much fun ? :) Sometimes I am just too dense for words... Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A little help with this select?
On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? Maybe I'm doing something wrong. THANKS! Best Regards, -- Tyler Longren J.D. Web Services, L.C. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi Tyler The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. In terms of the syntax of your query, if you wish to return records from the worker table when the webprojectassign table is empty, you either have to use a LEFT JOIN (which willl include all records from workers whether they have a matching workerid in webprojectassign or not) or remove the condition from your query above. In both cases you are no longer doing what your query originally intended (which I assume is to list all workers currently assigned to projects). If you are just testing, I would suggest that a better bet would be to put dummt entries into the empty table rather than hacking the query. Regards Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A little help with this select?
On 6 Jan 2004 at 9:31, Noamn wrote: Rory wrote: The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. No'am adds: I think that Tyler wanted the workers who aren't listed in the webprojectassign table - his query contains WHERE worker.workerid!=webprojectassign.workerid Is != a different way of writing ? If he wants the unassigned workers, then he needs what I've seen referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program. -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:19 AM To: Tyler Longren; [EMAIL PROTECTED] Subject: Re: A little help with this select? On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Oooops...my apologies to all...I guess it helps if one actually reads properly. In this case, the solution to Tyler's problem will be a left join. The query should look something like this : SELECT worker.fname,worker.lname,worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC This will retrieve all the worker details where there are no matching entries in the webprojectassign table. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
RE: A little help with this select?
Hi Tyler You're welcome..I just wish I had got it right the first time :). On 6 Jan 2004 at 3:15, Tyler Longren wrote: Rory, that's awesome. Exactly what I needed. After reading your first reply I wrote a query that was very similar to the one you just posted (included below). It didn't quite work right however. It was still a lot closer than I was before. Thanks again! SELECT worker.fname,worker.lname,worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC Tyler On Tue, 2004-01-06 at 02:22, Rory McKinley wrote: On 6 Jan 2004 at 9:31, Noamn wrote: Rory wrote: The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. No'am adds: I think that Tyler wanted the workers who aren't listed in the webprojectassign table - his query contains WHERE worker.workerid!=webprojectassign.workerid Is != a different way of writing ? If he wants the unassigned workers, then he needs what I've seen referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program. -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:19 AM To: Tyler Longren; [EMAIL PROTECTED] Subject: Re: A little help with this select? On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Oooops...my apologies to all...I guess it helps if one actually reads properly. In this case, the solution to Tyler's problem will be a left join. The query should look something like this : SELECT worker.fname,worker.lname,worker.workerid FROM worker LEFT JOIN webprojectassign ON worker.workerid = webprojectassign.workerid WHERE webprojectassign.workerid IS NULL ORDER BY worker.lname ASC This will retrieve all the worker details where there are no matching entries in the webprojectassign table. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
On 2 Dec 2003 at 7:20, Greg Jones wrote: snip select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c where l.custsysid=c.custsysid and l.ltsysid in (select l2.ltsysid from lientrak as l2 where l2.lientraknum like '2003-%') snip Hi greg Yopur problem is that you are using a subquery - MySQL 4.0.1.5 does not support subqueries - you will need 4.1 for that... A possible workaround (based on your query above - so please tolerate any mistakes :) ) : select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c where l.custsysid=c.custsysid and l.lientraknum like '2003-%' Actually, having written the above workaround, I am puzzled as to why you would need the subquery at all :)...unless of course, above workaround is completely wrong, and I have to eat humble pie again. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
LEFT JOIN problem
Hi List I am currently running a query that reads records from one table (Table 1) links these records to an id value in another table (Table 2) and returns the result. The tables are as follows, with sample data: Table 1in DB 1: line_number | category_name | category_value | line_type 1 | Rent | 100.00| 13 2 | Usage |50.00| 13 3 | Services |75.00| 13 Table 2 in DB 2: parameter_ID | parameter_trigger | parameter_value 1 | 1| Rent 1 | 2| Usage The returned records will be line_number, category_value from Table 1 and Parameter_Trigger from Table 2 (Table 1 and Table 2 are joined on table 1.Category Name = Table 2.Parameter_Value). As you can see from the sample the third record in table 1 does not have a matching entry in Table 2. In this case, I want the returned record to still show the line_number and category_value, except in place of the parameter_trigger the field should be set to zero (so that I can see that I have a category name that I haven't accounted for). So I am using the following query: SELECT a.line_number, a.category_value, IFNULL(b.parameter_trigger, 0) FROM DB1.Table 1 AS a LEFT JOIN DB2.Table 2 AS b ON a.category_name = b.parameter_value WHERE a.line_type = 13 AND b.parameter_ID = 13 The only problem is that the query only returns the first two records and not the third record - contrary to my expectations. I am using MySQL 4.0.15-standard together with PHPMyAdmin 2.5.3. Does anybody know what I am doing wrong? Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
Re: command line operation problems
Maybe I am stating the obvious but instead of typing mysql -username root -p try : mysql -u root -p HTH Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Kelley Lingerfelt [EMAIL PROTECTED] To: Scott Purcell [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 6:52 PM Subject: Re: command line operation problems Well, according to your message, you might want to make sure you are typing mysql and not mysqld(which is the server daemon) According to the docs when I go to the cmd window, I should type in mysql -username and then get prompted for a password. But I get this: look at what you typed, you typed in mysqld not mysql C:\mysql\binmysqld -username root Kelley Scott Purcell wrote: Hello, I am new to mysql and have a couple of questions that I can't seem to get through at this time. I have installed the product on my win2000 box. I followed the docs for PC, and removed the generic user in the users table. Here is my question: According to the docs when I go to the cmd window, I should type in mysql -username and then get prompted for a password. But I get this: C:\mysql\binmysqld -username root Can't start server: Bind on TCP/IP port: No error 031022 10:46:57 Do you already have another mysqld server running on port: 3306 ? 031022 10:46:57 Aborting 031022 10:46:57 mysqld: Shutdown Complete But if I type just mysql C:\mysql\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 4.0.15-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I also tried this, and this is how I need to connect with JDBC C:\mysql\binmysql -username root -p Enter password: ** ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) But it fails. I know I have a root user with a valid password? Where am I going wrong? I did create the 'menagerie' database, and can't get back to it. Thanks, Scott -- 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]
Re: GRANT problem
Hi Jonas Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 9:14 AM Subject: GRANT problem Hello I've created an account by using: GRANT insert,update,select,delete on phonewatch.* to datatal identified by 'MYPASSWORD' I cant connect(mysql.exe or myodbc) to the db with the new account, and I did not receive any errormessages when creating the account. Have I forgot something? Med vänlig hälsning/Best Regards Datatal Utveckling AB Jonas Gauffin Tel direct: +46 (0) 498 25 30 16 Tel Support: +46 (0) 498 25 30 30 Fax: +46 (0) 498 25 30 99 http://www.datatal.se I try every day, but sometimes I fail -- 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: GRANT problem
Ok. I'm stumped. If I'm reading the manual correctly, you were right about not hving to specify the host..the only difference between your version and the manual is that they use single quotes around the user name but I can't see how that makes any difference. I normally just specfiy hosts because using wildcards for hosts makes me nervous - hey, even paranoid people have enemies :) If anyone knows the answer to this I would be interested in knowing what it is too Sorry I can't be of more help Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 12:05 PM Subject: SV: GRANT problem it works if I specify the host. Why doesn't % work as host? -Ursprungligt meddelande- Från: Rory McKinley [mailto:[EMAIL PROTECTED] Skickat: den 23 oktober 2003 11:56 Till: Datatal AB - Gauffin, Jonas Ämne: Re: GRANT problem thinking. In one of your follow up posts I see that you have the command as this : C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -pMYPASSWORD Try C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -p And enter password at the prompt Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Datatal AB - Gauffin, Jonas [EMAIL PROTECTED] To: Rory McKinley [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 11:45 AM Subject: SV: GRANT problem Not sure if this will help - in your GRANT statement do you not need to specify a host for the user e.g. GRANT.. to datatal @ your_host_name.?. GRANT uses % as host if none is specified. % = all hosts. any other ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: integer field
Have you tried casting the $myrow element to integer e.g $increase = (int)$myrow['first'] + 1; I think PHP will return the field as text by default and hence the addition will fail.. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: ketvin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 23, 2003 8:25 AM Subject: integer field Dear lists, Simple question for geniuses outthere: my database has an integer field where i created with CREATE table test (first int(10)); so when i use php to grab the first field and read it in $myrow[first] , then try to make the number increase by doing a $increase = $myrow[first] + 1; but the $increase is still holding the same number. is anything wrong with my mysql db configuration or php scripts? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: export to textfile
Hi Bernd If you are just interested in dumping the data in CSV (or whatever) format you can do it like so: SELECT * INTO OUTFILE 'arb_file.csv' FIELDS TERMINATED BY ',' LINE TERMINATED BY '\n' FROM arb_table WHERE arb_conditions There are quite a few options available, so I suggest you check the MySQL manual, to see them all. HTH. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Bernd Tannenbaum [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 12:42 PM Subject: export to textfile Hello all, Have only a small problem with my mysql and hope that i can get a lil hint from ya how to go on. Currently i`m importing Log-files in the mysql-database with a cronjob (bashscript in Linux). After some processing in the db itself i try now to export the results into a textfile. But i must be blind reading the mysql documentation cause i cannot find a mysqlexport. Hmm, Mysqldump can be used to backup the db, thats not what i need. Then i found mysql -e and it rly writes the chosen fields to a textfile but i dont know how to get options to that (like fileds-terminated-by=';' or stuff like that). So did i miss something? Is there a better way to go than mysql -e? If not, where can i find a good explanation of the possible options of this, every hint welcome. Hope for a lil help, Bernd -- One OS to rule them all, one OS to find them. One OS to bring them all, and in the darkness bind them In the land of Redmond, where the shadows lie. -- 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: export to textfile
Hi Bernd Will the query be static? E.g. could you put the SELECT ..INTO OUTFILE query into a batch file and just call that batch file from the command line? mysql -u user -p arb_file.bat Obviously this will be a little more of a problem if you generate the query dynamically each time. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Bernd Tannenbaum [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 1:40 PM Subject: Re: export to textfile Helloand ty for the fast answers, but Am Dienstag, 21. Oktober 2003 13:10 schrieben Sie: Hi Bernd If you are just interested in dumping the data in CSV (or whatever) format you can do it like so: SELECT * INTO OUTFILE 'arb_file.csv' FIELDS TERMINATED BY ',' LINE TERMINATED BY '\n' FROM arb_table WHERE arb_conditions Well, my problem is i need to export from a script (currently bash-script in Linux) so im looking for the options to give with mysql -e. What u wrote in ur mail, i found too. But how do i give the FIELDS TERMINATED BY option in the command line of a script. Example for import (working fine): ./mysqlimport --local --fields-terminated-by=';' --columns=record_type,...[...] dbname /PATH/file --password=xxx Now the export (not working) ./mysql FIELDS TEMINATED BY ';' --skip-column-names -e select ek_satzart,[...] from table dbname /PATH/file --password= See, me wants to add options like Fields terminated by or other stuff to the command-line-export and i cannot find an explanation of Synatx here. In ur example u just export from within the db which is explained fine in the dokumentation but not what i need Hope i made the problem more clear now. Ty, Bernd -- One OS to rule them all, one OS to find them. One OS to bring them all, and in the darkness bind them In the land of Redmond, where the shadows lie. -- 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: Challenging query....
Hi If you have a way to generate the query code dynamically (e.g. using a loop in C, PHP etc.), you can build a query using aliased tables : SELECT YEAR(a.date) AS year, MONTH(a.date) AS month, SUM(a.revenue) AS cust1_rev, SUM(b.revenue) AS cust2_rev, SUM(c.revenue) AS cust3_rev FROM revenue a, revenue b, revenue c WHERE a.customer_id = 1 AND (YEAR(b.date) = YEAR(a.date) AND MONTH(b.date) = MONTH(a.date) AND b.customer_id = 2) AND (YEAR(c.date) = YEAR(c.date) AND MONTH(c.date) = MONTH(c.date) AND c.customer_id = 3) GROUP BY year, month Regards Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 16, 2003 4:21 PM Subject: Challenging query I have a table that contains customer revenue information. REVENUE TABLE: Date, customer name, CustomerID, revenue, quantity I need to create a query that will produce the following result Year, Month, Customer1_rev, customer2_REV, customer3_rev, etc... 2002, 01, 0, $30.00, $15.00 2002, 02, $25.00, $50.00, $10.00 2002, 03, $10.00, $25.00, $40.00 Etc.. Can this be done with a single query??? Jeff -- 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: Challenging query....
Hi Jeff
OK, aliasing table is creating a copy of one table but calling it something
different, so you compare a table to itself e.g.:
FROM revenue a, revenue b, revenue c COULD ALSO BE FROM revenue AS a,
revenue AS b, revenue AS c
I am referencing revenue three times but have aliased it as a, b, and c to
make sure that my predicate makes sense.
As for the loop, I can give you something off the top of my head in rough
(very!) PHP , if you don't come right, I can sit down and do the code a
little more detailed
For simplification purposes, I am going to assume that you can alias tables
as numbers (e.g. 1, 2, 3 instead of a, b, c) but I suggest you check if this
is possible - if you can't there is a work around that just requires a bit
more thought
//Assume you have an array that has all your client ids in
$client_id_array.
//Create base values based on the first id...
$select_base = YEAR(1.date) AS year, MONTH(1.date) AS month,
SUM(1.revenue) AS cust1_rev
$for_base = FROM revenue 1
$predicate_base = WHERE 1.customer_id = .$client_id_array[0]
//Now loop through and append additional items to each string for each
instance of a client
//Start at 1 not zero as we already have accounted for the first id above
for($j=1; $j count($client_id_array); $j++)
{
$select_base = $select_base.', SUM('.($j+1).') AS cust'.($j+1).'_rev';
$for_base = $for_base.', revenue '.($j+1);
$predicate_base = $predicate_base.' AND (YEAR('.($j+1).'.date) =
YEAR(1.date) AND MONTH('.($j+1).'.date) =MONTH(1.date) AND
'.($j+1).'.customer_id = '.$client_id_array[$j].')';
}
//Once your loop is done, put the parts together
$query = $select_base.$for_base.$predicate_base;
If you can't use numbers, you can use single letters, but that requires a
little more work incrementing ASCII numbers and then converting to letters -
also makes things way more complicated if you have more than 26 clients :)
but still doable.
HTH
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message -
From: Jeff McKeon [EMAIL PROTECTED]
To: Rory McKinley [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 6:51 PM
Subject: RE: Challenging query
If you have a way to generate the query code dynamically
(e.g. using a loop in C, PHP etc.), you can build a query
using aliased tables :
SELECT YEAR(a.date) AS year, MONTH(a.date) AS month,
SUM(a.revenue) AS cust1_rev,
SUM(b.revenue) AS cust2_rev, SUM(c.revenue) AS cust3_rev
FROM revenue a, revenue b, revenue c
WHERE a.customer_id = 1 AND (YEAR(b.date) = YEAR(a.date) AND
MONTH(b.date) =
MONTH(a.date) AND b.customer_id = 2)
AND (YEAR(c.date) = YEAR(c.date) AND MONTH(c.date) =
MONTH(c.date) AND c.customer_id = 3) GROUP BY year, month
Ok, that looks promising as I'll be using PHP, but I'm a little fuzzy on
the logic you've set. What are aliased tables and how would I define,
use them in an loop?
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT problem
Hi Andy What you can do is make a copy of your genre_titles table through aliasing, this will allow you to only return the results that have both genres and should reduce the need for programmatical sorting - aliasing also makes for less typing :). SELECT a.name, b.titleid FROM titles a, genre_titles b, genre_titles c WHERE b.genid = 4 AND (b.titleid = c.titleid AND c.genid = 5) AND a.titleid = b.titleid If the number of genres that have to be matched vary, you can always generate your code through a script that loops through and builds the additional parts of the predicate. HTH Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Andrew Barnes [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 2:46 AM Subject: SELECT problem Hi, I have three tables, a title's table, a genre's table and a genre_titles map table (to model the many to many relationship between genre's and title's). I need to write a query that will return title's that match two or more genre's. An example would be one title could be a comedy/drama and I would need to find other title's that have a reference to the genre's comedy and drama. I have tried this query - select titles.name, genre_titles.titleid from titles, genre_titles where genre_titles.genid = 4 and genre_titles.titleid = titles.titleid or genre_titles.genid = 5 and genre_titles.titleid = titles.titleid with programmatic sorting but the result sets are too large and the sorting algorithm is too slow. I was wondering whether there was a query that would return the exact result set needed. I am using mysql 4.0.13 Regards AndyB -- 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: LOAD DATA INFILE
Hi Kabbouri
Please see below, an excerpt from the MySQL manual, that answers your
question.
If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty
(''), a
fixed-row (non-delimited) format is used. With fixed-row format, no
delimiters are used
between fields (but you can still have a line terminator). Instead, column
values are
written and read using the display widths of the columns. For example, if
a column
is declared as INT(7), values for the column are written using 7-character
fields. On
input, values for the column are obtained by reading 7 characters.
Regards
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message -
From: Kabbouri Mohammed [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, October 14, 2003 9:58 AM
Subject: LOAD DATA INFILE
How can I find the way to import a text file with fixed width fields:
for example:
Import file content:
375120031010143902KABBOURIMOHAMMED
Imported in:
Field 1: 3 char : 375
Field 2: 5 char : 1
Field 3: 8 char : 20031010
Field 4: 6 char : 143902
Field 5: 20 char: KABBOURI
Field 6: 20 char: MOHAMMED
Regards,
Kabbouri
--
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: Data from two tables in one query
Hi Jeff Have you looked at UNIONS? An example would be something as follows: SELECT field_1, field_2, field_3 FROM table 1 WHERE blah blah blah UNION SELECT field_1, field_2, field_3 FROM table 1_old WHERE blah blah blah ORDER BY field_1 This should do the trick... Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 4:01 PM Subject: Data from two tables in one query We're currently running mysql ver 3.23 with plans to upgrade to 4.x soon but we're not ready yet. I have two tables that are identical, table1 and table1_old. Table1_old contains all data writen to table1 prior to july of 2003. I need to do a single query that pulls the same fields from both tables and output's them in the correct order. Is this possible? I'm outputing this data to PHP web page. I know in ver 4.x there is table called a merge table for this type of thing but we're stuck with 3.23 for now. Jeff -- 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: newbie select statement question
Hi Jordan
Have had a look at the date input page, and the source code that it produces
looks fine. If you still haven't sorted it out could I suggest the
following:
Output the tenure date at the following points in the application:
1) When it is returned by the input form.
2) When it is returned from the database (before being input into
fixdate()).
3) Within fixdate.
See if the results are consistent throughout.
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message -
From: Jordan Morgan [EMAIL PROTECTED]
To: Nobody [EMAIL PROTECTED]
Cc: Diana Soares [EMAIL PROTECTED]; mysql [EMAIL PROTECTED]
Sent: Friday, October 10, 2003 2:24 PM
Subject: Re: newbie select statement question
The date is stored as dates instead of timestamps. Yes, that fixDate
function is
defined by me and it worked great with other dates I have stored. I'm
really
thinking my dropdown fields for the MM, DD, and on the data entry
page has
somethng wrong with it.
do you think so?
ps: thanks for checking for me btw.
Nobody wrote:
I am not sure if this has any significance - but isn't 31 Dec 1969 the
day
before the UNIX epoch (1 Jan 1970 - i think)?
Jordan, how are the dates stored in the database - as dates or unix
timestamps?
Also, the function fixDate isn't in the online PHP manual - is it a
function
defined by you? - could that not be doing something weird?
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message -
From: Diana Soares [EMAIL PROTECTED]
To: Jordan Morgan [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Friday, October 10, 2003 8:58 AM
Subject: Re: newbie select statement question
Look at:
if ($tenureid=3)
You're not comparing $tenureid with 3, you're assigning 3 to
$ternureid... If you want to compare both values, you must use the
operator == (and not only =).
On Fri, 2003-10-10 at 05:43, Jordan Morgan wrote:
Hi,
I have the following statement:
echo $tenureidP;
if ($tenureid=3)
{
// get faculty employment record - award date
$sql = select TD.Award_Date from TenureDescription TD LEFT JOIN
InstitutionEmployment IE on TD.TenureDescriptionID=IE.Tenure WHERE
IE.FacultyMember='$fid';
$result = mysql_db_query($database, $sql, $connection) or die
(Error
in query: $sql. . mysql_error());
// obtain data from resultset
list($tenuredate) = mysql_fetch_row($result);
echo BDate Tenure Granted/B: ;
echo fixDate($tenuredate);
echo P;
}
else {
echo BDate Tenure Granted/B: NullP;
}
and I can't figure out why I always get the following result:
2
Date Tenure Granted: 31 Dec 1969
---
or
1
Date Tenure Granted: 31 Dec 1969
---
when 1) tenureid 3, and 2) no 31 Dec 1969 date in the database
anywhere.
The only thing I can think of is that I modified the dropdown box
for
the tenure date on the data entry page like this:
tr
tdDate Tenure Grantedbrfont size=-2(in mm-dd-
format)/font/td
td
select name=tmm
option value=0 selected='selected'/option
? for ($x=1; $x=12; $x++) { echo option value=\ .
sprintf(%02d,
$x) . \ . sprintf(%02d, $x) . /option; } ?
/select -
select name=tdd
option value=0 selected='selected'/option
? for ($x=1; $x=31; $x++) { echo option value=\ .
sprintf(%02d,
$x) . \ . sprintf(%02d, $x) . /option; } ?
/select -
select name=t
option value=0 selected='selected'/option
!-- display from 1970 to (current year) --
? for ($x=(date(Y, mktime())); $x=1970; $x--) { echo option
value=$x$x/option; } ?
/select
/td
/tr
by adding option value=0 selected='selected'/option to those 3
fields as I want null to be a default selection. but I can't imagine why
that'll mess up the if statement evaluation.
It seems that the 1st if statement just runs whatever the tenureid
is.
Can anyone help me on this? I'm using PHP 4.2.2 and MySQL 3.23.54
btw.
Thanks millions!
Jordan
--
Diana Soares
--
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]
--
Jordan
Re: Re[2]: [PHP-DB] MySQL Regular expression
HI Owen
I am not sure about strcmp - I have never used it on numbers before..MySQL
2nd Ed by DuBois PLUG Which is well worth the money - makes me look
intelligent /PLUG says that strcmp sorts lexically...and to be quite
honest, I need to go look that up.. but I think it will work...
However, using the code from your previous comment, you can try casting your
result as an unsigned integer like so (if yourMySQL is ver 4.0.2 or up)...
select * from ihrproperties where region = 'Kerry' and 0
CAST(substring_index(substring_index(rates, ',', 41), ',', -1) AS UNSIGNED
INTEGER) 1568
If you don't have 4.0.2 - I would suggest you try strcmp and see ...
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message -
From: O Franssen [EMAIL PROTECTED]
To: Rory McKinley [EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 6:22 PM
Subject: Re[2]: [PHP-DB] MySQL Regular expression
Actually in response to my previous comment, would the following
achieve wwhat I want?
... and strcmp('$foot_budget', substring_index(substring_index(rates, ',',
$selecteddate), ',', -1)) = -1 and strcmp('$head_budget',
substring_index(substring_index(rates, ',', $selecteddate), ',', -1)) = 1
--
Regards,
Owen Franssen
Twisted Designmailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: access denied .... php my admin .....
Hi Toby If Apache, PHP and MySQL are all sitting on the same box, try connecting to the loopback address and see if the problem goes away. Up until recently, I was running a similar setuo and I always used to connect to 127.0.0.1 and had no hassles, so I am hoping that this might fix the problem... Rory McKinley Nebula Solutions 082 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: toby - [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 10, 2003 6:24 AM Subject: Re: access denied php my admin . sorry for this late reply rory i just got back to work .. i am trying to connect to 192.168.x.y but the jerk connects to 192.168.xx.xxx the other ip yeha it connects to an ip . not the loop back adrs am i supposed to connect to loop back adrs thinggy ? im all screwed with this thing .. :'( thnx a bill toby From: Rory McKinley [EMAIL PROTECTED] To: toby - [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: access denied php my admin . Date: Thu, 9 Oct 2003 11:47:25 +0200 Hi toby If I log in to mysql via the command line on the hosting machine it says [EMAIL PROTECTED] ... therefore, stating the obvious :) it doens't think you're connecting from localhost - this may be because of the two NICs but I don't know enough to be sure .when you connect to the mysql server (when php, apache and mysql are all on the same box) do you connect to 127.0.0.l (loopback address) or do you connect to the IP address of one of the two NICs (e.g. 192.168.y.z)? Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: toby - [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 11:18 AM Subject: Re: access denied php my admin . thnx rory the thing is kwexchange is localhost you know . so it should work alwayz has before this dammed day . anywayz . i did install mysql on another machine and installed all the rest , php, apache, phpmyadmin on another when it gave me these access denied errors the very bloody same . i thought there was somethin the matter with keepin these things on two separate machines u know but now i have got em all on the same thing . but to no use . do you or anyone else on this list think it could be the two network cards running on the machine that are actually screwin this all up fo me all though i have tried ma best to some how change this ip thinggy to the one i want mysql server to user .. can anyone tell me where to go and what to do as im at ma wits end now .. thnx a mill rory .. :) toby From: Rory McKinley [EMAIL PROTECTED] To: toby - [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: access denied php my admin . Date: Thu, 9 Oct 2003 11:09:06 +0200 Hi Toby I am not an expert on permissions within mysql - boy, do I wish I was - but could the problem be that you have only granted permissions to [EMAIL PROTECTED] and not '[EMAIL PROTECTED]' ? Here endeth my knowledge of MySQL permissions Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: toby - [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 8:57 AM Subject: access denied php my admin . hay guyz im a bit stuck first i couldnt get any user but root to log into mysql on command prompt and myadmin now i can ,et my user to log in through command prompt but i get this weired error for myadmin Error MySQL said: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) zaban is my user ofcourse kwexchange is the machine i have installed all my php, apache, mysql on kwdomain is the domain we use here at ma work place another thing : winmysqladmin shows the local ip address to be kwexchange.kwdomain.com 192.168.x.xxx where it should only be 192.168.y.z there aint anything of the sort in my.ini where do i make changes for this o and another thing this machine has two cards 192.168.y.z and 192.168.x.xxx are the ips 192.168.x.xxx i can not use . i should ot infact . and i am running Win 2K Server PHP
Re: Backup, move, restore..?
Hi Taylor To quote from MySQL (2nd ED) by Dubois (pg 725) - InnoDB and BDB tables can be dumped using mysqldump, just like any other kind of tables. So the answer is: nope nothing different. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Taylor Lewick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 3:45 PM Subject: Re: Backup, move, restore..? OKay, thanks, any idea if something has to be done differently for InnoDB tables? Rory McKinley [EMAIL PROTECTED] 10/09/03 03:46AM Hi Taylor While I have only used mysqldump to backup MyIsam files, I have found the process to be fairly painless. Mysqldump creates batch f iles which effectively rebuild your database structure + database data. To restore the data all you need to do is run the backup files as a batch file i.e. : mysql -u root -p your_destination_db_name your_backup_file_name Then sit back and wait Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Taylor Lewick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 8:49 PM Subject: Backup, move, restore..? Hi all. I am needing to do a backup of two smalldatabases, both live on the same server and under the same Mysql binary installation... I would like to back them up, dump them into a file(s), make my move, install the same database, and then restore/load this dumped information back into the new database. I am using mysql-max 4.0.13 on HP-UX 11.00, 64 bit... I am also using some InnoDB tables... Is it as simple as doing mysqldumps and taring/zipping up those files? If so, how do I load these files back into the new database? Thanks Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Re[2]: [PHP-DB] MySQL Regular expression
Apologies for any confusion - wrong list. Should have gone to PHP-DB
instead.TGIF.
- Original Message -
From: Rory McKinley [EMAIL PROTECTED]
To: O Franssen [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, October 10, 2003 9:14 AM
Subject: Re: Re[2]: [PHP-DB] MySQL Regular expression
HI Owen
I am not sure about strcmp - I have never used it on numbers before..MySQL
2nd Ed by DuBois PLUG Which is well worth the money - makes me look
intelligent /PLUG says that strcmp sorts lexically...and to be quite
honest, I need to go look that up.. but I think it will work...
However, using the code from your previous comment, you can try casting
your
result as an unsigned integer like so (if yourMySQL is ver 4.0.2 or up)...
select * from ihrproperties where region = 'Kerry' and 0
CAST(substring_index(substring_index(rates, ',', 41), ',', -1) AS UNSIGNED
INTEGER) 1568
If you don't have 4.0.2 - I would suggest you try strcmp and see ...
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
There are 10 kinds of people in this world,
those who understand binary and those who don't (Unknown)
- Original Message -
From: O Franssen [EMAIL PROTECTED]
To: Rory McKinley [EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 6:22 PM
Subject: Re[2]: [PHP-DB] MySQL Regular expression
Actually in response to my previous comment, would the following
achieve wwhat I want?
... and strcmp('$foot_budget', substring_index(substring_index(rates,
',',
$selecteddate), ',', -1)) = -1 and strcmp('$head_budget',
substring_index(substring_index(rates, ',', $selecteddate), ',', -1)) = 1
--
Regards,
Owen Franssen
Twisted Design
mailto:[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]
Re: MySQL and Php
Hi Caspar Also try phpbuilder.com, and zend.com. Alternatively, try googling PHP tutorials. Regards Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Casper Matthee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 9:33 PM Subject: MySQL and Php Hi all I am new to the programming world so please forgive!!! Would like to get to get my hands on any info (Tutorials and such) to help me on me way whith SQL and Php Would appreciate any help... links / text / whatever... Thanx Casper South Africa Casper Matthee Consultant IT3 Educational Systems Tel: (021) 970 4005 Cell: 083 360 5669 Mail: [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]
Re: Help with a DATETIME Query PLEASE!
Shaun Not a 100 % sure if this is what you're looking for or if somebody's already suggested it also do not know how practical it is for your application If a booking spans more than one day (e.g. two) split it into two days - and write two records to the table one for each daythis fits with the spirit of your applicationa user is going to be interested in free time per day surely The problem with this is if you use a booking id which serves as a primary key and will therefore not not allow duplicates..but if that's the case, then there are workarounds for that too... Let me know if I'm warm:) Rory McKinley Nebula Solutions + 27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 10, 2003 12:37 PM Subject: Help with a DATETIME Query PLEASE! Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates how many hours are available between the hours of 09.00 and 17.30 so a user can see at a glance how many hours they have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date,'%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) Thanks for your help _ On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile -- 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: Backup, move, restore..?
Hi Taylor While I have only used mysqldump to backup MyIsam files, I have found the process to be fairly painless. Mysqldump creates batch f iles which effectively rebuild your database structure + database data. To restore the data all you need to do is run the backup files as a batch file i.e. : mysql -u root -p your_destination_db_name your_backup_file_name Then sit back and wait Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Taylor Lewick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 8:49 PM Subject: Backup, move, restore..? Hi all. I am needing to do a backup of two smalldatabases, both live on the same server and under the same Mysql binary installation... I would like to back them up, dump them into a file(s), make my move, install the same database, and then restore/load this dumped information back into the new database. I am using mysql-max 4.0.13 on HP-UX 11.00, 64 bit... I am also using some InnoDB tables... Is it as simple as doing mysqldumps and taring/zipping up those files? If so, how do I load these files back into the new database? Thanks Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * -- 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: access denied .... php my admin .....
Hi Toby I am not an expert on permissions within mysql - boy, do I wish I was - but could the problem be that you have only granted permissions to [EMAIL PROTECTED] and not '[EMAIL PROTECTED]' ? Here endeth my knowledge of MySQL permissions Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: toby - [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 8:57 AM Subject: access denied php my admin . hay guyz im a bit stuck first i couldnt get any user but root to log into mysql on command prompt and myadmin now i can ,et my user to log in through command prompt but i get this weired error for myadmin Error MySQL said: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) zaban is my user ofcourse kwexchange is the machine i have installed all my php, apache, mysql on kwdomain is the domain we use here at ma work place another thing : winmysqladmin shows the local ip address to be kwexchange.kwdomain.com 192.168.x.xxx where it should only be 192.168.y.z there aint anything of the sort in my.ini where do i make changes for this o and another thing this machine has two cards 192.168.y.z and 192.168.x.xxx are the ips 192.168.x.xxx i can not use . i should ot infact . and i am running Win 2K Server PHP 4.2.2 apache 1.3.24 mysql 3.23.58 i would really appriciat any help with this thnx a million toby _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- 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: access denied .... php my admin .....
Hi toby If I log in to mysql via the command line on the hosting machine it says [EMAIL PROTECTED] ... therefore, stating the obvious :) it doens't think you're connecting from localhost - this may be because of the two NICs but I don't know enough to be sure .when you connect to the mysql server (when php, apache and mysql are all on the same box) do you connect to 127.0.0.l (loopback address) or do you connect to the IP address of one of the two NICs (e.g. 192.168.y.z)? Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: toby - [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 11:18 AM Subject: Re: access denied php my admin . thnx rory the thing is kwexchange is localhost you know . so it should work alwayz has before this dammed day . anywayz . i did install mysql on another machine and installed all the rest , php, apache, phpmyadmin on another when it gave me these access denied errors the very bloody same . i thought there was somethin the matter with keepin these things on two separate machines u know but now i have got em all on the same thing . but to no use . do you or anyone else on this list think it could be the two network cards running on the machine that are actually screwin this all up fo me all though i have tried ma best to some how change this ip thinggy to the one i want mysql server to user .. can anyone tell me where to go and what to do as im at ma wits end now .. thnx a mill rory .. :) toby From: Rory McKinley [EMAIL PROTECTED] To: toby - [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: access denied php my admin . Date: Thu, 9 Oct 2003 11:09:06 +0200 Hi Toby I am not an expert on permissions within mysql - boy, do I wish I was - but could the problem be that you have only granted permissions to [EMAIL PROTECTED] and not '[EMAIL PROTECTED]' ? Here endeth my knowledge of MySQL permissions Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: toby - [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 8:57 AM Subject: access denied php my admin . hay guyz im a bit stuck first i couldnt get any user but root to log into mysql on command prompt and myadmin now i can ,et my user to log in through command prompt but i get this weired error for myadmin Error MySQL said: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) zaban is my user ofcourse kwexchange is the machine i have installed all my php, apache, mysql on kwdomain is the domain we use here at ma work place another thing : winmysqladmin shows the local ip address to be kwexchange.kwdomain.com 192.168.x.xxx where it should only be 192.168.y.z there aint anything of the sort in my.ini where do i make changes for this o and another thing this machine has two cards 192.168.y.z and 192.168.x.xxx are the ips 192.168.x.xxx i can not use . i should ot infact . and i am running Win 2K Server PHP 4.2.2 apache 1.3.24 mysql 3.23.58 i would really appriciat any help with this thnx a million toby _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.58 and sub-seletcs
Hi boka Nope sub-selects to my understanding ar only supported from version 4.1 onwards (still in beta I think) Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: boka [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 11:54 AM Subject: MySQL 3.23.58 and sub-seletcs Hi ! Are sub-selects supported by MySQL 3.23.58, because below query does not work (syntax error in console): select iphdr.ip_src, timestamp \ from iphdr , acid_event \ where iphdr.sid = acid_event.sid \ and iphdr.cid = acid_event.cid \ and acid_event.sig_name = STATEMENT \ and acid_event.timestamp select max(another_database.dataz.timestamp) \ from another_database.dataz; greetz boka -- 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: Easy (?) conditional SELECT
Problem 1 : Your query contains a subquery : SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) subqueries are (AFAIK) not supported in the latest production version of MySQL (4.0.15) but are coming soon...might already be in a beta ver Two possible solutions - 1) If there are a fixed number of artifacts per product (n): SELECT * FROM artifacts WHERE product_id = 1 ORDER BY plan_submission_number DESC LIMIT 1, n This retrieves the n most recent records for the specific product_id But obviously this only works if n is fixed... 2)Otherwise the only solution I can think of is to have two queries - One gets the max submission number and the other gets the artifacts (using the result of the first query) Not much help I guess..but my 2c. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- 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]
