how to show data when a cell contains more multivalue separated by commas

Question

I have following code but i have a problem.

$query = "SELECT p.*,(TO_DAYS(p.start_date) - TO_DAYS(NOW())) AS daydiff FROM #__jblance_project p ".
                 "WHERE p.status=".$db->quote('COM_JBLANCE_OPEN')." AND p.approved=1  AND '$now' > p.start_date AND  p.id_category=202 ".
                 "ORDER BY p.is_featured DESC, p.id DESC ".

the problem is when the field id_category has more than just one value like

202,203,204

it is not showing any result althout i have changes the code to

AND  p.id_category=202 OR .id_category=203 OR .id_category=204

as the cell contains all values separated by comma

any advice.

Thanks D.

@user1859876 Parado did it for you, you can edit your question now to see how he changed it. — jedwards, Nov 28 '12 at 12:57
You should consider changing your database to contain only one entry there, instead of a list of comma separated values. But you might check out the [`FIND_IN_SET`](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set) syntax. — jedwards, Nov 28 '12 at 12:59

score 0 · Answer 1 · answered Nov 28 '12 at 12:56

0

$query = "SELECT p.*,(TO_DAYS(p.start_date) - TO_DAYS(NOW())) AS daydiff FROM #__jblance_project as p ". "WHERE p.status=".$db->quote('COM_JBLANCE_OPEN')." AND p.approved=1 AND '$now' > p.start_date AND p.id_category=202 ". "ORDER BY p.is_featured DESC, p.id DESC ".

add as FROM #__jblance_project as p

answered Nov 28 '12 at 12:56

rOcKiNg RhO

631
1
6
16

Hi I have already edited the code with the p. but still no result i can see. I only see result when t cell has one value for example 202 but not when a cell has two values 202,203 – user1859876 Nov 28 '12 at 12:59

score 0 · Answer 2 · edited May 23 '17 at 12:18

Expanding on my comment, consider changing this:

SELECT p.*,(TO_DAYS(p.start_date) - TO_DAYS(NOW())) AS daydiff 
FROM #__jblance_project p
WHERE p.status=<something>
AND p.approved=1  AND '$now' > p.start_date 
AND p.id_category=202
ORDER BY p.is_featured DESC, p.id DESC

To something like:

SELECT p.*,(TO_DAYS(p.start_date) - TO_DAYS(NOW())) AS daydiff 
FROM #__jblance_project p
WHERE p.status=<something>
AND p.approved=1  AND '$now' > p.start_date 
AND 0 < FIND_IN_SET(202, p.id_category)
ORDER BY p.is_featured DESC, p.id DESC

Also, I'd probably write the full PHP statement as:

$query = sprintf('SELECT p.*,(TO_DAYS(p.start_date) - TO_DAYS(NOW())) AS daydiff 
    FROM #__jblance_project p
    WHERE p.status=%s
    AND p.approved=1  AND '%s' > p.start_date 
    AND 0 < FIND_IN_SET(202, p.id_category)
    ORDER BY p.is_featured DESC, p.id DESC', $db->quote('COM_JBLANCE_OPEN'), $now);

Note that this is more of a workaround than the "correct" solution. You should really consider reformatting your database such that you have one entry per field, as opposed to a comma separated list.

The second two use the FIND_IN_SET string function which should awkwardly accomplish your goal.

This related question and especially the answers and comments may be instructive.

score 0 · Answer 3 · answered Nov 28 '12 at 13:05

There is no really solid way to do this... You're stuck with something ugly like:

id_category LIKE "%,123" OR
id_category LIKE "123,%" OR
id_category LIKE "%,123,%" OR
id_category = "123"

The reason this is so ugly, is because it's a bad idea to use delimited fields like this. Instead you should create a separate table with two columns:

An id for projects
An id for categories

This is a many-to-many relationship.

If you do want to go further with your hack, I would suggest to not format the id_category like :

123,456,789

But instead:

,123,456,789,

So include a comma before and after. At the very least you can simplify your like query to:

id_category LIKE "%,123,%"

Edit: I did not know about FIND_IN_SET. This sounds like a valid solution, although normalizing your data will still give you a much better outcome.

score 0 · Answer 4 · answered Nov 28 '12 at 13:09

0

You have use IN() keyword in mysql.

p.id_category IN (202,203,204)

Here you can pass the dynamic category id array like this.

$array_cat_id = impload(',' ,$pass_array_of_the_id);

p.id_category IN ($array_cat_id)

answered Nov 28 '12 at 13:09

Chirag Patel

516
4
14

how to show data when a cell contains more multivalue separated by commas

4 Answers4