10 Replies - 635 Views - Last Post: 18 June 2012 - 08:38 AM Rate Topic: -----

#1 hckyplyr606  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 15-November 11

Query a SQL database

Posted 14 June 2012 - 02:28 PM

Hello,

I am having a hard time interacting with my database. It is a MySQL database that is stored on my own localhost server. It lets me connect to it but once I try to query it, it won't display any results. It says that there are no results. So the query goes through, but doesn't find anything.

I am pulling a search term from a seperate HTML document and passing it into my php document and then using the LIKE SQL keyword to try to quer my database. Unfortunatly this is not working correctly and it is telling me that there are no results.

Any help would be greatly appreciated.

Thanks,
hckyplyr606


<html>
    <head>
        <title>Search the Database</title>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>
    <body>
        <form action ="searchDatabase2.php" method ="get">
            
            Search:
            <br /><input type="text" name="theSearch">
            <input type ="submit" name="submit" value="Submit">
        </form>
        <div></div>
    </body>
</html>




<?php

  $var = @$_GET['theSearch'] ;
  $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;
  }

  $databaseName = "locasetion";
//connect to your database ** EDIT REQUIRED HERE **
$dbConnection = mysql_connect("***", "***", "***"); //(host, username, password)

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

// Build SQL Query  
$query = "select * from BeerTable where BeerName like "%$trimmed%"  
  order by BeerName"; // EDIT HERE and specify your table and field names for the SQL query
$query .= "select * from BeerTable where BeerStyle like "%$trimmed%"  
  order by BeerStyle";
$query .= "select * from BeerTable where DistributorID like "%$trimmed%"  
  order by DistributorID";
$query = "select * from BeerTable where BeerID like "%$trimmed%"  
  order by BeerID";

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



This post has been edited by JackOfAllTrades: 14 June 2012 - 03:15 PM
Reason for edit:: Removed db login information


Is This A Good Question/Topic? 0
  • +

Replies To: Query a SQL database

#2 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,477
  • Joined: 07-April 08

Re: Query a SQL database

Posted 14 June 2012 - 02:43 PM

It looks like your queries are not formed properly. Also on line 37 your resetting $query to the last query instead of appending it like the ones above it. Try this and see if it works

$query = "select * from BeerTable where BeerID LIKE '%" . $trimmed . "%' ORDER BY BeerID";



you will notice that i added in ' after like and also moved the % to within the double quotes. In SQL a string (varchar, nvarchar, char, ect) is always wrapped in single quotes. Also you want the %'s to be within the query so you put them within double quotes.
Was This Post Helpful? 1
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Query a SQL database

Posted 14 June 2012 - 02:50 PM

You're wide open to SQL injection. You'd be best off learning about prepared statements to do your querying.
Was This Post Helpful? 3
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Query a SQL database

Posted 14 June 2012 - 04:41 PM

#1

The first 21 lines of your code are a bit odd. The logic there is basically this:

  • You copy $_GET["theSearch"] into $var without checking to see if it has a value first.

  • You trim $var (which may or may not have a value) and put it into $trimmed.

  • You check if $trimmed is empty.

  • You (finally) check if $var is set.


Do you see the problem there? You should be checking if $_GET["theSearch"] isset before anything else.


#2
The mysql_query function is incapable of executing multiple statements in one execution, so what you do in lines #31 through #40 would not work, even if the error rgfirefly24 pointed out is fixed. - If you want to execute multiple statements you need to call mysql_query for each of them, or use PDO or MySQLi. (Or, ideally, combine them into one statement.)

#3
Never ever use the result of a mysql_query call without verifying that it is valid. You shouldn't assume that the query is successful, or you'll likely end up with a bunch of errors down the line.
$res = mysql_query($sql);
if (!$res) {
    trigger_error(mysql_error(), E_USER_ERROR);
}
$count = mysql_num_rows($res); // Or whatever...



#4
All HTML documents should have a Doctype Declaration at the top. It is the single most important line in any HTML page! Don't ignore it.
Was This Post Helpful? 4
  • +
  • -

#5 CitooZz  Icon User is offline

  • New D.I.C Head

Reputation: -5
  • View blog
  • Posts: 4
  • Joined: 15-June 12

