Page 1 of 4

php search query

Posted: Sat Aug 31, 2013 3:39 am
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(); 
?> 

Re: php search query

Posted: Sat Aug 31, 2013 5:22 am
by Celauran
You need to use wildcards in your search.

Code: Select all

SELECT foo FROM bar WHERE this LIKE '%that%';

Re: php search query

Posted: Sat Aug 31, 2013 5:24 am
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!)

Re: php search query

Posted: Sat Aug 31, 2013 5:36 am
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?

Re: php search query

Posted: Sat Aug 31, 2013 5:44 am
by requinix
chris98 wrote:I don't know how to change the database password & username, or I would have.
Then learn.

Re: php search query

Posted: Sat Aug 31, 2013 4:41 pm
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}%'";

Re: php search query

Posted: Sun Sep 01, 2013 3:32 am
by chris98
Right, I have got this working, how could I transfer it to PDO?

Re: php search query

Posted: Sun Sep 01, 2013 7:13 am
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>";
}

Re: php search query

Posted: Sun Sep 01, 2013 10:46 am
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.

Re: php search query

Posted: Sun Sep 01, 2013 4:07 pm
by Christopher
Can you post your PDO code so we can see where the problem is?

Re: php search query

Posted: Sun Sep 01, 2013 4:53 pm
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.

Re: php search query

Posted: Mon Sep 02, 2013 1:54 am
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...

Re: php search query

Posted: Mon Sep 02, 2013 5:39 am
by Celauran
You aren't limiting your query. You're fetching all results initially, so there is no next page.

Re: php search query

Posted: Mon Sep 02, 2013 5:47 am
by chris98
How could I limit it?

Re: php search query

Posted: Mon Sep 02, 2013 6:01 am
by Celauran
SELECT Syntax

Add a LIMIT clause to your query.

Code: Select all

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