[PHP-DB] OCI Binding problem
Hi, Imagine this code: ?php $database_connection = ocilogon(username, password, connection string); // the actual connection code is slightly different but that is not relevant to my problem $postalcode = 3055; // option 1: paste the postalcode into the query: $rowset1 = array(); $statement1 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=' . $postalcode . '); ociexecute($statement1); ocifetchstatement($statement1, $rowset1, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset1 contains some records from the table // option 2: use namebinding: $rowset2 = array(); $statement2 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=:postalcode); ocibindbyname($statement2, :postalcode, $postalcode, 4); ociexecute($statement2); ocifetchstatement($statement2, $rowset2, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset2 is still an empty array ? Both queries should result in the same data but as soon as I use the binding no rows are returned. I can't see what I'm doing wrong here. Can someone help me? Jos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] OCI Binding problem
Sorry, trigger happy. Also try :postalcode in uppercase. Neil -Original Message- From: Juffermans, Jos [mailto:[EMAIL PROTECTED] Sent: 20 April 2005 12:18 To: 'php-db@lists.php.net' Subject: [PHP-DB] OCI Binding problem Hi, Imagine this code: ?php $database_connection = ocilogon(username, password, connection string); // the actual connection code is slightly different but that is not relevant to my problem $postalcode = 3055; // option 1: paste the postalcode into the query: $rowset1 = array(); $statement1 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=' . $postalcode . '); ociexecute($statement1); ocifetchstatement($statement1, $rowset1, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset1 contains some records from the table // option 2: use namebinding: $rowset2 = array(); $statement2 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=:postalcode); ocibindbyname($statement2, :postalcode, $postalcode, 4); ociexecute($statement2); ocifetchstatement($statement2, $rowset2, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset2 is still an empty array ? Both queries should result in the same data but as soon as I use the binding no rows are returned. I can't see what I'm doing wrong here. Can someone help me? Jos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] OCI Binding problem
Jos, This may or may not sort out the problem, but OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW should be OCI_ASSOC+OCI_FETCHSTATEMENT_BY_ROW. Neil -Original Message- From: Juffermans, Jos [mailto:[EMAIL PROTECTED] Sent: 20 April 2005 12:18 To: 'php-db@lists.php.net' Subject: [PHP-DB] OCI Binding problem Hi, Imagine this code: ?php $database_connection = ocilogon(username, password, connection string); // the actual connection code is slightly different but that is not relevant to my problem $postalcode = 3055; // option 1: paste the postalcode into the query: $rowset1 = array(); $statement1 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=' . $postalcode . '); ociexecute($statement1); ocifetchstatement($statement1, $rowset1, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset1 contains some records from the table // option 2: use namebinding: $rowset2 = array(); $statement2 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=:postalcode); ocibindbyname($statement2, :postalcode, $postalcode, 4); ociexecute($statement2); ocifetchstatement($statement2, $rowset2, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset2 is still an empty array ? Both queries should result in the same data but as soon as I use the binding no rows are returned. I can't see what I'm doing wrong here. Can someone help me? Jos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] OCI Binding problem
Hi, Since flags are normally bitmaps, FLAG1 | FLAG2 should have the same result as FLAG1 + FLAG2. I've tried your suggestion anyway but it had no result. I've also tried to uppercase :postalcode (in the query and in the bindbyname call) but that didn't help either. Jos -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 20 April 2005 13:40 To: [EMAIL PROTECTED]; php-db@lists.php.net Subject: RE: [PHP-DB] OCI Binding problem Sorry, trigger happy. Also try :postalcode in uppercase. Neil -Original Message- From: Juffermans, Jos [mailto:[EMAIL PROTECTED] Sent: 20 April 2005 12:18 To: 'php-db@lists.php.net' Subject: [PHP-DB] OCI Binding problem Hi, Imagine this code: ?php $database_connection = ocilogon(username, password, connection string); // the actual connection code is slightly different but that is not relevant to my problem $postalcode = 3055; // option 1: paste the postalcode into the query: $rowset1 = array(); $statement1 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=' . $postalcode . '); ociexecute($statement1); ocifetchstatement($statement1, $rowset1, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset1 contains some records from the table // option 2: use namebinding: $rowset2 = array(); $statement2 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=:postalcode); ocibindbyname($statement2, :postalcode, $postalcode, 4); ociexecute($statement2); ocifetchstatement($statement2, $rowset2, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset2 is still an empty array ? Both queries should result in the same data but as soon as I use the binding no rows are returned. I can't see what I'm doing wrong here. Can someone help me? Jos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Losing the ability to connect to Oracle database
Turns out that it's covered in bug report 30808. I've applied the edit mentioned there and will see if this solves my problem (have to wait for the next Oracle restart overnight). The discussion there is quite revealing about the fact that oci8 connections are always persistent even if you do not ask for a persistent connect of explicitly call for a disconnect. Seems to me that ought to be stated in the oci8 documentation. Christopher Jones wrote: Leo D. Geoffrion wrote: I recently upgraded to PHP5 and now have a curious Oracle problem. The PHP scripts query the database fine. Then overnight, the database shuts down for backup and restarts. The next day, PHP can no longer connect to the database until I restart Apache. Then, it's happy until Oracle restarts the next night. We did not encounter this problem with PHP4. I've tried adjusting the php command from the old ocilogon() to oci_connect() but the problem continues. Incidentally, I am using a simple connection, not a persistent one. Can someone advise what's wrong here or how to get PHP/Apache to survive a database restart. There were some changes to connection in PHP5. I guess this might be a symptom, but I recall similar things reported with PHP4. Can you log a PHP bug so the problem can be tracked? Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Recall: [PHP-DB] OCI Binding problem
Juffermans, Jos would like to recall the message, [PHP-DB] OCI Binding problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] OCI Binding problem
Update: I've found a workaround: ?php $database_connection = ocilogon(username, password, connection string); // the actual connection code is slightly different but that is not relevant to my problem $postalcode = 3055; $rowset2 = array(); $statement2 = ociparse($database_connection, SELECT services FROM location WHERE postalcode='' || :postalcode); ocibindbyname($statement2, :postalcode, $postalcode, 4); ociexecute($statement2); ocifetchstatement($statement2, $rowset2, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // this returns the records ? Somehow Oracle will interpret the :postalcode as a numeric value in this case, eventhough the column is a varchar. By adding '' || Oracle converts it to a string. Jos -Original Message- From: Juffermans, Jos [mailto:[EMAIL PROTECTED] Sent: 20 April 2005 14:05 To: 'php-db@lists.php.net' Subject: RE: [PHP-DB] OCI Binding problem Hi, Since flags are normally bitmaps, FLAG1 | FLAG2 should have the same result as FLAG1 + FLAG2. I've tried your suggestion anyway but it had no result. I've also tried to uppercase :postalcode (in the query and in the bindbyname call) but that didn't help either. Jos -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 20 April 2005 13:40 To: [EMAIL PROTECTED]; php-db@lists.php.net Subject: RE: [PHP-DB] OCI Binding problem Sorry, trigger happy. Also try :postalcode in uppercase. Neil -Original Message- From: Juffermans, Jos [mailto:[EMAIL PROTECTED] Sent: 20 April 2005 12:18 To: 'php-db@lists.php.net' Subject: [PHP-DB] OCI Binding problem Hi, Imagine this code: ?php $database_connection = ocilogon(username, password, connection string); // the actual connection code is slightly different but that is not relevant to my problem $postalcode = 3055; // option 1: paste the postalcode into the query: $rowset1 = array(); $statement1 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=' . $postalcode . '); ociexecute($statement1); ocifetchstatement($statement1, $rowset1, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset1 contains some records from the table // option 2: use namebinding: $rowset2 = array(); $statement2 = ociparse($database_connection, SELECT services FROM location WHERE postalcode=:postalcode); ocibindbyname($statement2, :postalcode, $postalcode, 4); ociexecute($statement2); ocifetchstatement($statement2, $rowset2, 0, 100, OCI_ASSOC | OCI_FETCHSTATEMENT_BY_ROW); // at this stage $rowset2 is still an empty array ? Both queries should result in the same data but as soon as I use the binding no rows are returned. I can't see what I'm doing wrong here. Can someone help me? Jos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Losing the ability to connect to Oracle database
Hi Leo, We are having the same problem. Check out the bug I filed many months ago http://bugs.php.net/bug.php?id=30808 I can't for the life of me figure out why the bug is suspended, as this seems to be a common issue with Oracle and php5. Perhaps you should also and your comments to the bug report. To resolve the issue, we do an apache restart every time the db goes down. This flushes whatever connections php pools to Oracle (sounds weird considering we are doing oci8nlogon). I really hope that the OCI8 library gets some serious attention soon. In my mind this is a show stopper, and it appears that there are a few other big ones lying around... Michael -Original Message- From: Leo D. Geoffrion [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 19, 2005 8:27 AM To: php-db@lists.php.net Subject: [PHP-DB] Losing the ability to connect to Oracle database I recently upgraded to PHP5 and now have a curious Oracle problem. The PHP scripts query the database fine. Then overnight, the database shuts down for backup and restarts. The next day, PHP can no longer connect to the database until I restart Apache. Then, it's happy until Oracle restarts the next night. We did not encounter this problem with PHP4. I've tried adjusting the php command from the old ocilogon() to oci_connect() but the problem continues. Incidentally, I am using a simple connection, not a persistent one. Can someone advise what's wrong here or how to get PHP/Apache to survive a database restart. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php CONFIDENTIALITY NOTICE This message contains confidential information intended only for the use of the individual or entity named as recipient. Any dissemination, distribution or copying of this communication by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify us and delete your copy. Thank you. AVIS DE CONFIDENTIALITÉ Les informations contenues aux présentes sont de nature privilégiée et confidentielle. Elles ne peuvent être utilisées que par la personne ou l'entité dont le nom paraît comme destinataire. Si le lecteur du présent message n'est pas le destinataire prévu, il est par les présentes prié de noter qu'il est strictement interdit de divulguer, de distribuer ou de copier ce message. Si ce message vous a été transmis par mégarde, veuillez nous en aviser immédiatement et supprimer votre copie. Merci. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Losing the ability to connect to Oracle database
Leo, Please let me know how that works for you. I don't remember the specifics (if we where testing using plogon, nlogon or just logon), but we did try and implement that fix and it didn't work for us. Thanks, Michael -Original Message- From: Leo D. Geoffrion [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 20, 2005 6:03 AM To: Michael Caplan Subject: Re: [PHP-DB] Losing the ability to connect to Oracle database Yes, I just found 30808. It's suspended because the fix is stated in the discussion (uncomment out a small section of oci8.c), but for performance reasons they don't want to make this part of the standard code. I've rebuilt php5 now with the change in place and will see if this fixes the problem for my systems. I can confirm that it's at least harmless. Michael Caplan wrote: Hi Leo, We are having the same problem. Check out the bug I filed many months ago http://bugs.php.net/bug.php?id=30808 I can't for the life of me figure out why the bug is suspended, as this seems to be a common issue with Oracle and php5. Perhaps you should also and your comments to the bug report. To resolve the issue, we do an apache restart every time the db goes down. This flushes whatever connections php pools to Oracle (sounds weird considering we are doing oci8nlogon). I really hope that the OCI8 library gets some serious attention soon. In my mind this is a show stopper, and it appears that there are a few other big ones lying around... Michael -Original Message- From: Leo D. Geoffrion [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 19, 2005 8:27 AM To: php-db@lists.php.net Subject: [PHP-DB] Losing the ability to connect to Oracle database I recently upgraded to PHP5 and now have a curious Oracle problem. The PHP scripts query the database fine. Then overnight, the database shuts down for backup and restarts. The next day, PHP can no longer connect to the database until I restart Apache. Then, it's happy until Oracle restarts the next night. We did not encounter this problem with PHP4. I've tried adjusting the php command from the old ocilogon() to oci_connect() but the problem continues. Incidentally, I am using a simple connection, not a persistent one. Can someone advise what's wrong here or how to get PHP/Apache to survive a database restart. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Leo D. Geoffrion, Ph.D. EMAIL: [EMAIL PROTECTED] Skidmore College Retiree PHONE: 518 580-0555 Currently: Senior Systems Administrator SGS Testcom Inc. 2911 Route 9 Ballston Spa, NY 12020 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= CONFIDENTIALITY NOTICE This message contains confidential information intended only for the use of the individual or entity named as recipient. Any dissemination, distribution or copying of this communication by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify us and delete your copy. Thank you. AVIS DE CONFIDENTIALITÉ Les informations contenues aux présentes sont de nature privilégiée et confidentielle. Elles ne peuvent être utilisées que par la personne ou l'entité dont le nom paraît comme destinataire. Si le lecteur du présent message n'est pas le destinataire prévu, il est par les présentes prié de noter qu'il est strictement interdit de divulguer, de distribuer ou de copier ce message. Si ce message vous a été transmis par mégarde, veuillez nous en aviser immédiatement et supprimer votre copie. Merci. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL JOIN query : Seeking solution - Please Help
I am still new to this, but I turned the manual upside down, or at least I
tried.
I am designing a payroll application by which payslips can be processed for
each employee for each period.
The problem lays where I would like to display a list of employees that
don't have transactions (payslips) for a particular period. I can easily
display a list of employees that do have transactions (payslips) for a
particular period. I tried reversing this, but it doesn't seem to work, or
I'm missing something somewhere. Please help.
# Table structure for table `employees`
emp_idemp_numemp_titleemp_surnameemp_nameemp_initials
emp_termdate // etc.
INSERT INTO `employees` VALUES (1, 'EMP001', 'Mrs.', 'Surname1', 'Name1',
'N1.', '-00-00');
INSERT INTO `employees` VALUES (2, 'EMP002', 'Mr.', 'Surname2', 'Name2',
'N2.', '-00-00');
INSERT INTO `employees` VALUES (3, 'EMP003', 'Mr', 'Surname3', 'Name3',
'N3.', '-00-00');
INSERT INTO `employees` VALUES (4, 'EMP004', 'Mr.', 'Surname4', 'Name4',
'N4.', '2003-08-31');
INSERT INTO `employees` VALUES (5, 'EMP005', 'Mr.', 'Surname5', 'Name5',
'N5.', '2004-02-28');
INSERT INTO `employees` VALUES (6, 'EMP006', 'Mr.', 'Surname6', 'Name6',
'N6.', '-00-00');
INSERT INTO `employees` VALUES (7, 'EMP007', 'Mr.', 'Surname7', 'Name7',
'N7.', '-00-00');
# Table structure for table `payperiods`
pp_idpp_periodpp_status
INSERT INTO `payperiods` VALUES (1, 'JAN 2004', 'inactive');
INSERT INTO `payperiods` VALUES (2, 'FEB 2004', 'active');
# Table structure for table `transactions`
CREATE TABLE `transactions` (
`tran_id` int(11) NOT NULL auto_increment,
`pp_id` int(11) NOT NULL default '0',
`emp_id` int(11) NOT NULL default '0',
`tran_basicsal` decimal(11,2) NOT NULL default '0.00',
`tran_OT_rate` decimal(11,2) NOT NULL default '0.00',
`tran_OT_qty` decimal(11,2) NOT NULL default '0.00',
`tran_DT_rate` decimal(11,2) NOT NULL default '0.00',
`tran_DT_qty` decimal(11,2) NOT NULL default '0.00',
`tran_bonus` decimal(11,2) NOT NULL default '0.00',
`tran_commission` decimal(11,2) NOT NULL default '0.00',
`tran_travelall` decimal(11,2) NOT NULL default '0.00',
`tran_cellall` decimal(11,2) NOT NULL default '0.00',
`tran_leavepdout_days` decimal(11,2) NOT NULL default '0.00',
`tran_leavepdout_rate` decimal(11,2) NOT NULL default '0.00',
`tran_uif_emp` decimal(11,2) NOT NULL default '0.00',
`tran_uif_com` decimal(11,2) NOT NULL default '0.00',
`tran_sdl` decimal(11,2) NOT NULL default '0.00',
`tran_paye` decimal(11,2) NOT NULL default '0.00',
`tran_staffloan` decimal(11,2) NOT NULL default '0.00',
`tran_unpaidleave_days` decimal(11,2) NOT NULL default '0.00',
`tran_unpaidleave_rate` decimal(11,2) NOT NULL default '0.00',
`tran_leave_taken` decimal(10,2) NOT NULL default '0.00',
`tran_sl_taken` decimal(11,2) NOT NULL default '0.00',
PRIMARY KEY (`tran_id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=12 ;
#
# Dumping data for table `transactions`
#
INSERT INTO `transactions` VALUES (6, 1, 1, '3500.00', '29.84', '1.00',
'39.78', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '159.12',
'36.70', '36.70', '36.70', '366.96', '50.00', '0.00', '159.12', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (7, 1, 2, '2000.00', '17.04', '1.00',
'22.72', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '90.88',
'21.40', '21.40', '21.40', '213.98', '50.00', '0.00', '90.88', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (8, 1, 3, '9000.00', '68.18', '1.00',
'90.90', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '409.05',
'92.59', '92.59', '92.59', '925.91', '50.00', '0.00', '409.05', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (11, 2, 1, '3500.00', '29.84', '1.00',
'39.78', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '159.12',
'36.70', '36.70', '36.70', '366.96', '50.00', '0.00', '159.12', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (10, 2, 7, '2200.00', '16.67', '1.00',
'22.22', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '99.99',
'23.39', '23.39', '23.39', '233.89', '50.00', '0.00', '99.99', '0.00',
'0.00');
#
My query is as follows:
?php
// IF COUNT OF EMPLOYEES DIFFER WITH COUNT OF TRANSACTIONS, DO THIS
// DISPLAY ONLY EMPLOYEES THAT DON'T HAVE TRANSACTIONS FOR THE
CURRENT (active) PERIOD.
else {
$t1 = 'employees';
$t2 = 'transactions';
$selectempquery = mysql_query(SELECT DISTINCT $t1.*, $t2.emp_id
FROM $t1
LEFT JOIN $t2 ON $t1.emp_id=$t2.emp_id
WHERE NOT $t1.emp_termdate
AND ($t2.pp_id != '$pp_id' OR $t2.pp_id IS NULL)); // This
doesn't work right
// if i replace the above AND with:
AND $t2.emp_id IS NULL); // it won't return any rows if
employees have transactions in other periods,
// because it searches the ON $t1.emp_id = $t2.emp_id
[PHP-DB] MySQL 5 Beta
Hi, I've been trying to get MySQL 5 installed with php-5.0.4(windows). I'm getting several messages about entry points not being found in dlls etc etc. Am I being over optimistic to expect this configuration to work? Indeed has anyone got it working? If it should work, I'll press on and try to find the bug. Google has not been my friend at all on this one. Best regards... Dusty -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL JOIN query : Seeking solution - Please Help
When checking for unmatched records, you use a left join as you are doing. This assures all records are retained from the left side table (i.e. employees), regardless if there is a matching record or not. Obviously if you have a filter on the left side table it will narrow down the result. To find unmatched records, just check if a field from the related table is NULL. Don't check for a non-matching id or some other unmatched key. Something like this: SELECT DISTINCT $t1.*, $t2.emp_id FROM $t1 LEFT JOIN $t2 ON $t1.emp_id=$t2.emp_id WHERE NOT $t1.emp_termdate AND $t2.pp_id IS NULL To get a quicker response to your questions in the future, don't post sample data and full table descriptions. Summarize your table structure and data to only the relevant pieces. What you posted is a lot to read through for the fairly straightforward answer you needed. For instance, I don't know what $t1.emp_termdate is and I really didn't read through your post to figure it out. I assume it's a form of boolean value ( 0, 1, Y, N, etc.), but I can't tell from the query. Comparison should be obvious. Remember, a boolean could be three possible values: True, False, NULL. On Apr 20, 2005, at 10:09 AM, jinxed wrote: I am still new to this, but I turned the manual upside down, or at least I tried. I am designing a payroll application by which payslips can be processed for each employee for each period. The problem lays where I would like to display a list of employees that don't have transactions (payslips) for a particular period. I can easily display a list of employees that do have transactions (payslips) for a particular period. I tried reversing this, but it doesn't seem to work, or I'm missing something somewhere. Please help. # Table structure for table `employees` emp_idemp_numemp_titleemp_surnameemp_name emp_initials emp_termdate // etc. INSERT INTO `employees` VALUES (1, 'EMP001', 'Mrs.', 'Surname1', 'Name1', 'N1.', '-00-00'); INSERT INTO `employees` VALUES (2, 'EMP002', 'Mr.', 'Surname2', 'Name2', 'N2.', '-00-00'); INSERT INTO `employees` VALUES (3, 'EMP003', 'Mr', 'Surname3', 'Name3', 'N3.', '-00-00'); INSERT INTO `employees` VALUES (4, 'EMP004', 'Mr.', 'Surname4', 'Name4', 'N4.', '2003-08-31'); INSERT INTO `employees` VALUES (5, 'EMP005', 'Mr.', 'Surname5', 'Name5', 'N5.', '2004-02-28'); INSERT INTO `employees` VALUES (6, 'EMP006', 'Mr.', 'Surname6', 'Name6', 'N6.', '-00-00'); INSERT INTO `employees` VALUES (7, 'EMP007', 'Mr.', 'Surname7', 'Name7', 'N7.', '-00-00'); # Table structure for table `payperiods` pp_idpp_periodpp_status INSERT INTO `payperiods` VALUES (1, 'JAN 2004', 'inactive'); INSERT INTO `payperiods` VALUES (2, 'FEB 2004', 'active'); # Table structure for table `transactions` CREATE TABLE `transactions` ( `tran_id` int(11) NOT NULL auto_increment, `pp_id` int(11) NOT NULL default '0', `emp_id` int(11) NOT NULL default '0', `tran_basicsal` decimal(11,2) NOT NULL default '0.00', `tran_OT_rate` decimal(11,2) NOT NULL default '0.00', `tran_OT_qty` decimal(11,2) NOT NULL default '0.00', `tran_DT_rate` decimal(11,2) NOT NULL default '0.00', `tran_DT_qty` decimal(11,2) NOT NULL default '0.00', `tran_bonus` decimal(11,2) NOT NULL default '0.00', `tran_commission` decimal(11,2) NOT NULL default '0.00', `tran_travelall` decimal(11,2) NOT NULL default '0.00', `tran_cellall` decimal(11,2) NOT NULL default '0.00', `tran_leavepdout_days` decimal(11,2) NOT NULL default '0.00', `tran_leavepdout_rate` decimal(11,2) NOT NULL default '0.00', `tran_uif_emp` decimal(11,2) NOT NULL default '0.00', `tran_uif_com` decimal(11,2) NOT NULL default '0.00', `tran_sdl` decimal(11,2) NOT NULL default '0.00', `tran_paye` decimal(11,2) NOT NULL default '0.00', `tran_staffloan` decimal(11,2) NOT NULL default '0.00', `tran_unpaidleave_days` decimal(11,2) NOT NULL default '0.00', `tran_unpaidleave_rate` decimal(11,2) NOT NULL default '0.00', `tran_leave_taken` decimal(10,2) NOT NULL default '0.00', `tran_sl_taken` decimal(11,2) NOT NULL default '0.00', PRIMARY KEY (`tran_id`) ) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=12 ; # # Dumping data for table `transactions` # INSERT INTO `transactions` VALUES (6, 1, 1, '3500.00', '29.84', '1.00', '39.78', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '159.12', '36.70', '36.70', '36.70', '366.96', '50.00', '0.00', '159.12', '0.00', '0.00'); INSERT INTO `transactions` VALUES (7, 1, 2, '2000.00', '17.04', '1.00', '22.72', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '90.88', '21.40', '21.40', '21.40', '213.98', '50.00', '0.00', '90.88', '0.00', '0.00'); INSERT INTO `transactions` VALUES (8, 1, 3, '9000.00', '68.18', '1.00', '90.90', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '409.05', '92.59', '92.59', '92.59', '925.91', '50.00', '0.00', '409.05', '0.00', '0.00'); INSERT INTO `transactions` VALUES (11, 2, 1, '3500.00', '29.84',
Re: [PHP-DB] MySQL JOIN query : Seeking solution - Please Help
Get those with transaction into a php array
Get those without transaction into a php array
Then use array_diff() (or one of its variants)
graeme.
jinxed wrote:
I am still new to this, but I turned the manual upside down, or at least I
tried.
I am designing a payroll application by which payslips can be processed for
each employee for each period.
The problem lays where I would like to display a list of employees that
don't have transactions (payslips) for a particular period. I can easily
display a list of employees that do have transactions (payslips) for a
particular period. I tried reversing this, but it doesn't seem to work, or
I'm missing something somewhere. Please help.
# Table structure for table `employees`
emp_idemp_numemp_titleemp_surnameemp_nameemp_initials
emp_termdate // etc.
INSERT INTO `employees` VALUES (1, 'EMP001', 'Mrs.', 'Surname1', 'Name1',
'N1.', '-00-00');
INSERT INTO `employees` VALUES (2, 'EMP002', 'Mr.', 'Surname2', 'Name2',
'N2.', '-00-00');
INSERT INTO `employees` VALUES (3, 'EMP003', 'Mr', 'Surname3', 'Name3',
'N3.', '-00-00');
INSERT INTO `employees` VALUES (4, 'EMP004', 'Mr.', 'Surname4', 'Name4',
'N4.', '2003-08-31');
INSERT INTO `employees` VALUES (5, 'EMP005', 'Mr.', 'Surname5', 'Name5',
'N5.', '2004-02-28');
INSERT INTO `employees` VALUES (6, 'EMP006', 'Mr.', 'Surname6', 'Name6',
'N6.', '-00-00');
INSERT INTO `employees` VALUES (7, 'EMP007', 'Mr.', 'Surname7', 'Name7',
'N7.', '-00-00');
# Table structure for table `payperiods`
pp_idpp_periodpp_status
INSERT INTO `payperiods` VALUES (1, 'JAN 2004', 'inactive');
INSERT INTO `payperiods` VALUES (2, 'FEB 2004', 'active');
# Table structure for table `transactions`
CREATE TABLE `transactions` (
`tran_id` int(11) NOT NULL auto_increment,
`pp_id` int(11) NOT NULL default '0',
`emp_id` int(11) NOT NULL default '0',
`tran_basicsal` decimal(11,2) NOT NULL default '0.00',
`tran_OT_rate` decimal(11,2) NOT NULL default '0.00',
`tran_OT_qty` decimal(11,2) NOT NULL default '0.00',
`tran_DT_rate` decimal(11,2) NOT NULL default '0.00',
`tran_DT_qty` decimal(11,2) NOT NULL default '0.00',
`tran_bonus` decimal(11,2) NOT NULL default '0.00',
`tran_commission` decimal(11,2) NOT NULL default '0.00',
`tran_travelall` decimal(11,2) NOT NULL default '0.00',
`tran_cellall` decimal(11,2) NOT NULL default '0.00',
`tran_leavepdout_days` decimal(11,2) NOT NULL default '0.00',
`tran_leavepdout_rate` decimal(11,2) NOT NULL default '0.00',
`tran_uif_emp` decimal(11,2) NOT NULL default '0.00',
`tran_uif_com` decimal(11,2) NOT NULL default '0.00',
`tran_sdl` decimal(11,2) NOT NULL default '0.00',
`tran_paye` decimal(11,2) NOT NULL default '0.00',
`tran_staffloan` decimal(11,2) NOT NULL default '0.00',
`tran_unpaidleave_days` decimal(11,2) NOT NULL default '0.00',
`tran_unpaidleave_rate` decimal(11,2) NOT NULL default '0.00',
`tran_leave_taken` decimal(10,2) NOT NULL default '0.00',
`tran_sl_taken` decimal(11,2) NOT NULL default '0.00',
PRIMARY KEY (`tran_id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=12 ;
#
# Dumping data for table `transactions`
#
INSERT INTO `transactions` VALUES (6, 1, 1, '3500.00', '29.84', '1.00',
'39.78', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '159.12',
'36.70', '36.70', '36.70', '366.96', '50.00', '0.00', '159.12', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (7, 1, 2, '2000.00', '17.04', '1.00',
'22.72', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '90.88',
'21.40', '21.40', '21.40', '213.98', '50.00', '0.00', '90.88', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (8, 1, 3, '9000.00', '68.18', '1.00',
'90.90', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '409.05',
'92.59', '92.59', '92.59', '925.91', '50.00', '0.00', '409.05', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (11, 2, 1, '3500.00', '29.84', '1.00',
'39.78', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '159.12',
'36.70', '36.70', '36.70', '366.96', '50.00', '0.00', '159.12', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (10, 2, 7, '2200.00', '16.67', '1.00',
'22.22', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '99.99',
'23.39', '23.39', '23.39', '233.89', '50.00', '0.00', '99.99', '0.00',
'0.00');
#
My query is as follows:
?php
// IF COUNT OF EMPLOYEES DIFFER WITH COUNT OF TRANSACTIONS, DO THIS
// DISPLAY ONLY EMPLOYEES THAT DON'T HAVE TRANSACTIONS FOR THE
CURRENT (active) PERIOD.
else {
$t1 = 'employees';
$t2 = 'transactions';
$selectempquery = mysql_query(SELECT DISTINCT $t1.*, $t2.emp_id
FROM $t1
LEFT JOIN $t2 ON $t1.emp_id=$t2.emp_id
WHERE NOT $t1.emp_termdate
AND ($t2.pp_id != '$pp_id' OR $t2.pp_id IS NULL)); // This
doesn't work right
// if i replace the above AND with:
AND $t2.emp_id IS NULL); // it won't
