Making website by PHP , HTML and MYSQL: How to count the results of a search

Search boxes are importent part of a forum . User wants to find a topic very fast , he doesn't want to see all of the pages to find his favorite thing. The picture below shows a search box :

Put the following codes in "search.php" , they will show a form for search on the screen.

code:

<?php
include "auth.inc"; // we check that if user has loged in or not

include "funcs.php"; // the functions look like : connect (to connect to the database) and creatcode (for verification text) can be put in a file and then we include that file wherever it is required.

?>

<?php
include "auth.inc";
?>
<a href="logout.php">log out</a>
 

// the following html codes show a menu-bar on the page . they have been created by Frontpage . You can omit them.

<html dir="ltr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title> search </title>
</head>
<body bgcolor="#99FF66" >
<table border="0" cellpadding="0" cellspacing="0" width="1055" height="625">

<tr>
 <td valign="top" colspan="3" height="148">
 
 
 
 
<a href="index.php"><img src="_derived/index.php_cmp_poetic000_hbtn.gif" width="154" height="37" border="0" alt="index.php" align="middle"></a> <a href="about.php"><img src="_derived/about.php_cmp_poetic000_hbtn.gif" width="154" height="37" border="0" alt="about.php" align="middle"></a> <a href="search.php"><img src="_derived/search.php_cmp_poetic000_hbtn.gif" width="154" height="37" border="0" alt="search.php" align="middle"></a> 
 
  search </td>
</tr>
<tr>
 <td valign="top" width="300">
 

 
 main titles 
<?php

// the following is just a list of forums

echo"
 
 
1.<a href=\"showforum.php?link=satire\">satire</a>
 
 
2.<a href=\"showforum.php?link=drama\">drama</a>
 
 
3.<a href=\"showforum.php?link=tragic\">tragic</a>
 
 
4.<a href=\"showforum.php?link=joke\">joke</a>
 
 
5.<a href=\"showforum.php?link=others\">others</a>
";
?>
  </td>
 <td valign="top" width="747">
 
 
 
 
 
<?php
if($_GET['q'] == "" or !isset($_GET['q']))
{

// This condition checks that if the search box is empty or not . If no search has begun the search box and the search button are shown , unless the results of search are shown.

?>
<form method="get" action="search.php">

// the search form is sent to "search.php" again.


<input type="text" name="q" size="20">
<input type="submit" value="Search" name="searchBtn">

</form>
<?php
}else{

// if the search box is not empty , we do a search over the database by the entered word.

// some prosses over the entered word

$key = $_GET['q'];
$key = htmlspecialchars($key); // change html characters in to regular text , this is an anti-hack process

$key = trim($key); // trim omits "space" characters from two sides of a text

// omit the dangerous characters from the entery .

$key = str_replace("'", "", $key);
$key = str_replace("\\", "", $key);

if(ereg("[^a-zA-z0-9\ ]",$key)){

// here we check the word format that it contains only numbers and letters.

echo "incorrect format for search phrase, just use letters , numbers and space";
echo " <a href = \"search.php\" >Search again</a> ";
exit;
}
connect();

// search the posts titles for the entered word.
// Notice to this query , '%" means whatever.

$sql = "SELECT * FROM topics WHERE title LIKE '%".$key ."%' ";
$SearchResult = mysql_query($sql);

$TotalResults = mysql_num_rows($SearchResult);
if($TotalResults == 0){

// if there's no result show the message "no match"

echo "no matches";
echo " <a href = \"search.php\" >Search again</a> ";
exit;
}
for($i = $TotalResults - 1; $i != -1; $i-- )
{

// print the search results on the screen .

$TextTitle = mysql_result($SearchResult, $i, 3);
$Textauthor = mysql_result($SearchResult, $i, 0);
$Textlink = mysql_result($SearchResult, $i, 5);

echo "<a href=\"showtopic.php?Textlink=" .$Textlink. " \">" . $TextTitle . "</a>";
echo " ";
}
}
?>
</td>
 <td valign="top" height="427" width="147">
 
  </td>
</tr>
<tr>
 <td valign="top" colspan="3" height="50">
 
 
 

 <?php

 echo get_pages_html(); // you can add page numbering to search results but I haven't done it here , this line is just a draft and isn't necessarry

 ?>
  </td>
</tr>

</table>
</body>
</html>

 

<html dir="ltr">

There's enough comments between codes , so I don't explain more.
You can read the following related posts to understand codes here better:

http://webprogram4beginners.blogspot.com/2011/02/php-orders-to-work-with-database-and.html

