• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problem With WHERE Clause

#1
[eluser]james182[/eluser]
I am trying to get this to work with showing data where the colour is equal to D,E,F,G.
Post data is:
certi all,
clarity all,
colour = D-E-F-G-,
cut = all,
flou = all,
maxprice = 100000,
maxval = 5,
minprice = 100,
minval = 0.01,
pg = 1,
polish = all,
scat_id = null,
shape = Princess,
sym = all

Code:
public function get_diamond_by_shape($shape,$scat_id,$colour,$clarity,$certification,$cut,$polish,$symmetry,$fluorescence,$min_carat,$max_carat,$min_price,$max_price)
{
  
  //$colours = "'". implode("','", explode('-', substr_replace($colour ,"",-1))) ."'";
  
  
  $colours = explode('-', substr_replace($colour ,"",-1));
  $clarities = explode('-', substr_replace($clarity ,"",-1));
  $certifications = explode('-', substr_replace($certification ,"",-1));  
  
  print_r($shape);
  
  $this->db->where('shape', $shape);
  $this->db->or_where_in('colour', $colours);
  $this->db->or_where_in('clarity', $clarities);
  $this->db->or_where_in('certification_type', $certifications);
  /*
  $this->db->where('cut', $cut);
  $this->db->where('polish', $polish);
  $this->db->where('symmetry', $symmetry);
  $this->db->where('fluorescence', $fluorescence);    
  */
  $this->db->where('carat >=', $min_carat);
  $this->db->where('carat <=', $max_carat) ;
  $this->db->where('cost BETWEEN ' . $min_price . ' AND ' . $max_price);
  
  $diamonds = $this->db->get('products_diamonds');
  
  //print_r($diamonds->result_array());
      
  return $diamonds->result_array();
  
}

#2
[eluser]marcogmonteiro[/eluser]
Can you give us the query that that is reproducing, and the actually results you're getting?

#3
[eluser]james182[/eluser]
SO what is happening is the shape is not staying as defined and the colours and clarity aren't showing what is defined.

I should only see data that's colour is only D,E,F,G and clarity VVS1, VVS2 and the stone shape Princess.
But i'm not!


POST:
Code:
certi all
cla VVS1-VVS2-
col D-E-F-G-
cut all
flou all
maxprice 100000
maxval 5
minprice 100
minval 0.01
pg 1
polish all
scat_id
shape Princess
sym all

