• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

Instering to MySQL table

This program is supposed to upload an Excel Spreadsheet into a MySQL table in a MySQL database:
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.

* 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 
// 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?
Richard Korts
Richard Korts
  • 2
  • 2
2 Solutions
Ray PaseurCommented:
This is line 66:
$res = mysqli_query($link, $qryi);

Open in new window

You should test the value that is returned by mysqli_query() and stored in $res.  In case it's FALSE, you want to print out the value in mysqli_error($link).  This may tell you what went wrong.  For example, if there is a syntax error in the query, you may need to put backticks around the column names.
Given what you've said, I think you have 2 probable causes here. Either:

#1. $link is not set properly.


#2. You're connected but you haven't selected a database, or you're connected to the wrong database.

I'd lean towards #2. Maybe you copied the script but the db_connect isn't pointing to the database you expect?
Ray PaseurCommented:
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

Richard KortsAuthor Commented:
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.

Richard KortsAuthor Commented:
See my comment.

Thanks, guys!!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now