Re: Query a SQL database

Posted 15 June 2012 - 03:33 AM

something wrong in your code dude :nottalkingtoyou:
Was This Post Helpful? -5
  • +
  • -

#6 hckyplyr606  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 15-November 11

Re: Query a SQL database

Posted 15 June 2012 - 03:03 PM

View PostAtli, on 14 June 2012 - 04:41 PM, said:

#2
The mysql_query function is incapable of executing multiple statements in one execution, so what you do in lines #31 through #40 would not work, even if the error rgfirefly24 pointed out is fixed. - If you want to execute multiple statements you need to call mysql_query for each of them, or use PDO or MySQLi. (Or, ideally, combine them into one statement.)


So if I understand correctly (and forgive me if I am wrong, I am relatively new to PHP and SQL) but the query should look something like this? After typing this out I am not sure if this is right or not..


$query = "SELECT * FROM BeerTable WHERE BeerName LIKE '%" . $trimmed . "%' ORDER BY BeerName";
$numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

$query .= "SELECT * FROM BeerTable WHERE BeerStyle LIKE '%" . $trimmed . "%' ORDER BY BeerStyle";
$numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

$query .= "SELECT * FROM BeerTable WHERE DistributorID LIKE '%" . $trimmed . "%' ORDER BY DistributorID";
$numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

$query .= "SELECT * FROM BeerTable WHERE BeerID LIKE '%" . $trimmed . "%' ORDER BY BeerID";
 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);




Thanks again for all of your help. It is greatly appreciated.

hckyplyr606
Was This Post Helpful? 0
  • +
  • -

#7 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6066
  • View blog
  • Posts: 23,526
  • Joined: 23-August 08

Re: Query a SQL database

Posted 15 June 2012 - 03:56 PM

You really want ONE query here, not four, using a few ORs in your WHERE. As you have it now, you are simply going to overwrite the results of each query, ending up only with the results of the final query.
Was This Post Helpful? 0
  • +
  • -

#8 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Query a SQL database

Posted 15 June 2012 - 04:07 PM

Yea, JackOfAllTrades is right. You don't need four queries there, only one query with four conditions.

The WHERE clause accepts a list of conditions, so if you want the query to match one of four conditions, you would write that out like this:
SELECT stuff FROM myTable
WHERE condition1 OR condition2 OR condition3 OR condition4


Where each conditionN is a Boolean expression, like: `myColumn` = 'something', or more to the point of this thread: myColumn LIKE '%something%'.
Was This Post Helpful? 0
  • +
  • -

#9 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Query a SQL database

Posted 15 June 2012 - 04:12 PM

Also, you're wide open to SQL injection. What happens if a malicious user searches for this:
% UNION ALL SELECT Username + "::" + Password FROM Users WHERE Username LIKE %


Maybe your table Beertable has more than one row. No problems, they then search for this:
% UNION ALL SELECT Username + "::" + Password, NULL FROM Users WHERE Username LIKE %


...and so on and so forth, under they get the right number of rows, and the Username and Password is spat out in the search results for every single user.

You haven't got a Users table? No worries, let's try this next:
% UNION ALL SELECT table_name, column_name from information_schema.columns WHERE column_name LIKE %

...then the malicious user can build up from there, eventually figuring out how many tables you have, how many rows per table, and eventually the information stored in every row of every table.

If you use Prepared Statements, then you're safe from these sorts of attacks.
Was This Post Helpful? 4
  • +
  • -

#10 hckyplyr606  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 15-November 11

Re: Query a SQL database

Posted 18 June 2012 - 05:56 AM

Great! Thanks to all who have helped. I have got it working now without the prepared statements. That will be my first major update as I still have a bit to learn.

My next question, and I'm not sure if I should make a new thread or not, is what is the best way to display this information on my website? I obviously don't want plain text. Ideally I would like it to be displayed in some type of table that matches the rest of my website.

Thanks again for the help.
Was This Post Helpful? 0
  • +
  • -

#11 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3000
  • View blog
  • Posts: 10,390
  • Joined: 08-August 08

Re: Query a SQL database

Posted 18 June 2012 - 08:38 AM

Do not display it until you have switched to prepared statements. If you do, you're going to learn the hard way.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1