MySQLi query problem
Solved
firpic
-
jordane45 Posted messages 30426 Registration date Status Modérateur Last intervention -
jordane45 Posted messages 30426 Registration date Status Modérateur Last intervention -
Hello, I have a problem with my code, I created a registration form that was working but now it doesn't work anymore.
I don't understand why.
Here is the code for the page:
And the connection to the MySQL server works because I can log in to my site with credentials set with phpMyAdmin.
For more details, please ask me.
Thank you.
I don't understand why.
Here is the code for the page:
<!DOCTYPE html> <html> <head> <meta name="googlebot" content="noindex"> <link rel="icon" type="images/png" sizes="512x512" href="android-chrome-512x512.png"> <link rel="stylesheet" href="serie/allstyle.css"> <link rel="stylesheet" href="style.css"> <link rel="stylesheet" href="style1.css"> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Streawer</title> </head> <body style="background-color: #444444;"> <!-- Page header --> <script> $(document).keydown(function(e){ if(e.which === 123){ return false; } }); </script> <div class="entete" onclick="window.location.reload()"> <img src="img/logo.png" id="logo" alt="Logo Streawer"/> <a href="main.php" id="nm" style="color: #4DB89A;"><h1 id="nom">Streawer</h1></a> </div> <?php require('config.php'); if (isset($_REQUEST['username'], $_REQUEST['email'], $_REQUEST['password'])){ // check if username exists $select = mysqli_query($conn, "SELECT * FROM `users` WHERE username='".$_POST['username']."'"); // retrieve username and remove slashes added by the form $username = stripslashes($_REQUEST['username']); $username = mysqli_real_escape_string($conn, $username); if(mysqli_num_rows($select)){ // retrieve email and remove slashes added by the form $email = stripslashes($_REQUEST['email']); $email = mysqli_real_escape_string($conn, $email); // retrieve password and remove slashes added by the form $password = stripslashes($_REQUEST['password']); $password = mysqli_real_escape_string($conn, $password); // SQL query + encrypted password $query = "INSERT into `users` (username, email, password) VALUES ('$username', '$email', '".hash('sha256', $password)."')"; // Execute the query on the database $res = mysqli_query($conn, $query); if($res){ echo " <div class='sucess'> <h3>You have successfully registered.</h3> <p>Click here to <a href='https://streawer.000webhostapp.com/'>log in</a></p> </div>"; } }else{ $message = "The username is already in use."; } }else{ ?> <form class="box" action="" method="post"> <h1 class="box-logo box-title"><a href="https://streawer.000webhostapp.com/">Streawer</a></h1> <h1 class="box-title">Register</h1> <input type="text" class="box-input" name="username" placeholder="Username" required /> <input type="text" class="box-input" name="email" placeholder="Email" required /> <input type="password" class="box-input" name="password" placeholder="Password" required /> <input type="submit" name="submit" value="Register" class="box-button" /> <?php if (! empty($message)) { ?> <p class="errorMessage"><?php echo $message; ?></p> <?php }?> <p class="box-register">Already registered? <a href="https://streawer.000webhostapp.com/">Log in here</a></p> </form> <?php } ?> </body> <?php include('footer.php'); ?> </html> And the connection to the MySQL server works because I can log in to my site with credentials set with phpMyAdmin.
For more details, please ask me.
Thank you.
2 réponses
Hello,
Display PHP errors
(put this code at the beginning of your PHP script)
and for each query, manage the display of any mysqli errors in case of issues with your queries
in the format:
come back to us with the modified code and any error messages if the problem persists
--
.
Best regards,
Jordane
Display PHP errors
(put this code at the beginning of your PHP script)
error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); and for each query, manage the display of any mysqli errors in case of issues with your queries
in the format:
if (!mysqli_query($conn, " Your sql query ...")) { printf("Error message: %s\n", mysqli_error($link)); } come back to us with the modified code and any error messages if the problem persists
--
.
Best regards,
Jordane
Several things.
First, we try to place as much PHP code outside of the HTML (generally before the HTML code)
We should also avoid using $_POST. In your case, you are using POST... so you need to use $_POST
We place the JavaScript at the end of the page, just before the </body> preferably
We don't place anything outside the <body></body> tags
--
.
Best regards,
Jordane
First, we try to place as much PHP code outside of the HTML (generally before the HTML code)
We should also avoid using $_POST. In your case, you are using POST... so you need to use $_POST
We place the JavaScript at the end of the page, just before the </body> preferably
We don't place anything outside the <body></body> tags
<?php //display PHP errors error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); //database connection require_once('config.php'); //I split the code into "functions" (which you could place in a separate file if //you need it in other pages) /** * Retrieves user info via their username */ function getUserByName($username){ global $conn; $username = mysqli_real_escape_string($conn, $username); $sql = "SELECT * FROM `users` WHERE username='$username'"; if(!$select = mysqli_query($conn, $sql)){ printf("Error message: %s\n", mysqli_error($conn)); return false; }else{ return $select; } } /** * check if the username exists */ function checkIfUserExist($username){ $select = getUserByName($username); return mysqli_num_rows($select) ? true : false; // returns true if it exists otherwise false } function addUser($username,$email,$password){ global $conn; $username = mysqli_real_escape_string($conn, $username); $email = mysqli_real_escape_string($conn, $email); $password = mysqli_real_escape_string($conn, $password); $sql = "INSERT into `users` (username, email, password) VALUES ('$username', '$email', '".hash('sha256', $password)."')"; if (!mysqli_query($conn, $sql)) { printf("Error message: %s\n", mysqli_error($link)); return false; }else{ return true; } } //just for testing, to see if your form submits correctly echo "<br> ---> POST :"; var_dump($_POST); // CLEAN retrieval of variables BEFORE using them // for that, I use the ternary operator (kind of IF/ELSE) $username = !empty( $_POST['username']) ? $_POST['username'] : NULL; $email = !empty( $_POST['email']) ? $_POST['email'] : NULL; $password = !empty( $_POST['password']) ? $_POST['password'] : NULL; //form processing if($username && $email && $password ){ if( checkIfUserExist($username) ){ $message = "The username is already in use."; }else{ if(addUser($username,$email,$password))){ $message = "<div class='sucess'> <h3>You have successfully registered.</h3> <p>Click here to <a href='https://streawer.000webhostapp.com/'>log in</a></p> </div>"; }else{ $message = " Error while inserting into DB"; } } } ?> <!DOCTYPE html> <html> <head> <meta name="googlebot" content="noindex"> <link rel="icon" type="images/png" sizes="512x512" href="android-chrome-512x512.png"> <link rel="stylesheet" href="serie/allstyle.css"> <link rel="stylesheet" href="style.css"> <link rel="stylesheet" href="style1.css"> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Streawer</title> </head> <body style="background-color: #444444;"> <!-- Page header--> <div class="entete" onclick="window.location.reload()"> <img src="img/logo.png" id="logo" alt="Logo Streawer"/> <a href="main.php" id="nm" style="color: #4DB89A;"><h1 id="nom">Streawer</h1></a> </div> <form class="box" action="" method="post"> <h1 class="box-logo box-title"><a href="https://streawer.000webhostapp.com/">Streawer</a></h1> <h1 class="box-title">Sign Up</h1> <input type="text" class="box-input" name="username" placeholder="Username" required /> <input type="text" class="box-input" name="email" placeholder="Email" required /> <input type="password" class="box-input" name="password" placeholder="Password" required /> <input type="submit" name="submit" value="Sign Up" class="box-button" /> <?php if (!empty($message)) { ?> <p class="errorMessage"><?php echo $message; ?></p> <?php }?> <p class="box-register">Already signed up? <a href="https://streawer.000webhostapp.com/">Log in here</a></p> </form> <?php } ?> <?php include('footer.php'); ?> <script> $(document).keydown(function(e){ if(e.which === 123){ return false; } }); </script> </body> </html> --
.
Best regards,
Jordane
As soon as I access the page, this error appears:
Then, once the form is filled out and submitted, this error appears:
I modified line 44 because it wasn't the same variable, so I changed $link to $conn. Now it gives me this error:
---> POST :array(0) { } Then, once the form is filled out and submitted, this error appears:
---> POST :array(4) { ["username"]=> string(4) "test" ["email"]=> string(4) "test" ["password"]=> string(4) "test" ["submit"]=> string(10) "Sign Up" }
Notice: Undefined variable: link in /storage/ssd3/384/16626384/public_html/register.php on line 44
Warning: mysqli_error() expects parameter 1 to be mysqli, null given in /storage/ssd3/384/16626384/public_html/register.php on line 44
Error message :
I modified line 44 because it wasn't the same variable, so I changed $link to $conn. Now it gives me this error:
---> POST :array(4) { ["username"]=> string(4) "test" ["email"]=> string(4) "test" ["password"]=> string(4) "test" ["submit"]=> string(10) "Sign Up" } Error message: Field 'last_login' doesn't have a default value
Here is the new code:
<!DOCTYPE html> <html> <head> <meta name="googlebot" content="noindex"> <link rel="icon" type="images/png" sizes="512x512" href="android-chrome-512x512.png"> <link rel="stylesheet" href="serie/allstyle.css"> <link rel="stylesheet" href="style.css"> <link rel="stylesheet" href="style1.css"> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Streawer</title> </head> <body style="background-color: #444444;"> <!-- Page header --> <script> $(document).keydown(function(e){ if(e.which === 123){ return false; } }); </script> <div class="entete" onclick="window.location.reload()"> <img src="img/logo.png" id="logo" alt="Logo Streawer"/> <a href="main.php" id="nm" style="color: #4DB89A;"><h1 id="nom">Streawer</h1></a> </div> <?php error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); require('config.php'); if (isset($_REQUEST['username'], $_REQUEST['email'], $_REQUEST['password'])){ // Check if the username exists $select = mysqli_query($conn, "SELECT * FROM `users` WHERE username='".$_POST['username']."'"); // Retrieve the username and remove slashes added by the form $username = stripslashes($_REQUEST['username']); $username = mysqli_real_escape_string($conn, $username); if(mysqli_num_rows($select)){ // Retrieve the email and remove slashes added by the form $email = stripslashes($_REQUEST['email']); $email = mysqli_real_escape_string($conn, $email); // Retrieve the password and remove slashes added by the form $password = stripslashes($_REQUEST['password']); $password = mysqli_real_escape_string($conn, $password); //SQL query + hashed password //$query = "INSERT into `users` (username, email, password) VALUES ('$username', '$email', '".hash('sha256', $password)."')"; if (!mysqli_query($conn, "INSERT into `users` (username, email, password) VALUES ('$username', '$email', '".hash('sha256', $password)."')")) { printf("Error message: %s\n", mysqli_error($link)); } // Execute the query on the database $res = mysqli_query($conn, $query); if($res){ echo " <div class='sucess'> <h3>You have successfully registered.</h3> <p>Click here to <a href='https://streawer.000webhostapp.com/'>log in</a></p> </div>"; } }else{ $message = "The username is already in use."; } }else{ ?> <form class="box" action="" method="post"> <h1 class="box-logo box-title"><a href="https://streawer.000webhostapp.com/">Streawer</a></h1> <h1 class="box-title">Sign up</h1> <input type="text" class="box-input" name="username" placeholder="Username" required /> <input type="text" class="box-input" name="email" placeholder="Email" required /> <input type="password" class="box-input" name="password" placeholder="Password" required /> <input type="submit" name="submit" value="Sign up" class="box-button" /> <?php if (! empty($message)) { ?> <p class="errorMessage"><?php echo $message; ?></p> <?php }?> <p class="box-register">Already registered? <a href="https://streawer.000webhostapp.com/">Log in here</a></p> </form> <?php } ?> </body> <?php include('footer.php'); ?> </html>Do you have error messages?
Is the page blank?
to view the page: