0

In the Exam1 table, ID, examname, and exam_id values are added fine. But when I try to use the UPDATE query to update the points to another table (Question), it does not work.

        for($i = 1; $i<$arraysize; $i++){ //For every question

            $questionid = $array[$i]['questionid'];
            $points     = $array[$i]['points'];
            $ID = $ID+1;

            $queue ="INSERT INTO Exam1 (ID, examname, exam_id) VALUES
            ('$questionid','$examname', '$exam_id')";
            $result = mysqli_query($connection,$queue);


            $queue1 ="INSERT INTO points (ID, points) VALUES
            ( '$ID' , '$points')";
            $result1 = mysqli_query($connection,$queue1);



         $sql = "UPDATE Question SET points='$points' where ID ='$questionid'";

           $result1 = mysqli_query($connection,$sql);

        }

enter image description here

newbie
  • 85
  • 1
  • 9
  • Can you please add the DB schema definition (e.g. your "CREATE TABLE" statement)? What is the type of column `ID` in table `Question`? Do you really want to have column `points` as text-like data type (and not as integer)? Please elaborate on how records are `INSERT`ed to `Question`. What does `SELECT * from Question where ID = '$questionid'` return? – EagleRainbow Feb 24 '18 at 23:53
  • 2
    Side remark: Your coding is full of cases which might be subject to SQL injection. Those may pose a major security threat to your software. For example use parameter binding to fix that (see also https://stackoverflow.com/a/60496/6350762 ) – EagleRainbow Feb 24 '18 at 23:55
  • Also, learn about the `foreach` function. You will like it better than doing a for loop with a `$i` but yes, show us your structure of `question` table. – Forbs Feb 24 '18 at 23:56
  • can you post your table please? –  Feb 25 '18 at 01:26
  • @EagleRainbow The data type for the column ID is an int in Question table. The data type for the column points is an int. There is another php script where records are inserted `$queue = "INSERT INTO Question (id, question, points) VALUES ('$idmax','$question','$points')";` I am using an MVC architecture so in the front when the user submits the form I am sending a JSON array `[{"questionid":"1","points":"10"},{"questionid":"4","points":"15"}]` to my Question table. In my php script that is connected to the Exam1 backend table I am trying to update the points from the Question table – newbie Feb 25 '18 at 02:52

2 Answers2

0
 $myquery = "UPDATE Question SET points='$points' WHERE ID ='" . $questionid . "'";

   $resultset = mysqli_query($connection, $myquery);

Try this please

  • I tried the above statement, but it did not work. I also tried this ` $myquery = sprintf("UPDATE Question SET points= %s WHERE ID = %s", $points, $questionid);` but that also doesn't work. – newbie Feb 25 '18 at 02:35
0

First of all be careful with int types and putting values for it in UPDATEs and INSERTs into a quote: There is type cast happening, and it may come to results, which you may not expect.

Second, I still have big trouble to understand your data model of your database: You are joining the Exam1.ID over with the Question.ID. Both appear to be the surrogates for the entity to me. Otherwise I would not understand what the meaning of the column Exam_ID (most likely coming from table Question in your case) should be. It therefore looks to me that the WHERE clause of your UPDATE statement is incomplete (did you also mean to restrict on EXAM_ID? Otherwise you might be setting all points to 20 for all questions irrespectively of the exam...?)

If I got you wrong, please provide a detailed overview about your database schema setup including the primary keys of at least the following tables:

  • Exam1
  • points
  • Question

to allow us further help you (and me adjusting the answer here). Also giving us some insight how you think the foreign key relationship is between the tables might help us to help you.

EagleRainbow
  • 931
  • 5
  • 22