create simple search for db

pdn

Hello,

I would like to know how I might create a simple text search to display results from my db. This is what I currently have in place it displays all results from db in order.

<?php
$username="root";
$password="pass";
$hostname="localhost";

// Connect to Database
$dbh=mysql_connect($hostname, $username, $password)
	or die("Unable to connect to Database");
$selected=mysql_select_db("offenderdb",$dbh)
	or die("Unable to select offenderdb");

$result = mysql_query("SELECT ID, Name, AKA, DOB, Address, Conviction, Description, Victim, Comments, concat('<img width=320 height=240 src=\"./images/',sexoffenders.Pic, '\" border=\"0\">') AS Picture FROM sexoffenders"); 

if (mysql_num_rows($result) <= 0) { 
  echo '<h5>NO RESULTS FOUND</h5>'; 
} else 

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))

{

?>
<br>
<table border="1" cellspacing="5" width="720">
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Name/AKA:</font></th>
<td>
<?php print $row{'AKA'}. print $row{'Name'}."<br>"; ?>
</td><td rowspan="7" width="340"><?php print $row{'Picture'}."<br>"; ?></td></tr>
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Address:</font></th>
<td><?php print $row{'Address'}."<br>"; ?>
</tr>
<tr><th align="left" width="50"><font face="Verdana" size="2" color="#0000FF">D.O.B.:</font></th>
<td><?php print $row{'DOB'}."<br>"; ?>
</tr>
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Description:</font></th>
<td><?php print $row{'Description'}."<br>"; ?>
</tr>
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Conviction:</font></th>
<td>
<?php print $row{'Conviction'}."<br>"; ?>
</td></tr>
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Victim:</font></th>
<td><?php print $row{'Victim'}."<br>"; ?>
</tr>
</table>
</br>
<?php
}

mysql_close($dbh);

?>

paulnaj

Hi pdn,

It's a pretty big question ... there are lot's of possibilities and considerations. In the end, you need to provide a box for the user's search text, and a WHERE clause in your SQL.

This will get you going ...

<?php
$username="root"; 
$password="pass"; 
$hostname="localhost"; 

// Connect to Database 
$dbh = mysql_connect($hostname, $username, $password) 
    or die("Unable to connect to Database");
$selected = mysql_select_db("offenderdb", $dbh)
    or die("Unable to select offenderdb");
?>
<html>
<head>
<title></title>
</head>
<body>
<!-- Search form -->
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
<input type="text" name="search_text" size="10" maxlength="10"><input type="submit">
</form>
<?php
// Build the standard sql
// Notice I've taken out the compliated CONCAT() and just used `Pic` in the HTML
$sql = "
	SELECT ID, Name, AKA, DOB, Address, Conviction, Description, Victim, Comments, Pic 
	FROM sexoffenders
";

// If search_text has been sent ...
if(isset($_GET['search_text'])){
	// Add a WHERE clause. There are lot's of different ways to search ... here's one.
	$sql .= "
	WHERE CONCAT(Name,' ',AKA,' ',Address,' ',Conviction,' ',Description,' ',Victim,' ',Comments,' ') LIKE '%".$_GET['search_text']."%'
";
}
$result = mysql_query($sql);

if(mysql_num_rows($result) <= 0){ 
  echo '<h5>NO RESULTS FOUND</h5>'; 
} else {
	while($row = mysql_fetch_array($result,MYSQL_ASSOC)){
?> 
<br> 
<table border="1" cellspacing="5" width="720"> 
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Name/AKA:</font></th> 
<td> 
<?php print $row{'AKA'}. print $row{'Name'}."<br>"; ?> 
</td><td rowspan="7" width="340"><img width="320" height="240" src="./images/<?php print $row{'Pic'}; ?>" border="0"><br></td></tr> 
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Address:</font></th> 
<td><?php print $row{'Address'}."<br>"; ?> 
</tr> 
<tr><th align="left" width="50"><font face="Verdana" size="2" color="#0000FF">D.O.B.:</font></th> 
<td><?php print $row{'DOB'}."<br>"; ?> 
</tr> 
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Description:</font></th> 
<td><?php print $row{'Description'}."<br>"; ?> 
</tr> 
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Conviction:</font></th> 
<td> 
<?php print $row{'Conviction'}."<br>"; ?> 
</td></tr> 
<tr><th align="left" width="100"><font face="Verdana" size="2" color="#0000FF">Victim:</font></th> 
<td><?php print $row{'Victim'}."<br>"; ?> 
</tr> 
</table> 
</br> 
<?php 
	} 
	mysql_close($dbh); 
}
?>
</body>
</html>

