Search script not working

shail_arya · July 1, 2014, 7:20am

Hello,

I have written a search script but it is not giving any output, please review and help me in fixing the error please.

<?php
	$conx= mysqli_connect("localhost","root","sultan","search");

if(isset($_POST['submit']))
{
	$output="";
		$search = $_POST['search'];
	$sql = "SELECT firstname, lastname FROM data WHERE firstname LIKE '%search%' AND lastname LIKE '%search%'  ";
$user_query = mysqli_query($db_conx, $sql);
// Now make sure that user exists in the table
$numrows = mysqli_num_rows($user_query);
if($numrows < 1){
	echo "That user does not exist or is not yet activated, press back";
    exit();	
}
while ($row = mysqli_fetch_array($user_query, MYSQLI_ASSOC)) {
	$firstname = $row['firstname'];
	$lastname = $row['lastname'];
echo  "fist user is ".$lastname;}
	}
?>

<head>

</head>
<body>
<form>
Enter parameter to search:<input type="text" name="search" placeholder="enter parameter">
<input type="submit" value="search">


</form>
</body>

shail_arya · July 1, 2014, 9:38am

Hello, i made some changes on a friend suggestion and is working now. here again i am stuck with two problems.
the result being dispalyed are normal text i.e firstname.lastname. I want it to display in same format but when user clicks on it, he should be forwarded to the profile of that user. Ho can this be achieved.
Second, the results for search engine are true only if you enter by first name or last name,it isnt giving any result if you write full name. How can i make it to search full name also.

<?php
                        if(isset($_POST['completedsearch']))
                        {
                                $term = $_POST['query'];
                                $mysql = mysqli_connect("localhost","root","sultan","buddy");
                                $sql = "SELECT * FROM users WHERE firstname LIKE '%{$term}%' OR lastname LIKE '%{$term}%' OR email LIKE '%{$term}%'  ";
							$qu = mysqli_query($mysql,$sql);	//selects the row that contains ANYTHING like the submitted string
                                echo "
                                                <div>Results;</div>
												<hr>
                                                                                                             ";
                                while($row = mysqli_fetch_array($qu, MYSQLI_ASSOC))
                                           {
											   $firstname=$row['firstname'];
                                               $lastname = $row['lastname'];
											   $email = $row['email'];
											  echo "$firstname"." "."$lastname".","."<br>";
                                }
                                							
                        }
                ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form method="post" action="search.php">
<input type="text" value="Search..." name="query" />
<input type="submit" value="Find" name="completedsearch" />
</form>
</body>
</html>

Thanks

droopsnoot · July 1, 2014, 11:02am

For the first, wrap the name in a link (a href=) that contains whatever is needed to identify the user. If you have an id field in your database, you’d typically use that. The target page can then use that ID to access the specific record.

For the second, the issue is that you’re comparing either first name or last name to the search term. If the user enters the full name, you’d also need to compare it against “first name + last name” and “last name + first name” depending on which way around the seacher enters it. I’m not sure of the SQL syntax for that, but it would be something like:


$sql = "SELECT *, concat(firstname, " ", lastname) as name1, concat(lastname, " ", firstname) as name2 FROM users WHERE firstname LIKE '%{$term}%' OR lastname LIKE '%{$term}%' OR email LIKE '%{$term}%'  or name1 like '%{$term}%' or name2 like '%{$term}%'";

shail_arya · July 1, 2014, 12:10pm

Hi, tired your suggestion for full name search but it is not giving any result yet,…

same in link option. Can you explain me with a short example because when i am using (=a=href), its giving an error.

Drummin · July 1, 2014, 3:32pm

Links are made like so changing path and GET keys and values as needed.

<a href="pagename.php?id=<php echo $user_id;?>"><?php echo $username;?></a>

droopsnoot · July 1, 2014, 5:27pm

Yes, thinking again it will only return results if they type in the full name, or just the centre section of the full name. If they type in “red smi” and you have a row called “Fred Smith”, it will work, but if you have “Frederick Asmil” it won’t.

shail_arya · July 1, 2014, 6:23pm

Hi Drummin,

your script is showing error…

this is error: ( ! ) Parse error: syntax error, unexpected ‘<’ in C:\wamp\www\buddy\search.php on line 19
while using : <a href=“pagename.php?id=<php echo $user_id;?>”><?php echo $username;?></a>

