Working on a Database search and a bit stuck

[CraigH]

Hi

 

I'm working on a database search for a product but the products can be narrowed down to the client will get exactly what they're after, i've got a good amount of it working and sorted but the thing i'm stuck on is that you can get the products in an alternate finish, so you can get Brass, Nickel, Stainless Steel and what i have will only work if the database only has one option within the field value in the MySQL, but if i have Brass, Nickel in the value and search Brass it won't bring it back, i'm a little bit stuck on how to fix this.

 

Here is the code i've got for that like i say works if there is only one field in the MySQL, any help would be good. Thanks

 

<?php

include_once("mysql.php");

if (isset($_GET['Code_Of_Installation'])){
$Code_Of_Installation = $_GET['Code_Of_Installation'];
$Gland_Material = $_GET['Gland_Material'];
$Application_Category = $_GET['Application_Category'];
$Cable_Type = $_GET['Cable_Type'];
$Sealing_Configiration = $_GET['Sealing_Configiration'];
$Approvals = $_GET['Approvals'];
$Onshore_Offshore = $_GET['Onshore_Offshore'];
$where = array();
if ($Code_Of_Installation != '') $where['Code_Of_Installation'] = $Code_Of_Installation;
if ($Gland_Material != '') $where['Gland_Material'] = $Gland_Material;
if ($Application_Category != '') $where['Application_Category'] = $Application_Category;
if ($Cable_Type != '') $where['Cable_Type'] = $Cable_Type;
if ($Sealing_Configiration != '') $where['Sealing_Configiration'] = $Sealing_Configiration;
if ($Approvals != '') $where['Approvals'] = $Approvals;
if ($Onshore_Offshore != '') $where['Onshore_Offshore'] = $Onshore_Offshore;


$GLOBALS['r'] = smart_select('*','Search',$where);

include("search-results.php");

} else {

include("search-form.php");

}

[dgoosens]

... you know, you are allowed to put more than one sentence in a paragraph...

This is really hard to understand.

 

Anyways, you might also want to post your mysql.php script where, I guess, we can see what that smart_select function does.

[CraigH]

Heres the connection script

 

<?php


if (isset($_COOKIE['searchtest'])){
if (file_exists("dpDebug.php")) include("dpDebug.php");
}
if (!function_exists("deb")){
function deb($m,$l=5,$lb=''){
 if ($l <= 1) die($m);
}
}

if (is_dir("c:\\")){
  $_sql = mysql_connect("localhost","root","") or deb("Can't connect to db",1);
  mysql_select_db("Database",$_sql) or deb("Can't select db",1);
} else {
  $_sql = mysql_connect("localhost","User","Password") or deb("Can't connect to db",1);
  mysql_select_db("Database",$_sql) or deb("Can't select db",1);
}

function smart_select($what,$table,$where){
$q = "select $what from `$table`";
if (count($where) > 0) $q .= ' where';
foreach ($where as $field => $val){
 $comp = '=';
 if (substr($field,-2) == '!='){
	$comp = '!=';
	$field = substr($field,0,-2);
 }
 if (substr($field,-1) == '>'){
	$comp = '>';
	$field = substr($field,0,-1);
 }
 if (substr($field,-1) == '<'){
	$comp = '<';
	$field = substr($field,0,-1);
 }
 if (substr($field,-2) == '>='){
	$comp = '>=';
	$field = substr($field,0,-2);
 }
 if (substr($field,-2) == '<='){
	$comp = '<=';
	$field = substr($field,0,-2);
 }
 $q .= " `" . addslashes($field) . "` $comp '" . addslashes($val) . "' and";
}
if (count($where) > 0) $q = substr($q,0,-4);
deb("smart_select() query: $q",5);
return mysql_query($q);
}

[dgoosens]

my guess is the problem the "and" here:

$q .= " `" . addslashes($field) . "` $comp '" . addslashes($val) . "' and";

 

as for some cases it probably should be "OR"

[CraigH]

