PHP Mysql search

[squigs]

Hello, I'm working on a site search which I've never done from scratch before and I've run into a couple road blocks along the way.

 

The first problem is that in my search form it seems that there is no minimum amount of characters that can be searched. For example I enter no value into the text box and hit enter or click submit and all my database items are displayed.

 

My second issue is simply with the criteria of the search. say I were to search for something like 48 x 12 I would get nothing when in fact there is an item in my database with those characters in the description however they are seperated in this fashion 48 in X 12 in.

 

Lastly is the problem where when I search for results that should produce a message stating that my search returned no results instead I get my blank results table.

 

I'm sure that there are some easy solution and minor tweaks that can be made to my code to rectify some or all of these issues and would appreciate those willing to help to share their knowledge with me.

 

Here is my code:

 

<?php require_once('Connections/price_db.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }

 

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

 

$colname_Recordset1 = "-1";

if (isset($_GET['title'])) {

  $colname_Recordset1 = $_GET['title'];

}

mysql_select_db($database_price_db, $price_db);

$query_Recordset1 = sprintf("SELECT * FROM price_db WHERE tb_name LIKE %s OR tb_desc LIKE %s", GetSQLValueString("%" . $colname_Recordset1 . "%", "text"),GetSQLValueString("%" . $colname_Recordset1 . "%", "text"));

$Recordset1 = mysql_query($query_Recordset1, $price_db) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?>

 

and the html is as follows:

 

    <div id="search_results">

  <?php do { ?>

      <table width="208" border="0" align="left" style="margin-right:20px;">

        <tr>

          <td width="220"><img width="175px" height="175px" src="<?php echo $row_Recordset1['tb_img']; ?>" /></td>

        </tr>

        <tr>

          <td height="45"><h2><?php echo $row_Recordset1['tb_name']; ?></h2>

            <div id="search_desc"><?php echo $row_Recordset1['tb_desc']; ?></div></td>

        </tr>

        <tr>

          <td height="37"><div id="search_price">$ <?php echo $row_Recordset1['tb_price']; ?>

            <form action="/save_to_cart.php" method="get" style="padding-top:15px;">

              <input type="text" name="quantity" size="10" value="Quantity" style="margin-right:12px; color:#666" onfocus="this.value=''"/>

              <input type="button" name="Add" value="Select" onclick="this.form.submit()"/>

            </form>

          </div></td>

        </tr>

      </table>

      <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

 

</body>

</html>

<?php

mysql_free_result($Recordset1);

 

?>

 

Thanksagain for all help

 

 

 

 

[brianlange]

You can use mysql's match against to solve the second issue. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html#function_match

 

To use this your table must be MyISAM.

[dheeraja]

Hello,

 

Firstly change $_GET['title'] to $_Get['quantity'] because you have a test field naming quantity.

 

Now if you want to show a message at NO RESULT then use "$totalRows_Recordset1" variable like:

 

if($totalRows_Recordset1 == 0)echo "No result found";

 

And its much better if you write quries by yourself, not use Dreamweaver for this type of typical stuffs, it mainly helps you with some common quries.

[squigs]

Thank you for your responses,

 

 

Firstly change $_GET['title'] to $_Get['quantity'] because you have a test field naming quantity.

 

 

This is good to know however I am working on the first form in the table at the moment that is simply (or not so simply) there to display my search results. After the results are displayed then I will have an option to select quantity etc.

 

As suggested I have tried inserting the following code

 

 

if($totalRows_Recordset1 == 0)echo "No result found";

 

however it either leads to a syntax error(expected 'or') or simply does nothing at all. If this is the proper solution can I have a hand at implementing it properly?

 

Thank you

[squigs]

Seeing how I am using $_GET['title'] would it be possible to somehow say

if ('title'==0) echo "no results found" or someway to set the conditions of 'title'?

 

[BlueSkyIS]

yes, but title will not be equal to 0 unless it actually is 0. i would check for empty string, == ''

[squigs]

Do you mean to add it as something like this?

if ($title == '')echo "Search result empty";

[BlueSkyIS]

yes. but my habit is to always use curly-brackets and indents, so i would write it like this:

 

if ($title == '') {
      echo "Search result empty";
}

[squigs]

Ok so I've played with my php code and finaly got it acting the way I want.

 

I still have not figured out how to set the minimum amount of characters in my text box though so any help on that would be appreciated.

 

I am going to post the code that I have come up with. It may be somewhat hacked together so if someone wants to help clean it up a bit feel free to help me out.

 

<?php require_once('Connections/price_db.php'); ?>
<?php

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  
  }
  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);


$var = $_GET['search'] ;
  
  if ($var == "")
  {
  header("location:http://search_results_0.php");
  }  
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
$colname_Recordset1 = "-1";
if (isset($_GET ['search'])) {
  $colname_Recordset1 = $_GET['search'];
}
mysql_select_db($database_price_db, $price_db);
$query_Recordset1 = sprintf("SELECT * FROM price_db WHERE tb_name LIKE %s OR tb_desc LIKE %s", GetSQLValueString("%" . $colname_Recordset1 . "%", "text"),GetSQLValueString("%" . $colname_Recordset1 . "%", "text"));
$Recordset1 = mysql_query($query_Recordset1, $price_db) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);//this is the one that always shows up
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

if ($totalRows_Recordset1 == 0)
{
  header("location:http://search_results_0.php");
  }
?>

[Username:]

instead of doing

 

if($title = ''){
}

 

Do:

 

$title = trim($title);
if(strlen($title) == 0) {
DO STUFF HERE
} 

 

It stops user from doing "            " and looking for matches

[squigs]

I like that one Dave, thanks for that.

So is it possible to use that statement to specify a min string length like this somehow?

if (strlen ($var) > 3)
{DO THIS
}

Basically trying to make a minimum amount of characters work....

I know this example won't work but is there someway to make it work?

Cheers

[BlueSkyIS]

yes.

 

if (strlen($var) > 3) {
     // do this
}