Excel to PHP - MYSQL

Question

Below data is in Excel file , i need that value(23,34..etc) should be output when any one select that resp data from drop-down(PHP File)

In excel:

Update Excel file :

Link for Excel file :

------------------------------|------------------
                AB            |       XE
------------------------------|------------------
               | b1 | b2 | b3 |  X1 | X2  | X3
------------------------------|------------------
           | A | 23 | 34 | 45 |  20 | 19  | 80
0.45 - 0.90| B | 20 | 31 | 85 |  23 | 23  | 67
           | C | 23 | 32 | 23 |  10 | 12  | 23
           | D | 10 | 12 | 23 |  56 | 45  | 23

---

Above date is in Excel file

Note : 0.45-0.90 is range from 0.45 to 0.90

IN PHP Page I have value in drop down :

1 } 0.45-0.90 ( range ) : thats 0.45,0.55,0.69,..0.90
2 } AB , XE
3 } A,B,C,D
4 } b1,b2,b3,X1,X2,X3

that numbers : 23,34,45,..etc is output, that when any user select above drop-down data from php page , it should take value from excel and print output in browser:

Like : example as below : when any one select :

0.45 - AB - A - b1 : then he will get output in textbox as ***23***
0.45 - AB - A - b2 : then he will get output in textbox as ***34***
0.55 - AB - A - b2 : then he will get output in textbox as ***34***
0.69 - AB - B - b2 : then he will get output in textbox as ***31***

0.69 - XE - B - X2 : then he will get output in textbox as ***23***
0.90 - XE - C - X3 : then he will get output in textbox as ***23***
0.90 - XE - D - X1 : then he will get output in textbox as ***56***

Above is just one box sample...i have many box in excel for other size range.

can any tell me how i can get same in PHP MYSQL Or any other Best Way.

Update with excel link above

Answer 1

It looks like you are going for something like this:

First, the DB schema:

CREATE DATABASE soa_36427533;
USE soa_36427533;

CREATE TABLE soa_ranges (
    ID UNSIGNED NOT NULL AUTO_INCREMENT,
    min FLOAT(10,2),
    max FLOAT(10,2),
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE soa_range_taxonomies (
    ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64),
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE soa_category (
    ID UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64),
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE soa_category_taxonomies (
    ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64),
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

CREATE TABLE soa_range_category_relationships (
    rangeID BIGINT UNSIGNED NOT NULL,
    rangeTaxID BIGINT UNSIGNED NOT NULL,
    categoryID BIGINT UNSIGNED NOT NULL,
    categoryTaxID BIGINT UNSIGNED NOT NULL,
    value INTEGER,
    PRIMARY KEY (rangeID, rangeTaxID, categoryID, categoryTaxID),
    FOREIGN KEY (rangeID) REFERENCES soa_ranges (ID) ON DELETE CASCADE,
    FOREIGN KEY (rangeTaxID) REFERENCES soa_range_taxonomies (ID) ON DELETE CASCADE,
    FOREIGN KEY (categoryID) REFERENCES soa_categories (ID) ON DELETE CASCADE,
    FOREIGN KEY (categoryTaxID) REFERENCES soa_category_taxonomies (ID) ON DELETE CASCADE
) ENGINE=InnoDB;

SQL to get the valid ranges, categories, and taxonomies:

SELECT * FROM soa_ranges ORDER BY ID;
SELECT * FROM soa_range_taxonomies ORDER BY ID;
SELECT * FROM soa_categories ORDER BY ID;
SELECT * FROM soa_category_taxonomies ORDER BY ID;

PHP to display these results as select boxes:

<?php
$db = new mysqli(HOST, USER, PASSWORD, 'soa_36427533');
if ( $db->connect_errno ) die;

$range_select_vals = array();
$ranges = $db->query( "SELECT * FROM soa_ranges ORDER BY ID" );
while ( $range = $ranges->fetch_assoc() ) {
    $range_vals = array();
    for ( $i =  $range['min'] * 100; $i < ( $range['max'] * 100 ); $i++ ) {
        $range_vals[] = floatval( $i / 100 );
    }
    $range_select_vals[$range['ID']] = $range_vals;
}
$ranges->free();

$range_taxonomy_select_vals = array();
$range_taxonomies = $db->query( "SELECT * FROM soa_range_taxonomies ORDER BY ID" );
while ( $range_taxonomy = $range_taxonomies->fetch_assoc() ) {
    $range_taxonomy_select_vals[$range_taxonomy['ID']] = $range_taxonomy['name'];
}
$range_taxonomies->free();

$category_select_vals = array();
$categories = $db->query( "SELECT * FROM soa_categories ORDER BY ID" );
while ( $category = $categories->fetch_assoc() ) {
    $category_select_vals[$category['ID']] = $category['name'];
}
$categories->free();

$category_taxonomy_select_vals = array();
$category_taxonomies = $db->query( "SELECT * FROM soa_category_taxonomies ORDER BY ID" );
while ( $category_taxonomy = $category_taxonomies->fetch_assoc() ) {
    $category_taxonomy_select_vals[$category_taxonomy['ID']] = $category_taxonomy['name'];
}
$category_taxonomies->free();
?>
<select id="range" name="range"><?php

foreach ( $range_select_vals as $range_key => $range_values ) {
    foreach( $range_values AS $value ) {
    ?>    <option value="<?php echo $range_key; ?>"><?php echo $value; ?></option><?php
    }
}
?>
</select>
<select id="range_taxonomy" name="range_taxonomy"><?php

foreach ( $range_taxonomy_select_vals as $taxonomy_key => $taxonomy_name ) {
?>    <option value="<?php echo $taxonomy_key; ?>"><?php echo $taxonomy_name; ?></option><?php
}
?>
</select>
<select id="category" name="category"><?php

foreach ( $category_select_vals as $category_key => $category_name ) {
?>    <option value="<?php echo $category_key; ?>"><?php echo $category_name; ?></option><?php
}
?>
</select>
<select id="category_taxonomy" name="category_taxonomy"><?php

foreach ( $category_taxonomy_select_vals as $taxonomy_key => $taxonomy_name ) {
?>    <option value="<?php echo $taxonomy_key; ?>"><?php echo $taxonomy_name; ?></option><?php
}
?>
</select><?php

You should send the four values to a processing script, which can output the final result by querying the value column of the soa_range_category_relationships table.