spenceddd Posted February 4, 2010 Share Posted February 4, 2010 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 Quote Link to comment Share on other sites More sharing options...
schilly Posted February 4, 2010 Share Posted February 4, 2010 looks like your missing a space. $catQuery = "SELECT id FROM PortfolioItems WHERE categories IN (".$catArray.")"; Quote Link to comment Share on other sites More sharing options...
spenceddd Posted February 4, 2010 Author Share Posted February 4, 2010 Thanks for the suggestion, I tried it but it made no difference to the result... Quote Link to comment Share on other sites More sharing options...
schilly Posted February 4, 2010 Share Posted February 4, 2010 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']); Quote Link to comment Share on other sites More sharing options...
spenceddd Posted February 4, 2010 Author Share Posted February 4, 2010 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? Quote Link to comment Share on other sites More sharing options...
spenceddd Posted February 4, 2010 Author Share Posted February 4, 2010 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. Quote Link to comment Share on other sites More sharing options...
schilly Posted February 4, 2010 Share Posted February 4, 2010 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. Quote Link to comment Share on other sites More sharing options...
spenceddd Posted February 4, 2010 Author Share Posted February 4, 2010 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! Quote Link to comment Share on other sites More sharing options...
spenceddd Posted February 4, 2010 Author Share Posted February 4, 2010 Sorry slight mistake there it would be array([0]=>[101] categories=> array([0]=>[12], [1]=>[14], [2]=>[22])) Quote Link to comment Share on other sites More sharing options...
schilly Posted February 4, 2010 Share Posted February 4, 2010 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? Quote Link to comment Share on other sites More sharing options...
spenceddd Posted February 4, 2010 Author Share Posted February 4, 2010 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? Quote Link to comment Share on other sites More sharing options...
schilly Posted February 4, 2010 Share Posted February 4, 2010 hmmm not sure. never used that before. Quote Link to comment Share on other sites More sharing options...
spenceddd Posted February 4, 2010 Author Share Posted February 4, 2010 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?? Quote Link to comment Share on other sites More sharing options...
spenceddd Posted February 8, 2010 Author Share Posted February 8, 2010 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.