PHP Search of MySQL Database

[itsinmyhead]

I ran a search on the Forums to see if this topic had been covered/answered before.  I saw a bunch of posts with no replies and only a couple with - those ones with responses were from 2006 from the earliest, and I'm guessing things may have changed since then.  So, if these problems/questions have been answered more recently, please point me in that direction.  Otherwise, hopefully I can find some help here.

 

I've got a MySQL database setup that holds data for Classified ads.  The table is called "classifieds" with the data in the table being "ID" (set as the key & auto_increment), "category" and "description."  I was able to find a delightfully easy way to export our data from FileMaker Pro into a comma-separated file, which I could then upload to my server through PHPmyadmin.  It's sort of awesome that way.

 

Now, I just need my database to be visible and searchable online.  I'd like to be able to search keywords, sort by categories and sort by add date (which would be indicated by the ID number automatically given to the ads when imported).

 

I've done some searches online for these types of searches, but I'm not too sure about implementation.  Some of the PHP code asks for the server address, user name and password...  which isn't something I can see as a safe script.

 

I'm new to PHP & MySQL, so I'm looking for something that I can learn from while setting it up - not something that I can just upload to the site and be done with.

 

If anyone can help, please lend whatever advice you can.

 

Thanks so much!

[.josh]

in order for php to access the database it has to know the database hostname, username, password and database name. It has to be physically in the script somewhere.  People usually store those as constants or variables in a private folder above public_html/ and then include the file, so the outside world can't access it. 

 

It sounds to me like you should start with basic database handling and then build on that.

[itsinmyhead]

in order for php to access the database it has to know the database hostname, username, password and database name. It has to be physically in the script somewhere.  People usually store those as constants or variables in a private folder above public_html/ and then include the file, so the outside world can't access it. 

 

It sounds to me like you should start with basic database handling and then build on that.

 

Thanks for the quick reply, and I'll check out that link.

 

I do realize that the script would need to ask for that data, but several scripts that I came across had the request for that information in the same file as the search data itself...  meaning that, if that PHP file were uploaded, anyone could view/save the source and grab the password info.

 

Again, thanks for the reply, and if anyone else has any suggestions, please feel free to pass them along!

[.josh]

well actually, you can't view php source code.  php parses the code and spits out the resulting text (html, javascript code, css, whatever else is classified as "text" to the server).  No php code is sent to the browser, so people will never see your code that way. 

[itsinmyhead]

So, I'm playing around with a search script I came across.  It's giving me an error, though, and I can't figure out why.  The error is:

 

Parse error: syntax error, unexpected T_VARIABLE in search.php on line 9

 

Here's a snippet of the script:

 

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

  }

[DarkWater]

Missed a ; on the end if the trim() line.

[itsinmyhead]

No dice...  even with the semicolon there, I get the same error.

[DarkWater]

Are you sure you're reuploading the file? >_<

[itsinmyhead]

Haha, for sure!  I even quit out of my browser to make sure everything was reloading completely, and still nothing changed.

 

It's puzzling me, but probably you moreso since I don't know too much about PHP...  it SEEMS like it should be an easy fix.

[DarkWater]

That's strange, because if I use that code and add the ; on the trim() line, it shows no syntax errors.

[itsinmyhead]

Ugh, I'm just a dope.  I was adding the semicolon after the comment on that line instead of after the code itself.

 

It's interesting to note, though, that the semicolon is missing in the text file downloaded from the site that provided the script.

 

Ah well.  Thanks for your help!

[DarkWater]

Heh, no problem. >_<  Post back if you get any more errors or need some more assistance.

[itsinmyhead]

Alright!  I've got it working in the right direction so far.  I've got my database up, everything is searchable, and I've got the results ordered in the way that I want them to be ordered (newest come up first).

 

However, I'm only able to search one keyword.  Since we're going to be handling a number of classified ads, it would be helpful for people to be able to search more than one term in order to return a more specific result.

 

