phpmyadmin Question posted in
The official documentation can be found here.

How to use PHP to query a view and write edited data to an outfile – Phpmyadmin

Goal: Use php to read, parse, & edit and write data from a view into a pipe delimited outfile.

Setup: I have a view “my_view” that has 11 columns/fields. I need data from the first 10 written to the pipe delimited file.

Complicating Factor: The tenth field has a string of text that may contain many comma separated values or it may be blank

Requirement: Output a file that has data separated by exactly 26 pipes

My conceptual solution: Use php to query the view and return the table with fields separated by commas, then find and replace all commas with pipes. Then calculate to ensure that each record/row has a total of 26 pipes even if there is no data to separate.

[I am using nuBuilder and can attach php to a button to initiate an action. I will attach this code to be executed by the button in nubuilder. I am able how to insert the code in nubuilder when I figure out how to write the php]

I’ve attempted solving this as a stored procedure in phpMyadmin but I can’t seem to dynamically calculate out the required number of pipes.

My view (which I want the data from) looks like this:

Select
  Signals.SignalName
  Signals.SddName
  Signals.Address
  Signals.Word
  Signals.MSB
  Signals.LSB
  Signals.Resolution
  Signals.DataType
  Signals.Units
  Enumerations.TypeName (This is the column containing string of data        seperated by colums.
  Signals.FK_EnuID
  WHERE Signals FK_EnuID = Enumeratiopns.ID

I think I’ve come up with the following code:

<?php
$servername='localhost';
$username = 'user';
$password = 'password';
$dbname = 'database';

//Create a connection
$con = new mysqli($servername, $username, $password, $dbname);
$sql = "SELECT * FROM my_view";
$result = $conn->query($sql);

if (result->num_rows > 0) {
 while ($row = $result->fetch_assoc()){

//string replace commas with pipes
$data = str_replace (',', '|', $row);

//Count number of pipes
$fields = substr_count($row, '|');

//Calculate the number of empty fields & insert pipes
$emptyfields = str_repeat('$|', (26-$fields));

//concatenate data with empty fields
$data .= $emptyfields;

//write data to file
file_put_contents (signals.dat, $data);
 }//end while
}
else{
      echo "0 results";
}
?>
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    <?php
$servername = 'myserver';
$username = 'user';
$password = 'password';
$dbname = 'mydb';

//create a connection
$con = new mysqli($servername, $username, $password, $dbname, 3306);
$sql = "SELECT * FROM my_view";
$result = $con->query($sql);
$rowcount = 0;
$filecount = 1;

if ($result->num_rows > 0) {
  while ($row = $result->fetch_assoc()){
//build logic for splitting file.
$rowcount = $rowcount +1;

if (($rowcount % 10000)==)){
  $filecount = $filecount +1;

//join row elements into a string
$rowstr = join(","$row);

//String Replace commas with Pipes
$rowstr2 = str_replace(',', '|', $rowstr);

//Count number of Pipes
$fields = substr_count($rowstr2, '|');

//Calculate the number of empty fields & insert pipes
$emptyfields = str_repeat('|', 26-$fields));

//Concatenate data with empty fields
$rowstr2 .= $emptyfields;
$rowstr2 .= PHP_EOL;
$filename = 'signals_'.$filecount.'.dat';

//Write data to file
file_put_contents ($filename, $rowstr2, FILE_APPEND);
 }//end while
}
else {
  echo "0 results";
}
?>

  2. 0

    Why have any PHP code?

    Given this query:

    SELECT  province, city, population
    INTO  OUTFILE '/tmp/xx.txt' COLUMNS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
    FROM  canada
    WHERE  population > 0
    LIMIT  11;

    It generates the file xx.txt:

    "Alberta"|"Killam"|1056
"Alberta"|"Smoky Lake"|1063
"Alberta"|"Irricana"|1091
"Alberta"|"Viking"|1106
"Alberta"|"Two Hills"|1147
"Alberta"|"Spirit River"|1157
"Alberta"|"Bassano"|1388
"Alberta"|"Clairmont"|1483
"Alberta"|"Elk Point"|1514
"Alberta"|"Wembley"|1574
"Alberta"|"Bon Accord"|1611

    Meanwhile, in your code, if (...num_rows>0) is redundant because while(...) will do nothing if there are no rows. Removing the if will happen to fix the bug. The bug is result instead of $result.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search