MySQL Query

[ultraspoon]

Hi everyone,

 

I am having a problem with my code. Its hard to explain, so I will try my best. Here is the Query.

 

$partialNumber = $_POST['partialNumber']; // Post the Partial number
$partialNumber = strtoupper($partialNumber);
$sql = <<<SQL_BLOCK
SELECT keynumber_product_option_value_description.product_id, keynumber_product_option_value_description.name, product.image, keynumber_product_option_value.keynumber_product_option_id, keynumber_product_option_value.keynumber_product_option_value_id 
FROM keynumber_product_option_value_description LEFT JOIN product ON (keynumber_product_option_value_description.product_id = product.product_id)
LEFT JOIN keynumber_product_option_value ON (keynumber_product_option_value_description.product_id = keynumber_product_option_value.product_id)
WHERE keynumber_product_option_value_description.name LIKE '%$partialNumber%' GROUP BY keynumber_product_option_value_description.name
SQL_BLOCK;

$result= mysql_query($sql) or die(mysql_error());
$display = '';
while ($row = mysql_fetch_array($result)){
$display .= <<<HTML_BLOCK

 

Here are the bits for the database

 

-- Table structure for table `keynumber_product_option`
--

CREATE TABLE `keynumber_product_option` (
  `keynumber_product_option_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `sort_order` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`keynumber_product_option_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=297 ;

-- --------------------------------------------------------
--
-- Table structure for table `keynumber_product_option_description`
--

CREATE TABLE `keynumber_product_option_description` (
  `keynumber_product_option_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`keynumber_product_option_id`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------
--
-- Table structure for table `keynumber_product_option_value`
--

CREATE TABLE `keynumber_product_option_value` (
  `keynumber_product_option_value_id` int(11) NOT NULL AUTO_INCREMENT,
  `keynumber_product_option_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(4) NOT NULL DEFAULT '0',
  `subtract` int(1) NOT NULL DEFAULT '0',
  `price` decimal(15,4) NOT NULL,
  `prefix` char(1) COLLATE utf8_bin NOT NULL,
  `sort_order` int(3) NOT NULL,
  PRIMARY KEY (`keynumber_product_option_value_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=588 ;


-- --------------------------------------------------------
--
-- Table structure for table `keynumber_product_option_value_description`
--

CREATE TABLE `keynumber_product_option_value_description` (
  `keynumber_product_option_value_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`keynumber_product_option_value_id`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------
--
-- Table structure for table `keynumber_order_option`
--

CREATE TABLE `keynumber_order_option` (
  `order_keynumber_option_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `order_product_id` int(11) NOT NULL,
  `product_keynumber_option_value_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `value` varchar(255) COLLATE utf8_bin NOT NULL,
  `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `prefix` char(1) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`order_keynumber_option_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

 

I have 4 items added for the search so far. When I search for "C" for example, the four items will appear, with this information

 

• Keynumber Option ID314
  Keynumber Option value676
  Product id 3947
  
• Keynumber Option ID314
  Keynumber Option value676
  Product id 3947
  
• Keynumber Option ID315
  Keynumber Option value678
  Product id 3961
  
• Keynumber Option ID315
  Keynumber Option value678
  Product id 3961
  

 

The problem is, the Keynumber Option Values, should be different for each item. They should be

 

• 675
  
• 676
  
• 677
  
• 678
  

 

So my query is not looping through, its just assigning the same value for each. Any help would be greatly appreciated, as I am so close to getting this to work.

 

Thank you

[ultraspoon]

Muddy_Funster Helped me with this before, strangely enough, when he last helped me, it was working fine, nothings changed and it has stopped working.

[fenway]

What does "stopped working" mean?