php search query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

php search query

Post by chris98 »

Does anyone know a search query that will allow me to search for similar names.For example, if I entered "test", "test_file" might come out as a result.I have tried using the "LIKE" statement, but it only brings out exact matches.

Like Google for example.Google sometimes (or mostly) brings out results that aren't specific or exact matches.

(I also do know this is deprecated, but I just wanted to actually get it to work properly before I focus on security)

Here is the code I have been using:

Index.html

Code: Select all

<form method="post" action="search.php">
					<div>
			<label>Filename:<br /></label>
            <input type="text" class="textfield" name="search" value="" size="18" /><br /><br />
						<input class="button" type="submit" value="Search" title="Search" />
					</div>
				</form>
Search.php

Code: Select all

<?php 
$search = $_POST['search']; //This grabs everything the user has put into the search field (which has been labelled as "search" earlier from the .HTML page).
$conn = mysql_connect ("localhost", "root", "") or die ('I cannot connect to the database because: ' . mysql_error());
 $selected = mysql_select_db ("sh2_files") 
or die ("Could not select database"); 


// PHP Search Script
$sql = "select * from downloads where name LIKE '".$search."'";
 $result = mysql_query($sql,$conn)or die (mysql_error()); 


if (mysql_num_rows($result)==0){ 
echo "No files have been found matching your file name given.<a href='javascript:history.go(-1)'>Go back</a>?"; 
}else{ 
while ($row = mysql_fetch_array($result)){ 
echo "The server has processed your result and returned with the following result(s):";
echo "Name: " .$row['name']."<br>"; 
echo "Name: " .$row['category']."<br>"; 
echo "File Author: ".$row['username']."<br>"; 
echo "File: ".$row['file']."<br>"; 
echo "---------------------------------------------------------------------"."<br>";
 } 
} 

mysql_close(); 
?> 
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

You need to use wildcards in your search.

Code: Select all

SELECT foo FROM bar WHERE this LIKE '%that%';
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

Code: Select all

$conn = mysql_connect ("localhost", "root", "") or die ('I cannot connect to the database because: ' . mysql_error());
 $selected = mysql_select_db ("sh2_files")
Also, you really ought to avoid the mysql_ extension, and avoid getting into the habit of connecting to the database as root (which should have a password!)
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

I don't know how to change the database password & username, or I would have.And how would I define the %that%,because it brings back no results now?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: php search query

Post by requinix »

chris98 wrote:I don't know how to change the database password & username, or I would have.
Then learn.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

chris98 wrote:And how would I define the %that%,because it brings back no results now?

Code: Select all

$query = "SELECT field1, field2, field3 FROM downloads WHERE name LIKE '%{$search}%'";
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

Right, I have got this working, how could I transfer it to PDO?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

PDO Book

Try something like this

Code: Select all

<?php

$pdo = new PDO('mysql:host=localhost;dbname=whatever;', 'username', 'password');

$query = "SELECT name, category, username, file FROM downloads WHERE name = :search";
$stmt = $pdo->prepare($query);
$stmt->execute(array(':search' => '%' . $search . '%'));
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo "Name: {$row->name}<br>";
    echo "Category: {$row->category}<br>";
    echo "Author: {$row->username}<br>";
    echo "File: {$row->file}<br>";
}
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

What would the mysql_num_rows be in PDO?

For the

if (mysql_num_rows($ps)==0)

line?

EDIT: Never mind, I found it out.

I now can't get it to work though.

Fatal error: Cannot use object of type stdClass as array

Every time I remove the " .$row['name']." (and others) I can get it to work.Apart from that, it won't.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: php search query

Post by Christopher »

