-2

I cant able to Update my users table from different college table which is present in another main database. But my query running successfully on mysql but when I am trying to execute query using PHP it cant able to update the user database from the college database

    UPDATE users 

     SET 

     student_name = ( SELECT main.college.Student_Name
                       FROM main.college
                       WHERE main.college.Enroll_No =  '123456'
                       LIMIT 1 ) 
      WHERE user_id =5

Above query successfully running on phpmyadmin. But in PHP cant able to Update the user table

<?php



         require_once('dbConnect.php');

            $sql = "UPDATE users \n"
                    . "\n"
                    . "SET \n"
                    . "\n"
                    . "student_name = ( SELECT main.college.Student_Name\n"
                    . " FROM main.college\n"
                    . "WHERE main.college.Enroll_No = '123456'\n"
                    . "LIMIT 1 ) \n"
                    . "WHERE user_id =5";
                   if(mysqli_query($con,$sql)){

                             mysqli_query($con,$sql);

                             echo 'successfully registered';
            }else{
                echo 'oops! Please try again!';
            }


     mysqli_close($con);

?>
Arqam Syed
  • 1
  • Maybe i am wrong, but i do not think there a need to put `\n`. It maybe the reason why It is not executed succesfully. – youssouf Oct 27 '16 at 00:03
  • @youssouf: rather than guess what the problem is, it would better to diagnose what the *actual* error is, by retrieving the MySQL error message using `mysqli_error`. We're guessing that the code is successfully connecting to the database, and that the user has sufficient privileges on the referenced schema objects. – spencer7593 Oct 27 '16 at 00:11
  • undrestood. I'll try. – youssouf Oct 27 '16 at 00:14
  • Can you if possible post the error you got?? – youssouf Oct 27 '16 at 00:16
  • I tested your code in a database and it works fine. – youssouf Oct 27 '16 at 00:30

1 Answers1

0

The code appears to be calling the mysqli_query function two times, which is not necessary.

For debugging, I strongly recommend that you call mysqli_error (in the else block) to retrieve the MySQL error message, and echo that out. (That would let you see an error, rather that wondering what the problem is.

Also for debugging, echo out the contents of $sql before you send it to the database.

(We're assuming that the behavior you observe is the echo of 'oops! Please try again!'. But that's really just a guess, since the description of the behavior you observe is a rather vague "cant able to".)

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • I removed extra 'mysqli_query' and added 'mysqli_error' in 'else'. No error displaying. Its echo 'Oops!Please try Again'. @spencer7593 – Arqam Syed Oct 27 '16 at 00:28
  • Uhhmmm... `mysqli_error` just returns a string. To get the error message displayed/output, you can `vardump`, `printf` or `echo` the returned string e.g. `echo mysqli_error($con);` or you can write to your log output, or whatever, just as you would with any other string. http://php.net/manual/en/mysqli.error.php – spencer7593 Oct 27 '16 at 04:09
  • We're assuming that you've already made sure that PHP error reporting is enabled, and verified that the database connection is valid... the user you are connecting as has required permissions. That is, you've debugged the issue to the point you know that the problem is with the SQL statement. – spencer7593 Oct 27 '16 at 04:16