searching database from browser

August 3, 200818 yr

Hi,

I am slowly learning a little about mysql and PHP and am finding it challenging but exciting.

I figured out how to create a database and table and then allow new data to be entered into the table from a HTML/Browser form.

The next thing I am trying to figure out it how to allow visitors to my site to do keyword searches which will return results from the mysql database. For example if they are looking for a hairdresser - they could do a keyword search for "hairdresser" and it will return every entry in the database where the word hairdresser is mentioned.

I have been reading book called "PHP and MYSQL for Dynamic Web Sites" and while it is very helpful, it doesnt seem to address this particular question.

I was wondering if someone could either help me out or point me in the direction of a website or even book that could help me out.

As usual i really do appreciate your time and help, it really has been a godsend in the past!!

Thank you!!

August 3, 200818 yr

hi i need to do this my self i have found this link

http://usefulscripts.wordpress.com/2007/10...with-php-mysql/

if its not what you want let me now as i will be trying write a script today for this myself today so dont want hi jack your thread so may be we can work on it together,i was only thinking last night from a chat room script i have there is a list of words that carnt be used so maybe i can modyfy that into a search

if you find an answer first can you pm me,i just eed to do a few other things first thanks

August 3, 200818 yr

You want to look at using FULLTEXT Searches.

In its simplest form, a column in the database that you want to search needs to have a FULLTEXT index added to it.

You can then query that column for matches against the keyword using

SELECT * FROM table WHERE MATCH (yourcolumn) AGAINST ('searchword')

That's obviously a very simple example

August 3, 200818 yr

I have a search on my wifes site, you can check it out at www.sensuallady.co.uk, it does contain moderate adult material, try the search, if its what you are after, then I can give you the code.

P.S I know the site isnt great, I've neglected it for a few years

Pat

EDIT: just enter baby and hit search

August 3, 200818 yr

Thank you Pat for perking up my Sunday morning

August 3, 200818 yr

Thank you Pat for perking up my Sunday morning

Your welcome

August 4, 200818 yr

Author

pat that is exactly what im after!!

Thanks so much for the info so far!!

August 4, 200818 yr

pat that is exactly what im after!!

Thanks so much for the info so far!!

Got to pop out, will be back about 10pm and ill post the code.

Pat

August 4, 200818 yr

Hi

Here is the code.

First off make aconnection to your database, create a file called connect.php or whatever and put the following code in the file.

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname = "your hostname";
$database = "your database";
$username = "your username";
$password = "your password";
$connection = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR); 
?>

Then create a file called results.php and put the following code in that.

<?php require_once('connect.php');

mysql_select_db($database, $connection); ?>
<?php
$currentPage = $_SERVER["PHP_SELF"];

$maxRows_Recordset1 = 5;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
 $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$colname_Recordset1 = "-1";
if (isset($_GET['search-q'])) {
 $colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['search-q'] : addslashes($_GET['search-q']);
}
mysql_select_db($database, $connection);
$query_Recordset1 = sprintf("SELECT item_title, item_price, item_desc, item_image, hyper_link, `sql search` FROM store_items WHERE `sql search` LIKE '%%%s%%'", $colname_Recordset1);
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $connection) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
 $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
 $all_Recordset1 = mysql_query($query_Recordset1);
 $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
 $params = explode("&", $_SERVER['QUERY_STRING']);
 $newParams = array();
 foreach ($params as $param) {
if (stristr($param, "pageNum_Recordset1") == false && 
	stristr($param, "totalRows_Recordset1") == false) {
  array_push($newParams, $param);
}
 }
 if (count($newParams) != 0) {
$queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
 }
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);
?>

There are a few things to change in the above code. you can change the number of results per page by changing the following line.

$maxRows_Recordset1 = 5;

Just change the number to what you require.

You will also have to change the following.

