Refining results with new query

[squigs]

Hi,

I have set up a a site search using dreamweaver which I don't fully understand but is working to my liking so far. (shown below)

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

 

The trouble I have run into is when creating a new query which would allow me to show undisplayed results from the search results.

My primary search query looks something like this

<? php
$query_product = sprintf("SELECT * FROM inventory WHERE item_name LIKE %s OR item_desc LIKE %s ", GetSQLValueString("%" . $colname_product . "%", "text"),GetSQLValueString("%" . $colname_product . "%", "text"));
?>

 

That effectively shows the search results which I have set up in table format in my html. Next I want to query the same database table and show only results that aren't appearing from the primary search and I have been playing with it for a while now but keep getting undesired results. I have been using something like this.

<? php
$product_name = $row_product['item_name'];
$featured_list="";
$sql_features =mysql_query("SELECT * FROM inventory WHERE item_name <> '$product_name' GROUP BY sub_category_b LIMIT 5");
while ($row = mysql_fetch_array($sql_features)){	
$featured_id=$row['item_id'];
$featured_date=$row['date_added'];
$featured_name=$row['item_name'];
$featured_category=$row['category'];
$featured_cat_a=$row['sub_category_a'];
$featured_cat_b=$row['sub_category_b'];
$featured_low_price=$row['low_price'];
$featured_high_price=$row['high_price'];
$featured_image=$row['item_image'];
    $featured_list .= '<table width="140" border="0" align="left" style="font-family:Arial, Helvetica, sans-serif; margin-right:5px;">
    <tr>
      <td width="92" colspan="2" rowspan="1"><img src=" '.$featured_image.'" /></td>
      </tr>
    <tr>
      <td colspan="2" style="font-size:12px;">  '. $featured_name .'</td>
      </tr>
    <tr style="font-size:16px; font-weight:bold; color:#990000;">
      <td style="text-align:center;">
                                        $ '. $featured_low_price.' $'.$featured_high_price.'</td>
      </tr>
  </table>';
}
?>

 

This however is showing the same results as my primary query only grouping 1 result per category.

If anyone would care to have a look and perhaps explain how I would go about achieving this it would be much appreciated.

Thank you

[fenway]

You can't use * and GROUP BY.

[squigs]

Thanks, mental note taken!

 

However I still can't figure out how to use the results of my search query against items I would like to display in a "related but not the same" section of the same page.

 

 

 

[squigs]

So this provides the search results...

$searchSQL = mysql_query("SELECT  *, CONCAT('$', low_price) AS low_price FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%'");	  

 

and I want to create a query to display results called related items elsewhere on the page where there are no duplicate entries from the search results... Any tips or ideas?

[kickstart]

Hi

 

To get all the items not returned in that query you would use something like:-

 

SELECT a.*
FROM inventory a
LEFT OUTER JOIN (SELECT inventory_id FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%') b
ON a.inventory_id = b.inventory_id
WHERE b.inventory_id IS NULL

 

However I presume that there are other restriction rather than just all non displayed records.

 

All the best

 

Keith

[squigs]

Hi

 

To get all the items not returned in that query you would use something like:-

 

SELECT a.*
FROM inventory a
LEFT OUTER JOIN (SELECT inventory_id FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%') b
ON a.inventory_id = b.inventory_id
WHERE b.inventory_id IS NULL

 

However I presume that there are other restriction rather than just all non displayed records.

 

All the best

 

Keith

 

Amazing! Thanks for the response a couple tweaks and it is working like a champ. Now to change it, break it, and come crawling back for more help...

 

Cheers

[squigs]

So I have added another restriction which is not behaving as anticipated by me.. To the WHERE clause I want it to show results of equal sub_category_b but I'm not sure if I'm going about it the right way.

 

"SELECT a.*, CONCAT('$', low_price) AS low_price
FROM inventory a
LEFT OUTER JOIN (SELECT item_id, sub_category_b FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%') b
ON a.item_id = b.item_id
WHERE b.item_id IS NULL AND b.sub_category_b = a.sub_category_b LIMIT 5"

 

Thanks

[kickstart]

Hi

 

The assumption with that query is that it will only bring back any row where there is nothing found on the subselect. Hence if you want to check a value from the subselect to be something in particular it will never bring anything back.

 

However if you want to match against another field then put it in the ON clause.

 

"SELECT a.*, CONCAT('$', low_price) AS low_price
FROM inventory a
LEFT OUTER JOIN (SELECT item_id, sub_category_b FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%') b
ON a.item_id = b.item_id AND a.sub_category_b = b.sub_category_b
WHERE b.item_id IS NULL LIMIT 5"

 

All the best

 

Keith

[squigs]

Hi

 

The assumption with that query is that it will only bring back any row where there is nothing found on the subselect. Hence if you want to check a value from the subselect to be something in particular it will never bring anything back.

 

However if you want to match against another field then put it in the ON clause.

 

"SELECT a.*, CONCAT('$', low_price) AS low_price
FROM inventory a
LEFT OUTER JOIN (SELECT item_id, sub_category_b FROM inventory WHERE item_name LIKE '%$searchTermDB%' OR item_desc LIKE '%$searchTermDB%' OR sub_category_b LIKE '%$searchTermDB%') b
ON a.item_id = b.item_id AND a.sub_category_b = b.sub_category_b
WHERE b.item_id IS NULL LIMIT 5"

 

All the best

 

Keith

Thanks so much Keith, I had tried something similar but with the wrong syntax obviously. This will be a key ingredient in my understanding of writing proper queries!