|
| Hi,
My website has all of its articles stored in a mysql database. I want to provide a search that visitors to the site can use.
The code i have so far:
$que = $_GET[q];
//$que = mysql_real_escape_string ($que );
$cq1 = "select * from articles where (Headline like '%$que%' or Articletext like '%$que%'  ";
$cr1 = mysql_query ($cq1 ) or die (mysql_error ( ) );
$col = "c_sub1";
while ($ca1 = mysql_fetch_array ($cr1 ) )
{
if ($col == "c_sub1" { $col = "c_sub2"; }else{ $col = "c_sub1"; }
$aextract = get_some_words ($ca1[Articletext] );
$page_content .= "<tr><td class=\"".$col."\"><a href=\"index.php?t=1&a=".$ca1[ArticleID]."\">".$ca1[Headline]."</a><br>".$aextract."</td></tr>";
}
echo "<table width=\"100%\">".$page_content."</table>";
Now, that works, sort of. The problem is that it only seems to accept one word searchs. So, does anyone know how to adapt it for multiword searchs?
Thanks in advance...
Note: $cq1 has a one at the end, not an "l" |
|
| |
|
Administrator H/PC Oracle Posts: | 18,033 |
Location: | United Kingdom | Status: | |
| Check in your database that it isn't parsing the " " character as "& nbsp;" or " " (which looks like a space but isn't)
Don't use the SELECT *, it's sloppy
I also recommend that you upper case your SQL logic (SELECT, FROM, WHERE, OR, IF, LIKE) it makes it easier to read. |
|
| |
|
| They are uppercase, the forum changed them to lower case for some reason. I use every field in the table, so i thought select * was suitable (only 3 fields ).
Quote Check in your database that it isn't parsing the " " character as "& nbsp;" or " " (which looks like a space but isn't)
I dont think it is. The page gets called with ?q=some+text. The database itself has spaces stored as " ".
EDIT: It did it again! In the first paragraph of this post the select was is in block capitals. Edited by chazco 2006-04-28 2:14 PM
|
|
| |
|
Administrator H/PC Oracle Posts: | 18,033 |
Location: | United Kingdom | Status: | |
| * adds a processing overhead as the database has to request a list of fields from the table.
Try debugging the SQL string and the querystring into the browser, see what the database is being asked to do.
All my caps came out correctly in my post above, so may be it's your browser. |
|
| |
|
| Quote * adds a processing overhead as the database has to request a list of fields from the table.
Ah, good point. I'll update it.
Quote Try debugging the SQL string and the querystring into the browser, see what the database is being asked to do.
er... how? sorry, i'm new (ish ) to this
Quote All my caps came out correctly in my post above, so may be it's your browser.
Probably. I'm using Firefox 1.07 on Kubuntu 5.10. |
|
| |
|
Administrator H/PC Oracle Posts: | 18,033 |
Location: | United Kingdom | Status: | |
| Perform a response readout on the two variables:
<p>".$cq1."</p>
<p>".$que."</p> |
|
| |
|
| Using query of test pc
QUE:
test pc
(that's a space in the middle, not a non-break space )
CQ1:
select * from articles where (Headline like '%test pc%' or Articletext like '%test pc%'
Looks okay?
(Thanks for the help btw ) Edited by chazco 2006-04-28 4:49 PM
|
|
| |
|
Administrator H/PC Oracle Posts: | 18,033 |
Location: | United Kingdom | Status: | |
| Try this instead:
SELECT * FROM articles WHERE (Headline LIKE '*test pc*' OR (Articletext LIKE '*test pc*' |
|
| |
|
| Okay, i'll try it.
(ignore this: TEST TEST SELECT)
EDIT: I downgraded Firefox and its letting me use capitals, so must be a bug in the other version...
Edited by chazco 2006-04-29 2:58 AM
|
|
| |
|
| Nope, didnt work (it also stopped single word seachs from working). |
|
| |
|
Administrator H/PC Oracle Posts: | 18,033 |
Location: | United Kingdom | Status: | |
| Alright, didn't like boolean, try:
$que = $_GET[q];
//$que = mysql_real_escape_string ($que );
$que = "%".$que."%";
$cq1 = "select * from articles where (Headline like '$que' || Articletext like '$que' ";
$cr1 = mysql_query ($cq1 ) or die (mysql_error ( ) );
$col = "c_sub1";
while ($ca1 = mysql_fetch_array ($cr1 ) )
{
if ($col == "c_sub1" { $col = "c_sub2"; }else{ $col = "c_sub1"; }
$aextract = get_some_words ($ca1[Articletext] );
$page_content .= "<tr><td class=\"".$col."\"><a href=\"index.php?t=1&a=".$ca1[ArticleID]."\">".$ca1[Headline]."</a><br>".$aextract."</td></tr>";
}
echo "<table width=\"100%\">".$page_content."</table>"; |
|
| |
|
| That works with single word searchs, but still no luck with multiword ones. |
|
| |
|
Administrator H/PC Oracle Posts: | 18,033 |
Location: | United Kingdom | Status: | |
| It should fliping well work with * 
I'll have to see if I can spend some time looking at PHP syntax, I'm not a PHP programmer. |
|
| |
|
| What you want to do is use:
$words = explode (" ",$_GET['q'] ) //to make an array of the words (delimited by space ) then
if (is_array ($words ) ){
$where = "";
foreach ($words as $key => $val ){
if (strcmp ($where,"" == 0 ){
$where .= "where ";
}
else {
$where .= " and ";
}
$where .= " (Articletext like '%%' or Headline like '%%' ";
}
}
Let me know if that makes sense.  Edited by abyssknight 2006-04-29 10:48 AM
|
|
| |
|
| I think i see what you mean. Split up the query and put AND in between the words. I'll try it now, should take about 5 minutes.
Edited by chazco 2006-04-29 11:06 AM
|
|
| |