mysql_select_db($database, $connection);
$query_Recordset1 = sprintf("SELECT item_title, item_price, item_desc, item_image, hyper_link, `sql search` FROM store_items WHERE `sql search` LIKE '%%%s%%'", $colname_Recordset1);

You will have to put your fields and table into the above, the sql search field is where you store the words or phrases you want to be picked up by the search, obviously you can call that field anything you like.

The hyper_link field is also important as that stores the hyperlink in your database and makes your reults linkable.

The next piece of code can be placed where you want the results to be displayed on your results.php page.

<?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>

<?php do { ?>


<table width="100%" border="1">
<?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
 <tr>
<td><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><img src="<?php echo $row_Recordset1['item_image']; ?>" alt="" name="sensual" style="width:100px; height:100px;" border="0"></a></td>

<td width="20%" style="padding-left:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style5"><?php echo $row_Recordset1['item_title']; ?></span></a></td>

<td  style="padding-left:4px; padding-right:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style2"><?php echo $row_Recordset1['item_desc']; ?></span></a></td>

<td  style="padding-left:4px; padding-right:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style2">£<?php echo $row_Recordset1['item_price']; ?></span></a></td>
 </tr> 

</table>


  <?php } // Show if recordset not empty ?>



  <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>

	<?php } // Show if recordset not empty ?>


  <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>


 <br />
	<?php } // Show if recordset not empty ?>



 <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

  <div align="center" class="style59Copy">
	<?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
	  <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, max(0, $pageNum_Recordset1 - 1), $queryString_Recordset1); ?>"><br>
	  Back </a> <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, min($totalPages_Recordset1, $pageNum_Recordset1 + 1), $queryString_Recordset1); ?>">Next</a>
	  <?php } // Show if recordset not empty ?></div>
  <?php } // Show if recordset not empty ?>
  <p align="center" ><?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
   <span class="style43Copy">Page:</span>
 <?php
for ($i=0; $i <= $totalPages_Recordset1; $i++) {
 $TFM_PagesEndCount = $i + 1;
 if($i != $pageNum_Recordset1) {
printf('<a href="'."%s?pageNum_Recordset1=%d%s", $currentPage, $i, $queryString_Recordset1.'">'.$TFM_PagesEndCount."</a>");
 }else{
echo("<strong>$TFM_PagesEndCount</strong>");
 }
 if($i != $totalPages_Recordset1) echo("  ");
}
?>
 <?php } // Show if recordset not empty ?>

You can change the layout to suit yourself.

In this section of the above code I have hyperlinked every item as below

<table width="100%" border="1">
<?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
 <tr>
<td><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><img src="<?php echo $row_Recordset1['item_image']; ?>" alt="" name="sensual" style="width:100px; height:100px;" border="0"></a></td>

<td width="20%" style="padding-left:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style5"><?php echo $row_Recordset1['item_title']; ?></span></a></td>

<td  style="padding-left:4px; padding-right:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style2"><?php echo $row_Recordset1['item_desc']; ?></span></a></td>

<td  style="padding-left:4px; padding-right:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style2">£<?php echo $row_Recordset1['item_price']; ?></span></a></td>
 </tr> 

</table>

The remaining code adds a next/back link and page nos depending on the number of results returned.

I'm not great at explaining so any help needed either pm me or just post.

Cheers

Pat

August 9, 200818 yr

Author

Hi Pat (or anyone else)

I have had a go at the script - i have made on file called "connect.php" and another called "results.php"

The connect file seems to be connecting to the database fine, so i think that isnt the problem,

when i open the "results.php" page i get this error:

Unknown column 'sql search' in 'field list'

Im hoping you can give me some more hints....

This is the code in my "results.php" file

<?php require_once('connect.php');

mysql_select_db($database, $connection); ?>
<?php
$currentPage = $_SERVER["PHP_SELF"];

$maxRows_Recordset1 = 20;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
 $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$colname_Recordset1 = "-1";
