I am running apache 2.0 and php5.4.4 and have MS SQL database set as the DSN. I am able to run the SQL queries via OBDC, but the php code will not execute to extracting multiple rows of data from the database. I can't figure out what is going on and I am not sure what other steps I need to do. The following command do not work.
odbc_fetch_row
odbc_fetch_array
odbc_num_rows
odbc_num_fields
Please not I have noi trouble with connecting to the database and submitting odbc_exec commands. Any help would greatly appreciate it.
Please see code below.
<?php
ini_set('display_errors', 'ON');
error_reporting(E_ALL);
//Connect to Database
$conn=odbc_connect('*******','*******','********');
if (!$conn) {
exit("Connection Failed: " . $conn);
}
parse_str(strtolower($_SERVER['QUERY_STRING']), $arv);
$ordernumbers = explode("\n",$arv[ordernumbers]);
// Build SQL Where Clause
foreach ($ordernumbers as &$item) {
if (strlen($item)>1) {
$ord_num = $ord_num."'".$item."', ";
}
}
$ord_nums = substr($ord_num,0,strlen($ord_num)-2);
if (strlen($ord_nums)<1) {
$ord_nums = "''";
}
// Getting the Data from database.
$sql="SELECT ivh_invoicenumber as 'Invoice Number' , ord_number as 'Order Number' , ivh_invoicestatus as 'Invoice Status'
FROM invoiceheader with (nolock)
WHERE ord_number in ($ord_nums)";
/*WHERE ord_number = '3206818'";*/
// Update Invoice Status
/*$update="UPDATE invoceheader
SET ivh_invoicestatus='$InvoiceStatus'
WHERE ivh_invoicenumber='$invoicenumber'"; */
//echo "SQL Query:<br>".$sql
$rs=odbc_exec($conn, $sql); //execute the query
/*odbc_fetch_row($rs, 0); //arrays start with subscript of 0...
$ord_nums=odbc_result($result, 1);*/
//You have to define the variable $rowCouter as it's created automatically...
function odbc_fetch_array_alternative($result, &$odbc_count) {
if (!(is_int($odbc_count))) { $odbc_count = 1; }
while(odbc_fetch_into($result,$odbc_row,$odbc_count))
{
$odbcTotalFields = odbc_num_fields($result);
for($x=0;$x<$odbcTotalFields;$x++) {
$arr[odbc_field_name($result, ($x+1))] = odbc_result($result,($x+1));
}
$odbc_count++;
return $arr;
}
}
/*while ($r = odbc_fetch_row($result)) {
}*/ //($result, $rownumber = null);
while ($r = odbc_fetch_row($result,$rowCounter = 0)) {
} //($result, $rownumber = null);
if (!$rs) {
exit("error in sql");
}
echo"<!DCOTYPE html>";
echo"<html>";
echo"<body>";
// Build the style
echo"<style>";
echo"table,th,td{";
echo"border:0.1px solid black;";
echo"border-collapse:collapse;}";
echo"th,td{";
echo"padding:5px;}";
echo"</style>";
//end of style
// Build of form
echo"<form>";
echo"<div align=\"center\">";
echo"<strong>Please enter Order Number(s):</strong><br />";
echo"<textarea name=\"ordernumbers\" style=\"width:\"250px;\" height:\"20px;\">".$arv[ordernumbers]."</textarea><br /><br />";
echo "<input type=\"submit\" name=\"submit\" value=\"Submit\"> <input type=\"reset\" name=\"Reset\" value=\"Reset\">";
echo"</form></div><br />";
// end of form
//Build the form
echo "<form method=\"POST\"><div align=\"center\">";
echo "<table style=\"width:\"800px\"><tr>";
echo "<th>Select</th>";
echo "<th>Invoice Number</th>";
echo "<th>Order Number</th>";
echo "<th>Invoice Status</th></tr>";
/*odbc_fetch_row($rs, 0);
while (odbc_fetch_row($rs)) {*/
while (odbc_fetch_row($rs)) {
$invnum=odbc_result($rs,"Invoice Number");
$ordernum=odbc_result($rs,"Order Number");
$invstatus=odbc_result($rs, "Invoice Status");
echo "<tr>";
echo "<td><input type=\"checkbox\" name=\"chk_$invnum\"></td>";
echo "<td>$invnum</td>";
echo "<td>$ordernum</td>";
echo "<td><select name=\"invstatus_$invnum\">";
if ($invstatus=="HLA") {
echo "<option value=\"HLA\" selected>OnHold for Audit</option>";
}else {
echo "<option value=\"HLA\" >OnHold for Audit</option>";
}
if ($invstatus=="PRN") {
echo "<option value=\"PRN\" selected>Printed</option>";
}else {
echo "<option value=\"PRN\" >Printed</option>";
}
if ($invstatus=="RTP") {
echo "<option value=\"RTP\" selected>Ready To Print</option>";
}else {
echo "<option value=\"RTP\" >Ready To Print</option>";
}
if ($invstatus=="XFR") {
echo "<option value=\"XFR\" selected>Transferred</option>";
}else {
echo "<option value=\"XFR\" >Transferred</option>";
}
echo "</select></td>";
echo "</tr>";
}
echo"</table>"; //end of table
//echo"<h4>Delete EDI 210's</h4>";
echo"</form>"; // end of form
echo"</body>";
echo"</html>";
?>
<?php
// close the connection
odbc_close($conn); // or ($db)
?>