Welcome Guest, Not a member yet? Register   Sign In
Problem With WHERE Clause
#11

[eluser]jmadsen[/eluser]
You need to clarify a couple of things:

1) An item can be "D" or "E" or "F" or "G", and the search is asking for ANY of those four? Is that correct?

2) Run
Code:
echo $this->db->last_query();

and paste your actual sql here so we can see it.
#12

[eluser]james182[/eluser]
1) Yes.

2) My Query
Code:
SELECT * FROM (`default_products_diamonds`) WHERE `shape` = 'Princess' AND `colour` IN ('D', 'E', 'F') AND `clarity` IN ('VVS1', 'VVS2') AND `certification_type` IN ('GIA', 'AGS') AND `carat` >= '0.01' AND `carat` <= '5' AND `cost` BETWEEN 100 AND 100000

#13

[eluser]jmadsen[/eluser]
1) check that the carat datatypes are not causing problems. You are comparing to strings in your query, but it seems like those would be floats.

2) Go into your db query tool (such as phpmyadmin) and just remove parts of the where clause one by one until you get data, then investigate why that last removed condition might not be working correctly

3) If it all seems correct still, post all the data for a single diamond that you THINK should be found in that query, but is not. Your query looks correct, but we have no way to know without seeing your data
#14

[eluser]james182[/eluser]
my DB Table
Code:
CREATE TABLE `default_products_diamonds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stone` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `carat` decimal(4,2) NOT NULL DEFAULT '0.00',
  `certification_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `shape` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `colour` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `clarity` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cut` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `polish` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `symmetry` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `fluorescence` varchar(5) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cert_no` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `note` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `cost` decimal(9,2) NOT NULL DEFAULT '0.00',
  `discount` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6311 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Data
Code:
INSERT INTO `default_products_diamonds` (`id`, `stone`, `carat`, `certification_type`, `shape`, `colour`, `clarity`, `cut`, `polish`, `symmetry`, `fluorescence`, `cert_no`, `note`, `cost`, `discount`)
VALUES
(1, 'CD1002/(-385R)', 0.39, 'GIA', 'Asscher', 'F', 'VS1', '', 'VG', 'G', 'N', '12873565', '4.16X4.15X2.75', 1700.00, '34'),
(2, 'CD4186/(-319R)', 2.01, 'GIA', 'Marquise', 'G', 'VS2', '', 'G', 'G', 'STR', '11475928', '12.98x6.15x4.26', 11800.00, '38'),
(3, 'CD5063/(-299R)', 1.71, 'GIA', 'Emerald', 'G', 'VVS2', '', 'G', 'G', 'N', '11527700', '7.48x6.25x4.20', 9600.00, '28'),
(4, 'CD5406/(-186R)', 0.18, 'HRD', 'Emerald', 'E', 'VVS1', '', 'G', '', 'N', '20023393259', '4.03x3.03x1.58', 1360.00, '15'),
(5, 'CD5661/-196R', 0.25, 'HRD', 'Oval', 'F', 'VVS1', '', '', 'G', 'N', '25016220', '4.55X3.56X2.40', 1750.00, '15'),
(6, 'CD6107/(-214R)', 0.19, 'HRD', 'Emerald', 'F', 'VS2', '', 'G', '', '', '20020599710', '4.18x2.72x1.87', 1160.00, '23'),
(7, 'CD6112/(-194R)', 0.22, 'HRD', 'Emerald', 'D', 'SI1', '', '', 'G', '', '020599704', '4.30x2.92x1.98', 940.00, '13'),
(8, 'CD6114/(-174R)', 0.23, 'HRD', 'Emerald', 'E', 'SI1', '', 'G', '', 'N', '20020599702', '4.32X2.83X1.98', 1110.00, '13'),
(9, 'CD6427/(-193R)', 0.19, 'HRD', 'Emerald', 'E', 'VVS2', '', 'G', '', 'N', '20020627837', '4.15x2.88x1.81', 1360.00, '12'),
(10, 'CD6719/(-196R)', 0.14, 'HRD', 'Round', 'F', 'SI1', 'VG', 'G', '', 'N', '20020686405', '3.30x3.37x2.02', 860.00, '15'),
(11, 'CD6730/(-196R)', 0.18, 'HRD', 'Round', 'E', 'SI1', 'VG', 'G', '', 'N', '020686419'', '3.60x3.61x2.24', 1100.00, '15'),
(12, 'CD6813/(-174R)', 0.19, 'HRD', 'Emerald', 'G', 'VVS2', '', 'G', '', '', '20020686441', '3.80x2.81x1.90', 1240.00, '13'),
(13, 'CD6872/(-159R)', 0.17, 'HRD', 'Round', 'I', 'VVS2', 'VG', 'G', '', 'SL', '20025044805', '3.64x2.19', 1120.00, '18'),
(14, 'CD6950/386KMF', 0.44, 'GIA', 'Asscher', 'D', 'VVS1', '', 'G', 'G', 'F', '12355479', '4.21X4.18X2.98', 3400.00, '35'),
(15, 'CD8039/(-231R)', 0.19, 'HRD', 'Princess', 'E', 'VVS2', '', 'G', '', 'N', '20030035102', '3.30x3.27x2.21', 1360.00, '20'),
(16, 'CD9793/(-289R)', 1.65, 'GIA', 'Emerald', 'G', 'VVS2', '', 'VG', 'G', 'N', '12767044', '7.47x6.03x4.04', 9600.00, '28'),
(17, 'CGTR003A/(-395R', 0.41, 'IGI', 'Round', 'F', 'SI1', '', '', '', '', 'M234654', '', 2200.00, '34'),
(18, 'CGTR003B/(-285R', 0.49, 'IGI', 'Round', 'F', 'SI1', '', '', '', '', 'M234654', '', 2200.00, '24'),
(19, 'CGTR008A/(-397R', 0.39, 'IGI', 'Round', 'F', 'SI1', '', '', '', '', 'M234658', '', 1900.00, '36'),
(20, 'CGTR008B/(-285R', 0.56, 'IGI', 'Round', 'F', 'SI1', '', '', '', '', 'M234658', '', 3200.00, '24');
#15

[eluser]jmadsen[/eluser]
First of all, be clear that there is a difference between helping you to understand a problem and actually doing your work for you. I get paid to do this all week, and I assume you're being paid as well. Dumping a bunch of code & data on a forum and asking someone to figure it out for you is not a habit you want to continue with.

I went ahead and helped you because I can tell it is prolly late Friday night where ever you are and you are stressing out, and I knew it would take me 5 minutes. I was wrong; it took me 2 minutes, and would have taken you the same if you had followed my instructions.

Lecture over. Your problem is none of your data in the sample you gave fits your criteria.

You only have one Princess stone, and its certification type is HRD. The query you posted is "`certification_type` IN ('GIA', 'AGS')"

Hope that was all it is; if not, take your query apart piece by piece and figure out which line is causing the issue.
#16

[eluser]james182[/eluser]
I know how the forum thing works, and i thank you very much for your time in help me out.

I will do as you say. Thanks you again much appreciated.
#17

[eluser]james182[/eluser]
i think i found the problem.
nedd to change '\'E\',\'F\''
Code:
SELECT * FROM (`default_products_diamonds`) WHERE `shape` = 'Princess' AND `colour` IN ('\'E\',\'F\'')

Replace with:
Code:
SELECT * FROM (`default_products_diamonds`) WHERE `shape` = 'Princess' AND `colour` IN ('E','F')

it's coming from here:
Code:
$colours =  implode("','", explode('-', substr_replace($colour ,"",-1)));
Code:
"','"
is getting placed in the query as
Code:
"\',\'"




Theme © iAndrew 2016 - Forum software by © MyBB