Paul 🙂

videxx

gud day to you..

it has been suggested before for you to create a text box for search input.. by right, to search sometin, u need to get the input from the user by having the text box and pass the variable to be searched and compared to the database..

have u tried it? is it success? 😉

i have mine too, and it's more advanced than jus a normal search.. my search page can search from various fields from various linked tables with extra 3 options: Exact words, All words and Any words..
thus i would say, that is an advanced search..

from the code given earlier, it shud be sufficient enuf to cater the search keyword.. but, if u are interested to know how i did the advanced search, u may request it from me.. early picture for you.. it involves longer codes and functions.. trust me, i did it!

Best wishes..

videxx 😃

pdn

For my purposes this search string as displayed in the above code worked perfectly...

Thank you very much

I do have a question though for some reason my AKA field is attaching the number 1 behind whatever text I type in any thoughts on that?

 
<?php 
$username="root";  

$password="prov2711";  

$hostname="localhost";  

// Connect to Database 
$dbh = mysql_connect($hostname, $username, $password)  

    or die("Unable to connect to Database"); 
$selected = mysql_select_db("offenderdb", $dbh) 
    or die("Unable to select offenderdb"); 
?> 
<html> 
<head> 
<title></title> 
</head> 
<body> 
<!-- Search form --> 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get"> 
<input type="text" name="search_text" size="50" maxlength="50"><input type="submit"> 
</form> 
<?php 
// Build the standard sql 
// Notice I've taken out the compliated CONCAT() and just used `Pic` in the HTML 
$sql = " 
    SELECT ID, Name, AKA, DOB, Address, Conviction, Description, Victim, Comments, Pic 
    FROM sexoffenders 
"; 

