Am I using MySQLi UPDATE the right way?

Question

I have two tables in my database namely tutors and students with the following structure:

tutors table
+------------+--------------------+---------------------+
|tutor_id    |tutor_avail_credits |entry_time           |
+------------+--------------------+---------------------+
|    20      |      300           | 2017-01-22 11:53:10 |
+------------+--------------------+---------------------+
|    13      |      200           | 2017-01-24 13:58:45 |
+------------+--------------------+---------------------+

 students table
+------------+--------------------+---------------------+
|student_id    |student_credits   |entry_time           |
+------------+--------------------+---------------------+
|    20      |      50            | 2017-01-24 12:23:10 |
+------------+--------------------+---------------------+
|    13      |      100           | 2017-01-24 15:58:45 |
+------------+--------------------+---------------------+

I have a PHP script that allow students to reserve a tutor for a tutoring session that will run for minutes equivalent to student's credits. The way I had intended it to work was that, I run a query to fetch rows for the top most tutor(earliest since the list is sorted according to time)

 $query = "SELECT * FROM tutors 
             WHERE (tutor_avail_credits > 0 ) 
           ORDER BY entry_time ASC LIMIT 1 ";

If a student submits a form to reserve the top most tutor, his/her credits should be subtracted from the tutors credits until the tutor is fully reserved. Each time a reservation occurs tutor_avail_credits field should be updated until it's 0. So I have this code.

       <?php

         //Check if reserve button press
        if(isset($_POST['reserve'])){

           //Some code here.........

        if($student_credits < $tutor_avail_credits){
            $tutor_avail_credits = $tutor_avail_credits - $student_credits;
            //Update table field. 
            $update = "UPDATE tutors 
                       SET  tutors_avail_credits = '$tutors_avail_credits'
                       WHERE tutor_id = '$tutor_id'"
            }
           //Code continues.....
           ?>

Now the problem is that the field tutor_avail_credits will be accessed by multiple users at the same time, won't this cause some problems? if so, is it possible for me to have additional column in tutors table where I can set a flag after running a SELECT query so that it will be used as a condition allowing other users to select tutors which are currently not selected.Here is the query.

 $query = "SELECT * FROM tutors 
             WHERE (tutor_avail_credits > 0 AND status ='available') 
           ORDER BY entry_time ASC ";

Please I am a beginner point me in the right direction.

you might want to check this https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html — Gonzalo.-, Feb 09 '17 at 00:35
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Feb 09 '17 at 01:23
@tadman Thanks for such a great advice, I will change my code as per the requirement. — Ngenazy, Feb 09 '17 at 05:52
@Gonzalo.-I am looking at it now, will keep you posted of my progress.Thank you — Ngenazy, Feb 09 '17 at 05:54

Am I using MySQLi UPDATE the right way?

0 Answers0