Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
chris98
Forum Contributor
Posts: 103 Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom
Post
by chris98 » Sat Aug 31, 2013 3:39 am
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();
?>
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Sat Aug 31, 2013 5:22 am
You need to use wildcards in your search.
Code: Select all
SELECT foo FROM bar WHERE this LIKE '%that%';
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Sat Aug 31, 2013 5:24 am
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!)
chris98
Forum Contributor
Posts: 103 Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom
Post
by chris98 » Sat Aug 31, 2013 5:36 am
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?
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Sat Aug 31, 2013 5:44 am
chris98 wrote: I don't know how to change the database password & username, or I would have.
Then learn .
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Sat Aug 31, 2013 4:41 pm
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}%'";
chris98
Forum Contributor
Posts: 103 Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom
Post
by chris98 » Sun Sep 01, 2013 3:32 am
Right, I have got this working, how could I transfer it to PDO?
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Sun Sep 01, 2013 7:13 am
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>";
}
chris98
Forum Contributor
Posts: 103 Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom
Post
by chris98 » Sun Sep 01, 2013 10:46 am
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.
Christopher
Site Administrator
Posts: 13596 Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US
Post
by Christopher » Sun Sep 01, 2013 4:07 pm
Can you post your PDO code so we can see where the problem is?
(#10850)
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Sun Sep 01, 2013 4:53 pm
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.
chris98
Forum Contributor
Posts: 103 Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom
Post
by chris98 » Mon Sep 02, 2013 1:54 am
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.
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Mon Sep 02, 2013 5:39 am
You aren't limiting your query. You're fetching all results initially, so there is no next page.
chris98
Forum Contributor
Posts: 103 Joined: Tue Jun 11, 2013 10:47 am
Location: England, United Kingdom
Post
by chris98 » Mon Sep 02, 2013 5:47 am
How could I limit it?
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Mon Sep 02, 2013 6:01 am
SELECT Syntax
Add a LIMIT clause to your query.
Code: Select all
SELECT foo FROM bar WHERE this = that LIMIT 0, 10