$Variable = ISSET($_POST ... Not working as Anticipated.

Okay, there's a few things wrong here, but I'll caveat it by saying that I don't know if I fully understand this part of the question:
"but annoyingly only once it loaded the whole page to start with"

Moving onto what I -can- tell you:

1. If CatID is not sent in a POST, then you're giving it a value of a false boolean. Then you're using that value in your query as if it were a string.

Now, PHP doesn't complain when you mix types like that, and it tries to make assumptions about what you were intending to do, and sometimes those assumptions are bad. So the question is - what happens when you use a false boolean as if it were part of a string? A quick test will show us this:

<?php
$c = "a" . false . "b";
var_dump($c);
// Results in string(2) "ab"

Select allOpen in new window

...so basically it's as if there's no value at all. So this means that when $CatID is false, your query ends up being:

SELECT partID, partDescription, qtyInStock 
FROM inventory
WHERE partDescription like "%%"

Select allOpen in new window

...and LIKE "%%" will match EVERYTHING, so when $CatID is false, then you're querying the entire table. I'm guessing this is likely the problem you're talking about.

If you don't want to have any results before searching for a specific CatID, then just check to see if $CatID === false (yes, three = signs in a row) and don't run the query at all when it's false.

Moving onto other pieces:

#2. Ideally, you shouldn't mix and match your data types in your variables. PHP lets you do it, but it's really bad practice, because as your code grows, it becomes harder to know for sure what kind of value might be inside a variable. So instead of using false boolean, use something that's an invalid CatID, like -1 or something:

$CatID = isset($_POST['CatID']) ? $_POST['CatID'] : "-1";

#3. Your code is vulnerable to SQL injection because you're not sanitizing $CatID before using it in a query. If CatID is always going to be a numeric value like 100, -1, 9999, and so on, then use the intval() function to tell PHP to force it to be a number and get rid of anything that isn't a number:

$CatID = intval(  isset($_POST['CatID']) ? $_POST['CatID'] : "-1"  );

Now if someone tries to sneak in some malicious SQL, it will get stripped off. To give you an idea of how intval works:

<?php
$x = intval("abc1234"); // $x = 0
$x = intval("123abc4"); // $x = 123

Select allOpen in new window

So intval() reads from left to right, and will return whatever numbers it finds until it gets to a non-number. So since the first character in the first line is not a number, $x ends up being 0. In the second line, the initial set of numbers is 123, and once it hits a letter, it stops reading, so you end up with 123 (even though there's a 4 after the letters).

If CatID or any other value in a SQL query can have legitimate non-numeric characters, then you can use the real_escape_string function on the value:

$db = new mysqli(...);
$x = "malicious SQL in here";
$query = "SELECT * FROM table WHERE foo = '". $db->real_escape_string($x) ."'";

Select allOpen in new window

That should prevent the malicious SQL from working the way the attacker wants it to work.

Thanks for taking the time to explain.

Upon inspection the var_dump($CatID) produced a "bool(false)" and as you say that in conjunction with effectively "%%" it just runs the full query.

The variable $CatID has legitimate non-numeric characters so I've included "$mysqli->real_escape_string" in the query.

I therefore need to adjust it so that it does not run until I enter a variable into the form - I had a play with: -

IF($CatID===False) then {} else {

Select allOpen in new window

I did that as follows: -

if($CatID===false) then {} else {

$CatID = isset($_POST['CatID']) ? $_POST['CatID'] : false;

$query = "SELECT partID, 
				partDescription, 
				qtyInStock
				FROM inventory
				WHERE partDescription like '%".$mysqli->real_escape_string($CatID)."%' ";

	if (!$result = $mysqli->query($query)) {
		$err
		= "QUERY FAIL: "
		. $query
		. ' ERRNO: '
		. $mysqli->errno
		. ' ERROR: '
		. $mysqli->error
		;
		trigger_error($err, E_USER_ERROR);
		} 

	while ($row = $result->fetch_object()) {
		$tr = <<<EOD
				<tr>
					<td class="text-center"> $row->partID </td>
					<td> $row->partDescription </td>  
					<td class="text-center"> $row->qtyInStock </td>
					<td class="text-center">
					<button type="button" name="view" value="view" data-id="$row->partDescription" class="btn btn-primary btn-xs view_data" /><span class="glyphicon glyphicon-cog"></span></button></td>
				</tr> 
EOD;
		echo $tr;} 
}

Select allOpen in new window

But that causes the page to crash - so I still need a little clarification on what I did wrong please?

Couple of points about your data. Because you're using data from the user (POST['CatId'], you should be using a prepared statement. This will protect you from SQL Injection, and make your code safer. Also, there's no need to escape the data this way.

For your if ckeck, I would use the ternary operator to see if a value has been submitted and then use that variable to decide whether to run the query or not.

Take a look at this:

<?php
$catId = isset($_POST['CatID']) ? "%" . $_POST['CatID'] . "%" : null;

if ($catId):
    $inventory = $db->prepare("SELECT partID, partDescription, qtyInStock FROM inventory WHERE partDescription LIKE ?");
    $inventory->bind_param("s", $catId);
    $inventory->execute();

    $items = $inventory->get_result();

    while ( $item = $items->fetch_object() ):

        var_dump($item);

    endwhile;

endif;

Select allOpen in new window

Hi gr8gonzo!

I can tell the irony of this isn’t lost on you! Love it! ‘So you’re checking CatID value before it’s exists:’ 🤣

Everydays a school day! Always true. Sometimes we’re consciously competent, sometimes we’re unconsciously competent - (sadly) otherwise we’re just unconsciously incompetent!

Lesson? Never be afraid ask!

Will try and be back soon.

Yours unconsciously, incompetent!

Don't do this:

if($CatID===null) then {} else {

It just doesn't make any sense !!

Hi Chris,

I left in the empty condition block because in the long run, it's fairly likely that code could be added in at that point to handle the initial screen in a different fashion.

Hey gr8gonzo.

Fair point :)

I still think it makes sense to add in what's needed now and then build on it later. I've seen so much code that's been written in case it's needed. YAGNI springs to mind ;)

On a side note, prepared statements (as Chris suggested earlier) are often a better way to perform queries. They can run a bit faster and parameter binding removes the need to escape values so you are safe from SQL injection.

Morning Guys'

Thanks for all the help ... suddenly things are a little clearer and I now know I need to tweak some code in other area's. The switch round worked but concerned about the comments reference:-

Don't do this:

if($CatID===null) then {} else {

Also I tried the ternary way and I couldn't get it too work this is what I did: -

$catId = isset($_POST['CatID']) ? "%" . $_POST['CatID'] . "%" : null;

if ($catId):
    $inventory = $db->prepare("SELECT partID, partDescription, qtyInStock FROM inventory WHERE partDescription LIKE ?");
    $inventory->bind_param("s", $catId);
    $inventory->execute();

    $items = $inventory->get_result();

    while ( $item = $items->fetch_object() ):

        var_dump($item);

    endwhile;

endif;

Select allOpen in new window

It produced the following error: - Fatal error: Uncaught Error: Call to a member function prepare() on null

Ahh, sorry.

Usually when I code, I store the DB connection in a variable called $db. Looking back at your code, you're storing it in a variable called $mysqli, so just change the line to:

$mysqli->prepare(

The comments about the if statement where just an observation. For me it doesn't make sense to do this:

if($CatID===null) then {} else {

when you can do this:

if($CatID !== null)  {

You're putting an empty control block in for no reason (if catid is null then do nothing). Like gr8gonzo pointed out though, you may decide later on to use both the if and the else. My point was simply that if you decide you want to do it later on, then add it later on. Don't code stuff now just in case you need it later.