Drummin · July 1, 2014, 7:24pm

Yes. Corrected line

<a href="pagename.php?id=<?php echo $user_id;?>"><?php echo $username;?></a>

shail_arya · July 1, 2014, 7:36pm

Hi Drummin,

i already made that change while applying to my script, then also it is showing error.
this is error: ( ! ) Parse error: syntax error, unexpected ‘<’ in C:\wamp\www\buddy\search.php on line 19

Drummin · July 1, 2014, 9:57pm

Are you applying this link in a php echo? Then you would not include the php start and end tags, the echo or the semi-colon. Just place the variables in the echo statement.

Really all I was point out was <a href=“”></a> as you said you used (=a=href) and it was giving you an error. see W3C

Also you are using different connection names.

$conx= mysqli_connect("localhost","root","sultan","search");

AND

$user_query = mysqli_query($db_conx, $sql);

Drummin · July 2, 2014, 3:55am

For the fun of it, here’s a sample page. Querying for user_id, firstname and lastname and email also as a search parameter. Change fields as needed.

<?php 
if(isset($_POST['submit'])){
	//Add suffix for the fun of it
	function ordinal_suffix($num){
	    if($num < 11 || $num > 13){
	         switch($num % 10){
	            case 1: return 'st';
	            case 2: return 'nd';
	            case 3: return 'rd';
	        }
	    }
	    return 'th';
	}
	//Path to user detail page
	$page = "page.php";
	$output="";
	$search = mysqli_real_escape_string($db_conx, trim($_POST['search']));
	$cnt=0;
	$sql = "SELECT user_id, CONCAT(lastname, ', ', firstname) AS name FROM users WHERE firstname LIKE '%{$search}%' OR lastname LIKE '%{$search}%' OR email LIKE '%{$search}%'";
	$user_query = mysqli_query($db_conx, $sql);
	// Now make sure that user exists in the table
	$numrows = mysqli_num_rows($user_query);
	if($numrows < 1){
		$output .= "That user does not exist or is not yet activated, press back";
	}else{
		while ($row = mysqli_fetch_array($user_query, MYSQLI_ASSOC)) {
			$cnt++;
			$suf = ordinal_suffix($cnt);
			$output .= $cnt . $suf . ' user is <a href="' . $page . '?id=' . $row['user_id'] . '">' . $row['name'] . "</a><br />\\r";
		}
	}
} 
?>
<html>
<body>
<form action="" method="post">
Enter parameter to search:<input type="text" name="search" placeholder="enter parameter" />
<input type="submit" name="submit" value="search" />
</form>
<br />
<?php
if(!empty($output)){
	echo $output;
}   
?>
</body>
</html>

shail_arya · July 2, 2014, 10:43am

Thanks Drummin, i used the link id from your script suggestion. But the problem i am facing now is that when i click the link ,instead of directing me to that particular i page throws me out.

Drummin · July 2, 2014, 3:38pm

What does your link code look like and does the page url exist?

Drummin · July 2, 2014, 4:03pm

As per DroopSnoot’s suggestion, this version should account for both first and last names or partial parts of their names being entered
For example: John Doe

Any single part of first or last name (or email) will match, jo,do, oe, oh, @ etc.
john d will match.
doe j will match.
John Doe will match.
Doe John will match.

But Not a partial on both like joh d

Here’s the modified $sql.

	$sql = "SELECT user_id, CONCAT(lastname, ', ', firstname) AS name FROM users
	WHERE firstname LIKE '%{$search}%' OR
	lastname LIKE '%{$search}%' OR
	CONCAT(lastname, ', ', firstname) LIKE '%{$search}%' OR
	CONCAT(firstname, ' ', lastname) LIKE '%{$search}%' OR
	CONCAT(lastname, ' ', firstname) LIKE '%{$search}%' OR
	email LIKE '%{$search}%'";

droopsnoot · July 2, 2014, 6:11pm

Exactly what happens? Does it give an error message, in which case what does it say? Do you get a blank screen? Are your id fields named as per Drummin’s sample or have you changed the code to suit your column names?

Drummin · July 2, 2014, 6:18pm

If you are using my link line that I posted, have you edited the $page variable to what you are using?


    //Path to user detail page
    $page = "page.php";