2

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;

    $DEFECTIVEFORDISPOSAL=mysqli_query($con,"SELECT COUNT(colItem) 
                                             FROM tbInventory 
                                             WHERE colAssetStatus=\"DEFECTIVE FOR DISPOSAL\" 
                                              AND (colItem)=\"$itemname\" ");
    $DEFECTIVEFORDISPOSAL1 = mysqli_fetch_array($DEFECTIVEFORDISPOSAL);

Anything I'm missing with my statements?

*Edit:

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

Output

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.

Options

*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.

Ruther Melchor
  • 69
  • 2
  • 10
  • There appears to be a lot more going on in your real script. Maybe this is not the actual reason for you issue – RiggsFolly Aug 03 '16 at 08:15
  • NOTE: All upper case variable names are normally reserved for CONSTANTS and DEFINED values – RiggsFolly Aug 03 '16 at 08:18
  • We cannot help you with code we cannot see – RiggsFolly Aug 03 '16 at 08:20
  • The entire code/script seems to be working, its just that the particular lines of code stated above is disregarding the AND condition. I've also checked the variable $itemname if its fetching the correct data from colItem and it does. – Ruther Melchor Aug 03 '16 at 08:22
  • Echo the query and its result so you know what query returned what values. You will probably find your issue is somewhere else – RiggsFolly Aug 03 '16 at 08:25

2 Answers2

1

Try this

$DEFECTIVEFORDISPOSAL=mysqli_query($con,"SELECT COUNT(colItem) FROM tbInventory WHERE colAssetStatus = 'DEFECTIVE FOR DISPOSAL' AND colItem = '{$itemname}'");
Saurabh
  • 776
  • 1
  • 5
  • 15
  • Still doesn't work, the systems seems to be ignoring the AND condition as its printing the same data even if remove it entirely. – Ruther Melchor Aug 03 '16 at 08:03
  • can you post some sample data and expected output? – Saurabh Aug 03 '16 at 08:04
  • I've edited the question above, thank you for your attention! – Ruther Melchor Aug 03 '16 at 08:14
  • I am unable to understand what exactly is going on by looking at image.If possible post database column with values so that we can understand how data is coming and what needs to be done with query – Saurabh Aug 03 '16 at 08:58
  • Again, I've re-edited my question above for more information; I hope it helps @Saurabh – Ruther Melchor Aug 03 '16 at 09:11
  • What are you passing in `$itemname`? You have only 3 entries in `colAssetStatus` correct? – Saurabh Aug 03 '16 at 09:20
  • I'm storing the item's name from colItem so that I could use it to compare the results picked up by the query. Yes, there are only 3 entries in the system. – Ruther Melchor Aug 03 '16 at 09:24
  • If you have only those 3 item as you said than `AND` condition will not satisfy as `DEFECTIVE FOR DISPOSAL` does not exist in column. I was asking for actual values that you pass in`$itemname` – Saurabh Aug 03 '16 at 09:49
  • Any suggestions as to how can I compare the item's name first before I print the data? The item names should be the same from what is coming off out of the loop before I print them. The values on $itemname are auto-generated as I'm pulling them off the database. – Ruther Melchor Aug 03 '16 at 10:01
  • After some tweaking, your suggestion actually helped! I made some changes on some parts of the script and it worked, thanks! – Ruther Melchor Aug 03 '16 at 10:04
  • Welcome. Happy to help – Saurabh Aug 03 '16 at 10:07
0

You dont need all that escaping if you use a single quote inside the double quoted string like this

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

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

I also assume that you have a mysqli_fetch_assoc() or similiar after the above command as this on its own will not retrieve the result set.

NOTE Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared statement and parameterized statements

Community
  • 1
  • 1
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149