MySQLi query problem

Solved

firpic -
jordane45 Posted messages 30427 Registration date Status Moderator Last intervention - 3 Jun 2021 at 12:27

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:

 <!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 answers

jordane45 Posted messages 30427 Registration date Status Moderator Last intervention 4 832

Hello,

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

firpic

I included the code with my request and nothing is working yet.

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>

jordane45 Posted messages 30427 Registration date Status Moderator Last intervention 4 832 > firpic

Nothing works, what do you mean?
Do you have error messages?
Is the page blank?
0
firpic > jordane45 Posted messages 30427 Registration date Status Moderator Last intervention
I have no errors, the page is loaded correctly, it's just that the request is not being made and nothing shows up after clicking on it (lines 55 to 58 should be displayed).

to view the page:
```
https://streawer.000webhostapp.com/register.php
```
0

jordane45 Posted messages 30427 Registration date Status Moderator Last intervention 4 832

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

 <?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

firpic
As soon as I access the page, this error appears:
```
---> 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
```
0
1. jordane45 Posted messages 30427 Registration date Status Moderator Last intervention 4 832 > firpic
  
  ah well....
  
  So:
  Error message: Field 'last_login' doesn't have a default value
  
  This means that:
  Either you add this column to your INSERT query and give it a value
  Or, in your table configuration, you set this field with a default value.
  ( So it’s not a PHP issue.. but a MySQL one.)
  
  0
2. firpic > jordane45 Posted messages 30427 Registration date Status Moderator Last intervention
  
  How did they manage to hide this message:
  ---> POST :array(4) { ["username"]=> string(4) "test" ["email"]=> string(4) "test" ["password"]=> string(4) "test" ["submit"]=> string(10) "Sign up" }
  
  0
3. jordane45 Posted messages 30427 Registration date Status Moderator Last intervention 4 832 > firpic
  
  Well, it's easy... you read the comments I put in the code... and that will help you find it!
  
  0

MySQLi query problem

2 answers

Money 2005 file read-only

Vertical rule

Yodoit monitor connection

Hello, emails impossible to delete

Samsung ue40d5700 tv turns off after 2 minutes and restarts

Unable to scan

How to stop having to hammer on your code?

Create an instagram account

Many f a i offer a €2 subscription and the card €1"

No access to my ubisoft account