Search database (Help)

Era posted this at 23:30 — 14th November 2002.

Joined: Nov 2002

Hi. I'm working on a search script that will look for any value the user inputs through a form in a mysql database. But I'm having some problems. Can any one please help me out?

<?php
If (!isset($op)) {
  Echo (\"
<form action=?op=search method=post>
Enter your search below:<br>
<input type=text name=item value=\\"Search for\\"> 
In:
<SELECT NAME=\\"area\\" TABINDEX=\\"2\\">
<OPTION SELECTED VALUE=all>All tables</OPTION>
<OPTION VALUE=\\"id\\">User ID</OPTION>
<OPTION VALUE=\\"name\\">User Name</OPTION>
<OPTION VALUE=\\"first\\">First Name</OPTION>
<OPTION VALUE=\\"last\\">Last Name</OPTION>
</SELECT>

<br>    <input type=submit value=Search>&nbsp;&nbsp;</form></font></td>
\");
}

If ($op == \"search\") {
    Echo (\"All Tables<p>\"); 

$result = (\"SELECT * FROM `cp_pages` WHERE `content` LIKE '*$item*' LIMIT 0, 30\");  
    $data = mysql_query($result);
    while ($row = mysql_fetch_array($data))
    {
                $author = $row[\"author\"];


echo mysql_error();
 
   If ($area == \"all\") {
    Echo (\"$author <br>\"); }
}
}
?>

Thanks. Check it out here.

Peter J. Boettcher posted this at 02:37 — 15th November 2002.

They have: 812 posts

Joined: Feb 2000

Era,

I'm not familiar with PHP but I don't think you have to put single quotes in your select statement:

"SELECT * FROM `cp_pages` WHERE `content` LIKE '*$item*' LIMIT 0, 30"

Try:

"SELECT * FROM cp_pages WHERE content LIKE '" + $item + "' LIMIT 0, 30"

I'm not sure about the + $item + part, just coded it like JavaScript, maybe a php person could chime in?

PJ | Are we there yet?
pjboettcher.com

zollet posted this at 02:44 — 15th November 2002.

He has: 1,016 posts

Joined: May 2002

Actually in MySQL wildcard is % not * so the correct MySQL query would be...

<?php
$data = mysql_query(\"SELECT * FROM cp_pages WHERE content LIKE '%$item%' LIMIT 0, 30\");
?>

Also I suggest you use mysql_fetch_assoc() instead of mysql_fetch_array() if you're only going to use $row["name"] and not $row[0]

Mark Hensler posted this at 07:19 — 15th November 2002.

He has: 4,048 posts

Joined: Aug 2000

For a speed boost, try using this:
"SELECT * FROM cp_pages WHERE INSTR(content,'$item') LIMIT 0, 30"

INSTR() is a basic string comparison, while LIKE uses basic pattern matching ( '_' = single char; '*' = 0 or more chars).

Also note about INSTR(): This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.

mySQL Docs:
6.3.2 String Functions: INSTR()
6.3.2.1 String Comparison Functions: LIKE

Mark Hensler
If there is no answer on Google, then there is no question.