CODEWITHSUNDEEP
phpmysql
N. Wieczorek
N. Wieczorek

Reputation: 13

How to specify a export-path, with sql in php?

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.

Upvotes: 0

Views: 548

Answers (2)

N. Wieczorek
N. Wieczorek

Reputation: 13

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! :~)

Upvotes: 1

Damian Dziaduch
Damian Dziaduch

Reputation: 2127

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()));

Upvotes: 1

Related Questions