MySQL search with PHP

Question

MySQL search with PHP

I am making a project where I want a person to enter the name of any artist / group in a textbox where it will search my mysql database for event information and display the results / content on another page. The code below is in my index.php where it should get information from search.php (below also). I've looked everything and I'm not sure why it doesn't work and I can't figure out what to do. Help it would be great! (I really need to pass this class!) :)

(index.php)

<form name="search" action="search.php" method="get">
    <div align="center"><input type="text" name="q" />
    <p><input type="submit" name="Submit" value="Search" /></p>
</form>

(search.php)

<?php

//Get the search variable from URL

$var=@&_GET['q'];
$trimmed=trim($var); //trim whitespace from the stored variable

//rows to return
$limit=10;

//check for an empty string and display a message.
if($trimmed=="")
    {
    echo"<p>Please enter a name.</p>";
    exit;
    }

//check for a search parameter
if(!isset($var))
    {
    echo"<p>We don't seem to have a search parameter!</p>";
    exit;
    }

//connect to database
mysql_connect("localhost","root","password");

//specify database
mysql_select_db("itour") or die("Unable to select database");

//Build SQL Query
$query = "select * from events where artist_name like \"%trimmed%\" order by date";

$numresults=mysql_query($query);
$numrows=mysql_num_rows(numresults);

//If no results, offer a google search as an alternative

if ($numrows==0)
    {
    echo"<h3>Results</h3>";
    echo"<p>Sorry, your search: &quot;" .$trimmed . "&quot; returned zero results</p>";

    //google
    echo"<p><a href=\"http://www.google.com/search?q=".$trimmed . "\" target=\"_blank\" title=\"Look up ".$trimmed ." on Google\">
    Click here</a> to try the search on google</p>";
    }

//next determine if s has been passed to script, if not use 0
if(empty($s)) {
    $s=0;
    }

//get results
$query .=" limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");

//display what was searched for
echo"<p>You searched for: &quot;" .$var . "&quot;</p>";

//begin to show results set
echo "Results";
$count = 1 + $s;

//able to display the results returned
while ($row=mysql_fetch_array($result)) {
$title = $row["artist_name"];

echo"$count.)&nbsp;$title";
$count++;
}

$currPage = (($s/$limit) + 1;

echo"<br  />";

//links to other results
if ($s>=1){
    //bypass PREV link if s is 0
    $prevs=($s-$limit);
    print"&nbsp;<a href=\"$PHP_SELF?s=$prevs&q=$var\">&lt;&lt;
    Prev 10</a>&nbsp;&nbsp;";
}

//calculate number of pages needing links
$pages = intval($numrows/$limit);

//$pages now contains int of pages needed unless there is a remainder from diviison

if($numrows%$limit){
//has remainder so add one page
$pages++;
}

//check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1){

//not last page so give NEXT link
$news = $s+$limit;

echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";
}

$a = $s +($limit);
if($a > $numrows){$a = $numrows;}
$b = $s + 1;
echo "<p>Showing results $b to $a of $numrows</p>";

?>

+1

php mysql search

Holly 05 May '09 at 19:05

source to share

6 answers

Your where goofy clause ... try changing it to:

WHERE source_name of type '% $ trimmed%'

simply giving the trim will be interpreted literally as the string "trimmed". However, using the $ variable trimmed in your double quote will give the actual value of the variable.

+2

Zack marrapese 05 May '09 at 19:11

source to share

$query = "select * from events where artist_name like '%$trimmed%' order by date";

+1

Stefano D 05 May '09 at 19:36

source to share

To use a variable $trimmed

in a query, avoid it first. Otherwise, your script will be vulnerable to SQL injection attacks , and attackers will be able to run almost any query against your database. This problem is compounded by the fact that you are connecting to MySQL as root. Never do this in a production environment.

Also, to expand a variable on a string, you must include the $ character in front of the variable name.

$trimmed = trim($var);
$escaped = mysql_real_escape_string($trimmed);
$query = "select * from events where artist_name like \"%$escaped%\" order by date";

+1

Ayman hourieh 05 May '09 at 19:45

source to share

