I get An error when trying to delete from Database in my Plugin

[Jenksuy]

Hello there, 

 

I have written some php code to try and delete records from my module when they are selected and deleted. However when i go to delete the record i get the following error...

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

Here is my code

 

Delete.php

<?php 
    $db_name = "...";		// The database we created earlier in phpMyAdmin.
    $db_server = "...";	// Change if you have this hosted.
    $db_user = "...";		// Your USERNAME	
    $db_pass = "..."; 			// Your PASSWORD. Working locally, mine is blank. 
    $mysqli = new MySQLi($db_server, $db_user, $db_pass, $db_name) or die(mysqli_error());
     if($_POST["delete"]) // from button name="delete"
         {
              $checkbox = $_POST["checkbox"]; //from name="checkbox[]"
              $countCheck = count($_POST["checkbox"]);
              for($i=0;$i<$countCheck;$i++)

             {

                 $del_id  = $checkbox[$i];

                 $sql = "DELETE from job_details where id = $del_id";

                 $result = $mysqli->query($sql) or die(mysqli_error($mysqli));

                echo $del_id;

             }

} ?>

backend.php

function delete_a_job() {
    $con = mysql_connect("...","...","...");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
     
    mysql_select_db("...", $con);
     
    $result = mysql_query("SELECT * FROM job_details");
     
    echo '<form method="post" action="/wp-content/plugins/face-recruiter/delete.php">
    <table border="1">
    <tr>
    <th>Delete</th>
    <th>Job Reference</th>
    <th>Job Title</th>
    <th>Category</th>
    <th>Location</th>
    <th>Salary</th>
    <th>Type</th>
    </tr>';
     
    while($row = mysql_fetch_array($result))
      {
          echo '<tr>';
          echo '<td><input type="checkbox" name="checkbox[]" id="checkbox[]"  value='.$row['JobReference'].' />';
          echo '<td><a href="/details.php?id='.$row['JobReference'].'">' . $row['JobReference'] . '</a></td>';
          echo '<td>' . $row['JobTitle'] . '</td>';
          echo '<td>' . $row['Category'] . '</td>';
          echo '<td>' . $row['Location'] . '</td>';
          echo '<td>' . $row['Salary'] . '</td>';
          echo '<td>' . $row['Type'] . '</td>';
          echo '</tr>';
      }
    echo "</table>";
    
    // when the loop is complete, close off the list.
   echo '<p><input id="delete" type="submit" class="button" name="delete" value="Delete Selected Items"/></p></form>';
     
    mysql_close($con);
    
}

Can Anyone see what I am doing wrong??

[ginerjm]

Right off the top - you never set a value for $i which you then use to set $del_id which is in your where clause. I imagine your query looks like "DELETE from job_details where id = ";

[Jenksuy]

Thanks... the following code worked which I have updated above...

$mysqli = new MySQLi($db_server, $db_user, $db_pass, $db_name) or die(mysqli_error());
     if($_POST["delete"]) // from button name="delete"
         {
              $checkbox = $_POST["checkbox"]; //from name="checkbox[]"
              $countCheck = count($_POST["checkbox"]);
              for($i=0;$i<$countCheck;$i++)
             {
                 $del_id  = $checkbox[$i];
                 $sql = "DELETE from job_details where jobReference = $del_id";
                 $result = $mysqli->query($sql) or die(mysqli_error($mysqli));
                echo $del_id;
             }
          }

I added the for loop and changed id to jobReference in the query as that what $del_id was showing!

 

Thanks