Thanks for the help, i've tried changing it to or and i'm getting this error

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/website.com/httpdocs/search-results.php on line 105

 

I checked it and got this back.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Brass' at line 1

 

 

The code from search-results.php is below incase its something to do with that.

 

<?php

$houses = array();

$n = mysql_num_rows($GLOBALS['r']);
if ($n == 0){
print "<b>Sorry, no results were found</b>";
}

//print "<table width='606'>\n<tbody>";

for ($i = 0; $i < $n; $i++){
$house = mysql_fetch_array($GLOBALS['r']);
/*	print "<tr><td width='150' valign='top'>\n";
print " <img src='/images/" . $house['pic1'] . "' />\n";
print "</td>";
print "<td valign='top'>";
*/

// print $house['address1'] . "<br><br>";

  print displayhouse($house);

}

[dgoosens]

don't forget to change

if (count($where) > 0) $q = substr($q,0,-4);

into

if (count($where) > 0) $q = substr($q,0,-3);

as OR is one letter shorter

[CraigH]

Thanks thats fixed the error, but it's not searching by finished.

 

I'll try and explain better what i'm after it to do as my original post probably doesn't make much sense.

 

Each product has a range of finishes like Brass, Nickel, Non-Metallic

 

Product A comes in Brass and Nickel

 

Product B comes in Nickel and Non Metallic

 

Product C comes in Brass

 

With what i have at the minute it'll only bring in Product C when it should be bring in A and C if i want C to come in i have to have "Brass, Nickel" if you know how to make my script do that, that would be great. Thanks

[dgoosens]

hi,

 

maybe you could post us your database table definition...

that should help

[CraigH]

Hi

 

I've exported one of the rows from the database. Is this what you'd need to see? Thanks

 

INSERT INTO `Search` (`Product_Name`, `Code_Of_Installation`, `Gland_Material`, `Application_Category`, `Cable_Type`, `Sealing_Configiration`, `Approvals`, `Onshore_Offshore`, `Link`) VALUES ('BW', 'IEC', 'Brass, Nickel', 'Industrial, Marine', 'Single Wire Armour (SWA), Aluminium Wire Armour  (AWA)', 'No Seal', 'GOST R, GOST K, ABS, LLOYDS', 'Onshore, Offshore', 'bw.php');

[dgoosens]

ok...

I think I start to get this...

 

I would, in this case, create a column for each type of finish...

this will make your queries much simpler.

 

[CraigH]

Hi

 

Thanks for the help, i've got this working now.

 

Cheers

[CraigH]

Hi

 

I thought i had this working properly but i've hit another problem.

 

The search is working fine when doing one by a time, but if i'm wanting Gland Material and Cable Type to both search and then narrow it down to ones that have both of them, but it's ignoring the Gland Material and only searching the Cable Type.

 

This is the code for the search part if there is a problem with this that anyone can see. Thanks for any help.

 

<?php

include_once("mysql.php");