if (isset($_GET['search-q'])) {
 $colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['search-q'] : addslashes($_GET['search-q']);
}
mysql_select_db($database, $connection);
$query_Recordset1 = sprintf("SELECT business_name, industry, address1, suburb, phonenumber, `sql search` FROM business WHERE `sql search` LIKE '%%%s%%'", $colname_Recordset1);
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $connection) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
 $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
 $all_Recordset1 = mysql_query($query_Recordset1);
 $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
 $params = explode("&", $_SERVER['QUERY_STRING']);
 $newParams = array();
 foreach ($params as $param) {
   if (stristr($param, "pageNum_Recordset1") == false && 
       stristr($param, "totalRows_Recordset1") == false) {
     array_push($newParams, $param);
   }
 }
 if (count($newParams) != 0) {
   $queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
 }
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);
?>





===========================================================================



<?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>

<?php do { ?>


   <table width="100%" border="1">
   <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
 <tr>
   <td><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><img src="<?php echo $row_Recordset1['business_name']; ?>" alt="" name="sensual" style="width:100px; height:100px;" border="0"></a></td>

   <td width="20%" style="padding-left:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style5"><?php echo $row_Recordset1['suburb']; ?></span></a></td>

   <td  style="padding-left:4px; padding-right:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style2"><?php echo $row_Recordset1['phonenumber']; ?></span></a></td>

   <td  style="padding-left:4px; padding-right:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style2">£<?php echo $row_Recordset1['address1']; ?></span></a></td>
 </tr> 

</table>


     <?php } // Show if recordset not empty ?>



     <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>

       <?php } // Show if recordset not empty ?>


     <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>


 <br />
       <?php } // Show if recordset not empty ?>



 <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

     <div align="center" class="style59Copy">
       <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
         <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, max(0, $pageNum_Recordset1 - 1), $queryString_Recordset1); ?>"><br>
         Back </a> <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, min($totalPages_Recordset1, $pageNum_Recordset1 + 1), $queryString_Recordset1); ?>">Next</a>
         <?php } // Show if recordset not empty ?></div>
     <?php } // Show if recordset not empty ?>
     <p align="center" ><?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
   <span class="style43Copy">Page:</span>
 <?php
for ($i=0; $i <= $totalPages_Recordset1; $i++) {
 $TFM_PagesEndCount = $i + 1;
 if($i != $pageNum_Recordset1) {
   printf('<a href="'."%s?pageNum_Recordset1=%d%s", $currentPage, $i, $queryString_Recordset1.'">'.$TFM_PagesEndCount."</a>");
 }else{
   echo("<strong>$TFM_PagesEndCount</strong>");
 }
 if($i != $totalPages_Recordset1) echo("  ");
}
?>
 <?php } // Show if recordset not empty ?>

Also this is my table layout in case that helps..

August 9, 200818 yr

Are you certain you have a field called sql search

Pat

August 12, 200818 yr

Author

hmm no i dont...thank you...ill try again :fool:

August 13, 200818 yr

Author

sorry Pat, another question, do i add the sql search field in the "results" file? Is anywhere in there fine?

August 13, 200818 yr

Author

Hi Pat, I bet you regret offering your help by now :lol:

I added the sql search field at the top of the results page....

the error has now changed to:Unknown column 'sql search' in 'where clause'

Would appreciate your help again - and thank you so much for your time so far!!

<input name="sql search" type="text" />

<?php require_once('connect.php');

mysql_select_db($database, $connection); ?>
<?php
$currentPage = $_SERVER["PHP_SELF"];

$maxRows_Recordset1 = 20;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
 $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

$colname_Recordset1 = "-1";
if (isset($_GET['search-q'])) {
 $colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['search-q'] : addslashes($_GET['search-q']);
}
mysql_select_db($database, $connection);
$query_Recordset1 = sprintf("SELECT business_name, industry, address1, suburb, phonenumber, keywords, business_catagory, info `sql search` FROM business WHERE `sql search` LIKE '%%%s%%'", $colname_Recordset1);
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $connection) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
 $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
 $all_Recordset1 = mysql_query($query_Recordset1);
 $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$queryString_Recordset1 = "";
