View Full Version : mysql like function help please

05-24-2011, 11:50 PM
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());
$id = $row['catID'];
$parent = $row['catParent'];
case "dvd";
$childitems = "SELECT * FROM tbl_dvds WHERE filmDepartment like '%$id%' && filmBinding = 'DVD'";
case "cd";
$childitems = "SELECT * FROM tbl_cds WHERE cdDepartment like '%$id%'";
case "game";
$childitems = "SELECT * FROM tbl_games WHERE gameDepartment like '%$id%'";
case "book";
$childitems = "SELECT * FROM tbl_books WHERE bookDepartment like '%$id%'";
$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

05-25-2011, 12:31 AM
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.

05-25-2011, 12:34 AM
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


05-25-2011, 12:50 AM
This may help:

Check the 3rd to last post.

05-25-2011, 01:05 AM
thanks mate that just had a v quick look and that looks like its what im after thank you!