Code should be simple, general-purpose/reusable, secure, provide a good user experience, and either work or tell you (display/log) why it doesn’t work.
Your sql query statement has an incorrect mix of quotes and concatenation dots that has produced a query that will execute without error, but won’t match any values. Best, current programming practice for sql queries are to use a prepared query when supplying data values to the query, with a simple ? place-holder in it for each value. While a prepared query adds one extra php statement per query (provided you use the much simpler PDO extension), it simplifies your php code (eliminates the _escape_string() calls), and it simplifies the sql query (all the extra syntax - quotes, concatenation dots, and any {} are eliminated.)
Here’s a list of what your code should/not be doing -
- Use ‘require’ for things that your code must have for it to work.
- Detect if a post method form was submitted, instead of detecting if the submit button is set (there are cases where the submit button won’t be set when a form has been submitted.)
- Trim the input data so that you can detect if all white-space characters were entered. You can do this will a one total php statement (if I/others have time, we will post an example.)
- Do NOT copy variables to other variables without a good reason. This is just a waste of typing time. If you had 30 form fields, does it sound like you should spend time writing out code to create 30 different variables? Your answer to this should be no.
- Validate all input data, separately, putting the error messages into an array, with the form field name as the array index. This array is also an error flag. If the array is empty, there are no errors. If the array is not empty, there are errors. You can display the contents of this array at the appropriate place in your html document.
- After the end of the validation logic, if there are no errors, use the submitted data.
- For logging in, if the password is not empty, that’s all you need to test, because all you care about is if the username/password matches a user in the database table…Any length tests belong only in the registration code.
- Don’t store plain-text passwords. Use php’s password_hash() (in the registration code) and password_verify() (in the login code.) As a result of this, your sql query will only try to match the username. You will have a line of php code verifying the password.
- Use the much simpler and more consistent PDO extension. The PDO extension takes less lines of code and allows you to treat the result from both a prepared and non-prepared query the same.
- Use exceptions for database statement errors and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual database error information (database errors will ‘automatically’ get displayed or logged the same as php errors.)
- As already mentioned, use a prepared query.
- Don’t use a loop to retrieve the result from a query that will match at most one row.
- When a user successfully logs in, you would store the user’s id (auto-increment primary index from the members table) in a session variable to ‘remember’ who the current logged in user is. You would then test/use this session variable at any point you need to know if/who the logged in user is.
- If the user is already logged in, you would not display the login form, nor process the form submission.
- The form processing code should be before the start of your html document.
- Upon successful completion of the form processing code, with no errors, you should redirect to the exact same URL of the form processing/form page. This will cause a get request for that page. If you want to display a success message, store it in a session variable, then clear that variable after you have displayed the message.
- Php will destroy all resources created on a page, when the script ends. You do not need to close the database connection. Php will do this for you.
While this looks like a lot of work, some of the items will actually simplify the code/query, while others are adding features that your code is missing now.
