PDO mysql query like array values

Question

I am fairly new with PDO but i have come across something which probably has an easy answer.

I have an array of category numbers to which i need to search through a mysql database to find all items with the matching category number.

I know i have connected to DB correctly as i am receiving the category array from a different query.

$category = Array ( [0] => 3259 [1] => 3617 [2] => 3257 [3] => 3258 [4] => 3450 [5] => 3452 [6] => 3478 [7] => 3479 [8] => 3480 [9] => 3448 [10] => 3449 [11] => 3451 [12] => 3456 [13] => 3454 [14] => 3455 [15] => 3459 [16] => 3460 [17] => 3458 [18] => 3453 [19] => 3461 [20] => 3462 [21] => 3457 [22] => 3463 [23] => 3447 )

now i need to go through the item database to check for matching categories. Each item can have multiple categories, each category has 4 digits. multiple categories are seperate with a =

$categoryResults = $conn->prepare('SELECT * FROM items WHERE category like :cats ORDER BY promotions,price ASC');
$categoryResults->execute(array(':cats' => '%'.$category.'%'));
$categoryNumber = $categoryResults->rowCount();

This is where i seem to have gone wrong. With the mysql request i would have built it up manually using a loop through the array to get me a long sql query :

'Select * From items WHERE category like '%3259%' OR WHERE category LIKE '%3617%'........etc

But i am sure this is one of the advantages of using PDO. Just to add to it i need the results to be unique. If item 97 is in category 3259 and category 3617 i only want to get the result once.

Thanks in advance.

Good question. I personally use PDO::quote in a loop over the $category array to handle such SQL queries. — Adder, Jan 02 '13 at 11:09

score 0 · Accepted Answer · edited May 23 '17 at 11:43

0

I find it stange that you are looking for numbers with a LIKE statement. Isnt category just the ID of the category or how is it stored? If category is CSV list, I advise you to alter it to make use of a seperate table for many to many connections.

If it's already a numeric value, just use the IN statement

$categoryResults = $conn->prepare('SELECT * FROM items WHERE category IN (:cats) ORDER BY promotions,price ASC');
$categoryResults->execute(array(':cats' => implode(',', $category));
$categoryNumber = $categoryResults->rowCount();

Update Or even better, check out this question which is the same.

Can I bind an array to an IN() condition?

edited May 23 '17 at 11:43

Community

1
1

answered Jan 02 '13 at 11:09

Hugo Delsing

13,803
5
45
72

Each item can be many different categories so the client adds an item and chooses the category to display the item in. This can be as many categories as they think they want to display it in. so the item actually have categories saved to the database as a string seperated by a = ie.. 3617=3927=4125 so imploding category using an = would give me 3617 3927 4125. thats why i was using a like with a wildcard. If i am listing all items in category 3927 it then would display. – South Coast Web Jan 02 '13 at 11:28
2

Thats what I ment with using a seperate table instead of CSV (or in your case equalsign seperated list instead of comma seperated list). Use normalization on your database, then you wont be running into this problem. http://databases.about.com/od/specificproducts/a/normalization.htm – Hugo Delsing Jan 02 '13 at 11:52
Thank You for your answers. I will have a look at normalisation and come back. – South Coast Web Jan 02 '13 at 12:32
I have read through that article. In relation to this i would create a new table in my database with 2 columns. One for item number and one for category. That way if an item number has more than 1 category i can just insert an extra line. Then when i search through my $categories array i will end up with an array of item numbers. i can then apply array_unique to this array. Then i have to go through my item database and extract each items information that matches the array ? How using PDO do i go through the array and get the list in a certain order ? – South Coast Web Jan 02 '13 at 13:06
1

You dont need PHP. You could use mysql `JOIN` to combine tables or even use an in function with a new `SELECT`. `SELECT * FROM items WHERE id IN (SELECT ItemId FROM ItemCategoryTable WHERE CategoryId IN (1,2,3,4,5))` – Hugo Delsing Jan 02 '13 at 13:14
In the example above the IN(1,2,3,4,5)) relates to categories i am searching for. Those categories are in the original $category array. how do i get that into the new mysql statement? Thanks again for your answers. – South Coast Web Jan 02 '13 at 13:31
1

Check out the link in my question to binding an array to an IN condition. Much more detailed then I can put in the comments. It comes down to using implode(',', $array) to make a comma seperated list – Hugo Delsing Jan 02 '13 at 13:38
This can't possibly work. `IN` requires each item to be a separate value, it doesn't work with a comma-delimited string. – Barmar Sep 20 '19 at 18:19

score -2 · Answer 2 · answered Jan 02 '13 at 11:11

Do like this

<?php
$categoryId = array(1, 21, 63, 171);
$place_holders = implode(',', categoryId);


$sth = $dbh->prepare("SELECT * FROM items WHERE category in (?) ORDER BY 
                      promotions,price ASC");
$sth->bindParam(1, $place_holders, PDO::PARAM_STR, 12);
$sth->execute($params);

?>

PDO mysql query like array values

2 Answers2