http://webprogram4beginners.blogspot.com/2011/04/how-to-show-certain-post-by-user-demand.html

Here , I list a bunch of Mysql orders that are needed most.
To work with Mysql on yourown computer you can install WAMP server and then go to phpmyadmin page:

http://localhost/phpmyadmin/index.php

There , create a new database . A database is look like a matrix. It contains tables that are rows of the matrix and fields that are columns of the matrix.

phpmyadmin is very user friendly so you don't need to deal with the Queries that are needed to create a database , tables and fields .(the orders in SQL are called query)

On the other hand phpmyadmin will return you the queries of the creation process and you can learn them if you want. BUT most servers have database manager program that is very user friendly.
In a website you can create your databases , tables and fields on the server and then you use PHP codes to put or delete data inside the the databases.

PHP orders to work with Database:

-----------------------------------------------
Connecting to a database :

To connect the stream to a database we need two PHP orders :

mysql_connect and mysql_select_db

The form of these orders are in the following :

connection variable = mysql_connect( "Host name" , "Username" , "password");
mysql_select_db("database name" , connection variable);

For example :

Code:
$myconn = mysql_connect( "localhost" , "root" , ""); // When you use WAMP as sever your host would be "localhost" and the username for localhost is "root" and there's no password for it. This line of code would connect the stream to the localhost server , and assign "$myconn" variable to this connection.

mysql_select_db("mydb2",$myconn); // This line of code would connect the stream to "mydb2" database on the localhost server through "myconn" variable.

This code connects your stream to "mydb2" database on yourown computer.

-----------------------------------------------
Inject queries in to the database:

To order to your databases you can use This PHP order :

mysql_query (a string variable contains mysql query);

An example is in the following

-----------------------------------------------
Insert data in to a database :

This SQL query adds data in to your mysql :

INSERT INTO TableName (FieldName1,FieldName2,...) VALUES ('value for field1','value for field2',...)

For example :
Open your phpmyadmin and create a database with this features :

name : dabase2
tables : just one table "users" cotains three fields username , password , joindate.

Now after connection add these codes :

code :
$entered_usr = "Thomas";
$entered_pass = "secret";
$Today = date("Ymd");
$salt = "qw";
$entered_pass = crypt($entered_pass,$salt); // this order hashes the password so it can't be hacked easilly.
$sql = "INSERT INTO users (username,password,joindate) VALUES ('$entered_usr','$entered_pass','$Today')"; // this is our query to insert the data in to the table "users" .
mysql_query($sql); // this order sends the query in to the database to be acted.

-----------------------------------------------
Searching a database (extract data from database) :
The Query is :

SELECT * FROM table_name WHERE Field_name = 'Value_for_the_field'

Let's describe this part by an example :
If we want to find the password corresponds to a username we can use the following codes.

code:
$sql = "SELECT * FROM users WHERE username='$entered_usr' "; // this query select from "users" table the enteries that have username equals to '$entered_usr'.
$search_result = mysql_query($sql); // the result of the query is saved in "$search_result" variable
$password = mysql_result($search_result,0,1); // here the password is extracted from data. more explanation in the following.

The search result format is :

mysql_result($search_result,result index,Field index)

result index : that begins from zero. Here it should be zero because we should have just one result.
Field index : that begins from zero . for example it's zero for "username" and one for "password" field.

-----------------------------------------------
How to count the results of a search :
there are two way to search the number of results :

1. Direct counting

For Example : these codes count the number of results of an entered username and if there is any result for it , it shows a message that the entered username's already existed in the database and the user should choose an other username.

code :
$entered_usr = "James"; // it's just an example it can be replaced by a text box.
$Sql = "SELECT COUNT(*) FROM users WHERE username='$entered_usr' "; // this query counts the number of rows in the database that contain the entered username.
$Result = mysql_query($Sql) or die(mysql_error() . " SQL: " . $Sql); // the result of this query is just a number that would be put in "$Result" variable.
if(mysql_result($Result, 0) > 0 ){
echo "username's already existed";
}

2. Count After "SELECT" order

$Total_number_of_Results = mysql_num_rows($search_result); // it counts the number of results that are exracted from the database.

-----------------------------------------------
Update the entered data in a database
For example these codes change the password of a user to a new password in the "users" table.

code:
$sql = "UPDATE users SET password = '$new_entered_password' WHERE username = '$entered_usr' "; // "users" is the table name
mysql_query($sql);

Making website by PHP , HTML and MYSQL

The list of all PHP and Mysql posts

How to make a search page

PHP orders to work with Database and some useful MYSQL orders