write a text file from mssql data

This is my first attempt to write a file. I have what I think should work but it does not.
[php]

Status Screen <?php $serial=$_POST["id"]; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); }

$sql="SELECT OrbeData_KithKitchens.dbo.WIP_master.uompschedulenumber, case
when OrbeData_KithKitchens.dbo.WIP_master.uompscheduleColor=‘SHORT’ Then ‘White’ else OrbeData_KithKitchens.dbo.WIP_master.uompscheduleColor
end as uompscheduleColor,jmaPartID,OrbeData_KithKitchens.dbo.WIP_master.ompSalesOrderID, uomlSorP,serial,rail,panel,stile,upsdescription,itemtype,jmaPartShortDescription
, uomlStyleGroup,status ,convert(varchar(19),createdate,0) as createdate ,convert(varchar(19),lastupdate,0) as lastupdate ,
Case
when status=10 then ‘Created’
when status=3 then ‘PreSanding’
when status=4 then ‘PrePaint’
when status=5 then ‘Paint’
when status=6 then ‘PostPaint’ else
‘’ end as Status1, m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER
FROM WIP_master
left join m1_DC.dbo.SalesOrders on m1_DC.dbo.SalesOrders.ompSalesOrderID=OrbeData_KithKitchens.dbo.WIP_master.ompSalesOrderID
where OrbeData_KithKitchens.dbo.WIP_master.serial=’$serial’
order by OrbeData_KithKitchens.dbo.WIP_master.uompschedulenumber,uomlSorP,upsdescription,jmaPartID ";

$result =odbc_exec($connect,$sql);
if(!$result){
exit(“Error in SQL”);
}

while (odbc_fetch_row($result)) {
$bgcolor= odbc_result($result, “uompscheduleColor”);
$jmaPartID= odbc_result($result, “jmaPartID”);
$ompSalesOrderID= odbc_result($result, “ompSalesOrderID”);
$uomlSorP= odbc_result($result, “uomlSorP”);
$serial= odbc_result($result, “serial”);
$upsdescription= odbc_result($result, “upsdescription”);
$itemtype = odbc_result($result, “itemtype”);
$jmaPartShortDescription = odbc_result($result, “jmaPartShortDescription”);
$status1 = odbc_result($result, “status1”);
$createdate=odbc_result($result, “createdate”);
$lastupdate=odbc_result($result, “lastupdate”);
$uompschedulenumber=odbc_result($result, “uompschedulenumber”);
$truck=odbc_result($result, “UOMPTRUCKNUMBER”);
}
$file= fopen(“test.txt”,“w”);

fwrite ($file,"$jmaPartID;$ompSalesOrderID;$uomlSorP;$serial;$upsdescription;$itemtype;$jmaPartShortDescription;$status1;$createdate;$lastupdate;$uompschedulenumber;$truck;");
fclose($file);
odbc_close($connect);
?>

[/php]

Well, Kat, my first question is why? Using the database is always better use of your resources than using
a text file. Text files are slow on servers. But, for testing purposes, your code is basically correct. It does
not include any field delimiters and therefore would create a very hard to read text file. And, this would
make it unusable if read back by a program. (No way to tell the end of one field and start of the next!)

For debugging, it would be best to place the text into a variable and display it to make sure the data is there
and intact. To do that, change your file-write function to something like this instead…
[php]
$temp = $jmaPartID . $ompSalesOrderID . $uomlSorP . $serial . $upsdescription . $itemtype . $jmaPartShortDescription . $status1 . $createdate . $lastupdate . $uompschedulenumber . $truck;
echo $temp;
fwrite($file, $temp);
[/php]
In this way you can debug that you have the correct data. I would add in some delimiter to make it more
readable like this:
[php]
$temp = $jmaPartID . “-” . $ompSalesOrderID . “-” . $uomlSorP . “-” . $serial . “-” . $upsdescription . “-” . $itemtype . “-” . $jmaPartShortDescription . “-” . $status1 . “-” . $createdate . “-” . $lastupdate . “-” . $uompschedulenumber . “-” . $truck;
echo $temp;
fwrite($file, $temp);
[/php]
Well, at least this way you can see where the data is failing… Hope that helps!

When I get this working I won’t the output look like this Example:

*FORMAT,WIP_2.lwl
*PRINTERNAME,KK_PrePaint
*JOBNAME,RRS092815122735
*QUANTITY,1
*DUPLICATES,0
Serial,14379901006
uompschedulecolor,CRIMSON
ompsalesorderid,143799
uompschedulenumber,092815-02
uomlstylegroup,
jmapartid,ESDTBX24-24
jmapartlongdescription,22.375 x 21 x 4 w/ Blumotion
stile,
panel,
rail,
upsdescription,mapBW
uomlsorp,PAINT
unitcount,1
jmaproductionquantity,1
*PRINTLABEL

the file is saved in a folder and picked up by our labeling software and prints a label.

This is for lost labels that need to be reprinted from time to time

I get these errors

Notice: Undefined index: id in /var/www/html/kk_serial_reprint.php on line 7

Notice: Undefined variable: jmaPartID in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: ompSalesOrderID in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: uomlSorP in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: upsdescription in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: itemtype in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: jmaPartShortDescription in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: status1 in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: createdate in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: lastupdate in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: uompschedulenumber in /var/www/html/kk_serial_reprint.php on line 52

