Help a PHP newbie?

[nogenius]

Hello,

I am trying to generate landing pages for a magazine affiliate. The affiliate has provided me an Excel spreadsheet with the columns of the magazine titles, a basic description of each magazine, and links to thumbnails and the order pages. In the past, I have been manually copying and pasting the information into my pages, but I know it could be done so much easier with PHP/mySQL.

Can anyone offer me some starting tips of how I would get this Excel data into a usable mySQL table form? I envision eventually just using PHP to automate the creation of my landing pages.

If anyone wants to see the actual table, just send me a PM.

Any help would be greatly appreciated.

 

[wesley]

I use php excelreader: SourceForge.net: PHP-ExcelReader

Read the documentation, then loop over the excel file 1 row at a time, find the columns you need and insert that in your db with a query.

 

[Grower]

ExcelReader is probably too hard for this easy job. Just create SQL within Excel and dump it into Mysql.

 

[shaggz]

ExcelReader is probably too hard for this easy job. Just create SQL within Excel and dump it into Mysql.

Either that, or export the data as CSV if the CSV format will keep your data intact. Then, the data will be easier to work with in PHP.

 

[LazyD]

Heres the method i use....

You have to save your XLS file as CSV, just do Save As and you will see it in the Format list...

Save the CSV file as anything you want you will specify the name in the script

Make a new PHP script and call it csvimport.php - Put the code below into that file, upload the CSV into the same directory as the PHP file and go wild

<?
mysql_connect('localhost', "USERNAME", "PASSWORD");
@mysql_select_db("DATABASE") or die("Unable to select database");

$file = 'filename.csv'; //File Name Here
$lines = file($file);

foreach ($lines as $line) {
$cols = explode(',', $line);

$r = 0;
foreach($cols as $data) {
$cols[$r] = utf8_encode($data);
$r++;
}

$query = "INSERT INTO `Tablename` (Column1, column2, column3, etc.) VALUES ('$cols[0]', '$cols[1]', '$cols[2]', 'etc')"; //Add or subtract as necessary for your use

mysql_query($query)
}
?>

 

[paycoguy]

I've done this using the LOAD DATA INFILE command with mysql, however you must first convert the excel file into csv.

Here is the reference doc:
MySQL AB :: MySQL 5.0 Reference Manual :: 13.2.5 LOAD DATA INFILE Syntax

 

[DewChugr]

If you have phpMyAdmin you can import a .csv file directly into mysql. You will need to create the db table first.