Ruther Melchor Ruther Melchor - 1 year ago 43
MySQL Question

Combination of WHERE and AND conditions on SQL/PHP

I'm trying to pull up some data from my database using the code below, but the MYSQLI query seems to ignore my AND condition. I would like to compare data first (If the data pulled out and $itemname are the same) before I print them out.

$itemname= $row['colItem'];
echo $itemname;

FROM tbInventory
AND (colItem)=\"$itemname\" ");

Anything I'm missing with my statements?


Here's the data being showed (The green numbers):


and as you could see, there are only 3 records on file, so the total of all the green numbers should only be '3'. But instead, its counting all of the records regardless of their item classification. I.E., there should just be a single '1' on all 3 rows since there are a total of 3 records on file. Something like:

0, 0, 0, 1, 0, 0, 0

0, 1, 0, 0, 0, 0, 0

0, 0, 0, 0, 0, 1, 0

**Here are the options for the 'colAssetStatus', they represent the 7 numbers on the image above.


*Here are the database inputs for the column, 'colAssetStatus'; as you can see, there are 1 data for 3 of the options.

Database Inputs

Since they are 7 options that represents the 7 'green number's on the image above, and there are currently 3 records on file, it should display something like:

0, 0, 0, 1, 0, 0, 0

0, 1, 0, 0, 0, 0, 0

0, 0, 0, 0, 0, 1, 0

As it should only have a 'count' if there's a record on file for the same item, 'colItem', on the items given above. But the issue is the script that I created to count the entries is ignoring my AND condition thus it counts all the records on file without checking the item first if it matches with the colItem displayed.

Answer Source

Try this

$DEFECTIVEFORDISPOSAL=mysqli_query($con,"SELECT COUNT(colItem) FROM tbInventory WHERE colAssetStatus = 'DEFECTIVE FOR DISPOSAL' AND colItem = '{$itemname}'");