if (!empty($_SERVER['QUERY_STRING'])) {
 $params = explode("&", $_SERVER['QUERY_STRING']);
 $newParams = array();
 foreach ($params as $param) {
if (stristr($param, "pageNum_Recordset1") == false && 
	stristr($param, "totalRows_Recordset1") == false) {
  array_push($newParams, $param);
}
 }
 if (count($newParams) != 0) {
$queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
 }
}
$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);
?>





===========================================================================



<?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>

<?php do { ?>


<table width="100%" border="1">
<?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
 <tr>
<td><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><img src="<?php echo $row_Recordset1['business_name']; ?>" alt="" name="sensual" style="width:100px; height:100px;" border="0"></a></td>

<td width="20%" style="padding-left:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style5"><?php echo $row_Recordset1['suburb']; ?></span></a></td>

<td  style="padding-left:4px; padding-right:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style2"><?php echo $row_Recordset1['phonenumber']; ?></span></a></td>

<td  style="padding-left:4px; padding-right:4px;"><a href="<?php echo $row_Recordset1['hyper_link']; ?>"><span class="style2">£<?php echo $row_Recordset1['address1']; ?></span></a></td>
 </tr> 

</table>


  <?php } // Show if recordset not empty ?>



  <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>

	<?php } // Show if recordset not empty ?>


  <?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>


 <br />
	<?php } // Show if recordset not empty ?>



 <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

  <div align="center" class="style59Copy">
	<?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
	  <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, max(0, $pageNum_Recordset1 - 1), $queryString_Recordset1); ?>"><br>
	  Back </a> <a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, min($totalPages_Recordset1, $pageNum_Recordset1 + 1), $queryString_Recordset1); ?>">Next</a>
	  <?php } // Show if recordset not empty ?></div>
  <?php } // Show if recordset not empty ?>
  <p align="center" ><?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
   <span class="style43Copy">Page:</span>
 <?php
for ($i=0; $i <= $totalPages_Recordset1; $i++) {
 $TFM_PagesEndCount = $i + 1;
 if($i != $pageNum_Recordset1) {
printf('<a href="'."%s?pageNum_Recordset1=%d%s", $currentPage, $i, $queryString_Recordset1.'">'.$TFM_PagesEndCount."</a>");
 }else{
echo("<strong>$TFM_PagesEndCount</strong>");
 }
 if($i != $totalPages_Recordset1) echo("  ");
}
?>
 <?php } // Show if recordset not empty ?>

August 13, 200818 yr

This line:

$query_Recordset1 = sprintf("SELECT business_name, industry, address1, suburb, phonenumber, keywords, business_catagory, info `sql search` FROM business WHERE `sql search` LIKE '%%%s%%'", $colname_Recordset1);

you need to change the 'sql search' bit to the column that you are searching in.

August 14, 200818 yr

Author

Thanks Jameset,

Am i allowed to list 7 different columns to be searched eg:

$query_Recordset1 = sprintf("SELECT business_name, industry, address1, suburb, phonenumber, keywords, business_catagory, info `sql search` FROM business WHERE `name, industry, suburb, address1, keywords, info, catagory` LIKE '%%%s%%'", $colname_Recordset1);

Or is it not that simple?

Thanks heaps

August 14, 200818 yr

Author

I thought the `sql search`

was referring to the search field?

searching database from browser

Featured Replies

Create an account or sign in to comment

Recently Browsing 0

Member Statistics

Forum Statistics

Join WDF

Account

Navigation

Search

Configure browser push notifications

Chrome (Android)

Chrome (Desktop)

Safari (iOS 16.4+)

Safari (macOS)

Edge (Android)

Edge (Desktop)

Firefox (Android)

Firefox (Desktop)