Why is mysqli not in not working

Question

0.00/5 (No votes)

See more:

I have a list of products.
Each product contain two categories.
Some products contain the same name in both categories. This is not wrong - most products have multiple categories, but some have only one category in which case the name is displayed in both category fields.

I want the amount of products for each category.
For example:
Food = 35;
Drink = 20;

The problem is if a product have the same name in both categories, it gets counted twice. I have tried the "NOT IN" and have tested all the output fields. Even thou is should work, it definitely does not.

Please see my code below

What I have tried:

PHP

$mysqpi = mysqli_query($conn, "SELECT * FROM products WHERE category = 'Food'")
or die(mysqli_error($conn));

while($geego = mysqli_fetch_array($mysqpi)){
$aldisis .= $geego['id'] . ", ";	
}
$aldisis = substr($aldisis, 0,-2);	
$touristmeen = mysqli_num_rows($mysqpi);

$mysqpii = mysqli_query($conn, "SELECT * FROM products WHERE categorydos = 'Food' AND id NOT IN ($aldisis)")
or die(mysqli_error($conn));

$touristmtwee = mysqli_num_rows($mysqpii);

$touristm = $touristmeen + $touristmtwee;

Posted 24-Mar-21 2:41am

JimmiWillTakeThisUsername

Updated 24-Mar-21 9:10am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Accepted Answer · 2021-03-24T09:10:00

Solution 1

Maybe adjust your query to something like:

SQL

SELECT DISTINCT * FROM products WHERE category='food' 
UNION
SELECT DISTINCT * FROM products WHERE categorydos='food'

which gives you the count of unique items (no duplicate records)

Now you can then get the count from this by various means (such as making this into a SQL subquery or via the PHP.

Posted 24-Mar-21 9:10am

W∴ Balboos, GHB

Comments

JimmiWillTakeThisUsername 25-Mar-21 3:29am

Thanks for your input, but unfortunately this does not solve the problem. The results still show double the actual amount.

I wasn't aware of the 'DISTINCT' feature, so thank you for introducing me to it.

I think the query run on both sides, before it slams it together with 'UNION'. That is why 'DISTINCT' have no effect. That is only my theory.

Do you have any idea why "NOT IN" is not working in my query above?
If I print out the variables, the $aldisis contains the id numbers (100,101,103) and then the second query go and count exactly the rows with that id numbers.

Again, I appreciate your time

W∴ Balboos, GHB 25-Mar-21 6:26am

You do not need to run it twice with what I posted for you. It gets them all in one shot so if you run it twice each one will give them all and you get two of each!

JimmiWillTakeThisUsername 25-Mar-21 7:16am

...yeah, so I am not running it twice. There is one query and one output. The code is something like:

$mysqpi = mysqli_query($conn, "SELECT DISTINCT * FROM products WHERE category='food' UNION SELECT DISTINCT * FROM products WHERE categorydos='food'')")
or die(mysqli_error($conn));

$touristm = mysqli_num_rows($mysqpi);

I am not 100% sure why you thought I would run the query twice and expect the result of one. I think you might have misunderstood me. I only have a theory on how I think the computer process the code. That way I can think of possible solutions.

W∴ Balboos, GHB 25-Mar-21 9:03am

At his point, without seeing your data, I cannot go further. It may well be it is giving you the correct answer for the data you have. The UNION (even without DISTINCT - it's implied in UNION) supplies only unique records.