Some time ago I did a course at TAFE the course was Diploma In Web Development. We had to create a website (fictitious or real) which could be eCommerce/ Business/ Blog… but it had to have both Front End and Backend Scripts. I used PHP MySQL with mine as did most of the other students. In the end it all worked fine, and I received a Destination in my Grades. Unfortunately that was a few years back, and I have noted that there are errors across just about all of the Code:
Connect to Database (Fixed)
Login… Errors,
Search… Errors,
Create Account… Errors.
I found some pointers that have fixed some errors, including Connecting to the Database, that works now, but the Codes for the Others I can find no solution to the errors.
Search:
I use a “text Box” to enter the item I am searching for, then click a button… nothing unusual there - Pretty standard, But I click the Button… and error…:
Error Reads:
Fatal error: Uncaught ArgumentCountError: mysqli_query() expects at least 2 arguments, 1 given in C:\xampp\htdocs\ComputerSuperStore\search.php:39 Stack trace: #0 C:\xampp\htdocs\ComputerSuperStore\search.php(39): mysqli_query(‘select * from p…’) #1 {main} thrown in C:\xampp\htdocs\ComputerSuperStore\search.php on line 39
Code:
<?php // Queries All Items using Brand or Looking for specific items
$query = "select * from products where Brand like \"%$trimmed%\" OR Item like \"%$trimmed%\" OR ItemType like \"%$trimmed%\" OR Category like \"%$trimmed%\" order by Brand";
**Line 39** $numresults=mysqli_query($query); **Line 39**
$numrows=mysqli_num_rows($numresults);
if($numrows==0)
{
echo"<h2>Results</h2>";
echo"<p><h3>Sorry, your search for</h3><h2> " ". $trimmed . " " </h2><h3>Returned zero results...</h3></p>";
}
if(empty($s))
{
$s=0;
}
$query .= " limit $s,$limit";
$result = mysqli_query($query) or die("Couldn't execute query");
//echo"Results";
$count = 1 + $s;
while ($row=mysqli_fetch_assoc($result)) {
include ('tablelogincheck.php');
}
?>
Hopefully you can see the code and I highlighted the Line where it says there is an error.
Again, bear in mind this code worked when it was originally created.
I get similar errors on the other scripts (Login, Create Account)
I don’t use mysqli much (I prefer PDO) but has it ever been the case that the syntax
$result = mysqli_query($query);
would work without error? I know that the old mysql call was formed that way:
$result = mysql_query($query);
but that was because there is only one database connection in the old mysql library. One of the changes to mysqli and PDO was that you could make multiple database connections, and so you have to specify the connection in the query, so that the library knows which one you are talking about. This is clear in the documentation for mysqli_query, isn’t it? https://www.php.net/manual/en/mysqli.query.php
This sort of problem is typical when someone wrote the code using the old mysql_ calls, found it no longer works because that library has been deprecated for more than a decade (since PHP 5.5.0), and was removed from the language completely in PHP7 in 2015, and then for quick fix, just went through and did a global change from mysql_ to mysqli_. Could that be the case here? Or are you saying that no code at all has changed from the point where it used to work, to the point where it no longer works? If that’s the case, what did change - the server environment, for example?
Yes that was the first possibility I looked at and I think if you have a look at my code there you will find I am actually using mysqli, instead of mysql. I am fairly certain I have changed all instances of mysql to mysqli.
You have, but you don’t seem to have checked the syntax for mysqli_query, which is why you are getting the error. I linked to the documentation in my first post. You can’t just do a global replace and add an “i” to all the function names.
Exactly this.
The syntax is different between mysql ans mysqli. The mysqli_query() function requires at least two paramters, the first being the connection, the second being the query. The third result mode is optional.
Though if you are going through the process of updating your code to current PHP I would highly recommend skipping mysqli and moving on to PDO instead.
There’s a lot to say here. The thing about PHP errors is that it’s direct and straightforward. The error you’re receiving relates to what @SamA74 is trying to say. You can’t just blatantly global replace mysql_ to mysqli_. The error in itself tells you what’s going on with your code.
The next issue you’ll have is SQL injection. You should be using prepared statements. The thing I dislike about a lot of tutorials or web videos is that they don’t teach you the right way to do it. They just put together a hodgepodge of things and then go “I’ll distribute this to the masses who are just learning”. If the course was done the correct way, there wouldn’t be these unnecessary changes or refactors. Not that you won’t ever have refactors, just that it’ll be less the more you do it the correct way.
The next thing is, you’d want to separate your logic, HTML aka output from your general PHP logic e.g. grabbing data from the database. You want to do this to allow better maintainability. It makes it easier for developers to come and say “Ok, this is what this piece of code is doing.” When you mishmash HTML inside of JavaScript inside of PHP inside of JavaScript inside of HTML inside of PHP, it makes it extremely hard and cumbersome to try and debug what the heck is even going on.
I’d suggest going forward to think about how to make your like easier and not harder. Try to find ways to reduce redundancy or unnecessary things to make your life that much better. In all seriousness, we’re not going to be the ones maintaining your code. It’s going to be you.
I’ve learned that this is often an intentional act from an academic perspective.
I was helping my son with something similar a few years ago, and I showed him the better way to do something (explaining it thoroughly while I did it), and he got docked for it when he turned in the work.
The excuse he was given was that the learning is foundational and builds upon itself. They start with the bare-boned minimum and easier to understand than the “right” way to do so, which from a layman’s perspective, I can see.
Prepared statements are more secure, no arguments. But they can be harder to understand, and also harder to debug - a simple print of a SQL statement vs the debug info available when using the prepared statements.
Not saying it’s right or wrong, but it’s the theory. Problem comes in when people swoop in and watch one video and think they know all they need…nope.
Correct, but in fairness, there was no such thing with mysql. It’s possible with mysqli, but easier with PDO.
But certaily it should be a part of updating the code.
This would be the bare minimum to make it “work” but not recommended as it still leaves the old security holes open. I don’t recall all the differences between mysql and mysqli (it’s been a long time since I bothered with either) but there are probably other cases where simply adding an i won’t work.
If you are going to update, update to today not the oldest thing that will still work.
If we are going to pick at security:-
We should be escaping output too, assuming $trimmed is only trimmed input.
And:-
I wonder what hashing algorithm is being used here?
Not relating to the PHP issues, but you should not be putting h2 and h3 tags in a p tag, but then you should not be using h2 and h3 tags to make the text larger. That’s what styles are for.
Though I mean is it any different then learning say an uploading system or a login system? Majority of academics already teach complex systems like this. What I find interesting and ironic is the same institutions will have courses about security and having best practices around security while on the other hand writing a whole lot of security holes in these programming languages that they teach.
Yes because you’re talking about a different level of focus. An uploading or login is focused on functionality for the user - the presentation/application layer if you will. Dealing with prepared statements and such are dealing at a fundamental level as it’s in the db implementation layer.
It’s knd of the same way they still teach bubble sorting even though it’s not the most efficient way to handle sorting. It’s a fundamental approach and the thought is a student needs to understand the fundamentals before they move onto a newer, better way. Not saying it’s right, but there is some validity to it. I look at it like it’s no different than “learning” css or javascript solely by using a framework. It works, but you’re missing the fundamentals which are needed in case something unique is needed, or a framework stops being supported.
In my son’s case, they did eventually teach them the right way - I just did it first (I explained the fundamental way too, but showed him the better way)
But it’s not just in computing. My oldest once lost points on a math test when he put the exact (correct) answer down because the problem instructions said to estimate the answer. They wanted him to round his numbers and do simple math and he just did the math. I asked the teacher at that point if they wanted an engineer who would estimate the length a bridge or if it would be better to have one that could calculate the exact length of it. Got told it was the curriculum and that was it…
Ok so my error is caused by mysqli wanting 2 parameters… So, I took that part back to mysql_.
I get an error on the same line but a slightly differently worded error:
Fatal error: Uncaught Error: Call to undefined function mysql_query() in C:\xampp\htdocs\ComputerSuperStore\search.php:39 Stack trace: #0 {main} thrown in C:\xampp\htdocs\ComputerSuperStore\search.php on line 39
So I am guessing that the newer version of PHP does not recognise mysql_query()
So, I think I’ll just give in, nothing I have done works, and a lot of the information I have received is very vague. I get told to go spend more $$$$$$ and get tutorials to use PDO. I spent heaps of $$$$$ at Tafe for the Course years ago, the price has doubled since. I am a pensioner trying to make some headway but to no avail… I quit!!
Mysql doesn’t exist anymore, it was removed from PHP years ago.
Well just adding the other parameter would do it. Thougn as mentioned, to bring the code truly up to date and make it secure would require much more work than simply making it function without error.
Well hold on here. On these forums, we don’t technically always give people the answers because we want you to learn it, but I mean if there’s nothing left for your options, I can definitely help. I’m pretty well versed in both PDO and mysqli_*. Just that I’m not home right now to help out. It’ll be another 5 or so hours, but I can jump on and help out. I’ll need some of the beginning database schema. Don’t need the data.
Don’t be discouraged because people are telling you to switch to PDO. It’s just that it’s preferred for majority, but if you’re not comfortable with it, you can always just use mysqli_*. It’s the learning curve that needs to be addressed that’s all.
I can give you a “somewhat” starting point, but I don’t have everything off the top of my head right now.
—
So to start, I’d suggest switching to the OOP (object oriented programming) version so that because even if you switch to PDO, you’ll have to learn OOP regardless.
$mysqli = new mysqli('host name', 'username', 'password', 'database');
That’s how you create the database connection. Next step is to properly use prepared statements. I haven’t used mysqli in a hot minute so I’ll have to do some real quick testing to make sure I got the right operations, so I’ll get back to you on the next steps when I get back home.
Did you follow the link I put in my first answer to the documentation for mysqli_query() where the exact syntax is explained in some detail? That would be a good first step, and would probably solve most of your initial problems. Once they’re working, you can look at why the register and login pages have problems and come back if you need more assistance.
As @spaceshiptrooper said above, it’s not usual that someone will just edit your code or show exactly what you need to do, the aim is to send you to read and understand the documentation or example code. There is a tendency (and I am as guilty of it as anyone) of then going on to pick on stuff like concatenating variables into query strings because that’s not a good way to do it any more. But for now, I’m sure you can get this working with a bit more time.
Converting old mysql based code to use prepared queries with the PDO extension is fairly straightforward, assuming you understand what the current code is doing.
After you make the connection using the PDO extension -
Remove, and keep for later, any php variables that are inside the sql query statement. Any wild-card characters in a LIKE comparison are kept with the php variable.
Remove any quotes and {} around the value and any concatenation dots/extra quotes that were used to get the php variable into the sql query statement.
Put a simple ? prepared query place-holder into the sql query statement for each value.
Call the PDO prepare method for the sql query statement.
Call the PDOStatement execute method with an array of the variables you removed in step #1.
For a query that returns a result set, fetch the data from the query into an appropriately named php variable. See the fetch() method when fetching a single row of data. The fetchAll() method when fetching all the rows of data at once. And occasionally the fetchColum() method when fetching a single column from a single row of data. Forget about any num rows function. Just fetch then test if/how many rows of data there are.
Some additional points for the posted code -
If you separate the database specific code, that knowns how to query for and fetch the data, from the presentation code, that knows how to produce the output from the data, it is easier to write, test, debug, and maintain your application code.
Don’t select all your data just to determine if there is matching data for pagination. Use a SELECT COUNT(*) … query instead for actual pagination. Since it appears that you are supplying the offset value directly, instead of a page number, you would add the LIMIT … term to the query, then prepare and execute it, and report if it didn’t match any data.
Don’t use or die() for error handling. use exceptions instead (which is the default setting now in php8+), and only catch and handle database exceptions in your code for user recoverable errors, such as when inserting/updating duplicate user submitted data. For all other query errors and all other types of queries, simply do nothing in your code and let php catch and handle any database exception.
Use require for things your code must have.
Require/include are not functions. Simply leave out the () around the filename.
Typical PDO connection code -
$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions (this is the default now in php8+
PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries whenever possible
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // set default fetch mode to assoc so that you don't need to specify it in each fetch statement
];
$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);
For the posted code -
// the database specific code
if(empty($s))
{
$s=0;
}
$sql = "SELECT *
FROM products
WHERE Brand LIKE ? OR Item LIKE ? OR ItemType LIKE ? OR Category LIKE ?
ORDER BY Brand
LIMIT ?,?";
$stmt = $pdo->prepare($sql);
$stmt->execute([ "%$trimmed%", "%$trimmed%", "%$trimmed%", "%$trimmed%", $s, $limit ]);
$product_data = $stmt->fetchAll();
// at the point of producing the output
if(!product_data)
{
echo"<h2>Results</h2>";
echo"<p><h3>Sorry, your search for</h3><h2> " $trimmed " </h2><h3>Returned zero results...</h3></p>";
}
$count = 1 + $s;
// loop over the data
foreach($product_data as $row)
{
require 'tablelogincheck.php';
}
So not to sound mean or anything, but; I’m going to tear your code apart, explain to you why you shouldn’t do this, then give you a better solution and give you a working piece of code for that code as an alternative. Basically just a dissection of what you posted here.
The first thing you don’t want to do is doing a “select all”. Typically you just only grab what you need, you don’t need all of the columns in the database. For performance reasons, if you’re only looking for 2 columns out of say 20, does it make sense to pull all 20 columns into your search? Not really. You should start to think about reducing unnecessary things you don’t really need. When you start to become more self aware as a developer, you start to think of ways to reduce what you typically don’t need and save yourself time.
So for this solution, I would say something like
SELECT item, category, company, brand FROM products .....
2 things here
You really shouldn’t be outputting your HTML here. Like I said before, try to separate your logic and your view to make it more easier to maintain.
Please escape anything you output as anyone can inject malicious things into whatever you allow the user to put in. For instance, if $trimmed is coming from a variable that is being grabbed from the URL such as $_GET['search_item'], you will want to escape this so that someone can’t just do <script>alert('You have now been hacked!')</script>.
Now even though I said you should separate your logic from your view, I’m going to use your example and give the absolute “solution” you want without you having to mess around with things too much. I strongly suggest you separate it though.
Why are we re-running the query again? Why not just do your limits at the beginning based on criteria that meet your requirements?
I honestly don’t know what this is used for, but if you’re not looping through a loop, does it makes total sense to have a count? Your loop is right below this count so should it actually be in the loop itself or how are you incrementing this variable?
So here is the final “working” mysqli_ solution. The -> is the OOP way. This typically means you’re trying to access a method (function in the procedural way) or a property. Not sure if you know the basics of OOP, but I strongly suggest you learn that as well. OOP is typically taught in majority of popular programming languages. So if you know OOP in PHP, you’ll know OOP in Java, C#, C++, etc because the fundamentals and principles are universal in all languages that use OOP.
<?php
// Assuming $trimmed, $limit, and $s are all passed way up here or before hand.
$mysqli = new mysqli('localhost', 'root', 'root', 'test');
if(empty($s)) {
$s = 0;
}
$trimmed = '%' . $trimmed . '%';
$sql = 'SELECT item, category, brand FROM products WHERE brand LIKE ? OR item LIKE ? OR ItemType LIKE ? OR category LIKE ? ORDER BY brand LIMIT ?, ?';
$prepare = $mysqli->prepare($sql);
$prepare->bind_param('ssssii', $trimmed, $trimmed, $trimmed, $trimmed, $s, $limit);
$prepare->execute();
$prepare->store_result();
if($prepare->num_rows) {
$count = 1 + $s; // Not sure why this is needed, but will add it here
$prepare->bind_result($item, $category, $brand);
while($prepare->fetch()) {
// print 'Item: ' . $item . '<br>';
// print 'Category: ' . $category . '<br>';
// print 'Brand: ' . $brand . '<br><br>';
require_once 'tablelogincheck.php';
}
} else {
// Please escape the $trimmed variable before even outputting.
$trimmed = escape($trimmed);
$trimmed = ltrim($trimmed, '%'); // You'll want to trim the beginning percent sign because you don't want to display this to the user. That's not what they inputted.
$trimmed = rtrim($trimmed, '%'); // You'll want to trim the ending percent sign because you don't want to display this to the user. That's not what they inputted.
print '<h2>Results</h2><p><h3>Sorry, your search for</h3><h2> " ' . $trimmed . ' " </h2><h3>Returned zero results...</h3></p>';
}
function escape(string $str): string {
return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}
Please change the columns to your liking because I was just going based off what I saw in the WHERE clause.
So what does each piece of the code I proposed is doing?
This piece is the database connection, I suggest changing it to match your database credentials. Please read post #17 for what each argument is.
$mysqli = new mysqli('localhost', 'root', 'root', 'test');
What this piece of the code does is that we’re attaching an end and beginning % to the “original” $trimmed variable. The reason why you want to do it here rather than inside of the query is because when you use prepared statements, everything is parametrized so you’ll want to add in your user inputs before hand. This means that when you use prepared statements, you allow PHP (or the language) to escape user inputs rather than you attempting to allow arbitrary values to be used.
$trimmed = '%' . $trimmed . '%';
For both mysqli_* and PDO prepared statements, you can use the ? placeholder. This is how you use prepared statements. In PDO, you can also use what’s called “named parameters” which allows you to use human readable placeholders. So something like :trimmed or even :username in your SQL query will be treated as a “named parameter”.
$sql = 'SELECT item, category, brand FROM products WHERE brand LIKE ? OR item LIKE ? OR ItemType LIKE ? OR category LIKE ? ORDER BY brand LIMIT ?, ?';
Using prepared statements is just as easy as that. It literally is. It’s even it the name ->prepare. This statement is used for both mysqli_* and PDO.
$prepare = $mysqli->prepare($sql);
Here is where it gets a little tricky. So you know where we put all of those ? placeholders in the query? You have to count and remember how many you put in the query. That’s the amount of placeholders you have to bind. This is the same for both mysqli_* and PDO. Where they differ is for mysqli_*, you have to use ->bind_param and also specify the datatype you’re trying to bind.
So you see all those “s”? That means “string”. You see all of those “i”? That means “integer” or “number” if you will.
“How can you tell if they’re strings or integers?” You may ask. Simple. You can determine what they are based on what you’re trying to pass in. Are you passing in a full set of words? Well that’s a string then. Are you just passing in numbers say for instance 1 or maybe 10 or maybe 1,000? Well those are integers. What about passing in letters and numbers (or what we call “alphanumerics”)? Then that’s a string because you have both letters and numbers and it makes absolutely no sense to set the data type as an integer if there’s also letters that come with it.
You will also need to pass in the exact variables as parameters to bind them. So first segment of the method (or functions in dummy terms) is the entire set of datatypes combined. The rest of the arguments are the exact variables in order in which you are trying to bind. So to determine what needs to go first, you should look at your query and place those variables accordingly as your query expects them.
A good trick is to count all of the ? placeholders in your query. Next count the total amount of datatypes you have put into the 1st argument of the method. Lastly count the total amount of variables you have tried to bind. They should all total the same exact amount. If they don’t, you will receive an error regarding unmatched parameters. In this example, there are a total of 6 ?, a total of 6 combined datatypes, and a total of 6 variables we want to bind. See how they are all 6? That’s what you want. If you have 12 ?, you’ll want to have 12 total combined datatypes and 12 variables you are trying to bind.
Next it’s super simple. We execute our query since we have now bound our placeholders with our variables.
$prepare->execute();
Then we’ll want to store those results.
$prepare->store_result();
Then we check to see if any results are returned. If there aren’t any, PHP will return 0 which means it returned nothing.
if($prepare->num_rows) {
Next we bind our results to a set of variables we define ourselves. This allows us to give meaningful variables to each column we’re selecting. Remember, the order in which you define the variables matter. That’s the order you have in your query.
$prepare->bind_result($item, $category, $brand);
Now we loop through those results. In the comments I have put in how you would output those values. Remember, each row will be returned if you have more than 1 result being returned.
while($prepare->fetch()) {
In this section, we’re calling a function called escape() which is a function I provided to you to escape HTML elements so that you aren’t allowing users to just randomly put in malicious things into any of your user input endpoints. You also want to trim both the left and right side of the variable as we had to manually add in the % sign for our LIKE clause. You typically wouldn’t want to output that as this isn’t what the user submitted. You’ll want to only output what the user submitted. You wouldn’t want to output something that they didn’t put in such as an error or extra information they didn’t provide. It makes the users suspicious or even malicious at that point if they found that you have a security hole in your system. You want to minimize that as much as possible.
$trimmed = escape($trimmed);
$trimmed = ltrim($trimmed, '%'); // You'll want to trim the beginning percent sign because you don't want to display this to the user. That's not what they inputted.
$trimmed = rtrim($trimmed, '%'); // You'll want to trim the ending percent sign because you don't want to display this to the user. That's not what they inputted.
This function simply just escapes all HTML output so that you aren’t being maliciously attacked.
function escape(string $str): string {
return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}
Ok I’ll try to explain as best I can the “Why’s”
Firstly, this is a fictitious web site - it doesn’t actually exist in the real world - That said I am hoping to create a web site for my DJ Karaoke Business where Punters can search for a specific song, or an Artist etc.
Now the site where the errors are is a Fictitious Computer Store. Customers can create an account, Look for Products, to buy a product they must be logged in.
Now, In the Search area it is just a text box and a button. And when they search, I want to display all details about the product, as well as an image (or images for multiple Products) so Select * From Product (table) is necessary in this case - now the search option is available for all users whether they are logged in or not. So we need to have the Brand, the Size, The Image, The Price, ant The Details displayed on the screen once the search is completed. Hence why there is only one “Parameter” Let’s say thew search is for Monitors. You Type in Monitors in the Text Field, hit Search, then all Monitors should be displayed in the result screen (Not just Text Listing) but all the details of each product/ Monitor Will be displayed. Now all of this worked fine without any errors at all when I first built the site in 2012 when I did the course, BUT 2.5 years later, Nothing worked after I installed the Later Version Of PHP & MySQL. So I have been trying to solve these issues since about Mid 2015, some of the problems I was able to fix. The first one was connecting to the Database. Now that is done in a file of its own (in this case header2.php) Now each page has at the very top
Then there is all the HTML code for the page
Now From memory we used get ('header2.php");
But that was the very first error the new version did not recognise “get” when it come to adding pages using php, so I found the new term was “require” - that was fixed
Now the first lines of header2.php has:
<?php
session_start();
ob_start();
?>
Then the HTML
Originally the php further down read
<?php
$con = mysqli_connect("localhost", "root", ""); //create connection to the database""
if(!$con){
die('Sorry databse is unavailable: ' . mysql_error());
}
mysqli_select_db($con, "computersuperstore");
require ('topbar.php');
?>
Before the $con term/ script… whatever.
I did but it has made no difference
That being said It does connect to the database - if not - the page would stop loading
Let’s say there is a hell of a lot of Code even in just the Header.
Way too much to put here.
All that said, my point is, is that EVERYTHING worked before I Updated PHP and MySQL
I’m a bit old school - If it ain’t broke, don’t fix it - I have no Idea why after updating, nothing works properly. The Home Page works fine - No Errors - but Sign in, Add Account, and Search is not working.
So, I can’t even tell if selecting an item and putting into the cart will work (After Sign In Gets Fixed)
I am stressing as to just how much “wont” work anymore.
As for why we used all the coding we did - well that was as per instruction - so there was not much choice as “I” was learning from scratch - I had front end coding pretty much set, but the backend coding was all new to me, so I could only be guided by what the instructor told me. Perhaps, back then (In 2012) it was ok to use the coding practices we used… I was an inexperienced student when it came to Backend coding… I do get times change, but maybe some things should be backward compatible…
I am not sure how to get the coding to you but like I said there is heaps of it
header2.php has 64 Lines php and html
search.php has 64 lines php and html
index.php has 44
And so On
So tell me what you need from me to make sure earlier code wont clash with what you are giving me
There are 75 Separate php item that the site uses (not all at the same time) but if and when they are required, there is an include parameter.
As I said earlier I have spent years trying to work out the problems, I get told to do “this” but later I get told that that was wrong… hence my outburst I Quit…