0

This might be less complicated than I'm making it, but I'm stuck. I have two tables with columns that have related data that I'd like to join when the user searches for one or two terms. Example tables would look like this, using placeholder examples:

table: FoodCategories
+---------+--------------+
| ID (INT)| food (VCHAR) |
+---------+--------------+
| 40      | Seafood      | 
| 41      | Pasta        | 
| 42      | Salad        | 
| 43      | Bread        |
+------------------------+

And

table: FoodItems
+---------+--------------+
| ID (INT)| item (VCHAR) |
+---------+--------------+
| 40      | Seabass      | 
| 40      | Shrimp       | 
| 40      | Lobster      | 
| 41      | Spaghetti    | 
| 41      | Lasagne      | 
| 41      | Linguine     |
| 42      | Cesar        | 
| 42      | Tossed       | 
| 43      | Baguette     | 
| 43      | Rolls        | 
| 43      | Breadsticks  |
+------------------------+

What I am trying to do is output the data from both tables together, but leave it open to search either table if the term is not found in the second when the user searches one more more words, dealing with comma separated words using IN (which I've learned to bindparam in PDO, and I assume this is what is causing the issue, but I'm not sure). I can make it work with ONE word search and with two or more words from the SECOND table, but if the word happens to be from the FIRST table, I get null.

So, for example, if we search the word "Shrimp", I want to get

"ID: 40, food: Seafood, item: Shrimp"

If we search "Cesar, Breadsticks" I want to get

"ID: 42, food: Salad, item: Cesar"
"ID: 43, food: Bread, item: Breadsticks"

Now, if I want the user also to be able to get results from the first table, this is where I can't seem to get it to work. For example, searching "Seafood, Rolls" I want to get

"ID: 40, food: Seafood"
"ID: 43, food: Bread, item: Rolls"

or if I search "Pasta, Salad" I want to get simply

"ID: 41, food: Pasta"
"ID: 42, food: Salad"

This is the query which actually works to some extent. Every other attempt fails.

$questionmarks = "";
$query ="SELECT DISTINCT FoodCategories.ID,FoodCategories.food FROM FoodCategories
INNER JOIN 
FoodItems ON FoodCategories.ID = FoodItems.ID
WHERE FoodItems.items IN (";
for($i=0;$i<count($searchterms);$i++)
{
    $questionmarks .= "?,";
}
$query .= trim($questionmarks,",");
$query .= ")
;";

So, 1. what kind of JOIN should I use if INNER JOIN is wrong, 2. how to I get the data for FoodItems.item in my SELECT (I've tried subqueries but don't know if that's the right approach) and 3. How do I perform an AND/OR situation to pull the data from either or both tables (items AND/OR food) while still being able to bind my IN statement?

Niwa
  • 67
  • 1
  • 5
  • you should really take PDO out of equation because your question is really about SQL. Why not to leave a few *placeholder* search items instead of this PHP code? this indeed make your question "less complicated" and let people have an idea on what are you talking about – Your Common Sense Feb 06 '22 at 05:50
  • and after getting through this unnecessary PDO, I can say that you need two queries here. One for categories and one for items join categories. – Your Common Sense Feb 06 '22 at 05:56
  • Thanks for the input, mate. I know that binding the params is important to avoid injections, and in particular it seems IN statements are hard to bind in PDO. That's why I thought to include it in this question as I thought it was relevant, but as I'm still relatively a noob at this, so forgive my oversight. With that said, can I do two queries and still get JSON output without any issues? Or is that irrelevant, too? – Niwa Feb 06 '22 at 06:02
  • yes that's irrelevant. you can always get results of two queries into a single array. just repeat `while ($row = $stmt->fetch()) {$resultArray[] = $row}` two times after two queries. the second query result will be fluently added to the first – Your Common Sense Feb 06 '22 at 06:11
  • Thank you! Going to give this a try. As always, I think I'm overcomplicated things in my mind, and that causes me to get stuck. – Niwa Feb 06 '22 at 06:18
  • you are right about the importance of binding params but you should only put them in after getting your SQL ready. Basically you fiddle with your SQL in the sql console, without any PDO (or PHP for that matter). You need to have your SQL first. And only then translate this working SQL into php code – Your Common Sense Feb 06 '22 at 06:19
  • Got it. I'm trying my best to learn binding so that I can incorporated it without any issues. Thank you for your advice. One more quick question related to performing two queries. How do I prepare two queries (as in `$response = $pdo -> prepare($query);` )? Do I name both differently, like $query1 and $query2 with `$response = $pdo -> prepare($query,$query2);` For some reason, that doesn't seem like it'll work. :P – Niwa Feb 06 '22 at 06:28
  • you just run them completely separated. perform the first one entirely from prepare to getting the array with data from it. And only then proceed to the second one. add the data to the same array. – Your Common Sense Feb 06 '22 at 06:37

0 Answers0