mysql like function help please

Hi All,

I Have a function which selects records where a field is like a varible value something like this

$query = mysql_query("SELECT * FROM tbl_categories WHERE catParent like '%$cat%'")or die(mysql_error());


now this works find 90% of the time but the problem im having is that say
$cat = 19
catParent = 123344 12235 98 19 3334445

that would be returned by the query as expected but if
$cat = 19
catParent = 123344 12235 98 1267619 3334445

that would also be returned because that is also a valid result when using the like function

i am trying to ammend it so that it only returns a record if it matches the whole integer number so just 19 on its own and not a number with 19 in it,

is this possible?

my catParent field will always have several numbers stored in it seperated by a space, could i use the spaces to get this to work, so search for space19space for example???

here is my full code
function children($cat,$pageid)
{
echo "<ul>";
$query = mysql_query("SELECT * FROM tbl_categories WHERE catParent like '%$cat%'")or die(mysql_error());
while($row=mysql_fetch_array($query))
{
$id = $row['catID'];
$parent = $row['catParent'];
if(isset($pageid))
{
switch($pageid)
{
case "dvd";
$childitems = "SELECT * FROM tbl_dvds WHERE filmDepartment like '%$id%' && filmBinding = 'DVD'";
break;
case "cd";
$childitems = "SELECT * FROM tbl_cds WHERE cdDepartment like '%$id%'";
break;
case "game";
$childitems = "SELECT * FROM tbl_games WHERE gameDepartment like '%$id%'";
break;
case "book";
$childitems = "SELECT * FROM tbl_books WHERE bookDepartment like '%$id%'";
break;
}
}
$items = mysql_query($childitems);
$rows = mysql_num_rows($items);?>
<li><a href="?cat=<?php echo $id?>"><?php echo $row['catName']." (".$rows.")"?></a></li><?php
}
}


any help is greatly appreciated, thanks
Luke

Normalize your database (http://en.wikipedia.org/wiki/Database_normalization) and you will see this problem disappear.

Otherwise, you could do a regexp match instead. Without a normalization, you will lose the benefit of an index.

hi mate,

it would take too much time at the moment to normalise this table all the others are done but not this one.

when you say use a regexp to acheive this can u please provide an example and where/how to incoroperate it into my code please

thanks
Luke

This may help:
http://www.webdeveloper.com/forum/showthread.php?t=83515

Check the 3rd to last post.

thanks mate that just had a v quick look and that looks like its what im after thank you!