Jump to content

problem in where clause to check if the date(TIME_OUT) is exist in another table


newphpcoder
 Share

Recommended Posts

Hi..I have this code for checking if the date(TIMEOUT) is already exist in nrs table:

	  $EMP_NO = $_GET['EMP_NO'];	  $DATE_NRS = $_GET['DATE_NRS'];	  $TIME_IN = strtotime($_GET['TIME_IN']);	  $TIME_OUT = strtotime($_GET['TIME_OUT']);	  $APPROVE = $_GET['APPROVE'];$sql = "SELECT EMP_NO, TIME_IN, TIME_OUT, TOTAL_HOURS, NRS_STATUS FROM nrs WHERE EMP_NO = '$EMP_NO' AND DATE(TIME_OUT) = '$TIME_OUT'"; //echo $sql;  $RsOtData = $conn2->Execute($sql);   $numrows = $RsOtData->RecordCount();    if($numrows > 0){  echo "<script>alert('Transaction cannot be process')</script>"; echo "<script>navigate('NRSEmp.php')</script>";    }  else{  $saverec['EMP_NO'] = $EMP_NO;  //$saverec['DATE_NRS'] = $DATE_NRS;  $saverec['TIME_IN'] = $TIME_IN;  $saverec['TIME_OUT'] = $TIME_OUT;  $saverec['TOTAL_HOURS'] = $TOTAL_HOURS;  $saverec['NRS_STATUS'] = $APPROVE;   $insertSQL = $conn2->GetInsertSQL($RsOtData, $saverec);  $conn2->Execute($insertSQL);  }

now I need revise my query to check also if the date from $TIME_OUT is equal to reg_att .for example of reg_att table data:EMP_NO = 00000221LOGIN = 2012-03-01 05:35:00LOGOUT = 2012-03-01 13:35:00 if Date from $TIMEOUT = date(LOGOUT) if condition will work.I trid this query

SELECT n.EMP_NO, n.TIME_IN, n.TIME_OUT, TOTAL_HOURS, NRS_STATUS FROM nrs n WHERE EMP_NO = '00000221' AND DATE(TIME_OUT) = '2012-03-01' OR DATE(TIME_OUT) = (SELECT DATE(LOGOUT) FROM reg_att r WHERE r.EMP_NO = n.EMP_NO);

and i got an error:Error Code : 1242Subquery returns more than 1 row(0 ms taken)Thank you

  • Like 1
Link to comment
Share on other sites

The issue you're having is here:

(SELECT DATE(LOGOUT) FROM reg_att r WHERE r.EMP_NO = n.EMP_NO);

That query is returning more than one row, thus the join doesn't know which row to choose. Have you tried running this query separately? SELECT DATE(*date here*)FROM reg_att rWHERE r.EMP_NO = n.EMP_NO Then you can see where you're going wrong.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...