...

View Full Version : mysql like function help please



LJackson
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());
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

Fou-Lu
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.

LJackson
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

thanks
Luke

shadowmaniac
05-25-2011, 12:50 AM
This may help:
http://www.webdeveloper.com/forum/showthread.php?t=83515

Check the 3rd to last post.

LJackson
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!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum