PHP/MySQL UPDATE not working for second table

[Anonymous]

Hello.

I have 2 tables that store the same information for different groups. When I run this for the "pilots", the query executes successfully and the record is changed. However, when the "data" variable is mentors, the query executes and throws no errors, but does not change the cells information.

In the query if, you can see all the debugging I have attempted. Both data values outpout correctly, all the values are matching what they should, but the mentor table does not update.

Any ideas are much appreciated.

Thank you!

<?php
  $id = $_GET['id'];
  $status = $_GET['status'];
  $database = $_GET['data'];

  switch ($database) {
    case 'ip':
      $database = "pilots";
      break;
    case 'im':
      $database = "mentors";
      break;
    default:
      echo "Invalid";
      exit();
  }
  switch ($status)
  {
    case 's1':
      $status = "Email Sent";
      break;
    case 's2':
      $status = "Account Created";
      break;
    case 's3':
      $status = "Contacted";
      break;
    case 's4':
      $status = "Ready to Pair";
      break;
    case 's5':
      $status = "Active-Paired";
      break;
    case 's6':
      $status = "Inactive";
      break;
    default:
      $status = "FALSE";
      echo 'Invalid';
      exit();
  }

  if (isset($status) && $status != "FALSE")
  {
    // Create connection
    $conn = mysqli_connect("localhost", "root", "", "interestedpilots");
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "UPDATE $database SET status='$status' WHERE id='$id'";

    if (mysqli_query($conn, $sql)) {
      $result = mysqli_query($conn, $sql);
        /*header("Location: interestedPilots.php");*/
        echo $result ? 'good' : 'failed with ' . mysqli_error($connection);
        echo '<br>';
        echo $sql;
        echo '<br>';
        echo $database;
        echo '<br>';
        echo $status;
        echo '<br>';
        echo $id;
    } else {
        echo "Error updating record: " . mysqli_error($conn);
    }

    mysqli_close($conn);
  }
?>

(Page output)
good
UPDATE mentors SET status='Account Created' WHERE id='6'
mentors
Account Created
6

 

[Anonymous]

There are a few issues with your code, you must never trust user supplied data, MySQLi has a method for preparing queries which help protect your data, I've re-written some of your script to update it, but you would be better using PDO:

<?php
/*Do not trust user input, it is dangerous, i.e. values supplied from forms as they can be faked*/
  /*$id = $_GET['id'];*/
  $status = $_GET['status'];
  $database = $_GET['data'];

  switch ($database) {
    case 'ip':
      $database = "pilots";
      break;
    case 'im':
      $database = "mentors";
      break;
    default:
      echo "Invalid";
      exit();
  }
  switch ($status)
  {
    case 's1':
      $status = "Email Sent";
      break;
    case 's2':
      $status = "Account Created";
      break;
    case 's3':
      $status = "Contacted";
      break;
    case 's4':
      $status = "Ready to Pair";
      break;
    case 's5':
      $status = "Active-Paired";
      break;
    case 's6':
      $status = "Inactive";
      break;
    default:
      $status = "FALSE";
      echo 'Invalid';
      exit();
  }

/* this check is not needed as you have already checked the values
  if (isset($status) && $status != "FALSE")
  {*/
    // Create connection
    $conn = new mysqli("localhost", "root", "", "interestedpilots");
    // Check connection
    if ($conn -> connect_errno) {
        die("Connection failed: " . $conn -> connect_errno);
    }
    
    // Use placeholders in your query to allow for user supplied data checking
    $sql = "UPDATE $database SET status=? WHERE id=?";
    
    // protect your database by preparing queries
    $prepared_sql -> prepare($sql);
    $prepared_sql -> execute(['status'=>'$status', 'id'=$_GET['id']]);

    if ($prepared_sql -> affected_rows() > 0){
      echo 'Success';
    } else {
      echo 'Oops';
    }
/*
    if (mysqli_query($conn, $sql)) {
      $result = mysqli_query($conn, $sql);/* query executed twice? */

        /*header("Location: interestedPilots.php");*/

        /* if You're transfering to another page, this is pointless */
/*      echo $result ? 'good' : 'failed with ' . mysqli_error($connection);
        echo '<br>';
        echo $sql;
        echo '<br>';
        echo $database;
        echo '<br>';
        echo $status;
        echo '<br>';
        echo $id;
    } else {
        echo "Error updating record: " . mysqli_error($conn);
    }

    mysqli_close($conn); the connection will close when the script ends
  }
?> closing not needed */

code not checked

As to your original question, are you mixing up databases for tables?

Have you double checked that you have a table called 'mentors'?

If 'mentors' does exist, have you checked that it have columns 'status' and 'id'

Have you checked your error log?


If this is a current status thing, you might want to consider adding a date column to record when status changes are made?