RE: search through one/several tables
The best I've been able to come up with involves some shell scripting. If you're running Linux, using unpacked MyISAM tables, and have some scripting ability from whatever language you're writing your application in, you can run this in your MySQL directory: strings -f *.MYD | grep search string | cut -f1 -d'.' | sort -u It will give you back the table names that have that string somewhere inside them. Then in your scripting language you can check the columns on just the tables that the shell script returned. Note that it is NOT fast at all. My 1.7GB of database takes about 10 minutes to crunch through the data. If you're going to do something like this make sure you understand the security ramifications of running shell scripts with user input and how to secure it in your language of choice. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. (469) 384-6009 -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 23, 2005 10:09 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: search through one/several tables I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost connection during query when using SSH tunneling
-Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 12:08 PM To: [EMAIL PROTECTED] Subject: Lost connection during query when using SSH tunneling So I use Putty to create the SSH tunneler and then connecting to the local port. Now the problem, is that whenever I execute a long query or something that returns big resultset, i get an error - Lost connection during query. If I use direct connection - everythings fine. Putty has an option in the Connection category of the configuration for Seconds between keepalives. It's set to zero (off) by default. If you set it to 0 number, putty will send a null packet at the server to try and keep the connection alive. Have you tried that? -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. (469) 384-6009 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost connection during query when using SSH tunneling
-Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 2:17 PM To: Adams, Pat 006; [EMAIL PROTECTED] Subject: RE: Lost connection during query when using SSH tunneling I am not sure of Putty as I am using plink.exe that is the SSH tunneler for tunnel. The thing is that I call up plink.exe from my app to create an ssh tunnel and then set the correct values for mysql_real_connect() in my app. What I am doing is that I am bundling plink.exe with my app and then ask the user for SSH info. Depending upon that I create a plink.exe process and then connect my app to the SSH process. I referred to Putty as many people seem to be using it and putty usese plink.exe which is the back end for it. Should I look into some options like what you suggested? plink can use putty's saved sessions, so you could set one of those up. Unfortunately it doesn't look like there's a command-line option to set the timeout or keepalive settings of plink. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Conditonal where
-Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: Sunday, November 14, 2004 11:30 AM To: [EMAIL PROTECTED] Subject: Conditonal where ?php $sql .= SELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobs; if ($s_Ind){ $sql .= WHERE VendorJobs.Industry IN ($s_Ind); } if ($s_State){ $sql .= AND VendorJobs.LocationState IN ($s_State); } What I think I need is some kind of default WHERE in the first statement. Both Ind and State are conditional based on whether the user input anything. Right now they would be forced to at least choose the Ind. So instead of the $s_Ind have a WHERE it should be an AND . You could add a where condition that's always true to the main part of the SQL statement so that you can just tack on more clauses conditionally. $sql .= SELECT PostStart, JobTitle, Industry, LocationState, VendorID . FROM VendorJobs . WHERE 1 = 1 ; if ($s_Ind) { $sql .= AND VendorJobs.Industry IN ($s_Ind) ; } if ($s_State) { $sql .= AND VendorJobs.LocationState IN ($s_State); } -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. (469) 384-6009 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Syntax Problem
-Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 4:08 PM To: [EMAIL PROTECTED] Subject: SQL Syntax Problem $sql = 'SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective FROM cia_people C, famarea2 F WHERE (C.Nationality is not null) AND (F.IDArea = \'eur\') ORDER BY $_POST[\'order\'], $_POST[\'direction\']'; $res = mysql_query($sql) or die('Failed to run ' . $sql . ' - ' . mysql_error()); If you change the single quotes on the outside of the SQL statement to double quotes, PHP will parse variables inside the string. Try $sql = SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective . FROM cia_people C, famarea2 F . WHERE (C.Nationality is not null) AND (F.IDArea = 'eur') . ORDER BY {$_POST['order']}, {$_POST['direction']}; Notice that you need to put the variables in curly braces when you have arrays being parsed. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. (469) 384-6009 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]