0

Hi all,
I have problem getting data from mysql table . What i am trying to do is that i want to get data from specific Category-id column and search a specific word or starting letter search from Name column , suppose this structure,

-Database
---Table
------Category-id, Name

My query is

"SELECT * FROM table WHERE Name LIKE '%2012%' 
ORDER BY Date DESC
limit $offset,$records_per_page"
2
Contributors
8
Replies
9
Views
5 Years
Discussion Span
Last Post by crackme
0

This is working fine for search result from name column but not from specific catid column . I want to get result the search query from name column and also restrict the query from a specific catid column ,Sorry for my bad english . thanks in advance

Edited by crackme

0

This is my fileand its working fine for search query from name , i dont understand how to add restriction with this type of result from specific catid and i have diffrent catid's in my database.

<?php
mysql_connect ("localhost", "root",")")  or die (mysql_error());
mysql_select_db ("dmsqlnew");


$sql = "SELECT COUNT(*) as total FROM dms_docman WHERE dmname like '%2012%'";
$res = mysql_query($sql);
$count = mysql_fetch_assoc($res);


$total_records = $count["total"];
$records_per_page = 10;
$no_of_pages = ceil($total_records / $records_per_page);
$page_no = intval($_GET['pageno']);
if($page_no != 0)
{
    $page_no = $page_no - 1;
}

$offset = $page_no * $records_per_page;


$sql = "SELECT * FROM dms_docman WHERE dmname like '%2012%' 
        ORDER BY dmdate_published DESC
        limit $offset,$records_per_page";

$res = mysql_query($sql);
echo "<table border=1 id='topa'>";
    echo "<tr>";
        echo "<td>";
        echo "Cat ID";
        echo "</td>";

        echo "<td>";
        echo "Title";
        echo "</td>";

        echo "<td>";
        echo "Contents";
        echo "</td>";

        echo "<td>";
        echo "post date";
        echo "</td>";
    echo "</tr>";
while($row = mysql_fetch_assoc($res))
{
    //print_r($row);
    echo "<tr>";
        echo "<td>";
        echo $row['catid'];
        echo "</td>";

        echo "<td>";
        $dmfilename = $row['dmfilename'];
        echo "<a href='";
        echo substr($dmfilename, 6);
        echo "'>".$row['dmname']."</a>\n";
        //echo $row['dmname'];
        echo "</td>";

        echo "<td>";
        $dmfilename = $row['dmfilename'];
        echo "<a href='";
        echo substr($dmfilename, 6);
        echo "'>".$row['dmdescription']."</a>\n";
        //echo $row['dmfilename'];
        echo "</td>";

        echo "<td>";
        echo $row['dmdate_published'];
        echo "</td>";
    echo "</tr>";
}
echo "</table>";
for($loop=1;$loop<=$no_of_pages;$loop++)
{
    $url = "<a href='search.php";
    $url .= "?pageno=$loop'>";
    $url .= " ".$loop." ";
    $url .= "</a>";
    echo $url;
}
?>
0

You have to expand the WHERE clause:

SELECT * FROM dms_docman 
WHERE dmname like '%2012%' 
**AND catid like "%mysearchterm%"**
ORDER BY dmdate_published DESC
limit $offset,$records_per_page`

Edited by smantscheff

0

Thanks its working one more question what if i want to search more than one catid ?

0

OK i tried this code for more than one catid and it worked do you have any suggestion better than this or this code is fine ?

    SELECT * FROM dms_docman
    WHERE dmname like '%2012%'
    AND catid like "%mysearchterm%" OR catid like "%mysearchterm%"
    ORDER BY dmdate_published DESC
    limit $offset,$records_per_page`
0

Your code is flawed. AND has a higher precedence than OR, so you have to bracket the OR clauses for the desired results:

SELECT * FROM dms_docman
WHERE dmname like '%2012%'
AND (catid like "%mysearchterm%" OR catid like "%mysearchterm%")
ORDER BY dmdate_published DESC
limit $offset,$records_per_page`
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.