A simple search php script needed.

[Jack-]

Hey guys, I need a searching script, that'll search through a table in mysql database using some keywords.

Let's say I've a table field name "name", and I've got "David", "Leon" etc inserted to that field. So how do I make my members, simply type in their name on the form, like "Da", then press on the search button and there'll be list of result, displaying the names like "David", "Darren", "Darrel" etc..

I've google it, but yeah, can't find any working, easy to edit php script for it.
Anyone could help me in this?

[rangana]

$inp = $_POST['input']; // For post data
// $inp = $_GET['input']; // For get data
mysql_query("SELECT * FROM table WHERE (keywords LIKE '%".mysql_real_escape_string($inp)."%')") or die(mysql_error());

For further reading:
http://dev.mysql.com/doc/refman/5.0/...-matching.html
http://dev.mysql.com/doc/refman/5.0/...functions.html

[Jack-]

Hey, thanks for replying yeah.

So, from what you've gave me, I made this..

<?php
mysql_connect("localhost","root","password");
$inp = $_GET['input'];
mysql_query("SELECT * FROM user WHERE name LIKE '%".$name."%' ") or die(mysql_error());
$name = $_POST["name"];
if($name==NULL) {
echo("Please enter keywords to begin your search
<meta http-equiv=\"Refresh\" content=\"3; URL=search.php\">");
}else{
echo("$name was successfully search!
<meta http-equiv=\"Refresh\" content=\"3; URL=search.php\">");
}
?>

<form name="form" action="search.php" method="get">
<input name="name" type="text">
<input type="submit" name="search" value="Search">
</form>

It's not working though, how could I make it working?
And I wanna make it like, when it shows the result, I can somehow code a layout and so when a keyword is search, the result page will be custom one, rather than just a plain one.

Thanks yeah, I just started learning this so is still lousy at it.

[rangana]

What are you trying to do? Why are you refreshing the page?

Also, is the PHP script you've provided inside script.php?

You should selected a database. You only established a connection:

mysql_connect("localhost","root","password"); 

You should also check if connection was really established:

$connect=mysql_connect("localhost","root","password");
if(!$connect)
	print "Error: Could not connect to DB"; 

Giving us info on the error you've received will help too.

[Jack-]

Well, I'm trying to make search script that's able to search through my database and display the result.

mysql_connect("localhost","root","password"); 

Is to connect to my database yeah, and I selected my table..

mysql_query("SELECT * FROM user WHERE name LIKE '%".$name."%' ") or die(mysql_error()); 

The '%".$name."%' was suppose to be extracted from the form.. (Not sure if this is right)

$name = $_POST["name"];
<form name="form" action="search.php" method="get">
<input name="name" type="text">
<input type="submit" name="search" value="Search">
</form>

It's like, I've this form for my users to type their keyword for searching in, then when they submit, the result will be displayed using the GET method.

if($name==NULL) {
echo("Please enter keywords to begin your search
<meta http-equiv=\"Refresh\" content=\"3; URL=search.php\">");
}else{
echo("$name was successfully search!
<meta http-equiv=\"Refresh\" content=\"3; URL=search.php\">");
}

This was added myself, which I've learnt from adding users into my database. If there's no value on the input=name, then it will display "Please enter keywords to begin your search" and autorefresh back to the main page.
But, if there's a value found, it will then say "$name (as in the value submitted) was successfully search!", which I wanna change it to display the result.

I know I'm getting more and more complicated, but yeah, simply I just need a form, then when my user type some search keywords and submit it, the result will then be shown. Like what I said on my first post, I need to be able to customize the result page too.

EDIT: Answering to your question, no I wanna it to be all at the same page, for easier editing.

[rangana]

Might work:

<?php
$connect=mysql_connect("localhost","root","password"); // Establish a connection
mysql_select_db('database_name'); // Name of your DB
if(!$connect) // If connection not established
    print 'Could not connect to the database'; // Show an error
    
if(isset($_GET['search'])) // If it's submitted
    {
    $inp = Clean($_GET['inpname']); // Clean my input
    $sQuery=mysql_query("SELECT name FROM user WHERE name LIKE '%".$inp."%' ") or die(mysql_error()); // mySql query
    mysql_query($sQuery) or die(mysql_error()); // If query fail, let me know the error
    if(mysql_affected_rows()===0) // If no match found
        echo "{$inp} is not in our database."; // Let me know it is'nt found in the table
    else
        {
        echo "<p>{$inp} was successfully searched.</p>"; // Yes, the query worked
        while($row=mysql_fetch_array($sQuery)) // Loop through the query results
            echo "{$row[0]}<br>"; // Show the results
        } // End of the else statement
    } // End of the if statement
    
function clean($str) // Clean my input
{
    return mysql_real_escape_string(strip_tags(trim($sStr))); // Remove traces of injection
}
?>

<form name="form" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
<input name="inpname" type="text">
<input type="submit" name="search" value="Search">
</form>

Please read through the comments, it'll help you explain your desire.

[Jack-]

Hey thanks for that, I could understand all of that now, but it isn't working for me. I got this error..

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #3' at line 1

When I searched for a name, I'm using MySQLi if I'm not wrong.

mysql
Client API version 	5.0.51b

mysqli
Client API library version 	5.0.51b
Client API header version 	5.0.51a

[rangana]

It was my bad. There was a mistake in the syntax I've given you.

Try this instead:

<?php
$connect=mysql_connect("localhost","root","password"); // Establish a connection 
mysql_select_db('database_name',$connect); // Name of your DB 

if(!$connect) // If connection not established 
    print 'Could not connect to the database'; // Show an error 
     
if(isset($_GET['search'])) // If it's submitted 
    { 
    $inp = Clean($_GET['inpname']); // Clean my input 
    $sQuery="SELECT column FROM table WHERE column LIKE '%".$inp."%' "; // mySql query 
    $r = mysql_query($sQuery) or die(mysql_error()); // If query fail, let me know the error 
    if(mysql_affected_rows()===0) // If no match found 
        echo "{$inp} is not in our database."; // Let me know it is'nt found in the table 
    else 
        { 
        echo "<p>{$inp} was successfully searched.</p>"; // Yes, the query worked 
        while($row=mysql_fetch_array($r)) // Loop through the query results 
            echo "{$row[0]}<br>"; // Show the results 
        } // End of the else statement 
    } // End of the if statement 
     
function Clean($str) // Clean my input 
{ 
    return mysql_real_escape_string(strip_tags(trim($sStr))); // Remove traces of injection 
}
?>

[Jack-]

It works, but when I search for a name, it shows all the data that i have in my database rather than just those name that contains the keyword.
Anyway, sorry for late reply, I was making WordpressMu working with my vBulletin forum.

[rangana]

There was a typo in the script. See if removing highlighted helps:

return mysql_real_escape_string(strip_tags(trim($sStr)));