Write output to a txt file. Not csv or json just plan txt and I need to be to format the output

kat35601 · May 16, 2017

I have two queries that produce results that I need to write to a file. The file has requirements like customer should be 22 characters so if my customer number is 4567 then I will need to pad the other 18 characters with spaces to the right. So think I can do that but I don't know how to write the output to a file I have used fwrite() but can't seem to get it to print more than one line. I just need a point in the right direction. my code is without the fwrite()

/* write file */

$handle = fopen($my_file, 'w+') or die('Cannot open file: '.$my_file);

fwrite($handle, "$cit_cust_id");

fwrite($handle, "$record_type");

fwrite($handle, "name");

etc............

fclose($handle);*/

<html>
<head>
</head>
<body>
<?php
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}
$gr_total = 0;
 $gr_count =0;
$sql="


SELECT distinct '6767' AS CIT
		
	,''+'A'+ltrim(SO.ompCustomerOrganizationID)as customer
	--,ltrim(rtrim(so.ompShipLocationID)) as location
	,ltrim(rtrim(cmoName)) as name
	,ltrim(rtrim(cmoAddressLine2)) as address1
	,ltrim(rtrim(cmoCity)) as city
	,ltrim(rtrim(cmoState)) as state
    ,ltrim(rtrim(cmoPostCode)) as postal
	,ltrim(rtrim(cmoPhoneNumber)) as phone
	
FROM m1_kf.dbo.SalesOrders SO
LEFT JOIN m1_kf.dbo.Organizations ON cmoOrganizationID = SO.ompCustomerOrganizationID
WHERE SO.ompOrderDate >= CONVERT(VARCHAR, GETDATE(), 101) 
 
