Excel to MySQL Database - and fetching the data

[LondonT]

Hello,

 

I've made a relatively simple Excel spreadsheet, that I need to display the contents of on a website. After researching, it appears this data needs to be put into a Database in order to pull from it in a way that looks acceptable (A table).

 

Can someone outline how to convert to a MySQL database, please? I have tried doing the .csv option and it works, but unsure where to go from there.

 

The Excel sheet can be found here: https://docs.google.com/a/imediums.co.uk/spreadsheets/d/1u_H6ywHHYkuDGo0NBUpTLnky9ACHRnTuAUQESBKmcb8/edit?usp=sharing

 

The spreadsheets values change depending on the cost of 1 item (see the above link)

 

RE: the website - I need to have a form (a simple box) where a user can enter a number from 1 - 99. Depending on the value entered, depends on the data returned (see above, the values change depending on the price of one item). So, how do I get the form to return the correct data, based on the excel (and ultimately converted to MySQ database) data?

 

Many thanks!

[Barand]

Access to your file on Google was denied.

[requinix]

CSV is the right first step.

 

If you only need to show the information there, not do any sophisticated searching or make any modifications, and as long as there isn't "too much" data, then you can keep it as the CSV file and display it using PHP code.

Otherwise your best bet is, yes, to import it into a database. That also raises the question of whether the spreadsheet changes and, if so, how often and how you want to deal with updating the database.

 

If you do think the first option is appropriate, consider whether you'll ever possibly want to do more in the future. If there's a chance then you should do the database thing now.

Edited June 14, 2015 by requinix

[LondonT]

Access to your file on Google was denied.

 

Sorry, please see this URL (Updated to Public)

 

 

CSV is the right first step.

 

If you only need to show the information there, not do any sophisticated searching or make any modifications, and as long as there isn't "too much" data, then you can keep it as the CSV file and display it using PHP code.

Otherwise your best bet is, yes, to import it into a database. That also raises the question of whether the spreadsheet changes and, if so, how often and how you want to deal with updating the database.

 

If you do think the first option is appropriate, consider whether you'll ever possibly want to do more in the future. If there's a chance then you should do the database thing now.

 

There is 99 entries to display, depending on the number entered on the website.

 

For example: If you entered 1 in the form on the website, the 99 rows would show one set of data.

 

If you entered 9 in the form on the website, the 99 rows would show a different set of data.

 

In Excel,it's got the formulas ready to make the changes, when you edit the number (same as entering it into the formn on a webpage). It's this I Want to display on a site, but make it look presentable and not just a mess.

[Barand]

This function will display the content of a csv file as an html table;

function CSV2Table($csvfile, $columns=null) 
{
    $fp = fopen($csvfile, 'r');
    $header = fgetcsv($fp,2048);
    if ($columns) {
        $head_array = array_intersect($header, $columns);
    }
    else {
        $columns = $header;
        $head_array = $header;
    }
    $heads = array_flip($head_array);
    $out = '<table border="1"><tr><th>'.join('</th><th>', $columns)."</th></tr>\n";
    while ($rec = fgetcsv($fp, 2048)) {
        $out .= '<tr>';
        foreach ($columns as $c) {
            $out .= '<td>' . $rec[$heads[$c]].'</td>';
        }
        $out .= "<tr>\n";
    }
    $out .= "</table>\n";
    fclose($fp);
    return $out;
}

Example usage

I have a csv file with these column heads

"Feed_ID","Vehicle_ID","FullRegistration","Colour","FuelType","Year","Mileage","Bodytype","Doors","Make","Model",
"Variant","EngineSize","Price","PreviousPrice","Transmission","PictureRefs","ServiceHistory","PreviousOwners",
"Description","FourWheelDrive","Options","Comments","New","Used","Site","Origin","V5","Condition","ExDemo",
"FranchiseApproved","TradePrice","TradePriceExtra","ServiceHistoryText","Cap_ID"

But I want just some of these in different order

$csvfile = 'DrivenCarSales2.csv';

$required = array(
    "Make",
    "Model",
    "Year",
    "EngineSize",
    "Transmission",
    "Colour",
    "FuelType",
    "Mileage",
    "Price",
    );
echo CSV2Table($csvfile, $required);

or, for my wife

echo CSV2Table($csvfile, array("Make", "Model", "Colour") );

[requinix]

In Excel,it's got the formulas ready to make the changes, when you edit the number (same as entering it into the formn on a webpage). It's this I Want to display on a site, but make it look presentable and not just a mess.

Um, formulas?