What is wrong with this code?

[spenceddd]

Hi, I am trying to return an array of ID's from a mysql table where any of the values such as (5,6,7) are contained in the string of the colomn 'categories'.

 

All I am getting though is an empty array...

 

Here is the code:

 

$categoriesArrayToCheck = array(5,6,7);
$catArray = implode(',', $categoriesArrayToCheck);
$portfolioItemCats = array();

foreach ($categoriesArrayToCheck as $value) {
$catQuery = "SELECT id FROM PortfolioItems WHERE (categories IN(".$catArray.")) ";
$catResult = mysql_query($catQuery)or die('Query failed. ' . mysql_error());
                array_push($portfolioItemCats, mysql_fetch_assoc($catResult));
}

print_r($portfolioItemCats);

 

 

Could anyone please tell me what I am doing wrong here.

 

The result I end up with is:

Array ( [0] => [1] => [2] => ) 

 

Any help greatly appreciated!

 

Spencer

[schilly]

looks like your missing a space.

$catQuery = "SELECT id FROM PortfolioItems WHERE categories IN (".$catArray.")";

 

[spenceddd]

Thanks for the suggestion, I tried it but it made no difference to the result...

[schilly]

also why are you looping through your array values when you are searching by the IN clause.

 

dont you want to loops through the results of your query instead?

$catQuery = "SELECT id FROM PortfolioItems WHERE categories IN (".$catArray.")";
$catResult = mysql_query($catQuery)or die("Query failed - $catQuery - "  . mysql_error());

while($row = mysql_fetch_array($catResult))
                array_push($portfolioItemCats, $row['id']);

 

[spenceddd]

Well its a bit hard to explain but each each field in the colomn category is filled with a string like "2,11,16,17". So I am trying to check which fields have any values that match the test string which in my example is "5,6,7". Does that make sense?

[spenceddd]

Doh, I have just realised that my test string didn't match any of the values in my database! It's still return a multi-dimesional array which isn't waht I expected though.

[schilly]

if category is filled with strings like "2,11,16,17" I dont think your IN query will work.

 

Your query SELECT id FROM PortfolioItems WHERE categories IN (5,6,7) is the same as SELECT id FROM PortfolioItems WHERE categories 5 OR categories = 6 OR categories = 7  and hence I don't think it will match any records, like you said.

 

Because these numbers aren't quoted either, it thinks they are ints.

[spenceddd]

Yes having thought about it a bit more my query is more complicated that originally expected.

 

If I was to work backwards what I want to end up with is: array(id=>[101] categories=> array([0]=>[12], [1]=>[14], [2]=>[22])) ...I think.

 

So basically the test strings or ints would be in this case say "4, 5, 12, 14, 21, 22" and the result showed that only one row in the table matched any of the test ints and that was row with the id = 101 matching only 12, 14 and 22.

 

I fear this is quite a request!

[spenceddd]

Sorry slight mistake there it would be

 

array([0]=>[101] categories=> array([0]=>[12], [1]=>[14], [2]=>[22]))

[schilly]

i think im starting to understand what you want to do but i don't think you'll be able to use the ON query with your db field as a string.

 

you're going to need to use LIKE and have a strict string storing convention in the DB. you will need to have a separating character after every value in the string. so "4, 5, 12, 14, 21, 22" in the db will need to be ",4, 5, 12, 14, 21, 22," so you can properly identify each value.

 

then you can do something like

 

SELECT id FROM PortfolioItems WHERE categories like "%,5,%" OR categories like "%,6,%" OR categories like "%,7,%"  etc etc

 

 

does that make sense?

 

[spenceddd]

Yes I think I've got it. The only problem is the test values will need to be dynamic in the end. So I guess that means I will need to loop through them. Do you know if using the 'FIND_IN_SET; function will help at all?

 

[schilly]

hmmm not sure. never used that before.

[spenceddd]

Yeah me niether! I'm a total novice with php! Damn.

 

But at least I know what I need to do in english.

 

Loop through all the rows in the table{

    for every row check if any of the following dynamically created strings eg("12", "15", "19") are there and  if so{

                                put them into an array and then append the resulting array with a its id to the main array of this for loop

                              }

}

 

I think thats it!

 

Thanks very much for your help with that. Does anyone else have any ideas??

[spenceddd]

Having had some time to think about this now and get help from a friend it seems the most logical way to address a many to one relationship (like the id numbers string in my 'categories' colomns) is to add another table. This table although I haven't created it yet will contain a single row for every reference (portfolioItem <> catetgory) and will therefore be easier and more efficient to query.

 

Now I have got to make it work!

 

Thanks for your help on this thread schilly.