Search Logic Help

[ballouta]

Hello

I am looking here for a 'scenario' only not for any full code (till now)

i am searching more than 14 tables, all of them contain text and articles,

surely i cant predict at all how many rows i will get from each table,

what i want to do is that to make pagination and set certain number of results in each page to avoid long scroll,

you know I might get 1 result from the first table and 40 from the second, and what if i want (for example) only 25 results to be shown in each page,

how i will save and maintain all the results and divide them?

 

Thank You

[premiso]

With basic pagination:

 

http://www.phpfreaks.com/tutorial/basic-pagination

[AdRock]

If you're trying to search the database for keywords and you're using mysql then look at using mysql full text

[ballouta]

Hello

I am trying to follow this tutorial (http://devzone.zend.com/article/1304) but i have a warning with NO results:

(Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /XXX/Search/index2.php on line 25)

Surely i am searching for a word that I see in the body in one of the articles...

I altered my table in the begining to be FULLTEXT and i had NO problem with this step.

 

My PHP code is exactly this one:

<?php

include ("../admin/global.inc.php");
$keyword = $_POST['keyword'];

    $sql = "SELECT *, MATCH(`title`, `body`) AGAINST(`$keyword`) AS score 
            FROM `news_hiv_en` 
		WHERE MATCH(`title`, `body`) AGAINST($keyword) 
            ORDER BY score DESC "; 
   $rest = mysql_query($sql); 
?> 
<table> 
<tr><td>SCORE</td><td>TITLE</td><td>ID#</td></tr> 
<?php 
        while($row = mysql_fetch_array($rest)) {  //this is line 25
            echo "<tr><td>{$sql2['score']}</td>"; 
            echo "<td>{$sql2['title']}</td>"; 
            echo "<td>{$sql2['id']}</td></tr>"; 
        } 
        echo "</table>"; 

?> 

 

 

 

Please Help

Thank You

[Ken2k7]

Unless $keyword is a table name or a column name, don't use back ticks.

[ballouta]

I removed the back ticks but still the same problem!

[Ken2k7]

Run the SQL in MySQL and see what error it gives you.

[ballouta]

yes I have problems with the query

 

keyword = disease

 

The first problem is Unknown column in the first line: SELECT *, MATCH(`title`, `body`) AGAINST(disease) AS score

If I replace disease with 'body' for example, I get another problem: Incorrect arguments to AGAINST

 

I think I dont understand this query.

There are two against may you explain them for me please?

 

SELECT *, MATCH(`title`, `body`) AGAINST(XXX) AS score

            FROM `news_hiv_en`

    WHERE MATCH(`title`, `body`) AGAINST(XXX)

            ORDER BY score DESC

 

Thank You

 

[Ken2k7]

It's a string. Use quotes. AGAINST('disease')

[ballouta]

YES! You caught the problem, and the result was correct for the 'disease' keyword.

 

one small thing is that i searched for the keyword 'HIV' but it didnt show any result! where I can see this word in the title of many articles for sure...

 

The second problem is that there's a problem in the PHP (i think) because when i searched for 'disease' the php didnt show any result.

<?php

include ("../admin/global.inc.php");
$keyword = $_POST['keyword'];

    $sql = "SELECT *, MATCH(`title`, `body`) AGAINST('$keyword') AS score 
            FROM `news_hiv_en` 
		WHERE MATCH(`title`, `body`) AGAINST('$keyword') 
            ORDER BY score DESC "; 
   $rest = mysql_query($sql); 
?> 
<table> 
<tr><td>SCORE</td><td>TITLE</td><td>ID#</td></tr> 
<?php 
        while($row = mysql_fetch_array($rest)) { 
            echo "<tr><td>{$sql2['score']}</td>"; 
            echo "<td>{$sql2['title']}</td>"; 
            echo "<td>{$sql2['id']}</td></tr>"; 
        } 
        echo "</table>"; 

?> 

 

May Thanks

[Ken2k7]

What's $sql2?

[andrewgauger]

$sql = "SELECT *, MATCH(`title`, `body`) AGAINST('$keyword') AS score

            FROM `news_hiv_en`

WHERE MATCH(`title`, `body`) AGAINST('$keyword')

            ORDER BY score DESC ";

 

I would suspect should be:

$sql = "SELECT * FROM `news_hiv_en` 
		WHERE MATCH(`title`, `body`) AGAINST(mysql_real_escape_string($keyword)) 
            ORDER BY score DESC "; 

AND

 

            echo "<tr><td>{$sql2['score']}</td>";

            echo "<td>{$sql2['title']}</td>";

            echo "<td>{$sql2['id']}</td></tr>";

 

            echo "<tr><td>{$row['score']}</td>"; 
            echo "<td>{$row['title']}</td>"; 
            echo "<td>{$row['id']}</td></tr>"; 

 

Try the second part fist though.

[Ken2k7]

I didn't know you can inline a PHP function into a string and still have it be functional. 

[andrewgauger]

$sql = "SELECT * FROM `news_hiv_en` 
		WHERE MATCH(`title`, `body`) AGAINST(".mysql_real_escape_string($keyword).") 
            ORDER BY score DESC "; 

 

Good catch.  Sorry.

[ballouta]

Thanks to ALL for helping me in this search code.

you are kind,

[ballouta]

one more question please, is it possible to use the same query for more than ONE table which have the same structure (title & body)? How?

 

Thank You

[andrewgauger]

replace table_name with:

 

table_name, table_name2

 

so

 

select * from table

select * from table, table2

 

that is the same thing as:

select * from table inner join table2

 

What that does it combines the two tables and returns results from both.  Is that what you are looking for?

[ballouta]

Thank you, this is what i am looking for.