Added a new insert to register, now users are not inserting

My register page was fully working but i needed a second table to update on my sql, but now my second table ( times ) creates something and not ( users and times ) upon sign up, here is what i added to my page that messed it up but also did what i needed, how would i make my sql update them both during sign up :smiley:

$sql = "INSERT INTO times ( Username ) VALUES ( $username )";

<?php
// Include config file
require_once "config.php";

 
// Define variables and initialize with empty values
$username = $password = $confirm_password = "";
$username_err = $password_err = $confirm_password_err = "";
 
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
 
    // Validate username
    if(empty(trim($_POST["username"]))){
        $username_err = "Please enter a username.";
    } else{
        // Prepare a select statement
        $sql = "SELECT id FROM users WHERE username = ?";
        
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "s", $param_username);
        

            // Set parameters
            $param_username = trim($_POST["username"]);
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                /* store result */
                mysqli_stmt_store_result($stmt);
                
                if(mysqli_stmt_num_rows($stmt) == 1){
                    $username_err = "This username is already taken.";
                } else{
                    $username = trim($_POST["username"]);
                }
            } else{
                echo "Oops! Something went wrong. Please try again later.";
            }

            // Close statement
            mysqli_stmt_close($stmt);
        }
    }
    
    // Validate password
    if(empty(trim($_POST["password"]))){
        $password_err = "Please enter a password.";     
    } elseif(strlen(trim($_POST["password"])) < 6){
        $password_err = "Password must have atleast 6 characters.";
    } else{
        $password = trim($_POST["password"]);
    }
    
    // Validate confirm password
    if(empty(trim($_POST["confirm_password"]))){
        $confirm_password_err = "Please confirm password.";     
    } else{
        $confirm_password = trim($_POST["confirm_password"]);
        if(empty($password_err) && ($password != $confirm_password)){
            $confirm_password_err = "Password did not match.";
        }
    }
    
     // Check input errors before inserting in database
     if(empty($username_err) && empty($password_err) && empty($confirm_password_err)){
    

     $otime=time();

        // Prepare an insert statement
        $sql = "INSERT INTO users (username, password) VALUES (?, ?)";
        
        
      $sql = "INSERT INTO times ( Username ) VALUES ( $username )";



    
         
        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "ss", $param_username, $param_password);
            
            // Set parameters
            $param_username = $username;
            $param_password = $password;

             
            
            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                // Redirect to login page
                header("location: login.php");
            } else{
                echo "Something went wrong. Please try again later.";
            }

            // Close statement
            mysqli_stmt_close($stmt);
        }
    }
    
    // Close connection
    mysqli_close($link);
}
?>
 
<!DOCTYPE html>
<html lang="en">

To post code, use bbcode [code][/code] tags around your code. I have made this change to your post above.

1 Like

The code you have is mostly from w3schools, is filled with repetitive and unnecessary things, and is missing useful error handling. It’s also using prepared queries with the mysqli extension, which has their own problems (I believe it was recommend in your last thread to use the better and much simpler PDO extension.) If I/someone has time, we will post an example of form processing code that is simple, easy to add to, and will eliminate over half of that (database) logic.

