MYSQL IN working like AND

[Halisco]

I have searched this on google and this forum and and struggling to figure this out.

 

I have a table:

 

| id | fruit_name |

 

The id column is not unique i have several identical enstries and the name is also not unique and has several identical entries:

 

| 1 | apple |

| 2 | banana |

| 2 | apple |

| 1 | orange |

| 3 | grapefruit |

| 3 | apple |

| 1 | banana |

| 2 | peach |

| 3 | peach |

 

I have a multiple select list with the id="fruit" and using join to create a variable for my query:

 

$fruit_type = join("','",$_POST['fruit']);

 

 

$fruit_results = $conn->query("SELECT * FROM fruit WHERE fruit_name IN ('$fruit_type ')");

 

 

while($row = $fruit_results ->fetch_assoc()) {

    echo $row['id'];

}

 

This is giving me all the id of any row that has any type of fruit that is selected in the list.  I am trying to the id's that contain all of the fruit selected not just one or the others.  Can anyone help me out please?

 

Thanks, 

 

Halisco

 

 

 

 

 

 

[requinix]

I'm going to ignore the SQL injection and go for the question itself. In a case like this I would do a GROUP BY + HAVING COUNT

SELECT id
FROM fruit
WHERE fruit_name IN ("apple", "banana", "orange")
GROUP BY id
HAVING COUNT(1) = 3

Make sure the id + fruit_name pair is unique, even if the two aren't individually.

[Halisco]

I understand the injection is there and i posted a quick and dirty to avoid posting any other code.  I have seen the exact code you have replied with on google and have tried using it already.  If apple and banana are selected from my multiple selection I should only get id 1 and id 2 using my while statement.  I think i have modified the code to represent what i need.  The final number 3 you posted is a dynamic number depending on the amount of fruit type selected.  When i try to echo the results i get a an error:

 

 

$fruit_results = $conn->query("SELECT id FROM fruit WHERE fruit_name IN ('$fruit_type ') GROUP BY id HAVING COUNT(1) = count('$fruit_type ')");

        

        

while($row = $fruit_results ->fetch_assoc()) {

    echo $row['id'];

}

 

Call to a member function fetch_assoc() on boolean

 

[Jacques1]

Yeah. Maybe you should just stop writing quick and dirty code and do it properly. Then you'll simultaneously solve your injection problems and the PHP issues.

[Halisco]

Jacques1

You comment is not of assistance with the actual resolution to my problem.  please feel free to assist me with the actual question or i would appreciate you not post at all.  I am here seeking assistance not insult. Is anyone else able to offer some assistance? 

[Jacques1]

I've just given you the solution. All you have to do now is stop complaining and start programming.

 

In case you still haven't understood the problem:

• When you randomly dump PHP variables into query strings, you not only create vulnerabilities, you also screw up the SQL syntax. This is what happened.
• You claimed you already know how to properly pass values to queries. Do it then. It's all you need. If you don't know how to do this, look up prepared statements and then the solution for IN lists (note that mysqli is very cumbersome; in the long run, you might want to switch to PDO).

Besides that, you definitely need to enable error reporting for mysqli, because you're obviously not willing to check each query manually (which I can understand).

 

The template for the prepared statement is

SELECT id
FROM fruit
WHERE fruit_name IN (?, ?, ...)    -- this is where the list items go
GROUP BY id
HAVING COUNT(1) = ?    -- this is where count($fruit_types) goes

Edited November 17, 2016 by Jacques1

[Halisco]

This is producing results for me however it is still not giving me results where every item selected has to be associated with the id.  It still produces results if any of the id's have the selected items.

 

$fruit_count = count($_POST['fruit']);

$fruit_types = join("','",$_POST['fruit']);

 

$fruit_results = $conn->query("

        SELECT id 

        FROM fruit 

        WHERE fruit_name IN ('$fruit_types')

        GROUP BY id 

        HAVING COUNT(1) = count($fruit_count)

        ");

[Barand]

That will give something like

... HAVING COUNT(1) = count(3)

What you want is

... HAVING COUNT(1) = 3

[Jacques1]

 

 

It amazes me how PHP “programmers” rather spend 3 hours making the same mistake over and over again instead of simply fixing their damn code.

 

I even posted the statement template. No. He just cannot live without his SQL injection vulnerabilities.

[Halisco]

 

 

It amazes me how PHP “programmers” rather spend 3 hours making the same mistake over and over again instead of simply fixing their damn code.

 

I even posted the statement template. No. He just cannot live without his SQL injection vulnerabilities.

If you are not going to aid in teaching don't comment Being an A****ole does not help.  Im no guru and i come here for help not to listen to people like you.  Stay off my post.

[benanamen]

The id column is not unique i have several identical enstries and the name is also not unique and has several identical entries:

 

Isn't that the real problem? Where is the logic in that?

Edited November 17, 2016 by benanamen

[Halisco]

Isn't that the real problem? Where is the logic in that

A question answered with a non helpful question.  I used to use this forum several years ago and never had such ignorant comments from users.  Thanks to those that have been trying to help. I think i will have to go to a forum where users understand people can be ignorant and need help..

[benanamen]

Rather than copping an attitude at people that are trying to help you for FREE, why don't you just answer the question? From what you provided, it logically doesn't make sense, at least not to me. It appears to be an XY problem. You can see my signature for an explanation of that.

[Jacques1]

I'm pretty sure he's just trolling.