Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Exclamation 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

    Code:
    $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
    PHP 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

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Normalize your database 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.

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    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

  • #4
    Regular Coder
    Join Date
    Apr 2005
    Location
    Ohio
    Posts
    254
    Thanks
    1
    Thanked 63 Times in 63 Posts
    This may help:
    http://www.webdeveloper.com/forum/sh...ad.php?t=83515

    Check the 3rd to last post.

  • Users who have thanked shadowmaniac for this post:

    LJackson (05-25-2011)

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,099
    Thanks
    297
    Thanked 12 Times in 12 Posts
    thanks mate that just had a v quick look and that looks like its what im after thank you!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •