Try to execute query on sql server via php

I need to load data onto a sql db I have searched and got examples and used them to get this far.
If I run this part I am able to connect to the db.
[php]<?php

$mysqli = new mysqli(‘localhost’,‘username’,‘password’,‘DB_Name’);
/* check connection */
if (mysqli_connect_errno()) {
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
};
?>[/php]
this is the full output of the code, It shows a echo but nothing is shown on the page a blank page is displayed so im not sure where my error is.
1)

[php]<?php

$mysqli = new mysqli(‘localhost’,‘username’,‘password’,‘DB_Name’);
/* check connection */
if (mysqli_connect_errno()) {
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
}

$sql = “LOAD DATA LOCAL INFILE ‘Data-Bin.CSV’
INTO TABLE Stats_Day
FIELDS TERMINATED BY ‘,’ ENCLOSED BY '”’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(@txdate, department, code, descript, QTY, Total)
SET txdate = STR_TO_DATE(@txdate, ‘%Y/%m/%d’);

//Try to execute query (not stmt) and catch mysqli error from engine and php error
if (!($stmt = $mysqli->query($sql))) {
echo “\nQuery execute failed: ERRNO: (” . $mysqli->errno . ") " . $mysqli->error;
};
?>[/php]
2)
Is there a way that I could ad this to the bunch?
DELETE Stats_Day;
this is a script that needs to import the stats daily so I need to clear the table data and then load the new data?
Im new to this and all help would be very much appreciated.

For starters, you are missing the closing quote on your $sql satement.

ok so I added the
[php]mysqli_close($con);
[/php] to the end of the sql but still not importing? not sure how I missed it thanks:)
[php] <?php

$mysqli = new mysqli(‘localhost’,‘username’,‘password’,‘DB_Name’);
/* check connection */
if (mysqli_connect_errno()) {
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
}

$sql = “LOAD DATA LOCAL INFILE ‘Data-Bin.CSV’
INTO TABLE Stats_Day
FIELDS TERMINATED BY ‘,’ ENCLOSED BY '”’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(@txdate, department, code, descript, QTY, Total)
SET txdate = STR_TO_DATE(@txdate, ‘%Y/%m/%d’);

