Data submitted to your site ($_GET, $_POST, $_COOKIE, $_FILES, and some $_SERVER variables) can come from anywhere, not just your links/forms, can be set to anything, and cannot be trusted. You must securely use all this data in whatever context it is being used in.
For data being used in an sql query context, the simplest way of preventing any sql special characters in a value from being able to break the sql query syntax, which is how sql injection is accomplished, is to use a prepared query.
If you use simple positional ? place-holders and implicit binding, by supplying an array of the input values to the ->execute([…]), you can greatly simplify all the database specific code needed for a prepared query.
Here’s a list of point for the posted code -
- The global keyword only has meaning inside of a function, and even then it should be avoided. If this code isn’t inside a function the global line does nothing and should be removed. If this code is inside a function, you should supply all inputs to a function as call-time parameters.
- The connection variable should be named as to the type of connection, e.g. $pdo, so that anyone reading your code will know what it is using.
- Don’t copy variables to other variables for nothing. This is just a waste of your typing time. Just use the original variables that data is in. If you modify or change the meaning of a value, you should use a new variable.
- You should trim (mainly so that you can detect they are all white-space characters), then validate all inputs before using them.
- You should use a single LEFT JOIN query for this.
- There’s no guarantee that the submitted $_GET[‘p_cat’] value will match a row of data in the product_categories table. After you run the query, if there are no matching rows, it means that there was no matching product category.
- You should list out the columns you are SELECTing. This helps make your code self-documenting and will result in only selecting the columns you are using in your code.
- Just about every query that can match more than one row needs an ORDER BY term in it.
- You should use general variable names, e.g. what they are being used for, an $sql query statement, a pdo $stmt object. After you put a main comment in your code describing what it is currently tying to do, all the code up to the next main comment is for the current task. Only the final variable holding task specific data should be uniquely named.
- RowCount() is not guaranteed to work with SELECT queries. You should instead just fetch the data and test if there is any data.
- You should NOT repeat part of or the whole table name in each column name. This is just more work for you keeping track of all the verbose naming.
- Don’t use name-numbered column names. If you do have multiple same meaning data, you would store the multiple values, in a related table, one row per value.
- Any link you build should include any existing get parameters, such as the $_GET[‘p_cat’] value. The general purpose way of doing this is to get of copy of any existing $_GET data, set/modify the element the current link is for, then use http_build_query() to build the query string part of the link.
- The closing } for the product data’s else {…} conditional branch belongs after the end of the loop, i.e. the code looping over the product data and displaying it should be inside that else {…} branch, not after it.
Here’s example code, with everything but the join query (didn’t feel like spending more time on this), showing these points -
// condition and trim the input
$p_cat = trim($_GET['p_cat']??'');
// validate the input
if($p_cat ==='')
{
echo "A product category must be selected.";
}
else
{
$sql = "SELECT title, `desc` FROM product_categories WHERE id=?";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $p_cat ]);
$cat_data = $stmt->fetch();
$sql = "SELECT id, title, price, img
FROM products WHERE cat_id=? ORDER BY title";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $p_cat ]);
$product_data = $stmt->fetchAll();
if(!$cat_data)
{
echo "<div class='box'>
<h1>Product Category doesn't exist</h1>
</div>";
}
else
{
if(!$product_data)
{
echo "<div class='box'>
<h1>No Product Found In This Product Category</h1>
</div>";
} else {
echo "<div class='box'>
<h1>{$cat_data['title']}</h1>
<p>{$cat_data['desc']}</p>
</div>";
// get a copy of any existing $_GET data
$get = $_GET;
foreach($product_data as $row)
{
// id, title, price, img
// set the pro_id element for the links
$get['pro_id'] = $row['id'];
// build the query string part of the links
$qs = http_build_query($get,'','&');
echo "<div class='col-md-4 col-sm-6 single'>
<div class='product'>
<a href='details.php?$qs'>
<img src='admin_area/product_images/{$row['img']}' class='img-fluid'>
</a>
<div class='text'>
<h3><a href='details.php?$qs'>{$row['title']}</a></h3>
<p class='price'>\${$row['price']}</p>
<p class='buttons'>
<a href='details.php?$qs' class='btn btn-outline-primary'>View details</a>
<a href='details.php?$qs' class='btn btn-primary'>
<i class='fa fa-shopping-cart'></i> Add to cart</a>
</p>
</div>
</div>
</div>";
}
}
}
}
