1

I am trying to update three table of mysql database. i tried two ways but its not happening.The thing is, someone can check my database table and see the relation and then check my query either it works or not My tables are as follows:

projects     | students     | progress
============================================
Pk ProjectID | PK RegNo     | PK ProgressID
ProjectTitle | Name         | FK RegNo
             | FK ProjectID |

I tried different ways to achieve my updation.

$query= "UPDATE progress SET RegNo='$s1_id' 
         WHERE RegNo IN(SELECT RegNo FROM students WHERE ProjectID = '$id'); 

         UPDATE students SET RegNo='$s1_id', Name='$s1_name' 
         WHERE ProjectID = '$id';

         UPDATE projects SET ProjectTitle='$ptitle' 
         WHERE ProjectID='$id';";

2nd Way

 $query = "UPDATE projects 
           INNER JOIN students ON students.ProjectID = projects.ProjectID
           INNER JOIN progress ON students.RegNo = progress.RegNo
           SET projects.ProjectTitle = '$ptitle',
               students.RegNo = '$s1_id',
               students.Name = '$s1_name'  ,
               progress.RegNo = '$s1_id' 
           WHERE projects.ProjectID = '$id' ";

try
{
    $stmt = $conn->prepare( $query );  
    $result = $stmt->execute();
    $msg = "Record updated";

    header("location:adminhome.php");
} 
catch(PDOException $ex)
{
    $msg = $ex -> getMessage();
}
James Z
  • 12,209
  • 10
  • 24
  • 44
Mr_question
  • 71
  • 2
  • 10
  • In second query remove **;** after ` = projects.ProjectID`, Also do you get any error ? – M Khalid Junaid Jun 12 '18 at 11:57
  • 1
    First way: mysql does not allow multiple statements inside a single string ("statement 1; statement 2; ..."). Take a look over here: https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli.quickstart.multiple-statement.html . So you have to use `$mysqli->multi_query($sql)`. – ChristophS Jun 12 '18 at 12:13
  • First way is working with delete but not on update....i am working with pdo – Mr_question Jun 12 '18 at 12:17
  • i changed it but still not working M Khalid Junaid – Mr_question Jun 12 '18 at 12:20
  • So your question might be a duplicate: Use `$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);` as described here: https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd – ChristophS Jun 12 '18 at 12:22
  • Possible duplicate of [PDO support for multiple queries (PDO\_MYSQL, PDO\_MYSQLND)](https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd) – ChristophS Jun 12 '18 at 12:24
  • The thing is, someone can check my database table and see the relation and then check my query either it works or not thanks – Mr_question Jun 12 '18 at 13:06
  • you're open to SQL injection - this should be fixed as a priority :) from what I remember it's not possible via PDO, but may be possible in that linked question (haven't looked) – treyBake Jun 12 '18 at 13:24
  • i have seen that, can you please check my relation of database ....i am updating primary key which is foreign key in another table but not updating it. – Mr_question Jun 12 '18 at 13:27

0 Answers0