Your code still looks all over the place. I think the main reason it didn't work is the confusion of "and". You need to avoid variables before using them in your turn. mysql_real_escape_string is the lowest escaping form you should use. I would recommend you take a look at PDO though.

<?php

//Get the search variable from URL

$var = $_GET['q'];
$trimmed = mysql_real_escape_string(trim($var)); //trim whitespace and escape the stored variable

//rows to return
$limit = 10;

//check for an empty string and display a message.
if($trimmed == "") {
    echo"<p>Please enter a name.</p>";
    exit;
}

//check for a search parameter
if(!isset($var)){
    echo"<p>We don't seem to have a search parameter!</p>";
    exit;
}

//connect to database
mysql_connect("localhost","root","password");

//specify database
mysql_select_db("itour") or die("Unable to select database");

//Build SQL Query
$query = "SELECT * FROM events WHERE artist_name LIKE %$trimmed% ORDER BY DATE";

$numresults = mysql_query($query);
$numrows = mysql_num_rows(numresults);

//If no results, offer a google search as an alternative

if ($numrows==0){
    echo"<h3>Results</h3>";
    echo"<p>Sorry, your search: &quot;" .$trimmed . "&quot; returned zero results</p>";

    //google
    echo"<p><a href=\"http://www.google.com/search?q=".$trimmed . "\" target=\"_blank"\ title=\"Look up ".$trimmed ." on Google\">
    Click here</a> to try the search on google</p>";
 }

//next determine if s has been passed to script, if not use 0
if(empty($s)) {
    $s=0;
}

//get results
$query .=" limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");

//display what was searched for
echo"<p>You searched for: &quot;" .$var . "&quot;</p>";

//begin to show results set
echo "Results";
$count = 1 + $s;

//able to display the results returned
while ($row = mysql_fetch_array($result)) {
    $title = $row['artist_name'];

    echo $count.'&nbsp;'.$title;
    $count++;
}

$currPage = (($s/$limit) + 1;

echo "<br>";

//links to other results
if ($s>=1){
    //bypass PREV link if s is 0
    $prevs=($s-$limit);
    echo '&nbsp;<a href="'.$PHP_SELF.'?s='.$prevs.'&q='.$var.'">&lt;&lt';
    echo 'Prev 10</a>&nbsp;&nbsp;';
}

//calculate number of pages needing links
$pages = intval($numrows/$limit);

//$pages now contains int of pages needed unless there is a remainder from diviison

if($numrows%$limit){
    //has remainder so add one page
    $pages++;
}

//check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1){

//not last page so give NEXT link
$news=$s+$limit;

echo '&nbsp;<a href="'.$PHP_SELF.'?s='.$news.'&q='.$var.'">Next 10 &gt;&gt;</a>';
}

$a = $s +($limit);
if($a > $numrows){$a = $numrows;}
$b = $s + 1;
echo '<p>Showing results '.$b.' to '.$a.' of '.$numrows.'</p>';

?>

+1

Alexis tyler 23 Apr 13 at 13:17

source to share

Also, as Chad said, SQL injection is going to be easy since you don't clean up the input file before doing anything on it.

try adding

 foreach($_REQUEST as $param => $value)
{
   $_REQUEST[$param]=mysql_real_escape_string($value);
}

This way you avoid user input, so the user cannot tamper with the db. Read more about this method and SQL injection here: http://us2.php.net/mysql_real_escape_string

0

Philip broadway 05 May '09 at 19:41

source to share

Tom haigh · Accepted Answer · 2009-05-05T23:11:45+0000

You are missing the $ character. I think,

$var=@&_GET['q'];

it should be

$var=@$_GET['q'];

unless you really want the link, in which case it should be: (error suppression is not required at this point if you want a link, but you must check that $ var is set before trying to access it)

$var=& $_GET['q'];

I would be tempted to write this a little more.

if (!isset($_GET['q'])) {
    echo"<p>We don't seem to have a search parameter!</p>";
    exit;
}

$trimmed = trim($_GET['q']);

if($trimmed=="") {
    echo"<p>Please enter a name.</p>";
    exit;
}

MySQL search with PHP

More articles: