Link to home
Start Free TrialLog in
Avatar of Richard Korts
Richard KortsFlag for United States of America

asked on

Instering to MySQL table

This program is supposed to upload an Excel Spreadsheet into a MySQL table in a MySQL database:
<?php
error_reporting(E_ALL ^ E_DEPRECATED);
ini_set('display_errors', 1);
include "db_connect.php";
//echo "entered example.php<br>";
require_once 'Excel/reader.php';


// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();


// Set output Encoding.
$data->setOutputEncoding('CP1251');

/***
* if you want you can change 'iconv' to mb_convert_encoding:
* $data->setUTFEncoder('mb');
*
**/

/***
* By default rows & cols indeces start with 1
* For change initial index use:
* $data->setRowColOffset(0);
*
**/



/***
*  Some function for formatting output.
* $data->setDefaultFormat('%.2f');
* setDefaultFormat - set format for columns with unknown formatting
*
* $data->setColumnFormat(4, '%.3f');
* setColumnFormat - set format for column (apply only to number fields)
*
**/

$data->read('upload/Multi-Tower Pricing Kit_11.23.16.xls');

/*


 $data->sheets[0]['numRows'] - count rows
 $data->sheets[0]['numCols'] - count columns
 $data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column

 $data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
    
    $data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
        if 'type' == "unknown" - use 'raw' value, because  cell contain value with format '0.00';
    $data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format 
    $data->sheets[0]['cellsInfo'][$i][$j]['colspan'] 
    $data->sheets[0]['cellsInfo'][$i][$j]['rowspan'] 
*/
// clear existing products table
$qrycd = "DELETE from multi_tower";
$rescd = mysqli_query($link, $qrycd);
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
	$leftcell = $data->sheets[0]['cells'][$i][1];
	if ($leftcell != "Model" && $leftcell != "") {
		$qryi = "INSERT into multi_tower (model, 2tower, 3tower, 4tower) VALUES ('" . $data->sheets[0]['cells'][$i][1] . "', " . floatval($data->sheets[0]['cells'][$i][2]) . ", " . floatval($data->sheets[0]['cells'][$i][3]) . ", " . floatval($data->sheets[0]['cells'][$i][4]) . ")";
		echo "insert qry = " . $qryi . "<br>";
		$res = mysqli_query($link, $qryi);
	}
	
}

?>

Open in new window


This program was cloned from another similar one, I'm NOT doing anything new here.

Notice the echo's of the INSERT statements on line 65,this is so I can see EXACTLY what it is doing in case it doesn't work. The attached image is the output of that running of this.

When I look in the database, it is empty. No rows. So I manually copied one of the INSERT statements in the attached & used MySQL (thru phpMyadmin) to insert it.  That worked perfectly.

That's impossible.

What is wrong?
-multi_tower_inserts.jpg
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of gr8gonzo
gr8gonzo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just noticed this line.  Don't do this.  Instead use error_reporting(E_ALL) and correct any errors that are reported.
error_reporting(E_ALL ^ E_DEPRECATED); // NOPE, NOPE, NOPE, NOPE!!

Open in new window

Avatar of Richard Korts

ASKER

You are both right. gr8gonzo is right, $link is correct but everything is in the wrong folder, I need to be working in dev & I was stupidly working in the live environment with a database where that table did not exist.

I put in Ray's suggestion, saw it right away.

Away too long (four days) I forgot what I was doing.

Thanks
See my comment.

Thanks, guys!!