if (isset($_GET['Code_Of_Installation'])){
$Code_Of_Installation1 = $_GET['Code_Of_Installation'];
$Code_Of_Installation2 = $_GET['Code_Of_Installation'];
$Code_Of_Installation3 = $_GET['Code_Of_Installation'];
$Gland_Material1 = $_GET['Gland_Material'];
$Gland_Material2 = $_GET['Gland_Material'];
$Gland_Material3 = $_GET['Gland_Material'];
$Gland_Material4 = $_GET['Gland_Material'];
$Gland_Material5 = $_GET['Gland_Material'];
$Application_Category1 = $_GET['Application_Category'];
$Application_Category2 = $_GET['Application_Category'];
$Application_Category3 = $_GET['Application_Category'];
$Application_Category4 = $_GET['Application_Category'];
$Application_Category5 = $_GET['Application_Category'];
$Application_Category6 = $_GET['Application_Category'];
$Application_Category7 = $_GET['Application_Category'];
$Application_Category8 = $_GET['Application_Category'];
$Application_Category9 = $_GET['Application_Category'];
$Application_Category10 = $_GET['Application_Category'];
$Application_Category11 = $_GET['Application_Category'];
$Application_Category12 = $_GET['Application_Category'];
$Application_Category13 = $_GET['Application_Category'];
$Application_Category14 = $_GET['Application_Category'];
$Application_Category15 = $_GET['Application_Category'];
$Application_Category16 = $_GET['Application_Category'];
$Cable_Type1 = $_GET['Cable_Type'];
$Cable_Type2 = $_GET['Cable_Type'];
$Cable_Type3 = $_GET['Cable_Type'];
$Cable_Type4 = $_GET['Cable_Type'];
$Cable_Type5 = $_GET['Cable_Type'];
$Cable_Type6 = $_GET['Cable_Type'];
$Cable_Type7 = $_GET['Cable_Type'];
$Cable_Type8 = $_GET['Cable_Type'];
$Cable_Type9 = $_GET['Cable_Type'];
$Sealing_Configiration = $_GET['Sealing_Configiration'];
$Approvals1 = $_GET['Approvals'];
$Approvals2 = $_GET['Approvals'];
$Approvals3 = $_GET['Approvals'];
$Approvals4 = $_GET['Approvals'];
$Approvals5 = $_GET['Approvals'];
$Approvals6 = $_GET['Approvals'];
$Approvals7 = $_GET['Approvals'];
$Approvals8 = $_GET['Approvals'];
$Approvals9 = $_GET['Approvals'];
$Approvals10 = $_GET['Approvals'];
$Approvals11 = $_GET['Approvals'];
$Approvals12 = $_GET['Approvals'];
$Onshore_Offshore1 = $_GET['Onshore_Offshore'];
$Onshore_Offshore2 = $_GET['Onshore_Offshore'];
$where = array();
if ($Product_Name != '') $where['Product_Name'] = $Product_Name;
if ($Code_Of_Installation1 != '') $where['Code_Of_Installation1'] = $Code_Of_Installation1;
if ($Code_Of_Installation2 != '') $where['Code_Of_Installation2'] = $Code_Of_Installation2;
if ($Code_Of_Installation3 != '') $where['Code_Of_Installation3'] = $Code_Of_Installation3;
if ($Gland_Material1 != '') $where['Gland_Material1'] = $Gland_Material1;
if ($Gland_Material2 != '') $where['Gland_Material2'] = $Gland_Material2;
if ($Gland_Material3 != '') $where['Gland_Material3'] = $Gland_Material3;
if ($Gland_Material4 != '') $where['Gland_Material4'] = $Gland_Material4;
if ($Gland_Material5 != '') $where['Gland_Material5'] = $Gland_Material5;
if ($Application_Category1 != '') $where['Application_Category1'] = $Application_Category1;
if ($Application_Category2 != '') $where['Application_Category2'] = $Application_Category2;
if ($Application_Category3 != '') $where['Application_Category3'] = $Application_Category3;
if ($Application_Category4 != '') $where['Application_Category4'] = $Application_Category4;
if ($Application_Category5 != '') $where['Application_Category5'] = $Application_Category5;
if ($Application_Category6 != '') $where['Application_Category6'] = $Application_Category6;
if ($Application_Category7 != '') $where['Application_Category7'] = $Application_Category7;
if ($Application_Category8 != '') $where['Application_Category8'] = $Application_Category8;
if ($Application_Category9 != '') $where['Application_Category9'] = $Application_Category9;
if ($Application_Category10 != '') $where['Application_Category10'] = $Application_Category10;
if ($Application_Category11 != '') $where['Application_Category11'] = $Application_Category11;
if ($Application_Category12 != '') $where['Application_Category12'] = $Application_Category12;
if ($Application_Category13 != '') $where['Application_Category13'] = $Application_Category13;
if ($Application_Category14 != '') $where['Application_Category14'] = $Application_Category14;
if ($Application_Category15 != '') $where['Application_Category15'] = $Application_Category15;
if ($Application_Category16 != '') $where['Application_Category16'] = $Application_Category16;
if ($Cable_Type1 != '') $where['Cable_Type1'] = $Cable_Type1;
if ($Cable_Type2 != '') $where['Cable_Type2'] = $Cable_Type2;
if ($Cable_Type3 != '') $where['Cable_Type3'] = $Cable_Type3;
if ($Cable_Type4 != '') $where['Cable_Type4'] = $Cable_Type4;
if ($Cable_Type5 != '') $where['Cable_Type5'] = $Cable_Type5;
if ($Cable_Type6 != '') $where['Cable_Type6'] = $Cable_Type6;
if ($Cable_Type7 != '') $where['Cable_Type7'] = $Cable_Type7;
if ($Cable_Type8 != '') $where['Cable_Type8'] = $Cable_Type8;
if ($Cable_Type9 != '') $where['Cable_Type9'] = $Cable_Type9;
if ($Sealing_Configiration != '') $where['Sealing_Configiration'] = $Sealing_Configiration;
if ($Approvals1 != '') $where['Approvals1'] = $Approvals1;
if ($Approvals2 != '') $where['Approvals2'] = $Approvals2;
if ($Approvals3 != '') $where['Approvals3'] = $Approvals3;
if ($Approvals4 != '') $where['Approvals4'] = $Approvals4;
if ($Approvals5 != '') $where['Approvals5'] = $Approvals5;
if ($Approvals6 != '') $where['Approvals6'] = $Approvals6;
if ($Approvals7 != '') $where['Approvals7'] = $Approvals7;
if ($Approvals8 != '') $where['Approvals8'] = $Approvals8;
if ($Approvals9 != '') $where['Approvals9'] = $Approvals9;
if ($Approvals10 != '') $where['Approvals10'] = $Approvals10;
if ($Approvals11 != '') $where['Approvals11'] = $Approvals11;
if ($Approvals12 != '') $where['Approvals12'] = $Approvals12;
if ($Onshore_Offshore1 != '') $where['Onshore_Offshore1'] = $Onshore_Offshore1;
if ($Onshore_Offshore2 != '') $where['Onshore_Offshore2'] = $Onshore_Offshore2;
if ($Link != '') $where['Link'] = $Link;
  

$GLOBALS['r'] = smart_select('*','Search',$where);

include("search-results.php");

} else {

include("search-form.php");

}

