#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0

    Insert record based on condition if true


    hi guys, i'm a beginner in php programming and want to know how can i achieve my goal.

    1. insert record if the hours is not > 6 on the assigned date
    ex. i will sum the hours on 2013-07-04, if the total hours is >= 6 (FALSE), i will check the next record and if the next record is >=6 (TRUE), i will insert another row with the same date.

    id emp_id date hours
    1 1 2013-07-04 2
    2 1 2013-07-05 3
    3 1 2013-07-06 2
    4 1 2013-07-04 2
    5 1 2013-07-05 2
    6 1 2013-07-06 4

    im not sure which method to use as i can think of is to use "do while" or fetch each row then validate or get the least hours with the oldest date, etc. but i dont know where to start

    currently below is my code..

    thank you very much for the help

    Code:
    $result = mysql_query("SELECT *,SUM(hours) AS TOTAL_HOURS FROM `tbl_load` where emp_id='4' GROUP BY DATE", $connection) or die("error querying database");
     while($result_ar = mysql_fetch_assoc($result)){
    
     
    $result_id_emp = $result_ar['emp_id'];
    $result_date = $result_ar['date'];
    $result_hours = $result_ar['hours'];
    $result_total_hours = $result_ar['TOTAL_HOURS'];
    
     
    $hours_to_insert = "2.5";
    $no_of_record_to_insert = "4";
    $hours_to_insert2 = $result_total_hours + $hours_to_insert;
     
    
    if ($result_total_hours >= 6 ) {
     echo "OVERLOAD -- $result_id_emp $result_date $result_hours $result_total_hours<br>";
     } else {
    
    
    if ($result_total_hours >= 6 ) {
     echo "OVERLOAD -- $result_id_emp $result_date $result_hours $result_total_hours<br>";
     } else {
    
    
    $i=1;
     while($i!=$no_of_record_to_insert)
     {
     echo "INSERT -- $hours_to_insert -- $result_id_emp $result_date $result_hours $result_total_hours<br>"; 
    $i++;
     }
     } 
    }
     }
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    Originally Posted by elg3ne
    hi guys, i'm a beginner in php programming and want to know how can i achieve my goal.

    1. insert record if the hours is not > 6 on the assigned date
    ex. i will sum the hours on 2013-07-04, if the total hours is >= 6 (FALSE), i will check the next record and if the next record is >=6 (TRUE), i will insert another row with the same date.

    id emp_id date hours
    1 1 2013-07-04 2
    2 1 2013-07-05 3
    3 1 2013-07-06 2
    4 1 2013-07-04 2
    5 1 2013-07-05 2
    6 1 2013-07-06 4
    Not sure if I got what you want to do, if you want to get the result from the database and depending on how many hours the database gives you as result you need to use object.
    So the select would be like:
    PHP Code:
    $result mysql_query ("SELECT column from table where whatever = whatever;
      while (
    $row = mysql_fetch_object($result)){           
          
    $column=$row->column;
        if (
    $row->column=="whataver"){
    do what you want here 
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    hi, thank you for reply but im still confused how to do it.

    but here is what I have in mind now to do.

    1. select all rows on the table group by date
    2. after selecting row, run validation if hours is >= 6
    3. if validation is true, go to next row then validate again
    4. run the validation while x is not equal to n.

    is it possible?

    thanks
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    Originally Posted by elg3ne
    hi, thank you for reply but im still confused how to do it.

    but here is what I have in mind now to do.

    1. select all rows on the table group by date
    2. after selecting row, run validation if hours is >= 6
    3. if validation is true, go to next row then validate again
    4. run the validation while x is not equal to n.

    is it possible?

    thanks

    sorry, not sure if this is what you mean,

    PHP Code:
    $result mysql_query ("SELECT * from table group by date");
      while (
    $row mysql_fetch_object($result)){           
        if (
    $row->hours>="6" and != n){
    do 
    what you want here 
    Edited:
    I see now, you want to sum the result of rows to get hours,
    to sum would be something like
    PHP Code:
    $result mysql_query ("SELECT sum(hours) as hours from table group by date");
      while (
    $row mysql_fetch_object($result)){           
        if (
    $row->hours>="6" and != n){
    do 
    what you want here 
    do what you want here
    Last edited by newtonperri; July 8th, 2013 at 09:29 AM.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Hi newtonperri, I manage to get what I really want to do with the below code.

    1. What it will do is to insert record X times. ($insert_rec)
    2. Check each rows if sum_hours + hours_employee_load for that date is >= 7.. if yes, adjust the counter for $insert_rec
    3. If <= 7 insert the record based on selected save_date result
    4. Check each rows if sum_hours >= 7, if yes adjust the counter so that it can check the next date

    Not sure if my coding is the best approach but it gives the output what I want.

    Thanks

    PHP Code:
    $insert_rec=4;
    $date_format=new DateTime("2013-07-11");
    for (
    $i=1$i<=$insert_rec$i++) {
        
    $date_format->modify("+1 day");
        
    $save_date $date_format->format("Y-m-d");
        
        
    $result mysql_query("SELECT SUM(hours) AS TOTAL_HOURS FROM `tbl_employee_load` where id_employee='4' AND date='$save_date' GROUP BY DATE"$connection) or die("error querying database");
        
        
    $row mysql_fetch_row($result);
        
    $sum_hours $row[0];
        
    $hours_employee_load "4";
        
    $total_hours_to_insert $sum_hours $hours_employee_load;    
        
        if (
    $sum_hours <= 7) {
            if (
    $total_hours_to_insert >= 7) {
               echo 
    "OVER 7- $save_date | $sum_hours + $hours_employee_load = $total_hours_to_insert<br>";
            
    $insert_rec++;    
            } else {
            echo 
    "INSERT - $save_date | $sum_hours + $hours_employee_load = $total_hours_to_insert<br>";
            }
        } else {
           echo 
    "OVER - $save_date | $sum_hours + $hours_employee_load = $total_hours_to_insert<br>";
        
    $insert_rec++;    
           }



IMN logo majestic logo threadwatch logo seochat tools logo