//Try to execute query (not stmt) and catch mysqli error from engine and php error
if (!($stmt = $mysqli->query($sql))) {
echo “\nQuery execute failed: ERRNO: (” . $mysqli->errno . ") " . $mysqli->error;
}
mysqli_close($con);
?>[/php]

mysqli_close($con);

Where did you follow this statement? Look at the code, see how it is red? That shows that the quote is not closed.

Ok so I used a php code checker and this works
[php] <?php

$mysqli  =  new mysqli('localhost','username','password','DB_Name');
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$sql = "LOAD DATA LOCAL INFILE ‘Data-Bin.CSV’
INTO TABLE Stats_Day
";
mysqli_close($con);
?>[/php]

but im not sure why when I add the rest of the code

FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (@txdate, department, code, descript, QTY, Total) SET txdate = STR_TO_DATE(@txdate, '%Y/%m/%d');
it bombs out
am I missing some thing?
tried it with and without the ’ and ; and nothing?
please help

You start a string here

[php]$sql = "…[/php]

But you never end that string

[php]…";[/php]

[member=71845]JimL[/member], you’re the third person that told him what the problem is. Somebody is just not paying attention.

“Insanity: doing the same thing over and over again and expecting different results.” - Albert Einstein

Ok all criticism aside I have a problem that I hope can be resolve ???

this is my full code:
[php]<?php

$mysqli = new mysqli(‘localhost’,‘xxxxxxx’,‘xxxxxxxx’,‘xxxxxxx’);
/* check connection */
if (mysqli_connect_errno()) {
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
}
$sql = “LOAD DATA LOCAL INFILE ‘http://witsandmark.co.za/stats/JanaWitsand.CSV’ REPLACE INTO TABLE Stats_Day FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY '”’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (@txdate, department, code, descript, QTY, Total) SET txdate = STR_TO_DATE(@txdate, ‘%Y/%m/%d’)";
mysqli_close($con);

?>

<?php echo 'Total results: ' . $result->num_rows; ?>[/php]

if I run the query in sql it affects the rows no problem but the second I add the data to php no rows are affected?

:frowning:

LOAD DATA LOCAL INFILE 'http://witsandmark.co.za/stats/JanaWitsand.CSV' REPLACE INTO TABLE `Stats_Day` FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES (@txdate, department, code, descript, QTY, Total) SET  txdate = STR_TO_DATE(@txdate, '%Y/%m/%d')

please assist?!?

Ok so to try and see where my sql error is I ran this with no errors on the page??
[php]<?php

$mysqli = new mysqli(‘localhost’,‘xxxxx’,‘xxxxx’,‘xxxxx’);
/* check connection */
if (mysqli_connect_errno()) {
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
}

$query = “LOAD DATA LOCAL INFILE ‘xxxxx.CSV’ REPLACE INTO TABLE Stats_Day FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY '”’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (@txdate, department, code, descript, QTY, Total) SET txdate = STR_TO_DATE(@txdate, ‘%Y/%m/%d’)";
$res = $mysqli->query($query);

if ($mysqli->error) {
try {
throw new Exception(“MySQL error $mysqli->error
Query:
$query”, $msqli->errno);
} catch(Exception $e ) {
echo "Error No: ".$e->getCode(). " - ". $e->getMessage() . “
”;
echo nl2br($e->getTraceAsString());
}
}

mysqli_close($con);

?>[/php]
what am I missing?

All I get is a blank page :-[
no errors
and there is no data written to sql? :’(

used a php code checker to check the code and it seems fine?

I would start with this at the top of the page,

[php]error_reporting(E_ALL);
ini_set(‘display_errors’, 1);[/php]

Other than that, I don’t use a database the way it looks you are trying to, so I can’t be of much help, other than have you checked SQLfiddle?

Well, your code shows that it displays an error if one shows up in the TRY routine…

Also, PHP only prints or echo’s text if you tell it to… It can’t make up text to display for you!

So, in your code, if there are no errors, then it just ends. You can add a line at #23 like this:
[php]echo “Update completed!”;[/php]
then, if it works you will know it…

Normally, update code like this would be handled with a button on the page or called with a cron job.
If it is from a button, then you would have it show what happened on the page somehow. If it is a
batch job running on the server (cron job) then, it does not have to tell you anything. Either way, you
did not include any code to display that it works. So, it does! If that makes sense… (Did what you told
it to do and no more!)

Sorry for the slow responce,
It’s for a cron job and Im a newbie so not a lot of love as expected, so i tried figuring it out on my own but nothing!!
csv file looks like this:
[tt]“txdate”,“department”,“code”,“descript”,“QTY”,“Total”

“08/12/2013”,“002”,“600815037”,“TEABALL STRAINER”,“1”,“45”

“08/12/2013”,“002”,“600815044”,“ONTHOU BOEKIES”,“1”,“15”

“08/12/2013”,“004”,“004/001”,“Plastieksakkies”,“25”,“12.5”[/tt]
the code so far that I have modified substantually is this:
[php]<?php
error_reporting(E_ALL);
ini_set(‘display_errors’, 1);
// CONNECT TO THE DATABASE
$DB_NAME = ‘test’;
$DB_HOST = ‘localhost’;
$DB_USER = ‘test’;
$DB_PASS = ‘test1’;
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if (mysqli_connect_errno()) {
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
}

// A QUICK QUERY ON A FAKE USER TABLE
$query = “SELECT * FROM Stats_Day WHERE department=‘002’”;
$result = $mysqli->query($query) or die($mysqli->error.LINE);

// GOING THROUGH THE DATA
if($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo stripslashes($row[‘QTY’]);
}
}
else {
echo ‘NO RESULTS’;
}
// A QUICK QUERY ON A FAKE USER TABLE
$query = “LOAD DATA LOCAL INFILE ‘http://test.co.za/stats/Stats_Day.CSV
REPLACE INTO TABLE Stats_Day
FIELDS TERMINATED BY ‘,’ ENCLOSED BY '”’
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(@txdate, department, code, descript, QTY, Total)
SET txdate = STR_TO_DATE(@txdate, ‘%Y/%m/%d’)";
$result = $mysqli->query($query) or die($mysqli->error.LINE);

// GOING THROUGH THE DATA
if($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo stripslashes($row[‘QTY’]);
}
}
else {
echo ‘NO RESULTS’;
}
// CLOSE CONNECTION
mysqli_close($mysqli);

?>[/php]
the error that im getting is this:

1 Notice: mysqli::query(): send of 25 bytes failed with errno=104 Connection reset by peer in /home/sites/test.co.za/public_html/stats/phpjobscheduler/Stats_Day.php on line 37 Notice: mysqli::query(): send of 2 bytes failed with errno=32 Broken pipe in /home/sites/test.co.za/public_html/stats/phpjobscheduler/Stats_Day.php on line 37 Warning: mysqli::query(): HTTP request failed, unexpected end of socket! in /home/sites/test.co.za/public_html/stats/phpjobscheduler/Stats_Day.php on line 37 Notice: Trying to get property of non-object in /home/sites/test.co.za/public_html/stats/phpjobscheduler/Stats_Day.php on line 40 NO RESULTS
as you can see I have added a select command to the string just to test and I get the select result but still not able to execute the load data?? Im stuck please assist :'(
Sponsor our Newsletter | Privacy Policy | Terms of Service