USING PDO & MYSQL to get rows compared and switched to columns

I have a Products table, an Imprint table, a Manufacturer table and an ingredients table - along with other tables.

I am currently generating information and use what is called "file_name" as the id (it is a FDA assigned 36-40 digit id that goes with each product). What is happening now is people enter of few letters and the autocomplete dropdown provides the exact spelling. When they click SUBMIT and it brings up all entries that match. If they select a generic product it also includes the "brand" product - if they enter a brand it also includes the generic.

The current display is as follows:

Sold by:        Sold using   Available    Chemical name                Data based
                  name        Since:                                   on company 
                                                                     report submitted: 
C----- Health    Lortab      2011-01-13  Hydrocodone Bitartrate etc  2010-12-07

R—Distributors   Hydrocodone 2010-02-18  Hydrocodone Bitartrate etc  2009-12-17
                 Bitartrate
                 And Acetaminophen

C-- Health       Vicodin     1983-01-07   Hydrocodone Bitartrate etc  2009-11-03

R—Distributors   Hydrocodone 2010-07-30   Hydrocodone Bitartrate etc  2010-12-28
                 Bitartrate
                 And Acetaminophen

This is working fine. I will be adding a check box on the left which will allow up to 3 of the products to be chosen for comparison and additional information.

THE NEXT STEP:

After they check 1, 2 or 3 items, I want to display like this:

"ALL of the versions of the product you checked contain the following ingredients:" (Those ingredients common to all chosen products).

ACETAMINOPHEN, CELLULOSE, CORN, CROSPOVIDONE, HYDROCODONE BITARTRATE, MICROCRYSTALLINE, STARCH and STEARIC ACID

(Show the ingredients in each product NOT held in common by all. End result in COLUMNS)

“IN addition, EACH of the products you chose have the following ingredients:"

COLUMN 1(Product 1)         COLUMN 2 (Product 2)         COLUMN 3 ( Product 3)
COPOVIDONE                  MAGNESIUM STEARATE         CROSCARMELLOSE SODIUM
CROSCARMELLOSE SODIUM       POVIDONE                   D&C YELLOW NO. 10
D&C RED NO. 27              SILICON DIOXIDE            FD&C BLUE NO. 1
D&C RED NO. 30                                         POVIDONE
HYDRATED SILICA                                        SILICON DIOXIDE
MAGNESIUM STEARATE                                     SUCROSE

End of display

Ingredient table: t开发者_Python百科here are 20,000 rows,46 columns. Each row is a different drug and contains the id, file-name and then the ingredients, Each drug (row) has a different combination and number of ingredients. Unused fields are marked "Null";:

Ingredient columns in each row:

id, file_name, 0_gred, 1_gred, 2_gred, 3_gred, 4_gred, 5_gred, 6_gred, 7_gred, 8_gred, 9_gred, 10_gred, 11_gred, 12_gred, 13_gred, 14_gred, 15_gred, 16_gred, 17_gred, 18_gred, 19_gred, 20_gred (etc. up to 43_gred)

The question is how and what approach to use to get the format I need for the ingredients. I have experience in developing registration systems, I am fairly knowledgeable in PHP and am starting to get use to PDO. My Sql experience is minimal and is basically on a "need to know" basis.

I have wondered whether I should focus on a monster of a MySQL query or more on the php side. I thought about a query giving the ingredients common to all 3 and then subtracting that result from each individual drug list to get Part II but that appears to be quite advanced mysql – especially since I need the data to switch from row to COLUMN layout. Any help?

Another idea was to do a query that concatenated the ingredients of each row and then doing array procedures on the php side. Problems, I am having a hard time finding the right code to pull the ingredients out of each row since the number of “NULL” fields compared to “used” fields varies with each row. (How to count null COLUMNS in PDO query? I’ve tried and get the full count of columns in the table.)

To me, this is a braintwister with several steps. I’m looking for the “magic” MySql Code (if it exists) and or suggestions as to what approach (using php, mysql, PDO) you would pursue.

Your interest/help is appreciated!! Laura

I figured out a php way to do what I needed. I'm sure there is a faster MySql way. If you know of one please share! Laura

First, I set the Mysql empty values to default to "no_ingre".

Then I did the following code 3 times for the 3 choices: SELECT 'all the ingredient fields WHERE id=$name_of_id'

$result=$stmt->fetch(PDO::FETCH_ASSOC);
foreach($result as $key => $value) {
    if ($value == "no_ingre") unset($result[$key]);
}

FIND THE ingredients COMMON TO ALL:

$same_detect= array_intersect($result, $result2, $result3);
$p_same=(implode(", ", $same_detect));

echo "<br />The below ingredients are in all of the chosen products<br />";
echo $p_same;

To FIND THE ONES NOT COMMON TO ALL: Do the below code 3 times (for each ingredient array):

foreach ($result as $item) {
if (!in_array($item, $same_detect, true)) {
echo ("<tr><td>" . $item . "</td></tr>"); }