It looks like I may be heading towards a Full-Text Search...  is this correct?  And if so, do you have any good resources on this?  I've looked over a few sites, and I think this is what I'd need, but I want to make sure I'm diving into the right thing before I spend any time fooling with it.

 

I'm not sure if seeing any of my code would help in creating a Full-Text Search (or whatever it is that I'll need), but here it is anyhow.

 

I'm going to have a couple of other changes/additions to what I want to do, but I want to tackle this thing bit by bit rather than trying to figure it all out at once.

 

<?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(); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **

mysql_select_db("") or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "select * from Classifieds where Category like \"%$trimmed%\" or Description like \"%$trimmed%\"

  order by ID DESC"; // 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: "" . $trimmed . "" 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: "" . $var . ""</p>";

echo '<table border="1">
<TR><TD>Category</TD>
<TD>Description</TD></TR>';

while (list ($ID, $Category, $Description) = mysql_fetch_row($result)) {
echo "<TR><TD>$Category</TD>
<TD>$Description</TD></TR>";
}
echo '</table>'; 

$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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

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

[DarkWater]

Well, first you'd set up a FULLTEXT index on the Description field:

ALTER TABLE Classifieds ADD FULLTEXT INDEX (Description);

 

Then, when doing the query, it'll be something like:

 

SELECT * FROM Classifieds WHERE MATCH (Description) AGAINST ('$trimmed');

 

[itsinmyhead]

Heeeeey, lookit that!  It actually IS searchable with more than one keyword.  The problem before was that I was trying to search terms that were in the "Category" field and the "Description" field.  Since users are only going to be searching the Description field, I punched in some terms...  and it worked fine!

 

The thing I want to do next is to have a drop down menu to the side of the search box that will allow users to select a specific category of items to search for.  I figure it'll be a combination of a drop down form element with PHP scripting.

 

I'm going to look around for some details on that, but if you have any suggestions/ideas on that as well, fire away!

 

Thanks again for your help!

[itsinmyhead]

Here we go!  Much confusion!  The search is now online at: http://adkpennysaver.com/search.html

 

Yesterday, it worked splendidly.  Today?  There are some weird things happening.  Made even weirder because nothing was changed, heh.  When you perform a search that has more than one page of results, the "Previous" link isn't showing up.  Also, when you click "Next 10," nothing advances.  The page reloads and all, but it displays the same results as were there before.  I'm really puzzled as to what's going on here.

 

Here's the code....

 

<?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(); //(host, username, password)

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

// Build SQL Query  
$query = "select * from Classifieds where Category like \"%$trimmed%\" or Description like \"%$trimmed%\"

  order by Category"; // EDIT HERE and specify your table and field names for the SQL query
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</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: "" . $var . ""</p>";

echo '<table border="1">
<TR><TD width="20%" align="center">Category</TD>
<TD width="80%" align="center">Description</TD></TR>';

while (list ($ID, $Category, $Description) = mysql_fetch_row($result)) {
echo "<TR><TD>$Category</TD>
<TD>$Description</TD></TR>";
}
echo '</table>'; 

$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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

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

[itsinmyhead]

I just wanted to give this a little bump.  I had some good responses yesterday on my questions, so I just want to make sure the thread is able to be seen.  I'm really lost as to what the issue here is, especially since it was working perfectly fine yesterday.  I literally changed nothing, and now it's not working properly.  It's very bizarre.

[itsinmyhead]

Alright, this is just completely weird now.

 

The search worked fine yesterday in all aspects.  Today, it doesn't.

 

A new development...  it isn't working properly (won't advance to the next ten results) here: http://adkpennysaver.com/search.html

 

It DOES work here: http://phenixdesigns.net/psaver/test/search.php

 

Same exact code.  Same exact everything - just that the MySQL database is hosted on a different server.

 

How could it be that this problem is happening?  What is wrong with my server today that wasn't wrong yesterday?  Has anyone experienced anything like this before?