RE: Complex SQL involving 10 checkboxes
It comes down to detecting if the value you are adding to your list is a
continuation of the list or the first item.
You need to set a variable or even a counter at the start of processing then
you can use the value of that variable to determine if you are processing
the first list item or not.
like so... (I am not A PHP programmer so please forgive any syntax erros)
$First = TRUE
$SQL .= penpals.agegroup = ;
if (isset($HTTP_GET_VARS['check00'])) {
// if we are producing the first item in the list
// then we do not append the OR
if($First) {
$First = FALSE;
}else{
$SQL .= OR;
}
$SQL .= '00';
}
if (isset($HTTP_GET_VARS['check01'])) {
if($First) {
$First = FALSE;
}else{
$SQL .= OR;
}
$SQL .= '01';
}
and so on..
the best thing to do is put one block of the above code into a function and
pass in the HTTP_GET_VARS string, the SQL string, a ValueSeperator string
and First status like so...
(again please excuse sytnax, I don't know how to define a function in PHP)
FUNCTION (String HttpVarParam, String QueryValueParam, String
ValueSeperatorParam, Boolean First){
if (isset($HTTP_GET_VARS['HttpvarParam'])) { // Check if VAR exists in
HTTP VARS
if($First) {// Is it the
first one in our list?
$First = FALSE;
}else{
$SQL .= ValueSeperatorParam; // if not then we need
to append a
separator to the list
}
$SQL .= QueryValueParam;// and don't forget to
append the value
}
}
you can then call it like this
$First = TRUE
$SQL .= penpals.agegroup = ;
CALL FUNCTION ('check00','00','OR', $First) // $SQL will contain
penpals.agegroup = 00
CALL FUNCTION ('check01','01','OR', $First) // $SQL will contain
penpals.agegroup = 00 OR 01
CALL FUNCTION ('check04','04','OR', $First) // $SQL will contain
penpals.agegroup = 00 OR 01 OR 04
The beauty of this is that it is easy to change the sql value that is
associated with a n HTTP variable name
and you can change the seprator too in case you want your query to be using
an IN( , , , ) clause like this...
$First = TRUE
$SQL .= penpals.agegroup IN ( ;
CALL FUNCTION ('check00','00',',', $First) // $SQL will contain
penpals.agegroup IN ( 00
CALL FUNCTION ('check01','01',',', $First) // $SQL will contain
penpals.agegroup IN ( 00,01
CALL FUNCTION ('check04','04',',', $First) // $SQL will contain
penpals.agegroup IN ( 00,01,04
$SQL .= ); // $SQL will contain
penpals.agegroup IN ( 00,01,04 )
That should do the trick...
-Original Message-
From: Don Read [mailto:[EMAIL PROTECTED]
Sent: Friday, June 20, 2003 8:32 AM
To: vernon
Cc: [EMAIL PROTECTED]
Subject: RE: Complex SQL involving 10 checkboxes
On 19-Jun-2003 vernon wrote:
OK, I've done this.
Problem occurs when a user selects 1 and 9.
The SQL statement I have reads like so:
if (isset($HTTP_GET_VARS['check00'])) {
$age00 = '00';
$s_age00 = penpals.agegroup = $age00 AND;
}
Problem is the AND statement. Some times the user will pick checkbox 1
and 5
but not 2,3,4,6, 7, 8, and 9. And then again there will be times when a
user
only uses one check box needing the AND statement on the end, but at
other
times the user will select more than one needing an OR statement between
the
two checkboxes. Make sense?
Use an array.
$agesel=implode(', ', $HTTP_GET_VARS['ageselect,]);
$qry=SELECT * FROM foo
WHERE penpals.agegroup IN ('$agesel');
Your HTML boxes will look like:
input TYPE=CHECKBOX NAME=ageselect[] value=00
input TYPE=CHECKBOX NAME=ageselect[] value=10
input TYPE=CHECKBOX NAME=ageselect[] value=15 CHECKED
input TYPE=CHECKBOX NAME=ageselect[] value=20 CHECKED
input TYPE=CHECKBOX NAME=ageselect[] value=25
...
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
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: Impossible query??
I don't think that solves the problem. There are multiple test chains with Id's less than 7. ie 7-6-4-3-2 5 1 and your query looking for history on testId=7 SELECT * FROM tests WHERE testID=7 AND connect0; could return testId's 5 and 1 as well if they were part of longer chains What is needed (and does not exist) is a recursive query that can be started on a given testId, follows the connect-testId chain and terminates when it reaches a record with connect=0 A single query can not do this. You will be forced to solve this programatically by executing a query that selects a single record based on the previous record's connect field until you reach a connect=0. (Caution, if ever a connect value points back into the chain you will enter an endless loop!) Or Re-Design your table (and inserting code ) by adding a column called baseTestId that stores the original testId. Every time a new test is added that is an extension of a previous test the previous testId is copied into the new record and the previous baseTestId is also copied to the new record baseTestID. (If a test is the first one it uses it's own id as the baseTestID). Your sample table would look like this... assuming three chains exist 7-6-4-3-2 5 1 +-+--++--+ | testId | connect | baseTestId | result | +-+--++--+ | 1 | 0| 1 | ok | | 2 | 0| 2 | nok | | 3 | 2| 2 | nok | | 4 | 3| 2 | nok | | 5 | 0| 5 | ok | | 6 | 4| 2 | nok | | 7 | 6| 2 | ok | | 8 | 0| 8 | ok | +-+--+|--+ note how all the records that are in a chain have the same baseTestID! Then this single query will do what you want SELECT B.* FROM myTable AS A LEFT OUTER JOIN myTable AS B ON(A.baseTesID=B.baseTestID) WHERE (A.testId=7) It returns ++-++--+ | testId | connect | baseTestId | result | ++-++--+ | 2 | 0 | 2 | nok | | 3 | 2 | 2 | nok | | 4 | 3 | 2 | nok | | 6 | 4 | 2 | nok | | 7 | 6 | 2 | ok | ++-++--+ The idea here is that you need a single piece of data that can relate all the records in a chain of tests together. The where clause selects the test Id in question and the join collects all records whose baseTestID is the same as the record selected by the where clause. This query would return all tests in the chain no matter which id you used. If you queried on testId 4 the same set of data would be returned, basically all tests in the chain. Now if you already have tons of data in place you will need to update the table data so that the baseTestID field reflects these new rules. Mike Scott, Ind. Contractor -Original Message- From: Becoming Digital [mailto:[EMAIL PROTECTED] Sent: Saturday, June 14, 2003 4:13 PM To: [EMAIL PROTECTED] Subject: Re: Impossible query?? Your query seems relatively easy if you don't need the first test (in this case, testID 2) explicitly printed. mysql SELECT * FROM tests WHERE testID=7 AND connect0; +-+--+--+ | testId | connect | result | +-+--+--+ | 7 | 6| ok | | 6 | 4| nok | | 4 | 3| nok | | 3 | 2| nok | +-+--+--+ The history of testID 2 is implied by your table structure, such that you know testID 2 passed because it is the last connect value. The problem I see with this occurs when you have a repeating data set. A query for your next failure (after testID 7) will also return testID 7 and its history, as would be the case with the below data. Repairing this would require adding an additional parameter to the WHERE statement, probably using BETWEEN. +-+--+--+ | testId | connect | result | +-+--+--+ | 1 | 0| ok | | 2 | 0| nok | | 3 | 2| nok | | 4 | 3| nok | | 5 | 0| ok | | 6 | 4| nok | | 7 | 6| ok | | 8 | 0| ok | | 9 | 0| nok | | 10 | 9| nok | | 11
tables locked during connection failure processes not auto killed after timeout
Am I missing a configuration setting somewhere? I have been working on a MySQL Max (3.23.52-max-nt) database running on W2K Server. I am using a MySQL client remotely over a VPN but unfortunately the client's internet connection is severely overloaded and the connection dies on occasion. When this occurs the server process responsible for servicing the connection lingers around forever and if this occurs during an update there are table locks that also linger forever. I have set the following variables in my.cnf and restarted the server to have the server auto kill processes/connections after 60 seconds of inactivity. I have confirmed the variable settings after the server restart with SHOW VARIABLES wait_timeout=60 (seconds) interactive_timeout=60 (seconds) BUT the server never KILLS the processes! Even when the SHOW PROCESSLIST command indicates that the process has been around for 3000+ seconds. Way longer than the timeout values. Am I missing some variable or command line setting to enable the server to auto KILL processes? ** Reproduce as follows... 1) Configure server for short timeouts in my.cnf and restart server set-variable= wait_timeout=60 set-variable= interactive_timeout=60 2) Start and connect a MySQL client to the server OVER A NETWORK LINK 3) Run the STATUS command to find your connection/process id so you know which process to watch in step 8 4) Disconnect your network cable 5) Stop the MySQL client (so the server can't see the client shutdown, same as if VPN hangs) 6) Reconnect the network cable 7) Start and connect a new MySQL client to the server 8) Run SHOW PROCESSLIST and watch the entry for your previous process id, it never goes away! and its time just keeps incrementing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
