Solved

Instering to MySQL table

Posted on 2016-11-28
5
37 Views
Last Modified: 2016-11-28
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
0
Comment
Question by:Richard Korts
  • 2
  • 2
5 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 41904795
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.
0
 
LVL 34

Assisted Solution

by:gr8gonzo
gr8gonzo earned 250 total points
ID: 41904822
Given what you've said, I think you have 2 probable causes here. Either:

#1. $link is not set properly.

or

#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?
1
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41904859
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

0
 

Author Comment

by:Richard Korts
ID: 41904910
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
0
 

Author Closing Comment

by:Richard Korts
ID: 41904913
See my comment.

Thanks, guys!!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now