There are some other things the code is doing that are problems, mainly -

  1. Don’t try to SELECT data to find if it already exists. There’s a race/timing condition where multiple concurrent instances of your script can all find that the data doesn’t exist and will try to INSERT it. This will result in duplicate values. Instead, define the username column as a unique index in your database table, just attempt to INSERT the data, then detect if the query produced a duplicate index error number.
  2. Because you are not actually fetching the data from the SELECT query, you will get an ‘out of sync’ or similar error when you try to execute another query. This immediate problem will go away when you do item #1 on this list, but in general, if you are executing a SELECT query, it’s because you want to fetch and use the data from that query.
  3. Do you have php’s error_reporting set to E_ALL and display_errors set to ON, in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects? Some of the problems I see will be producing php errors and you also need php’s error related settings in a known state for the next item in this list.
  4. You ALWAYS need error handling for any statement that can fail. You currently don’t have error handling for all the database statements and you don’t have error handling that will tell you (display/log) why a statement has failed. For database statements, the easiest way of having error handling, WITHOUT adding logic at each statement that can fail (connection, query, prepare, and execute), is to use exceptions for errors and in most cases let php catch and handle the exception, where php will use its error related settings (see item #3 in this list) to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) You would then remove all the existing error handling logic, simplifying the code. The exception to this rule is when inserting/updating duplicate or out of range user submitted data. In this case, your code should catch the exception, detect if the error number is for something that your code is designed to handle, and set up a message telling the user what was wrong with the data that they submitted. For all other error numbers, just re-throw the exception and let php handle it.
  5. Every header() redirect needs an exit; statement after it to STOP code execution.

I’m not sure what the times database table is for, but -

  1. If it is going to hold data related to the users, you should use the user’s id (auto-increment primary index from the users table), not the username. This will use the least amount of data storage and result in the fastest queries.
  2. You should only insert data that exists. Inserting a row when the user registers, that you are probably going to UPDATE a column in later, is a waste of resources (a user may never actually login to your system after they register.) There’s actually a INSERT … ON DUPLICATE KEY UPDATE … query that will let you insert a new row or update an existing row that you can use when you need to do something with the times table.
1 Like

Thats true i was told about pdo in my last question, i assumed this was pdo though, i scrapped my whole script i was givin and found an up to date register page using google to learn from, i thought i found a pdo type.

The code i put in the question has abit of missing script because at first i didn’t know the bbcode [code*] so i tried to remove as much as i could and just left the part i thought was important to the question being asked :smiley:

i understand, hard to explain but i will try to explain, basically, the website will be a text based mafia game, you have 9 ways to lvl up on the game, each time you click an option you must wait before clicking it again, first rank up option ( crime ) = wait 15 seconds before clicking again / 2nd = 30 secs= 3rd =3 mins and so on, each time you click you get a rank+%, if the user does not enter my times table during sign up, they can go to the crimes page and rank up continuously without needing to wait for the timers

Thank you for your reply also, i shall do some research on error reporting also :smiley:

The following is an example registration script that makes use of the points made above, plus a few that I didn’t take the time to write out -

<?php
// example registration script with a single hard-coded unique column - a duplicate error will be in that column

session_start();

// detect if the current visitor is already logged in, meaning they are already registered
if(isset($_SESSION['user_id']))
{
	header('location:index.php'); // go elsewhere - main page
	exit;
}

require 'pdo_connection.php'; // put database connection code in an external .php file and require it when needed
// note: the connection code - sets the character set for the connection, sets the error mode to exceptions, sets emulated prepared queries to false, and sets the default fetch mode to assoc

$errors = []; // an array to hold errors
$post = []; // an array to hold a trimmed working copy of the form data

// post method form processing
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
	// trim the submitted data
	$post = array_map('trim',$_POST); // if any of the form fields are arrays, use a recursive trim call-back function here instead of php's trim function

	// validate the submitted data

	if($post['username'] == '')
	{
		$errors['username'] = "Username is empty.";
	}

	if($post['password'] == '')
	{
		$errors['password'] = "Password is empty.";
	}
	else
	{	
		if(strlen($post["password"]) < 6)
		{
			$errors['password'] "Password must have at least 6 characters.";
		}
	}
	
	if($post['confirm_password'] == '')
	{
		$errors['confirm_password'] = "Confirm password is empty.";
	}
	
	// if no password errors, compare password/confirm password
	if(empty($errors['password']) && empty($errors['confirm_password']) && $post['password'] != $post['confirm_password'])
	{
		$errors['confirm'] = "Password and the Confirm Password don't match.";
	}
	
	// if no errors, use the submitted data
	if(empty($errors))
	{
		$sql = "INSERT INTO users (username, password) VALUES (?, ?)";
		$stmt = $pdo->prepare($sql);
		
		try { // a 'local' try/catch to handle a specific error type 
			$stmt->execute([
				$post['username'],
				password_hash($post['password'], PASSWORD_DEFAULT)
				]);
				
		} catch (PDOException $e) {
			if($e->errorInfo[1] == 1062) // duplicate key error number
			{
				$errors['username'] = "Username is already in use.";
			} else { 
				throw $e; // re-throw the pdoexception if not handled by this logic 
			} 
		}
	}
	
	// if no errors, success
	if(empty($errors))
	{
		// redirect to the exact same url of this page to cause a get request - PRG Post, Redirect, Get.
		header("Refresh:0");
		exit;
	}
}