";
    
 $sql2="
  
 select  

 '6767'+''+'R'+ltrim(rtrim(ompCustomerOrganizationID))+' '+ltrim(rtrim(ompSalesOrderID)) as orderid
 , right('00000000'+cast(cast(round(ompOrderTotalBase,0)as int) as varchar(), as num
 ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') +'030'+
 REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as dateterm
 
 from m1_kf.dbo.SalesOrders where ompOrderDate >= CONVERT(VARCHAR, GETDATE(), 101) 
 ";   
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
 while ($row = odbc_fetch_array($result))
{
     $record_type=  $row['customer'];
 echo"<td><tr>" . $cit_cust_id = $row['CIT']."</td>";
 echo "<td>". str_pad($record_type,22,' ',STR_PAD_RIGHT)."</td>";
 echo "<td>". $name =$row['name']."</td>";
 echo "<td>".  $address=$row['address1']."</td>";
 echo "<td>".  $city=$row['city']."</td>";
 echo "<td>".  $state=$row['state']."</td>";
 echo "<td>".  $postal=$row['postal']."</td>";
 echo "<td>".   $phone=$row['phone']."</td></tr><br>";
     

 }

    $results =odbc_exec($connect,$sql2);
if(!$results){
exit("Error in SQL");
}
 while ($row = odbc_fetch_array($results))
{
 echo"<td><tr>" . $orderid = $row['orderid']."</td>";
 echo "<td>". $num=  $row['num']."</td>";
 echo "<td>". $dateterm =$row['dateterm']."</td></tr><br>";

     
 }

odbc_close($connect);
    


    
?>
</body>
</html>

kicken · May 17, 2017

You need to write in a new line if you want multiple lines.

fwrite($handle, "\n");

You can use fprintf to handle formatting your fields if needed. For example to get your 22-character wide, left-aligned customer ID you'd do:

fprintf($handle, "%-22s", $customerNumber);

With fprintf you could write out all your fields in a single call with your new line at the end of the format string.

kat35601 · May 17, 2017

A couple of question I have add the fprintf() to my code and I am getting output but I get a </td> in the file after the two items and on my screen output I get 22 and 30 at the bottom of the screen output that comes from

echo fprintf($handle, "%-22s", $record_type);

echo fprintf($handle,"%-30s",$name);

here I know this because if i change echo fprintf($handle,"%-30s",$name); to "%-31s" I then get 2231 on the screen

Thanks

SCREEN:

1234R10782 4573400002847052417030052417
1234R11976 4573500001462052417030052417
1234R11976 4573600002389052417030052417
1234R11267 4575500003352052517030052517
2231

FILE:

A11998 </td> FAMSA INC</td>

<html>
<head>
</head>
<body>
<?php
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}
$gr_total = 0;
 $gr_count =0;
$sql="


SELECT distinct '1234' AS CIT
		
	,''+'A'+ltrim(SO.ompCustomerOrganizationID)as customer
	--,ltrim(rtrim(so.ompShipLocationID)) as location
	,ltrim(rtrim(cmoName)) as name
	,ltrim(rtrim(cmoAddressLine2)) as address1
	,ltrim(rtrim(cmoCity)) as city
	,ltrim(rtrim(cmoState)) as state
    ,ltrim(rtrim(cmoPostCode)) as postal
	,ltrim(rtrim(cmoPhoneNumber)) as phone
	
FROM m1_kf.dbo.SalesOrders SO
LEFT JOIN m1_kf.dbo.Organizations ON cmoOrganizationID = SO.ompCustomerOrganizationID
WHERE SO.ompOrderDate >= CONVERT(VARCHAR, GETDATE()-1, 101) 
 
";
    
 $sql2="
  
 select  

 '1234'+''+'R'+ltrim(rtrim(ompCustomerOrganizationID))+' '+ltrim(rtrim(ompSalesOrderID)) as orderid
 , right('00000000'+cast(cast(round(ompOrderTotalBase,0)as int) as varchar(), as num
 ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') +'030'+
 REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as dateterm
 
 from m1_kf.dbo.SalesOrders where ompOrderDate >= CONVERT(VARCHAR, GETDATE()-1, 101) 
 ";   
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
    

 while ($row = odbc_fetch_array($result))
{
   
echo"<td><tr>" . $cit_cust_id = $row['CIT']."</td>";
  echo "<td>". $record_type=  $row['customer']."</td>";
 echo "<td>". $name =$row['name']."</td>";
echo "<td>".  $address=$row['address1']."</td>";
   echo "<td>".  $city=$row['city']."</td>";
   echo "<td>".  $state=$row['state']."</td>";
       echo "<td>".  $postal=$row['postal']."</td>";
  echo "<td>".   $phone=$row['phone']."</td></tr><br>";
     

 }

    $results =odbc_exec($connect,$sql2);
if(!$results){
exit("Error in SQL");
}
 while ($row = odbc_fetch_array($results))
{
 echo"<td><tr>" . $orderid = $row['orderid']."</td>";
 echo "<td>". $num=  $row['num']."</td>";
 echo "<td>". $dateterm =$row['dateterm']."</td></tr><br>";

 
 }
    /* write file */
$my_file = date('m-d-Y_hia').'.txt';
$handle = fopen($my_file, 'w+') or die('Cannot open file:  '.$my_file);
echo fprintf($handle, "%-22s", $record_type);
echo fprintf($handle,"%-30s",$name);
    
    
fclose($handle);


odbc_close($connect);
    

    

    
?>
</body>
</html>

Psycho · May 17, 2017

A couple of question I have add the fprintf() to my code and I am getting output but I get a </td> in the file after the two items and on my screen output I get 22 and 30 at the bottom of the screen output that comes from

echo fprintf($handle, "%-22s", $record_type);

echo fprintf($handle,"%-30s",$name);

If you don't want the "</td>" in the file output then DON'T put it in the variables

echo "<td>". $record_type= $row['customer']."</td>";
echo "<td>". $name =$row['name']."</td>";

Doing an assignment within an echo may "work" but it is not a good practice in any way. The assignment is setting the variable to everything that follows the equal sign until the semi-colon (indicating the end of a statement) is reached.

As to why you are getting the other two values at the bottom of the screen:

echo fprintf($handle, "%-22s", $record_type);
echo fprintf($handle,"%-30s",$name);

The fpringf() statements are to write to the file. So, what is the purpose of the echo?

There is just a lot here that doesn't make sense. Why, for example, are you running ltrim and rtrim on all the fields in the query? Values should be trimmed before storing them. But, if there is some legitimate reason not to, why not trim in the PHP code?

cyberRobot · May 18, 2017

...if there is some legitimate reason not to, why not trim in the PHP code?

@kat35601 - Note that MySQL also has a trim() function which removes both leading and trailing spaces. More information can be found here:

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_trim

kat35601 · May 18, 2017

Thanks for the help I have the output as i need it. I do have another question about how to add an sql update statement to update a transmission date is the sql table related to each orderID.

so I think I should add it to the sql2 while loop but I am not sure how.

Thanks for the help I am learning a lot.

The statement:


Update m1_kf.dbo.SalesOrders set uomptrans = 1 where ompSalesOrderID= $orderid

here is my code so far:

<html>
<head>
</head>
<body>
<?php
$connect = odbc_connect("removed");
if (!$connect) {
    exit("Connection Failed: " . $connect);
}
$gr_total = 0;
$gr_count = 0;
$sql      = "


SELECT distinct '1234' AS CIT
		,'A' as record_type
	,ltrim(rtrim(SO.ompCustomerOrganizationID))as customer
	--,ltrim(rtrim(so.ompShipLocationID)) as location
	,ltrim(rtrim(left(cmoName,30))) as name
	,ltrim(rtrim(cmoAddressLine2)) as address1
	,ltrim(rtrim(cmoCity)) as city
	,ltrim(rtrim(cmoState)) as state
    ,ltrim(rtrim(cmoPostCode)) as postal
	, ltrim(rtrim(REPLACE(REPLACE(REPLACE(cmoPhoneNumber, '(', ''), ')', ''), '-', ''))) as phone
	
FROM m1_kf.dbo.SalesOrders SO
LEFT JOIN m1_kf.dbo.Organizations ON cmoOrganizationID = SO.ompCustomerOrganizationID
WHERE SO.ompOrderDate >= CONVERT(VARCHAR, GETDATE()-1, 101) and cmoPhoneNumber !=''
 
";

$sql2   = "
  
 select  

 '1234' as clid
 ,'  ' as trade 
 ,'R'+ltrim(rtrim(ompCustomerOrganizationID)) as cust
 ,ltrim(rtrim(ompSalesOrderID)) as orderid
 , right('00000000'+cast(cast(round(ompOrderTotalBase,0)as int) as varchar(), as num
 ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as reqship
 ,'030' as terms
 ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as ship
 
 from m1_kf.dbo.SalesOrders where ompOrderDate >= CONVERT(VARCHAR, GETDATE()-1, 101) and ompOrderTotalBase >1
 ";
$result = odbc_exec($connect, $sql);
if (!$result) {
    exit("Error in SQL");
}
$mycount    = 0;
$tradestyle = ' ';
$my_file    = date('m-d-Y_hia') . '.txt';
$handle = fopen($my_file, 'w+') or die('Cannot open file:  ' . $my_file);
while ($row = odbc_fetch_array($result)) {
    $record_type = $row['record_type'];
    $cit_cust_id = $row['CIT'];
    $cust_num    = $row['customer'];
    $name        = $row['name'];
    $address     = $row['address1'];
    $city        = $row['city'];
    $state       = $row['state'];
    $postal      = $row['postal'];
    $phone       = $row['phone'];
    
     fprintf($handle, "%-4s%-2s%-1s%-15s%-30s%-30s%-17s%-2s%-9s%-10s", $cit_cust_id, $tradestyle, $record_type, $cust_num, $name, $address, $city, $state, $postal, $phone . "\n");
    
    $mycount = $mycount + 1;
    
}

$results = odbc_exec($connect, $sql2);
if (!$results) {
    exit("Error in SQL");
}
$mycount2 = 0;
$space6   = ' ';
$space12  = ' ';
$today    = date("mdy");
while ($row = odbc_fetch_array($results)) {
    $client     = $row['clid'];
    $trade      = $row['trade'];
    $cust       = $row['cust'];
    $orderid    = $row['orderid'];
    $num        = $row['num'];
    $reqship    = $row['reqship'];
    $terms      = $row['terms'];
    $ship       = $row['ship'];
    $mycount2   = $mycount2 + 1;
    $gr_total   = $gr_total + $row['num'];
    $tradestyle = '99';
    $custnum    = '999999999999999';
    $record     = 'S';
    $recordtype = '999999T999999999999999';
 fprintf($handle, "%-4s%-4s%-15s%-22s%-8s%-6s%-3s%-6s", $client, $trade, $cust, $orderid, $num, $reqship, $terms, $ship . "\n");
    
}
 fprintf($handle, "%-4s%-2s%-1s%-15s%06d%06d%-6s%012d", $client, $tradestyle, $record, $custnum, $mycount, $mycount2, $space6, $gr_total);
 fprintf($handle, "\n");
 fprintf($handle, "%-22s%06d%06d%-6s%012d%-12s%-6s", $recordtype, $mycount, $mycount2, $space6, $gr_total, $space12, $today);
fclose($handle);


odbc_close($connect);

?>
</body>
</html>

Sign In

Write output to a txt file. Not csv or json just plan txt and I need to be to format the output

Recommended Posts

kat35601

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

cyberRobot

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information