Can you post your PDO code so we can see where the problem is?
(#10850)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

chris98 wrote:Fatal error: Cannot use object of type stdClass as array
You're fetching an object but treating it as though it were an array. Either fetch an array or use object notation.
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

Here is the full relevant code, though the pagination also appears to work, but as soon as you press to go to the next page, it comes up with no results.


db_connection.php

Code: Select all

<?php
$dsn = "mysql:host=localhost;dbname=sh2_files";
$login = "root";
$password = "";
$opt = array(
    // any occurring errors wil be thrown as PDOException
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    // an SQL command to execute when connecting
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
);
$pdo = new PDO($dsn, $login, $password, $opt);
?>



search.php

Code: Select all

<center>
<?php
require_once('submit/db/db_connection.php');
$search = (isset($_POST['search'])) ? ($_POST['search']) : '' ;
define('PUN_ROOT', dirname(__FILE__).'/../');
include PUN_ROOT.'include/common.php';
require_once('submit/db/db_connection.php');
$query = "SELECT * FROM downloads WHERE name LIKE :search";
 $ps = $pdo->prepare($query);
 $ps->execute(array(':search' => '%' . $search . '%'));
 $num_rows = ($ps->fetchColumn());
?>
<?php 
$page=1;//Default page
 $limit=5;//Records per page
 $start=0;//starts displaying records from 0
 if(isset($_GET['page']) && $_GET['page']!=''){
 $page=$_GET['page'];
 }
 $start=($page-1)*$limit;
 ?> 
<?php 
function pagination($per_page = 10, $page = 1, $url = '', $total){ 

$adjacents = "2";
 
$page = ($page == 0 ? 1 : $page); 
$start = ($page - 1) * $per_page; 

$prev = $page - 1; 
$next = $page + 1;
 $lastpage = ceil($total/$per_page);
 $lpm1 = $lastpage - 1;
 
$pagination = "";
 if($lastpage > 1)
 { 
$pagination .= "<ul class='pagination'>";
 $pagination .= "<li class='details'>Page $page of $lastpage</li>";
 if ($lastpage < 7 + ($adjacents * 2))
 { 
for ($counter = 1; $counter <= $lastpage; $counter++)
 {
 if ($counter == $page)
 $pagination.= "<li><a class='current'>$counter</a></li>";
 else
 $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; 
}
 }
 elseif($lastpage > 5 + ($adjacents * 2))
 {
 if($page < 1 + ($adjacents * 2)) 
{
 for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
 {
 if ($counter == $page)
 $pagination.= "<li><a class='current'>$counter</a></li>";
 else
 $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; 
}
 $pagination.= "<li class='dot'>...</li>";
 $pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>";
 $pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>"; 
}
 elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
 {
 $pagination.= "<li><a href='{$url}1'>1</a></li>";
 $pagination.= "<li><a href='{$url}2'>2</a></li>";
 $pagination.= "<li class='dot'>...</li>";
 for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
 {
 if ($counter == $page)
 $pagination.= "<li><a class='current'>$counter</a></li>";
 else
 $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; 
}
 $pagination.= "<li class='dot'>..</li>";
 $pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>";
 $pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>"; 
}
 else
 {
 $pagination.= "<li><a href='{$url}1'>1</a></li>";
 $pagination.= "<li><a href='{$url}2'>2</a></li>";
 $pagination.= "<li class='dot'>..</li>";
 for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
 {
 if ($counter == $page)
 $pagination.= "<li><a class='current'>$counter</a></li>";
 else
 $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; 
}
 }
 }
 
if ($page < $counter - 1){
 $pagination.= "<li><a href='{$url}$next'>Next</a></li>";
 // $pagination.= "<li><a href='{$url}$lastpage'>Last</a></li>";
 }else{
 //$pagination.= "<li><a class='current'>Next</a></li>";
 // $pagination.= "<li><a class='current'>Last</a></li>";
 }
 $pagination.= "</ul>\n"; 
} 
return $pagination;
 } 

?> 
<style type="text/css">
 ul.pagination { font-family: "Arial", "Helvetica", sans-serif;
 font-size: 13px;
 height: 100%;
 list-style-type: none;
 margin: 20px 0;
 overflow: hidden;
 padding: 0; }
 ul.pagination li.details { background-color: white;
 border-color: #C8D5E0;
 border-image: none;
 border-style: solid;
 border-width: 1px 1px 2px;
 color: #1E598E;
 font-weight: bold;
 padding: 8px 10px;
 text-decoration: none; }
 ul.pagination li.dot { padding: 3px 0; }
 ul.pagination li { float: left;
 list-style-type: none;
 margin: 0 3px 0 0; }
 ul.pagination li:first-child { margin-left: 0; }
 ul.pagination li a { color: black;
 display: block;
 padding: 7px 10px;
 text-decoration: none; }
 ul.pagination li a img { border: medium none; }
 ul.pagination li a.current { background-color: white;
 border-radius: 0 0 0 0;
 color: #333333; }
 ul.pagination li a.current:hover { background-color: white; }
 ul.pagination li a:hover { background-color: #C8D5E0; }
 ul.pagination li a { background-color: #F6F6F6;
 border-color: #C8D5E0;
 border-image: none;
 border-style: solid;
 border-width: 1px 1px 2px;
 color: #1E598E;
 display: block;
 font-weight: bold;
 padding: 8px 10px;
 text-decoration: none; }
 </style> 
//show html...
Last edited by chris98 on Tue Nov 19, 2013 11:32 am, edited 1 time in total.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

You aren't limiting your query. You're fetching all results initially, so there is no next page.
User avatar
chris98
Forum Contributor
Posts: 103
Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom

Re: php search query

Post by chris98 »

How could I limit it?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php search query

Post by Celauran »

SELECT Syntax

Add a LIMIT clause to your query.

Code: Select all

SELECT foo FROM bar WHERE this = that LIMIT 0, 10
Post Reply