[CraigH]

I've tried making some modifications to this and i've still not been able to get it working, i can't see where i'm going wrong so any help would be great.

[Muddy_Funster]

Can you post up or explain your forms, I can't understand why you are assigning so many variables the same $_GET['input'] value.

[CraigH]

Hi

 

The search is at http://cmp-products.e-vps.net/search.php it's meant to narrow down for it gland material and code of installation but it's only using one and ignoring the other. Thanks

[Muddy_Funster]

OK, sonce you are only having one chosen value for each column in the DB per search, how about trying somthing like this :

<?php
     $pre_qry = "SELECT Code_Of_Instalation, Gland_Material, Cable_Type, Sealing_Configiration, Approvals, Application_Catagory, Onshore_Offshore FROM tableName";
     if ($_GET['Code_Of_Instalation'] && $_GET['Gland_Material'] && $_GET['Cable_Type'] && $_GET['Sealing_Configiration'] && $_GET['Approvals'] && $_GET['Application_Catagory'] && $_GET['Onshore_Offshore'] == ""){
     $qry_where = '';
     }
     else{
     $qry_where = 'WHERE ';

     if ($_GET['Code_Of_Instalation'] != ''){
     $code = $_GET['Code_Of_Instalation'];
     $qry_where = $qry_where.' Code_Of_Instalation = \''.$code.'\' AND ';
     }

     if ($_GET['Gland_Material'] != ''){
     $gland = $_GET['Gland_Material'];
     $qry_where = $qry_where.' Gland_Material = \''.$gland.'\' AND ';
     }

     if ($_GET['Cable_Type'] != ''){
     $type = $_GET['Cable_Type'];
     $qry_where = $qry_where.' Cable_Type = \''.$type.'\' AND ';
     }
     
     if ($_GET['Sealing_Configiration'] != ''){
     $seal = $_GET['Sealing_Configiration'];
     $qry_where = $qry_where.' Sealing_Configiration = \''.$seal.'\' AND ';
     }
     
     if ($_GET['Approvals'] != ''){
     $app = $_GET['Approvals'];
     $qry_where = $qry_where.' Approvals = \''.$app.'\' AND ';
     }
     
     if ($_GET['Application_Catagory'] != ''){
     $cat = $_GET['Application_Catagory'];
     $qry_where = $qry_where.' Application_Catagory = \''.$cat.'\' AND ';
     }
   
     if ($_GET['Onshore_Offshore'] != ''){
     $shore = $_GET['Onshore_Offshore'];
     $qry_where = $qry_where.' Onshore_Offshore = \''.$shore.'\' AND ';
     }
      $cutoff = (strlen($qry_where) - 4);
      $qry_where = substr($qry_where, 0, $cutoff);
     }
     
  $qry_full = $pre_qry.$qry_where;
  $result = mysql_query($qry_full) or die (mysql_error());
  echo '<table border="1"><tr><th>Code Of Instalation</th><th>Gland Material</th><th>Cable Type</th><th>Sealing Configiration</th><th>Approvals</th><th>Application Catagory</th><th>Onshore/Offshore</th></tr>';
  while ($row = mysql_fetch_assoc($result) {
  $code_out = $result['Code_Of_Instalation'];
  $gland_out = $result['Gland_Material'];
  $type_out = $result['Cable_Type'];
  $seal_out = $result['Sealing_Configiration'];
  $app_out = $result['Approvals'];
  $cat_out = $result['Application_Catagory'];
  $shore_out = $result['Onshore_Offshore'];
  echo '<tr><td>'.$code_out.'</td><td>'.$gland_out.'</td><td>'.$type_out.'</td><td>'.$seal_out.'</td><td>'.$app_out.'</td><td>'.$cat_out.'</td><td>'.$shore_out.'</td></tr>';
  }
  echo '</table>';   
?>

 

This is not tested, and will most certainly need tweeked a good bit, but it should be a little smoother than what you are using.  Let us know how it goes.

[CraigH]

Hi

 

Thanks very much for the help, i'm trying to get the above working, i'm just wondering on one thing, will this work for if i'm wanting to draw the information from Code_Of_Installation1, Code_Of_Installation2 and Code_Of_Installation3 when it does the search for Code of Installation.

 

The reason i had the other code to work as below was because i needed it to check all three of them, is it possible for it to use the below but instead of three have it in the $where to search all three as this is where the problem is coming in, as i've been able to get it to narrow it down by changing it back to And in the mysql.php file but it problem is now that it will only let me have one for the below and each product and each product has multiple finishes. Thanks

 

if ($Code_Of_Installation1 != '') $where['Code_Of_Installation1'] = $Code_Of_Installation1;
if ($Code_Of_Installation2 != '') $where['Code_Of_Installation2'] = $Code_Of_Installation2;
if ($Code_Of_Installation3 != '') $where['Code_Of_Installation3'] = $Code_Of_Installation3;

[Muddy_Funster]

Yeah, I'll be honest with you, I'm not 100% on what it is you are doing with that given

$Code_Of_Installation1 = $_GET['Code_Of_Installation'];
$Code_Of_Installation2 = $_GET['Code_Of_Installation'];
$Code_Of_Installation3 = $_GET['Code_Of_Installation'];

Looks to me as though they are all being given the same value.

I'm guessing it's some strange way of assigning the select option values for the inital form.

To keep using it you just need to make sure that the inital identifier variable in your isset($_GET['']) clause is the same name throughout just as it was before.

[dp]

CraigH, how did you acquire the code in your connection script? I wrote it and have not published it. Furthermore, it is not appropriate for your task.