skip to Main Content

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";
}
?>

2

Answers


  1. Chosen as BEST ANSWER
    <?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. 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