CODEWITHSUNDEEP
phphtmlmysqlvariables
lcolli98
lcolli98

Reputation: 169

How to use PHP Variables in an SQL statement

Before anybody says, I will protect myself against SQL injections, right after I fix this error. I am making an app where news reports are submitted to the database. This page is what removes a report from the database.

What I have tried: Every possible way of adding brackets to the SQL and speech marks. My ICT teacher and I have looked at this for nearly 2 hours and cannot find a fix. I have also searched Google and Stack Overflow but I cannot find an answer.

Ok, so the correct report_id displays when I echo it. When I put the actual id, eg 5, the report is deleted. But when I put $report_id, nothing is deleted.

Please could somebody tell me what correction I have to make to get this to work ?

Here is the code (EDIT: This is the fixed code. I added the hidden field in the form at the bottom, among a few other small changes (like taking out the extra form tag)):

<?php
  require_once('authorize.php');
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>Football Central - Remove a Report</title>
</head>
<body>
  <h2>Football Central - Remove a News Report</h2>

<?php
  require_once('img_details_reports.php');
  require_once('connect_db_reports.php');

   //Assign variables from admin_reports.php using $_GET
   $report_id = $_GET['id'];

    if (isset($_POST['submit'])) {
     if ($_POST['confirm'] == 'Yes') {

      $report_id = $_POST['id'];
      // Delete the image file from the server
      @unlink(IMAGE_UPLOADPATH . $image);

      // Connect to the database
      $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
      or die("Unable to connect to the database."); 

      // Delete the score data from the database
      $query = "DELETE FROM news_reports WHERE report_id = '".$report_id."' LIMIT 1" 
      or die("mysql_query failed - Error: " . mysqli_error());

      mysqli_query($dbc, $query) or die("mysql_query failed - Error: " . mysqli_error());
      mysqli_close($dbc);
     }
    }         

    //Display form to confirm delete
    echo '<p>Are you sure you want to delete the news report?</p>';
    echo '<form method="post" action="removereport.php">';
    echo '<input type="radio" name="confirm" value="Yes" /> Yes ';
    echo '<input type="radio" name="confirm" value="No" checked="checked" /> No <br />';
    echo '<input type="hidden" name="id" value="' . $report_id . '" />';
    echo '<input type="submit" value="Submit" name="submit" />';
    echo '</form>';

    echo '<p><a href="admin_reports.php">&lt;&lt; Back to admin reports page</a></p>';
?>

</body>
</html>

Upvotes: 2

Views: 2669

Answers (5)

Nishu Tayal
Nishu Tayal

Reputation: 20880

You need to check following things in your code.

  • Where is your ID element in the form
  • You have put POST method in form but retrieving data from $_GET, you should change it to $_POST.

  • Put mysqli_error after mysqli_query statement.

    $query = "DELETE FROM news_reports WHERE report_id = ".$report_id;

mysqli_query($dbc, $query);  or die("mysql_query failed - Error: " . mysqli_error());

Then check the error from mysql if it does not work.

Hope, it will help you in solving your issue.

Upvotes: 0

Carson Myers
Carson Myers

Reputation: 38574

You've got

$query = "..." or die(...);

Why?

Also, you've got two form opening tags -- it's not valid to nest forms.

I'm going to assume that the id variable comes in from some source other than the form because there's no form element that submits it.

Finally, make sure to specify get or post in your form. I'd recommend using post, and then change $_GET["submit"] and $_GET["confirm"] to $_POST["submit"] and $_POST["confirm"].

Upvotes: 0

DougM
DougM

Reputation: 2888

You shouldn't have to wrap the ID in single-quotes, if the ID is a number.

$query = "DELETE FROM news_reports WHERE report_id = '".$report_id."' LIMIT 1"

But that's not the problem. You did not include the ID in your confirmation request, or allow for retrieving the value from a session variable. Add a hidden input box with the id, in your "Display form to confirm delete" section.

(And have a different code branch for confirmation! And a test for an invalid ID! And move this to POST, at the very least!)

Upvotes: 0

SAVAFA
SAVAFA

Reputation: 818

You are sending the form with post method and retrieving it with get. That can be the source of the problem.

Also you are not sending the id parameter so, there won't be any value for $_get[id] nor $_post[id]

Upvotes: 0

Rikesh
Rikesh

Reputation: 26451

Your are mixing two statements. Just try below.

  // Delete the score data from the database
  $query = "DELETE FROM news_reports WHERE report_id = ".$report_id;  
  mysqli_query($dbc, $query) or die("mysql_query failed - Error: " . mysqli_error($dbc));

Upvotes: 3

Related Questions