CODEWITHSUNDEEP
phpmysqli
dan
dan

Reputation: 563

Updating row by row

Upon clicking on the button edit, i'm trying to update/insert my database (col:note). However if i change the value in one row, the same value is updated for all rows. I tried adding a WHERE condition (based on afnumber<-- unique id) for the insert and update sql, but couldn't get it to work. Any help please?

$conn = new PDO('mysql:host=localhost;dbname=jr', 'root', 'Js');
    $conn->exec("set names utf8");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
    $num_rows = $conn->query('SELECT COUNT(*) FROM employees')->fetchColumn(); 
    $pages = new Paginator($num_rows,9,array(15,3,6,9,12,25,50,100,250,'All'));
    echo $pages->display_pages();
    echo "<span class=\"\">".$pages->display_jump_menu().$pages->display_items_per_page()."</span>";
    $stmt = $conn->prepare("SELECT employees.afnumber,employees.name,employees.dateofemployment,employees.actualpost,employees.department FROM employees WHERE employees.status='Employed' AND (employees.afnumber LIKE '%$search%' OR employees.name LIKE '%$search%') ORDER BY employees.afnumber DESC LIMIT :start,:end");
    $stmt->bindParam(':start', $pages->limit_start, PDO::PARAM_INT);
    $stmt->bindParam(':end', $pages->limit_end, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchAll();

    $ewhtable = "<table class='sortable'><tr><th>AFNumber</th><th>Employee Name</th><th>Years of Service</th><th>Actual Post</th><th>Department</th><th>Note</th><th>Deducted Hours</th></tr>\n";
    foreach($result as $row) {
        $years=explode("/", $row[2]);
        $years[2]=intval(date ('Y')) - $years[2];

$sql="SELECT note,deductedwh FROM editedworkhours WHERE afnumber='$row[0]'";

$var = "";
$varr = "";  

$stmt = $conn->prepare($sql);
$stmt->execute();
$result2 = $stmt->fetchAll();

foreach ($result2 AS $row2) {
  $var .= $row2['deductedwh'] . "\n";
    $varr .= $row2['note'] . "\n";
}
        $Id = $row[0];
        $ewhtable .= "<tr><td>$row[0]</td><td>$row[1]</td><td>$years[2]</td><td>$row[3]</td><td>$row[4]</td><td><form method='post'><input type='text' name='Note' value='$varr' style=' padding: 10px;border: solid 2px #c9c9c9; width:200px; height:2px;'><input type='submit' id='search' name='edit' alt='search' value=''></form></td><td>$var</td></tr>\n";
    }



    $ewhtable .= "</table>\n";
    echo $ewhtable;

    exportTable(str_replace("&","",$ewhtable),"EmployeeDeductedWorkHoursTable");
    echo $pages->display_pages();
    echo "<p class=\"paginate\">Page: $pages->current_page of $pages->num_pages</p>\n";

          if(isset($_POST['edit']))
    {   
        $note = $_POST['Note'];  

     $sql1="SELECT Note FROM editedworkhours"; 

if ($result=mysqli_query($con,$sql1))
  {

  $rowcount=mysqli_num_rows($result);
  }
    if($rowcount==0)
     {
 $sql="INSERT INTO editedworkhours (Note) VALUES ('$note')";
 $result = mysqli_query($con,$sql);
     }
     else
     {
 $sql2 = "UPDATE editedworkhours SET Note= '$note'";
 $result2 = mysqli_query($con,$sql2);
     }

    }
    echo "</div>";

Upvotes: 0

Views: 37

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157888

There are too many errors in this code, starting from lack of error reporting, direct danger of SQL injection, mixing different DB APIs, lack of proper SQL and so on - too much to make it answerable in one post.

This question have to be closed as too broad and you have to sit with your textbooks for couple more hours.

Upvotes: 1

Related Questions