?
Solved

Instering to MySQL table

Posted on 2016-11-28
5
Medium Priority
?
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 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 35

Assisted Solution

by:gr8gonzo
gr8gonzo earned 1000 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 111

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

Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

777 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