// at the point of (re)displaying the form, use the data in $errors to display any error messages and in $post to repopulate the form fields (you may not desire to populate password fields)
// any 'dynamic' values should have htmlentities() applied when they are being output on a web page to help prevent cross site scripting
?>
you would start the html document here...

<?php
// display any errors
if(!empty($errors))
{
	echo implode('<br>',$errors);
}

// output the form
?>
<form method='post'>
Username: <input type='text' name='username' value='<?php echo htmlentities($post['username'] ?? '',ENT_QUOTES); ?>'><br>
Password: <input type='text' name='password' value='<?php echo htmlentities($post['password'] ?? '',ENT_QUOTES); ?>'><br>
Confirm password: <input type='text' name='confirm_password' value='<?php echo htmlentities($post['confirm_password'] ?? '',ENT_QUOTES); ?>'><br>
<input type='submit'>
</form>

Here is an example of the PDO connection code -

<?php

$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8'; // db character encoding. set to match your database table's character set

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // set default fetch mode to assoc
			];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

If you have a need to insert a row into a second table, you would get the last insert id from the first insert query (the PDO based code would be $user_id = $pdo->lastInsertId(); ) and use it as an input value when you execute the second insert query.

1 Like

All the php database statements must be from the same extension. To use the PDO extension, the connection code must create an instance of the PDO class.

Beyond using the mysqli extension, your posted connection code has two issues. The first is fairly picayune. By using defined constants, you can only make one database connection at a time (it would require you to create a new set of defined constant names and make corresponding edits to the connection statement, to make more than one connection.) If you use variables instead, you can assign new values to the variables and make additional connection(s), without editing the actual connection statement. The second is more serious. By unconditionally outputting connection errors on the web page, this will give hackers useful information when they intentionally trigger a connection error, by causing too many connections or consuming all of the database server’s resources so that it faults and shuts down. A connection error contains the database hostname/ip address, the database username, if you are using a password or not (you should always use one), and web server path information. You don’t want to give this information to a hacker. By using exceptions for database statement errors, as has been suggested, you can switch from displaying all database errors (during development) to logging all database errors (on a live server) simply by changing php’s display_errors and log_errors settings.

1 Like

I had a read over the 2-3 times until i understood what you was saying ( as i’m rather new ) the only bits i didn’t quite get was the 3 below, if you have time, could you explain this in another way for me ? :smiley:

All the php database statements must be from the same extension. To use the PDO extension, the connection code must create an instance of the PDO class.

By unconditionally outputting connection errors on the web page, this will give hackers useful information

logging all database errors (on a live server) simply by changing php’s display_errors and log_errors settings.

Based on the bold in the first statement, I’ll guess you are asking about using an existing Object Oriented Programming (OOP) Class. You don’t need to fully understand OOP in order to use an existing class. You just need to know some of the terminology and the syntax to use.

The class definition is a prototype for the class. It defines the properties (variables) and methods (functions) of the class. When you create a new instance of a class, using $obj = new class_name(); syntax, the $obj (object) variable is assigned that instance. You can then reference (call) the properties and methods of that instance of that class.

The PDO extension has three classes - PDO, PDOStatement, and PDOException. These classes mostly have methods (functions.) The PDO extension has very few properties (variables.) All the fine details about what any inputs, processing, or output/return values are for methods and any properties can be found in the php.net documentation.

In the code I have posted, the following is creating a new instance of the PDO class and assigning it to the variable $pdo -

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

You can then reference (call) the PDO class methods (functions.) The only PDO method I have used is ->prepare(). This method creates and returns a PDOStatement object, that has its own properties and methods. The following is calling the ->prepare() method and assigning the returned PDOStatement object to the variable $stmt -

$stmt = $pdo->prepare($sql);

The only PDOStatement method I have used in this code is ->execute(). The following is calling the ->execute() method -

$stmt->execute([
	$post['username'],
	password_hash($post['password'], PASSWORD_DEFAULT)
	]);

