MySql LIKE returns 0 rows

[Tunnleram]

I'm missing something stupid so I need another set of eyes.

I have a session value set that's a group concat of a few ids and I want to check to see if an id found by a query is in that group concat. The group concat variable is $session_unitidgrouping

The query doesn't return errors, but it doesn't return what I'd expect either. It returns nothing at all.

For instance, I have data where unit_id = 3 and $session_unitidgrouping = 3, 1, but it returns nothing.

Does anyone have any ideas?

CREATE TABLE IF NOT EXISTS `meetings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `meetingleader_id` int(11) NOT NULL,
  `unit_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `meeting_date` datetime NOT NULL,
  `type` varchar(50) NOT NULL,
  `length` varchar(50) NOT NULL,
  `agenda` varchar(1000) NOT NULL,
  `minutes` varchar(1000) NOT NULL,
  `notes` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;

This is the query I'm using to simplify things during testing.

$query="select meetings.meeting_date, meetings.unit_id as unitid
	from meetings
	where meetings.status_id = '20' and '$session_unitidgrouping' LIKE '%meetings.unit_id%'
	Order by meetings.meeting_date asc";
confirm_query($query);

$result=mysql_query($query, $connection);
$num=mysql_numrows($result);

If I do something like this it works fine

select meetings.meeting_date, meetings.unit_id as unitid
	from meetings
	where meetings.status_id = '20' and '3, 1' LIKE '%3%'
	Order by meetings.meeting_date asc

[urtrivedi]

if you are sure that value of $session_unitidgrouping will always like x,y,z where x y z are integers (not characters).

I mean separted by comma, then you must use IN keyword, not LIKE.

Here is your new query

$query="select meetings.meeting_date, meetings.unit_id as unitid	from meetings	where meetings.status_id = '20' and  

meetings.unit_id	in ($session_unitidgrouping) 

Order by meetings.meeting_date asc";

[Tunnleram]

Thanks! That worked partially. I used the query you suggested, but it doesn't work completely for some reason. It appears it only works if the first number of the variable matches.

For instance:

• If $session_unitidgrouping has a value of '3, 1' and meetings.unit_id has a value of 3, it works fine.
  
• However, if $session_unitidgrouping has a value of '3, 1' and meetings.unit_id has a value of 1, it returns nothing.

Is the issue the comma separating it? I have to use something to separate the numbers since there could be a value of 31 for instance.

Any ideas?

[urtrivedi]

You post your output of query here, also check that output query in phpmyadmin, see how it works

$query="you query";
echo $query;

[Tunnleram]

Thanks. Here's my exact query:

$query="select meetings.id as meetingid, meetings.unit_id as unitid, meetings.status_id as statusid, meetings.meeting_date, meetings.type, meetings.length, meetings.agenda, LEFT(meetings.agenda,200) as agendaexcerpt, meetings.minutes, meetings.notes, units.id, units.name as unit, units.codename as squad, statuses.id, statuses.name as status
	from meetings, units, statuses
	where meetings.unit_id = units.id and meetings.status_id = statuses.id and meetings.status_id = '20' and meetings.unit_id IN ('$session_unitidgrouping')
	Order by meetings.meeting_date asc";

If I use this in phpmyadmin it returns one row when it should return 2. I simplified the query for testing.

In phpmyadmin I ran this query:

select meetings.unit_id, meetings.status_id
	from meetings
	where meetings.unit_id IN ('3, 1') and meetings.status_id = '20'
	Order by meetings.meeting_date asc

It still returns one row. Here is my data:

INSERT INTO `meetings` (`id`, `meetingleader_id`, `unit_id`, `status_id`, `meeting_date`, `type`, `length`, `agenda`, `minutes`, `notes`) VALUES
(100, 26, 3, 20, '2011-08-09 09:00:00', 'Team Meeting', '60', '<p>test</p>', '', ''),
(101, 26, 1, 20, '2011-08-26 07:47:52', 'Team Meeting', '60', '<p>testing 1</p>', '', '');

So it appears I have two records both with status_id of 20 and unit_id of 3 and 1 yet only 3 will show up.

[urtrivedi]

try to write following portion of query without single quote

meetings.unit_id IN ($session_unitidgrouping)

[Tunnleram]

That was it! :) Thanks.

Any idea why it wasn't working with single quotes?

[urtrivedi]

we must separte variables by comma to consider for comparision.
If you enclose them with quote, then whatever between quotes is taken as one variable.
a) '1,3' searches for whole 1,3 as one unit

b) 1,3 searches for two separate variables 1 and 3

c) '1','3' has same effect as b above.