Results:
Code:
Array (
[0] => Array (
[id] => 1
[stone] => CD1002/(
-385R
)
[carat] => 0.39
[certification_type] => GIA
[shape] => Asscher
[colour] => F
[clarity] => VS1
[cut] =>
[polish] => VG
[symmetry] => G
[fluorescence] => N
[cert_no] => 12873565
[note] => 4.16X4.15X2.75
[cost] => 1700.00
[discount] => 34
)
[1] => Array (
[id] => 2
[stone] => CD4186/(
-319R
)
[carat] => 2.01
[certification_type] => GIA
[shape] => Marquise
[colour] => G
[clarity] => VS2
[cut] =>
[polish] => G
[symmetry] => G
[fluorescence] => STR
[cert_no] => 11475928
[note] => 12.98x6.15x4.26
[cost] => 11800.00
[discount] => 38
)
[2] => Array (
[id] => 3
[stone] => CD5063/(
-299R
)
[carat] => 1.71
[certification_type] => GIA
[shape] => Emerald
[colour] => G
[clarity] => VVS2
[cut] =>
[polish] => G
[symmetry] => G
[fluorescence] => N
[cert_no] => 11527700
[note] => 7.48x6.25x4.20
[cost] => 9600.00
[discount] => 28
)
[3] => Array (
[id] => 4
[stone] => CD5406/(
-186R
)
[carat] => 0.18
[certification_type] => HRD
[shape] => Emerald
[colour] => E
[clarity] => VVS1
[cut] =>
[polish] => G
[symmetry] =>
[fluorescence] => N
[cert_no] => 20023393259
[note] => 4.03x3.03x1.58
[cost] => 1360.00
[discount] => 15
)
[4] => Array (
[id] => 5
[stone] => CD5661/-196R
[carat] => 0.25
[certification_type] => HRD
[shape] => Oval
[colour] => F
[clarity] => VVS1
[cut] =>
[polish] =>
[symmetry] => G
[fluorescence] => N
[cert_no] => 25016220
[note] => 4.55X3.56X2.40
[cost] => 1750.00
[discount] => 15
)
[5] => Array (
[id] => 6
[stone] => CD6107/(
-214R
)
[carat] => 0.19
[certification_type] => HRD
[shape] => Emerald
[colour] => F
[clarity] => VS2
[cut] =>
[polish] => G
[symmetry] =>
[fluorescence] =>
[cert_no] => 20020599710
[note] => 4.18x2.72x1.87
[cost] => 1160.00
[discount] => 23
)

#4
[eluser]Sanjay Sarvaiya[/eluser]
Try this sql query without using where and or_where_in.
Code:
$query = 'SELECT * FROM products_diamonds WHERE (shape=? AND carat >= ? AND carat <= ? AND (cost BETWEEN ? AND  ?)) OR colour in (?) OR clarity in(?) OR certification_type in(?)';
$query = $this->db->query($query, array($shape, $min_carat, $max_carat, $min_price, $max_carat,$colours,$clarities,$certifications));

#5
[eluser]james182[/eluser]
Using this:
Code:
$sql = 'SELECT * FROM products_diamonds WHERE (shape=? AND carat >= ? AND carat <= ? AND (cost BETWEEN ? AND  ?)) OR colour in (?) OR clarity in(?) OR certification_type in(?)';
$query = $this->db->query($query, array($shape, $min_carat, $max_carat, $min_price, $max_carat,$colours,$clarities,$certifications));
Displays colour data:
Code:
OR colour in ('\'D\',\'E\',\'F\'')
Gives the error: Table 'products_diamonds' doesn't exist.

Which it does exist.

What should the input data be for $colours, etc...? Currently it's 'D','E','F','G'

$this->db->get('products_diamonds'); // Works

#6
[eluser]InsiteFX[/eluser]
Your select statements should be wrapped in double quotes not single quotes.

#7
[eluser]Sanjay Sarvaiya[/eluser]
yes you are wright InsiteFX.
I was busy and I forget to double quotes in select statements.


#8
[eluser]james182[/eluser]
i added the double quotes "SELECT * FROM.... "
but no joy.:down:

#9
[eluser]Matalina[/eluser]
Here is what I think you are trying to do but I could be wrong.

Code:
$this->db->where('shape', $shape);
  $this->db->where_in('colour', $colours);
  $this->db->where_in('clarity', $clarities);
  $this->db->where_in('certification_type', $certifications);
  $this->db->where('carat >=', $min_carat);
  $this->db->where('carat <=', $max_carat) ;
  $this->db->where('cost BETWEEN ' . $min_price . ' AND ' . $max_price);

#10
[eluser]james182[/eluser]
Here is what i have changed to:
Code:
public function get_diamond_by_shape($shape,$scat_id,$colour,$clarity,$certification,$cut,$polish,$symmetry,$fluorescence,$min_carat,$max_carat,$min_price,$max_price)
{
$colours = explode('-', substr_replace($colour ,"",-1)); // outputs an array - array('D','E','F')
  $clarities = explode('-', substr_replace($clarity ,"",-1));
  $certifications = explode('-', substr_replace($certification ,"",-1));  
  
  $this->db->where('shape', $shape);
  
  $this->db->where_in('colour', $colours);
  $this->db->where_in('clarity', $clarities);
  $this->db->where_in('certification_type', $certifications);
  $this->db->where_in('cut', $cut);
  $this->db->where_in('polish', $polish);
  $this->db->where_in('symmetry', $symmetry);
  $this->db->where_in('fluorescence', $fluorescence);    
  
  $this->db->where('carat >=', $min_carat);
  $this->db->where('carat <=', $max_carat) ;
  $this->db->where('cost BETWEEN ' . $min_price . ' AND ' . $max_price);
  
  $diamonds = $this->db->get('products_diamonds');
  
  print_r($diamonds->result_array());
      
  //return $diamonds->result_array();
}
But this return nothing..

Here is the post data, could someone please post a working solution, as i have run out of ideas.

Code:
certi
cla
col D-E-
cut
flou
maxprice 100000
maxval 5
minprice 100
minval 0.01
pg 1
polish
scat_id
shape Princess
sym


shape=Princess&scat;_id=&col=D-E-&cla;=&certi;=&cut;=&polish;=&sym;=&flou;=&minval=0.01&maxval=5&minprice=100&maxprice=100000&pg=1



Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.