// If search_text has been sent ... 
if(isset($_GET['search_text'])){ 
    // Add a WHERE clause. There are lot's of different ways to search ... here's one. 
    $sql .= " 
    WHERE CONCAT(Name,' ',AKA,' ',Address,' ',Conviction,' ',Description,' ',Victim,' 

',Comments,' ') LIKE '%".$_GET['search_text']."%' 
"; 
} 
$result = mysql_query($sql); 

if(mysql_num_rows($result) <= 0){  

  echo '<h5>NO RESULTS FOUND</h5>';  

} else { 
    while($row = mysql_fetch_array($result,MYSQL_ASSOC)){ 
?>  

<br> 
<table border="1" cellspacing="5" width="720"> 
<tr><th align="left" width="100"><font face="Verdana" size="2" 

color="#0000FF">Name/AKA:</font></th> 
<td> 
<?php print $row{'AKA'}. print $row{'Name'}."<br>"; ?>  

</td><td rowspan="7" width="340"><img width="320" height="240" src="./images/<?php print 

$row{'Pic'}; ?>" border="0"><br></td></tr> 
<tr><th align="left" width="100"><font face="Verdana" size="2" 

color="#0000FF">Address:</font></th> 
<td><?php print $row{'Address'}."<br>"; ?>  

</tr> 
<tr><th align="left" width="50"><font face="Verdana" size="2" 

color="#0000FF">D.O.B.:</font></th> 
<td><?php print $row{'DOB'}."<br>"; ?>  

</tr> 
<tr><th align="left" width="100"><font face="Verdana" size="2" 

color="#0000FF">Description:</font></th> 
<td><?php print $row{'Description'}."<br>"; ?>  

</tr> 
<tr><th align="left" width="100"><font face="Verdana" size="2" 

color="#0000FF">Conviction:</font></th> 
<td> 
<?php print $row{'Conviction'}."<br>"; ?>  

</td></tr> 
<tr><th align="left" width="100"><font face="Verdana" size="2" 

color="#0000FF">Victim:</font></th> 
<td><?php print $row{'Victim'}."<br>"; ?>  

</tr> 
</table> 
</br> 
<?php  

    }  

    mysql_close($dbh);  

} 
?> 
</body> 
</html>

videxx

AKA field is attaching the number 1 behind whatever text I type

can u explen to me in details? what is ur input? what happend? what is the result? what went wrong actually?

i'm sorry i couldnt understand ur problem.. :rolleyes: i'll help whenver i can..

Regards, videxx

pdn

If I enter something in my db...

My php result is something1

it is attaching the numeral 1 behind my text in the AKA field... don't know why

Thanks

paulnaj

Hi pdn,

I've noticed that there are a couple of closing tags (</td>, for example) missing in your table. You should fix those anyway.

But your problem is this line ...

<?php print $row{'AKA'}. print $row{'Name'}."<br>"; ?>

... see if you can work out what the problem is yourself.

Paul 🙂

videxx

Dear pdn..

i would like to say few things here to you..

first, quoted from paulnaj

But your problem is this line ...

<?php print $row{'AKA'}. print $row{'Name'}."<br>"; ?>

... see if you can work out what the problem is yourself.

i can see myself too where the code goes wrong.. can u figure it?

second..

If I enter something in my db... My php result is something1

my questions are, where do u actually made the data entry? directly into MySQL? or do u have a PHP page to add the data? because this problem can possiblly made by either one, ur MySQL has the problem or ur adding new data (the PHP page) gave u the problem.. u shud tell us in detail k..?
cuz i dun see any process of adding new data from the code u have pasted before this.. i wonder where it might be..

hope u can give us more details..

cherios~~

videxx 🆒

videxx

from paulnaj..

I've noticed that there are a couple of closing tags (</td>, for example) missing in your table.

u are very careless bout the tags.. unclosed or unbalance tags could effect your process flow you know..

shud u make sure that all tags are closed k..

regards.. videxx 😃

pdn

yes I realized that now... I have sinced fixed my code and all is working perfectly

Thanks so much for all the help 🙂

pdn

Well... I thought all was OK but am running into a little trouble.

My search function isn't working properly I will do a search where I know there should be multiple results but it just shows one result.

here is the code

<!-- Search form --> 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get"> 
<input type="text" name="search_text" size="50" maxlength="50"><input type="submit"> 
</form> 
<?php 
$sql = " 
    SELECT Name, AKA, DOB, Address, Conviction, Description, Victim, Comments, Pic 
    FROM sexoffenders 
"; 

// If search_text has been sent ... 
if(isset($_GET['search_text'])){ 
    $sql .= " 
    WHERE CONCAT(Name,' ',AKA,' ',Address,' ',Conviction,' ',Description,' ',Victim,' ',Comments,' ') LIKE 

'%".$_GET['search_text']."%' 
"; 
}

Thanks in advance for any help

paulnaj

Got any thoughts as to why?

pdn

I've looked everything over and don't have a clue.

Do you see something out of place?

Thanks

videxx

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
<input type="text" name="search_text" size="50" maxlength="50"><input type="submit">
</form>
<?php

//jus wondering.. are u actually selecting ALL fields from the table?
//y not u jus write SELECT * in stead?
$sql = "
SELECT Name, AKA, DOB, Address, Conviction, Description, Victim, Comments, Pic
FROM sexoffenders
";

// If search_text has been sent ...
// i dun understand y do u concat all fields to be search? hmm..
if(isset($_GET['search_text'])){

$sql .= "
WHERE CONCAT(Name,' ',AKA,' ',Address,' ',Conviction,' ',Description,' ',Victim,' ',Comments,' ') LIKE

'%".$_GET['search_text']."%'
";

}

i think i can show u a better suggestion.. in stead of what u have done before, u can follow as what i'll suggest here.. but, mine involve functions (sorry if it looks complicated.. but, the results are very Damn CLEAN.. trust me)

// Get Search Criteria for Basic Search
SetUpBasicSearch();

// Build Search Criteria
if ($sSrchBasic != "") {
	$sSrchWhere = $sSrchBasic; // Basic Search
}

// Build WHERE condition
$sDbWhere = "";
if ($sDbWhereMaster != "") {
	$sDbWhere .= "(" . $sDbWhereMaster . ") AND ";
}
if ($sSrchWhere != "") {
	$sDbWhere .= "(" . $sSrchWhere . ") AND ";
}
if (strlen($sDbWhere) > 5) {
	$sDbWhere = substr($sDbWhere, 0, strlen($sDbWhere)-5); // Trim rightmost AND
}

// a complete version of SQL statement
$sSql = "SELECT * FROM `agency`";
$sGroupBy = "";
$sHaving = "";

$sWhere = "";
if ($sDbWhere != "") {
	$sWhere .= "(" . $sDbWhere . ") AND ";
}
if (substr($sWhere, -5) == " AND ") {
	$sWhere = substr($sWhere, 0, strlen($sWhere)-5);
}
if ($sWhere != "") {
	$sSql .= " WHERE " . $sWhere;
}
if ($sGroupBy != "") {
	$sSql .= " GROUP BY " . $sGroupBy;
}
if ($sHaving != "") {
	$sSql .= " HAVING " . $sHaving;
}


//-------------------------------------------------------------------------------
// Function BasicSearchSQL
// - Build WHERE clause for a keyword

function BasicSearchSQL($Keyword)
{
	$sKeyword = (!get_magic_quotes_gpc()) ? addslashes($Keyword) : $Keyword;
	$BasicSearchSQL = "";
	$BasicSearchSQL.= "`AgencyName` LIKE '%" . $sKeyword . "%' OR ";
	$BasicSearchSQL.= "`OfficeAdd1` LIKE '%" . $sKeyword . "%' OR ";
	$BasicSearchSQL.= "`OfficeAdd2` LIKE '%" . $sKeyword . "%' OR ";
	$BasicSearchSQL.= "`OfficeAdd3` LIKE '%" . $sKeyword . "%' OR ";
	$BasicSearchSQL.= "`SubdistrictName` LIKE '%" . $sKeyword . "%' OR ";
	$BasicSearchSQL.= "`DistrictName` LIKE '%" . $sKeyword . "%' OR ";
	$BasicSearchSQL.= "`EmailAdd` LIKE '%" . $sKeyword . "%' OR ";
	$BasicSearchSQL.= "`WebsiteAdd` LIKE '%" . $sKeyword . "%' OR ";
	$BasicSearchSQL.= "`OfficeNum` LIKE '%" . $sKeyword . "%' OR ";
	$BasicSearchSQL.= "`FaxNum` LIKE '%" . $sKeyword . "%' OR ";
	if (substr($BasicSearchSQL, -4) == " OR ") { $BasicSearchSQL = substr($BasicSearchSQL, 0, strlen($BasicSearchSQL)-4); }
	return $BasicSearchSQL;
}

//-------------------------------------------------------------------------------
// Function SetUpBasicSearch
// - Set up Basic Search parameter based on form elements pSearch & pSearchType
// - Variables setup: sSrchBasic

function SetUpBasicSearch()
{
	global $HTTP_GET_VARS;
	global $sSrchBasic;
	$sSearch = (!get_magic_quotes_gpc()) ? addslashes(@$HTTP_GET_VARS["psearch"]) : @$HTTP_GET_VARS["psearch"];
	$sSearchType = @$HTTP_GET_VARS["psearchtype"];
	if ($sSearch <> "") {
		if ($sSearchType <> "") {
			while (strpos($sSearch, "  ") != false) {
				$sSearch = str_replace("  ", " ",$sSearch);
			}
			$arKeyword = split(" ", trim($sSearch));
			foreach ($arKeyword as $sKeyword)
			{
				$sSrchBasic .= "(" . BasicSearchSQL($sKeyword) . ") " . $sSearchType . " ";
			}
		}
		else
		{
			$sSrchBasic = BasicSearchSQL($sSearch);
		}
	}
	if (substr($sSrchBasic, -4) == " OR ") { $sSrchBasic = substr($sSrchBasic, 0, strlen($sSrchBasic)-4); }
	if (substr($sSrchBasic, -5) == " AND ") { $sSrchBasic = substr($sSrchBasic, 0, strlen($sSrchBasic)-5); }
}

Shud u wana use, u have to author the code.. but i think the code is cleared enuf for you to understand everytin.. do you?

hope my code can help you solve the problem.. and as for your code, from my opinion, i think it has to be somtin wif the CONCAT thingy.. it's quite awkward tho, u search a keyword to a string of combination many fields... (HUH? i'm stil confused...)

hmm.. dun know what else to write.. i'll try to configure what's wrong wif ur codes before..

orite then.. gud luck ~~Have a nice weeken~~ 😃

Warm regards, videxx 🆒

pdn

I tried the search engine you described and it is a little more complicated than what I'd hoped for... I'm not against using it, I just am not sure how to make it fit my need. Do you think there is a way to make the former one (my original search) work? Is it possibly some problem with my db?

Thanks

videxx

my ques..

jus wondering.. are u actually selecting ALL fields from the table?
2.y not u jus write SELECT * in stead?
3.i dun understand y do u concat all fields to be search? hmm.. it's kinda weird tho..

I'll explain later..

Cherios..

videxx 🆒