PHP variable doesn't load with MySQL update query

Question

I have omitted unnecessary code. Whenever I try to run this, it processes without any errors. However, in the update query, whenever I use WHERE student_id='$student_id', it doesn't update. No errors, it just doesn't update. However, when I use the numeric equivalent of the variable, such as 1, it works just fine. What am I missing? Thank you!

$resolved_student_id = $_GET['student_id'];

try {
   $request_sd = $db -> prepare("SELECT student_name,tutor,intervention FROM students WHERE student_id='$resolved_student_id'");
   $request_sd -> execute();
} catch ( Exception $e ) {
   echo "Could not query database.";
   exit;
}

$studentdata = $request_sd -> fetch();


if ( empty( $_POST ) === false ) {
   if ( empty( $_POST['student_name'] ) === true || empty( $_POST['student_tutor'] ) === true || empty( $_POST['student_intervention'] ) === true ) {
    $updateStudentInformation = "You need to fill out all fields.";
} else {
    $student_name = $_POST['student_name'];
    $student_tutor = $_POST['student_tutor'];
    $student_intervention = $_POST['student_intervention'];

    try {
       $updatedata = $db -> prepare("UPDATE students SET student_name='$student_name', tutor='$student_tutor', intervention='$student_intervention' WHERE student_id='$resolved_student_id'");
       $updatedata -> execute();
    } catch (Exception $e) {
       echo "Could not update database.";
       exit;
    }
    header("location: edit.php");
  }
}

If you're preparing, please use parameters instead of unescaped strings. Also, have you configured your (PDO?) database to throw exceptions? — Wrikken
– Wrikken, Commented Oct 15, 2013 at 0:04
Also $resolved_student_id != $student_id, could that be a cause? — Wrikken
– Wrikken, Commented Oct 15, 2013 at 0:05
@user2672698 No, don't do that. Stick with prepared statements but use parameter binding — Phil
– Phil, Commented Oct 15, 2013 at 0:08

Amit Singh · Accepted Answer · 2013-10-15 00:17:07Z

1

How come you are using both get and post methods here?

I guess this: $resolved_student_id = $_GET['student_id'];

Should be replaced by: $resolved_student_id = $_POST['student_id'];

and if you need both methods make sure to specify the GET data in the form URL.

eg:<form method="POST" action="abc.php?student_id=1">

And the reason its not updating where $student_id is you have not defined any such variable, not at least what i can see here in the code u posted.

edited Oct 15, 2013 at 0:17

answered Oct 15, 2013 at 0:11

Amit Singh

2,2754 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brooke Holmes Over a year ago

You helped me realise what I did wrong there, thank you very much. It is working now. I was using my form action request to the same page without including the GET variable it needed to process the form properly.

Machavity · Accepted Answer · 2013-10-15 00:04:42Z

0

You've written your query as a straight up query (where all the options are defined) but you're running prepare, where your query would look like this

UPDATE students SET student_name=?, tutor=?, intervention=? WHERE student_id=?

Then you would run bind_param to bind your data to the prepared statement and then execute()

So either convert your query to a prepared statement query and bind your parameters or change from prepare to query

answered Oct 15, 2013 at 0:04

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

1 Comment

Phil Over a year ago

1) PDO, not MySQLi. 2) Whilst it is certainly recommended to use parameter binding, you don't have to in order to use prepare

Phil · Accepted Answer · 2013-10-15 00:31:18Z

Several things here...

Configure PDO to throw exceptions. I like using this constructor for optimal MySQL usage

$db = new PDO('mysql:host=localhost;dbname=db_name;charset=utf8', 'username', 'password', array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC));

Stop catching exceptions and discarding them. This is most important while developing. Once you're confident that your code runs properly, then you can implement a high-level exception handling scheme. Simply remove the try controls and catch blocks from your code

Use parameter binding with your queries instead of string interpolation. This will make your queries much more resilient to SQL injection attacks.

$request_sd = $db->prepare("SELECT student_name,tutor,intervention FROM students WHERE student_id = ?");
$request_sd->execute([$resolved_student_id]);
// use array($resolved_student_id) if using an older version of PHP

You would do the same with your update query.

Collectives™ on Stack Overflow

PHP variable doesn't load with MySQL update query

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related