Solved

Instering to MySQL table

Posted on 2016-11-28
5
48 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 109

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 109

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

832 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