create a function

kat35601 · September 17, 2015, 2:43pm

I want to create a function I think for the results of my sql query . In the enclosed script how would I create this function of “$result” my hope is for easier changes and cleaner code. Thanks

[php]

Status Screen <?php include 'kk_order_inquiry_redtag_lk.php'; include 'db.php' ?> Todays Trucks <?php $connect =odbc_connect("removed"); if(!$connect) { exit("Connection Failed: " . $connect); }

$sql = $sql_redtag_select;
$sql .= $sql_case1;
$sql .= $sql_truck;
$sql .= $sql_create_redtag;

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

”;

echo “

”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;

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”);
$shiptruck=odbc_result($result, “truck”);

echo “

”;
echo “”;
echo “”;
echo “”;
echo “”;
echo "";
echo “”;
echo “”;
echo “”;
echo “”;
echo "";
echo “”;

}

odbc_close($connect);
?>

[/php]

PartID	OrderID	SorP	Serial	Description	ItemType	ItemDesc	StatusDesc	Create	Last	Schedule	Truck
$jmaPartID	$ompSalesOrderID	$uomlSorP	$serial	$upsdescription	$itemtype	$jmaPartShortDescription	$status1	$createdate	$lastupdate	$uompschedulenumber	$truck

benanamen · September 18, 2015, 12:15am

It appears you are well on your way to a mess of code. Post or attach all your code for review. Right off I see you are using way to much php to display 100% html. The whole table header html should be escaped out of php. Are you really wanting to create a table for one row of data and are you really only wanting to return one row of data? You also do not need to close the connection. It is automatically closed.

kat35601 · September 18, 2015, 1:17pm

I attached a sample jpg of the data output. as you will see I have a lot of rows. I am creating pages to narrow down the data for our users. Right now the users can look at this same report by OrderID, ItemType, Status and Truck. I created a page for each different filter but if I have to make a change then I have to go to each page and make a change so I am trying to learn how to create more dynamic pages. My code has change a little bit since the post I add more filters is all.

benanamen · September 18, 2015, 1:50pm

You don’t need a page for each filter. Unless you post your code you’re not going to get the best answers. A SQL dump of your database schema will also be helpful.

ErnieAlex · September 18, 2015, 4:23pm

One further comment is a function will slow down your database code. Every time you call the function, PHP
will search the page for the function to handle the call. Therefore, you are really duplicating the server’s
work. Here is a simple tutorial on selecting and display of data from a database. It is orientated to the
MySQLi code, but, is easy to alter to ODBC. Not sure if it helps, but, I would not use a function for this code.

http://www.w3schools.com/php/php_mysql_select.asp

kat35601 · September 18, 2015, 6:48pm

I attached some of the output of this very page.Thanks for the help I have about fourty of these that I am now changing and new there must be a better way.

[php]

Status Screen <?php $serial=$_GET["id"]; $connect =odbc_connect("orbe","sa","Jazz2008"); 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”);
}
echo “”;

echo “

”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;

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

echo “

”;
echo “”;
echo “”;
echo “”;
echo “”;
echo "";
echo “”;
echo “”;
echo “”;
echo “”;
echo "";
echo "";

$myarray= (odbc_fetch_array($result));
echo $myarray;

}
$myarray= (odbc_fetch_array($result));
echo $myarray;
odbc_close($connect);
?>

[/php]

Book1.txt (61.3 KB)

PartID	OrderID	SorP	Serial	Description	ItemType	ItemDesc	StatusDesc	Create	Last	Schedule	Truck
$jmaPartID	$ompSalesOrderID	$uomlSorP	$serial	$upsdescription	$itemtype	$jmaPartShortDescription	$status1	$createdate	$lastupdate	$uompschedulenumber	$truck

benanamen · September 18, 2015, 6:56pm

You attached the data. I meant the schema to recreate your database along with the insert sql for the data.

What database are you using? Since your using ODBC I am assuming it is not Mysql.

What parts of the forty files are the parts that are different?

For starters you could put the table header into a HEREDOC variable in your main config file and call it as needed.

Additionally, you are doing formating in your code. It should be done with CSS

It appears you are using more than one database? Why is that?

Your DB connection string should also be in one place only.

Looking over your data it is clear there are improvements that can be made to your database.

[php]<?php
$table_header = <<<EOT"

" EOT;

echo $table_header;

?>[/php]

PartID	OrderID	SorP	Serial	Description	ItemType	ItemDesc	StatusDesc	Create	Last	Schedule	Truck

ErnieAlex · September 19, 2015, 12:15am

Well, looking at the data, it is just as Kevin says… Data !

Your code is set up to read one row of the data at a time and display it. Normally, you do this inside WHILE
loop nearly as you did. But, inside the WHILE, you used fetch_row which will result in just one row of data
and the WHILE loop will process only that one row of data.

For looping thru all of the data in the recordset from the query, you can add an index to the end of it and
loop thru them. So, you would do that something like:

$i=0;
while (odbc_fetch_row($result, $i)) {
Do something…
$i = $i +1;
}
That would load each row thru the entire fetch_row… I do not use ODBC normally, so not sure of the
exact code, but…

ADDED: I guess you could use the count of the rows in the $result and then use a FOR loop to go thru
them, too. That would place the $i increment inside the for function… Just another way…

astonecipher · September 19, 2015, 1:03am

I disagree with using a function slowing down the processing. I would do this in a method, that extends the class holding the connection details for the database.

Single query, single call, can be used across many pages… I would also look in to datatables for you table display. It is a JQuery plugin that allows searching, pagination, and sorting out of the box.

benanamen · September 19, 2015, 4:55am

Is clear from the data that the database has not been properly normalized.

kat35601 · September 21, 2015, 2:01pm

The database is from there ERP and scanning system. It’s is a Microsoft Database. I have no control over it. I just picked up reporting of there scanning system so I can track what is going on.I am not a programmer as you can tell but it sure would help if I could learn to be one. I thank you for you help.

The data changes every few seconds and I am reporting where items are in there process as they go through the plants so workers can see what is coming to them. The company has different location that do different processes so by scanning them we can tell where a customers order is in the process also.

They have been scanning for years but have no real visibility other than a few crystal reports that they run from there ERP system.

I have placed Big screens throughout the company so they can see what they have and what is coming. The screens update on there on every 2 minutes.

This is something I would really like to continue with. Thanks again for the help.

ErnieAlex · September 21, 2015, 2:23pm

Okay, no control over the database and data. That is okay.

But, your code you showed us is still only reading one row and displaying it. You need to loop thru it and
display all of the rows. So, again, this line in your code:
while (odbc_fetch_row($result)) {
reads ONLY one row of data from the ODBC source. You can add an index behind it such as in the example
I posted before. Something like using a variable named $i in this example:
while (odbc_fetch_row($result, $i)) {
This will pull the row numbered in the variable. You can loop thru them with either a WHILE or a FOR loop.
You can locate the total numbers of row in the resulting DB query using this code and then loop thru ALL of
the rows using FOR this way…

$rows = odbc_num_rows( $result );
for( $i= 0; $i <= $rows; $i++ ) {
$row = odbc_fetch_row($result, $i);
… Display a row in your table for all the data in this row …
}

As you see, you figure out the number of items in the results of the query. Then, you loop thru them all,
displaying them as needed. It is a fairly simple process, just a simple for loop. Let us know if it works for
you. Oh, also, this would go after the display of the table’s headers as that is not part of the data.

And, thanks for explaining the process further. Now we understand why you can’t fix the database data
layout. ( What is called the database schema ! ) Good luck !