php and sql help

[hmtnotify21]

I am devloping a online sql data base and to search the database i have code that i use which is part php and part html. But the php script doesn't display search results. Code below. I put comments where the sql database name and hosting address goes .

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

<?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 search...</p>";
exit;
}

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

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","username","password"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("database") or die("Unable to select database"); //select which database we're using

// Build SQL Query
$query = "select * from the_table where 1st_field like \"%$trimmed%\"
order by 1st_field"; // EDIT HERE and specify your table and field names for the SQL query

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

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

if ($numrows == 0)
{
echo "<h4>Results</h4>";
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 the person searched for
echo "<p>You searched for: &quot;" . $var . "&quot;</p>";

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

// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$title = $row["1st_field"];

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

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

//break before paging
echo "<br />";

// next we need to do the 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 division

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>";

?>
Three major areas are covered in this script, the first is selecting data from the database which matches your entered keyword, the second is displaying the results on the web page and the last is generating the paging, which displays results in chunks of 10 with next/previous links where they are necessary.

Important: This script requires numerous edits before it can be adapted for use. The SQL query in this example is only selecting 1 field from a hypothetical database which doesn't pre-exist, nor is included in the downloadable files. Further down the script, the value of that field is being displayed on the page. The major amendments that need to be performed are 1.) The SQL statement, and 2.) The PHP which displays the results

 

[Submariner]

Read the SQL tutorial, not only will that help you display your results but it will cover sanitizing user input so you don't get accacked/hacked via SQL injection.

 

[misson]

When asking for help, describe what you expect and what you get, including any error messages and program output.

Please use

, [html] or [code] tags (as appropriate) rather than [quote] when posting code so as to preserve indentation and colorize it.

As Submariner mentions, your code is vulnerable to [url=http://unixwiz.net/techtips/sql-injection.html]SQL injection[/url], which is a very serious [url=http://bobby-tables.com/]security risk[/url]. To fix this hole, switch from the outdated [URL="http://x10hosting.com/forums/programming-help/162529-php-begin-deprecation-ext-mysql-start-moving-your-development-pdo-now.html"]mysql extension[/URL] to [URL=http://php.net/PDO]PDO[/URL] and use [URL=http://www.php.net/PDO.prepared-statements]prepared statements[/URL]. If you need a PDO tutorial, try "[URL=http://www.kitebird.com/articles/php-pdo.html]Writing MySQL Scripts with PHP and PDO[/URL]". The site you save may just be your own.

When posting [url=http://sscce.org/]code[/url], remove anything extraneous so as to produce a complete yet concise sample.

Database connection creation (including managing the database credentials) should be the responsibility of one class rather than handled separately in each script. The more scripts contain user credentials, the more files there are for you to secure and the greater chance of typos screwing things up. Reduce critical code repetition. See "[URL="http://x10hosting.com/forums/tutorials/12014-php-mysql-php-4.html#post613750"][PHP] MySQL and PHP[/URL]" and "[URL="http://x10hosting.com/forums/programming-help/117523-display-all-would-secret-while-mysql-broken.html#post665590"]Display all that would be secret while Mysql is broken[/URL]" for some partial examples.

[URL="http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select"]Don't use [FONT="Courier New"]SELECT *[/FONT][/URL]; select only the columns you need.

[URL="http://www.phpfreaks.com/blog/or-die-must-die"]Don't use [FONT="Courier New"]die[/FONT][/URL] when outputting HTML.

You don't need to number the results yourself. Use an [URL="http://www.w3.org/TR/html401/struct/lists.html#h-10.2"]ordered list[/URL] (<ol>).