0

Background: I work with phpMyAdmin (MySQL Workbench) in a mysql DB. I write some PHP code to import data in the DB and execute this with the task scheduler of windows. <= this works fine!

My Topic: Now I want to export some data into a file in a Windows folder. At first I write the SQL code in phpMyAdmin to see some debug-infos. Independent of php my sql-query works fine.

If I put the code in the php-programm my export didn't work. I think the problem occurs because of my path specification. The other programmparts, specially the Update-Part, do what they should.

Here is my code:

<?php

include "../config.php";
$conn = new mysqli('192.168.10.120', 'alb5', 'alb5','testdatenbank');
if ( $conn->connect_error ) {
    die( "Connection failed: " . $conn->connect_error );
} //$conn->connect_error


$sql = "set @sql = concat(\"SELECT `LS_ID_Nr`, `Stk_pro_Krt_DL` * `Krt_DL` + `RB_Stk_pro_Krt_DL` * `RB_Krt_DL`, `Umstellzeit`, `Produktionszeit`, `Teilmeldung`, `Fertigmeldung` 
INTO OUTFILE 'C:/Temp/Export/Test - \", DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),\" - Test.txt' 
fields terminated by ';' 
lines terminated by '\r\n' 
From praemie where Proof_P = 0\");";

$sql = "prepare s1 from @sql;";
$sql = "execute s1;";
$sql = "DROP PREPARE s1;";
$sql = "UPDATE praemie SET Proof_P = 1 WHERE Proof_P = 0;";

    $result           = $conn->query( $sql );
echo("Error description: " . mysqli_error($conn));
?>

Does anybody have an idea how I specify a export-path, with sql in php? Thanks in advance.

N. Wieczorek
  • 13
  • 4

2 Answers2

1

This is creepy way of doing this.

I suggest to fetch the data into the php and store it via file_put_contents.

Quick example:

<?php
include "../config.php";
$conn = new mysqli('192.168.10.120', 'alb5', 'alb5','testdatenbank');
if ( $conn->connect_error ) {
    die( "Connection failed: " . $conn->connect_error );
} //$conn->connect_error

$query = 'SELECT `LS_ID_Nr` AS `LS_ID_Nr`, `Stk_pro_Krt_DL` * `Krt_DL` + `RB_Stk_pro_Krt_DL` * `RB_Krt_DL` AS ``, `Umstellzeit`, `Produktionszeit`, `Teilmeldung`, `Fertigmeldung`FROM praemie WHERE Proof_P = 0';

$result = $conn->query($sql);

file_put_contents('C:/Temp/Export/Test/test.txt', json_encode($result->fetch_all()));
Damian Dziaduch
  • 2,107
  • 1
  • 15
  • 16
  • Hey Damian, thanks for your answer. Can you briefly explain what your example is doing. – N. Wieczorek Aug 14 '18 at 08:54
  • Hey Damian, at first: cool feature is the "Select As" part. So I can give the long stuff "`Stk_pro_Krt_DL` * `Krt_DL`....." a short mark. Thanks for that. Can you briefly explain what your example is doing. I try to make it suitable for my code. The query, in mySQL Workbench, get the correct result, but the export still doesn't work. – N. Wieczorek Aug 14 '18 at 09:12
  • Hi @N.Wieczorek. This will query the data from the database and put the results it into `$result` variable. Then the all data from results is pulled via `$result->fetch_all()` and it is passed into the `json_encode` function which encodes the data into json format. The result of this function is put into `file_put_contents` which puts a json string into a specific file :) – Damian Dziaduch Aug 15 '18 at 03:39
  • @N.Wieczorek please take a look at this -> https://stackoverflow.com/questions/6574927/exporting-mysql-table-to-txt-or-doc-file-using-php – Damian Dziaduch Aug 15 '18 at 03:44
  • I'll take a look at it right away. Thanks for your help. :-) – N. Wieczorek Aug 15 '18 at 09:41
1

yeeeah it works now! Only for other people out there who want do the same.

Two things I have to change. I execute my programm in an other path so I have to write $include_path = 'C:/xampp/php/pear/PEAR/config.php'; this fixed the first error.

The second point: Your quick example $result = $conn->query($sql); have to be $result = $conn->query($query);

Down below is the whole code:

<?php

$include_path = 'C:/xampp/php/pear/PEAR/config.php';
$conn = new mysqli('192.168.10.120', 'alb5', 'alb5','testdatenbank');
if ( $conn->connect_error ) {
    die( "Connection failed: " . $conn->connect_error );
} //$conn->connect_error


$query = 'SELECT `LS_ID_Nr`, `Stk_pro_Krt_DL` * `Krt_DL` + `RB_Stk_pro_Krt_DL` * `RB_Krt_DL` AS `Ges. Stück`, `Umstellzeit`, `Produktionszeit`, `Teilmeldung`, `Fertigmeldung` FROM praemie WHERE Proof_P = 0';

$result = $conn->query($query);

if (mysqli_num_rows($result)!==0)
{
    file_put_contents('C:/Temp/Export/' . date('Y-m-d H-i-s') . '.txt', json_encode($result->fetch_all()));
} 

$sql = "UPDATE praemie SET Proof_P = 1 WHERE Proof_P = 0";

$result = $conn->query( $sql );
echo("Error description: " . mysqli_error($conn));
?>

*Edit: I integrate a proof if the result (num_rows of query) is not equal to zero and give the file a unique name with the variable date('Y-m-d H-i-s') (attention with windows filename permissions H - i - s not H : i : s)

Thanks alot Damian for all your knowledge and the help of the forum!

I'm happy now.

(~: First attempts with php and it works now fine! :~)

N. Wieczorek
  • 13
  • 4