Notice: Undefined variable: truck in /var/www/html/kk_serial_reprint.php on line 52

[php]

Status Screen <?php $serial=$_POST["id"]; $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); }

$sql="SELECT OrbeData_KithKitchens.dbo.WIP_master.uompschedulenumber, case
when OrbeData_KithKitchens.dbo.WIP_master.uompscheduleColor=‘SHORT’ Then ‘White’ else OrbeData_KithKitchens.dbo.WIP_master.uompscheduleColor
end as uompscheduleColor,jmaPartID,OrbeData_KithKitchens.dbo.WIP_master.ompSalesOrderID, uomlSorP,serial,rail,panel,stile,upsdescription,itemtype,jmaPartShortDescription
, uomlStyleGroup,status ,convert(varchar(19),createdate,0) as createdate ,convert(varchar(19),lastupdate,0) as lastupdate ,
Case
when status=10 then ‘Created’
when status=3 then ‘PreSanding’
when status=4 then ‘PrePaint’
when status=5 then ‘Paint’
when status=6 then ‘PostPaint’ else
‘’ end as Status1, m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER
FROM WIP_master
left join m1_DC.dbo.SalesOrders on m1_DC.dbo.SalesOrders.ompSalesOrderID=OrbeData_KithKitchens.dbo.WIP_master.ompSalesOrderID
where OrbeData_KithKitchens.dbo.WIP_master.serial=’$serial’
order by OrbeData_KithKitchens.dbo.WIP_master.uompschedulenumber,uomlSorP,upsdescription,jmaPartID ";

$result =odbc_exec($connect,$sql);
if(!$result){
exit(“Error in SQL”);
}

while (odbc_fetch_row($result)) {
$bgcolor= odbc_result($result, “uompscheduleColor”);
$jmaPartID= odbc_result($result, “jmaPartID”);
$ompSalesOrderID= odbc_result($result, “ompSalesOrderID”);
$uomlSorP= odbc_result($result, “uomlSorP”);
$serial= odbc_result($result, “serial”);
$upsdescription= odbc_result($result, “upsdescription”);
$itemtype = odbc_result($result, “itemtype”);
$jmaPartShortDescription = odbc_result($result, “jmaPartShortDescription”);
$status1 = odbc_result($result, “status1”);
$createdate=odbc_result($result, “createdate”);
$lastupdate=odbc_result($result, “lastupdate”);
$uompschedulenumber=odbc_result($result, “uompschedulenumber”);
$truck=odbc_result($result, “UOMPTRUCKNUMBER”);
}
$file= fopen(“test.txt”,“w”);
$temp = $jmaPartID . “-” . $ompSalesOrderID . “-” . $uomlSorP . “-” . $serial . “-” . $upsdescription . “-” . $itemtype . “-” . $jmaPartShortDescription . “-” . $status1 . “-” . $createdate . “-” . $lastupdate . “-” . $uompschedulenumber . “-” . $truck;
echo $temp;
fwrite($file, $temp);

fclose($file);
odbc_close($connect);
?>

[/php]

Okay, thanks for the explanation! The output will NOT look like that, but, can be altered to do so with ease.
I showed you an example to add in the DASH delimiter. You would need to change those to a line-feed or a
carriage return or both depending on what the label-printing software requires. Quite often just placing a
simple “\n” instead of the dash should work.

The error you are getting is based on the input field not being sent to your code. In your sample page, you
show us your HTML page with basically nothing on it and your PHP code which is pulling the ID number from
a posted value. If this file is called from another file, that one needs to pass the posted ID number using a
field named “id”. (Note caps!) So, your line of code: $serial=$_POST[“id”]; is not getting the ID number
and therefore all of your query code does not work. How are you passing this field to the code you showed
us? Is it from the same page or from another one with a form that you didn’t show us?

To test the rest of your code, you can hard-code an ID number for testing purposes, just manually set the
value to $serial=“12345”, where the 12345 is an id you know currently exists. This will create your file and
you can peek at it and see if the rest of the code is valid or not.

Hope that helps. Let us know where you are getting the posted data from.

One other issue…

You have the code that checks if the DB query works:
$result =odbc_exec($connect,$sql);
if(!$result){
exit(“Error in SQL”);
}
The problem is that it could work and just not return any data. Therefore, the logic of your code is messy. It
should be more in this format: (Just loosely to explain the logic…)
$result =odbc_exec($connect,$sql);
if(!$result){
exit(“Error in SQL”);
} else {
// Do something with the results…
}
You also must check the row count of the results to see if you found something. If you run a query on the
DB and it just doesn’t find any data matching your query criteria, it will not return an error, but, it might give
you an empty results. (No results does not mean an error!) Therefore, inside the “do something” part in
the above sample, you need to further check for the data in the results. (If one item found or more than 0
found, you need to then create your text file. The function odbc_num_rows() will tell you how many records
were found in the results recordset. If positive, then create your text file. If not, display “no data for that
id was found!” or whatever.

Hope all of this makes sense to you… If not ask us…

Sponsor our Newsletter | Privacy Policy | Terms of Service