The only other PDO extension statement being used in this code is the PDOException errorInfo property (variable.) In the exception catch block, the $e variable will be assigned a PDOException object, with its own properties and methods. The errorInfo property, which is an array, contains the query error number in the [1] element of the array.

See the last paragraph in the previous post for the syntax to use to get the last insert id, which is calling the ->lastInsertId() method of the PDO class.

I’ll address the other points, time permitting.

The code you have, using die("ERROR: Could not connect. " . mysqli_connect_error()); for the connection error handling unconditionally (always) outputs the raw connection error information onto the web page. All a hacker needs to do is cause a connection error, by making a bunch of requests to pages that use the database, and they can see all the information contained in the raw connection error message.

When you are learning, developing, and debugging code/query(ies), you want to see all php and database statement errors, so that you can find and fix problems quickly. When you move your code to a live/public web server, you don’t want php and database statement errors to be output on the web page at all. A legitimate visitor cannot do anything about any of these type of errors and seeing this error information will just encourage a hacker to do more of the same things that managed to cause the error in the first place. You want to log these errors and only tell the visitor that the corresponding web page is not working at the current time. If you do nothing else in this case, you will ‘automatically’ get a http 500 error page. If you want to do more, you can write you own custom exception handler to replace php’s and/or you can produce a custom http error page.

If you use exceptions for database statement errors and let php catch and handle the exceptions, database statement errors will get displayed or logged the same as php errors, in the form of an uncaught exception that contains the file, line number, and error information.

Php’s error_reporting setting should ALWAYS be set to E_ALL (you want to report all php errors, which will now include database statement errors.)

When you are learning, developing, and debugging code/query(ies), you would set the display_errors setting to ON. When running your code on a live/public web server, you would set the display_errors setting to OFF and set the log_errors setting to ON.

1 Like

Thank you for this info phdr 8) very useful information. :smiley:

You know when you said there is no point adding data to a second table because the user may never log in?

ive created a register page that inserts into the table users only but once they do actually log in, is there a way i can get a user to be added to a second table by clicking on a page? or even clicking a button on that page?

When you have a user authentication system (register/login) and a user permission system (roles), you would query on each page request to get the user’s information (display name, game level, rank, …) and user permissions and use that information on the page. As part of this user page request logic, you can do anything else you want that’s related to that page, such as inserting/updating data in a database table.

For what you have described, when a user (first) visits the game page, if there are no game related record(s) for that user, you would insert any new record(s) that are needed for that game. I previously mentioned the INSERT … ON DUPLICATE KEY UPDATE … query, for inserting a new row or updating an existing row of data. Another useful query for what you are trying to do would be an INSERT IGNORE … query. This will insert a new row if data doesn’t exist or do nothing if it already exists. After the INSERT query, you can check if a new row was inserted to perform other actions, such as display a “welcome to the game” message.

i just done my research on the ( ignore and duplicate ) inserts and they will come in very handy i didn’t know about that :slight_smile: but what i mean is on my sql i have a table ( users ) with row 1 = id , row 2 = username, row 3 = password … )) - and then a second table called ( times ) with row 1 = username , row 2= crime 1, row 3=crime3 … ))

i thought duplicate ment it would for example on sign up create a username and duplicate that chosen user name into two tables but it means something else and works on the same table only is that right? instead of doing something like this below

( ( example code )

$sql = "INSERT INTO users ( Username ) VALUES ( $username ) ( and so on... ) "; ( $sql = "INSERT INTO times ( $id ) VALUES ( $id )"; )

Sorry i’m too nooby to explain what im trying to say better but did that make sense :D?

i tried this

$sql  = "INSERT INTO users ( username , password , email , ip , referedby , pin ) VALUES ( '$username', '$password', '$email', '$ip', '$ref', '$randompin129')";

	$query =mysqli_query($db, $sql);

    if(query){

        $sql2 = "INSERT INTO times ( Username ) VALUES ( $username )";
        $result=mysqli_query($db, $sql2);
        echo 'worked';
        
    }else{

        echo 'fail';

And it worked, but when i made a second account it stopped working and the same on the 3rd and so on, this does not make sense at all :laughing: Capital U on username because thats how it is in the times table,

Sponsor our Newsletter | Privacy Policy | Terms of Service