I cant seem to get my odbc_result to work with a Paradox database TIME
field. My result always comes back as a date of 1899-12-30.... this is
driving me nuts, thats not a time... thats a date! It also always
returns the exact same date even if the time is different. All the
other odbc_results return the proper information..... just not the TIME
field.
Does anyone know what is going on?!
Thorak wrote:
> I cant seem to get my odbc_result to work with a Paradox database TIME
> field. My result always comes back as a date of 1899-12-30.... this is
> driving me nuts, thats not a time... thats a date! It also always
> returns the exact same date even if the time is different. All the
> other odbc_results return the proper information..... just not the
> TIME field.
>
> Does anyone know what is going on?!
Most likely a problem in the query, which we cannot check when you don't
give us the info we need, like the query used and the logic afterwards.
Also, have you checked/tried this:
http://www.php.net/manual/en/ref.paradox.php
--
Grtz,
Rik Wasmus
//---------------------------------------------------
//--------------------------------------------
# SQL Statement.
$qry = 'SELECT OD.OrderID,
OD.CustomerID,
OD.SalePersonID,
OD.DueDate,
OD.DueTime,
EMP.EmployeeID,
EMP.FirstName
FROM "Order.DB" OD, "Employee.DB" EMP
WHERE EMP.EmployeeID = OD.SalePersonID;
$qry = odbc_prepare($db, $qry);
$exe_id = odbc_execute($qry);
while(odbc_fetch_row($qry)){
$DueDate = odbc_result($qry,'DueDate');
$DueTime = odbc_result($qry,'DueTime');
}
echo $DueDate.' - '.$DueTime;
Rik wrote:
> Thorak wrote:
> > I cant seem to get my odbc_result to work with a Paradox database TIME
> > field. My result always comes back as a date of 1899-12-30.... this is
> > driving me nuts, thats not a time... thats a date! It also always
> > returns the exact same date even if the time is different. All the
> > other odbc_results return the proper information..... just not the
> > TIME field.
> >
> > Does anyone know what is going on?!
>
> Most likely a problem in the query, which we cannot check when you don't
> give us the info we need, like the query used and the logic afterwards.
>
> Also, have you checked/tried this:
> http://www.php.net/manual/en/ref.paradox.php
>
> --
> Grtz,
>
> Rik Wasmus
Thorak wrote:
> //---------------------------------------------------
> //--------------------------------------------
>
> # SQL Statement.
> $qry = 'SELECT OD.OrderID,
> OD.CustomerID,
> OD.SalePersonID,
> OD.DueDate,
> OD.DueTime,
> EMP.EmployeeID,
> EMP.FirstName
> FROM "Order.DB" OD, "Employee.DB" EMP
> WHERE EMP.EmployeeID = OD.SalePersonID;
>
> $qry = odbc_prepare($db, $qry);
> $exe_id = odbc_execute($qry);
>
> while(odbc_fetch_row($qry)){
>
> $DueDate = odbc_result($qry,'DueDate');
> $DueTime = odbc_result($qry,'DueTime');
>
> }
>
> echo $DueDate.' - '.$DueTime;
var_dump($DueDate); //and
var_dump($DueTime);
Then we'll know what kind of data it holds.
(BTW: I'd make a single column Due, with both date & time, either in you
database's native format (haven't used Paradox), or as a unix timestamp.
Also, in MySQL, tablenames are not quoted (""), but backticked (``). Don't
know wether that is the case with Paradox....?
Furthermore, in a query using joins I don't trust the fieldname to be the
columnname anymore, so try:
OD.DueDate AS 'DueDate',
OD.DueTime AS 'DueTime',
As a last remark: this is not the exact code you used to get 1899-12-30,
because this code would fail.... HOW do you get to 1899-12-30?
--
Grtz,
Rik Wasmus
ok... so here is the output for the var_dump you asked for:
string(10) "2006-06-15"
string(10) "1899-12-30"
string(10) "2006-07-07"
string(10) "1899-12-30"
string(10) "2006-08-21"
string(10) "1899-12-30"
string(10) "2006-08-23"
string(10) "1899-12-30"
string(10) "2006-09-08"
string(10) "1899-12-30"
string(10) "2006-09-13"
string(10) "1899-12-30"
as re the query... i simplified it for you cause there was alot of data
in there I didnt think you needed.... but for sake of argument here is
the whole thing:
//-----------------------------------------------
$db=odbc_connect($ODBC_name,$ODBC_user,$ODBC_pass) or die ("Could not
connect");
# SQL Statement.
$qry = 'SELECT OD.OrderID,
OD.CustomerID,
OD.CustomerName,
OD.Description,
OD.OrdererFirstName,
OD.OrdererLastName,
OD.OrdererPareaCode,
OD.OrdererPhoneNumber,
OD.OrdererPhoneExtention,
OD.OrdererFaxNumber,
OD.OrdererEmailAddress,
OD.SalePersonID,
OD.Status,
OD.OnHold,
OD.DueDate,
OD.DueTime,
OD.ProofDate,
OD.OrderNotes,
OD.SalesTotal,
EMP.EmployeeID,
EMP.FirstName
FROM "Order.DB" OD, "Employee.DB" EMP
WHERE EMP.EmployeeID = OD.SalePersonID
AND OD.Status NOT LIKE \'Closed\'
AND OD.Status NOT LIKE \'Voided\'
AND OD.Status NOT LIKE \'Built\'
AND OD.Status NOT LIKE \'Picked-Up\'
AND EMP.FirstName LIKE \''.$_SESSION['CSR'].'\'
ORDER BY OD.DueDate, EMP.FirstName, OD.OrderID';
$qry = odbc_prepare($db, $qry);
$exe_id = odbc_execute($qry);
while(odbc_fetch_row($qry)){
// Define vars
//----------------------
$OrderID = odbc_result($qry, 'OrderID');
$CustomerID = odbc_result($qry, 'CustomerID');
$CustomerName = odbc_result($qry, 'CustomerName');
$OrderNotes = odbc_result($qry, 'OrderNotes');
$Description = odbc_result($qry, 'Description');
$OrdererFirstName = odbc_result($qry, 'OrdererFirstName');
$OrdererLastName = odbc_result($qry, 'OrdererLastName');
$OrdererPhoneNumberArea = odbc_result($qry, 'OrdererPareaCode');
$OrdererPhoneNumber = odbc_result($qry, 'OrdererPhoneNumber');
$OrdererFaxNumber = odbc_result($qry, 'OrdererFaxNumber');
$OrdererEmailAddress = odbc_result($qry, 'OrdererEmailAddress');
$Status = odbc_result($qry, 'Status');
$DueDate = odbc_result($qry,'DueDate');
$DueTime = odbc_result($qry,'DueTime');
$ProofDate = odbc_result($qry,'ProofDate');
$SalePersonID = odbc_result($qry, 'SalePersonID');
$SalePerson = odbc_result($qry, 'FirstName');
$SalesTotal = odbc_result($qry, 'SalesTotal');
var_dump($DueDate);
var_dump($DueTime);
}
Rik wrote:
> Thorak wrote:
> > //---------------------------------------------------
> > //--------------------------------------------
> >
> > # SQL Statement.
> > $qry = 'SELECT OD.OrderID,
> > OD.CustomerID,
> > OD.SalePersonID,
> > OD.DueDate,
> > OD.DueTime,
> > EMP.EmployeeID,
> > EMP.FirstName
> > FROM "Order.DB" OD, "Employee.DB" EMP
> > WHERE EMP.EmployeeID = OD.SalePersonID;
> >
> > $qry = odbc_prepare($db, $qry);
> > $exe_id = odbc_execute($qry);
> >
> > while(odbc_fetch_row($qry)){
> >
> > $DueDate = odbc_result($qry,'DueDate');
> > $DueTime = odbc_result($qry,'DueTime');
> >
> > }
> >
> > echo $DueDate.' - '.$DueTime;
>
> var_dump($DueDate); //and
> var_dump($DueTime);
>
> Then we'll know what kind of data it holds.
>
> (BTW: I'd make a single column Due, with both date & time, either in you
> database's native format (haven't used Paradox), or as a unix timestamp.
>
> Also, in MySQL, tablenames are not quoted (""), but backticked (``). Don't
> know wether that is the case with Paradox....?
>
> Furthermore, in a query using joins I don't trust the fieldname to be the
> columnname anymore, so try:
>
> OD.DueDate AS 'DueDate',
> OD.DueTime AS 'DueTime',
>
> As a last remark: this is not the exact code you used to get 1899-12-30,
> because this code would fail.... HOW do you get to 1899-12-30?
> --
> Grtz,
>
> Rik Wasmus
Thorak wrote:
> ok... so here is the output for the var_dump you asked for:
>
> string(10) "2006-06-15"
> string(10) "1899-12-30"
>
> string(10) "2006-07-07"
> string(10) "1899-12-30"
>
> string(10) "2006-08-21"
> string(10) "1899-12-30"
>
> string(10) "2006-08-23"
> string(10) "1899-12-30"
>
> string(10) "2006-09-08"
> string(10) "1899-12-30"
>
> string(10) "2006-09-13"
> string(10) "1899-12-30"
>
>
> as re the query... i simplified it for you cause there was alot of
> data
> in there I didnt think you needed.... but for sake of argument here is
> the whole thing:
>
> //-----------------------------------------------
>
> $db=odbc_connect($ODBC_name,$ODBC_user,$ODBC_pass) or die ("Could not
> connect");
>
> # SQL Statement.
> $qry = 'SELECT OD.OrderID,
> OD.CustomerID,
> OD.CustomerName,
> OD.Description,
> OD.OrdererFirstName,
> OD.OrdererLastName,
> OD.OrdererPareaCode,
> OD.OrdererPhoneNumber,
> OD.OrdererPhoneExtention,
> OD.OrdererFaxNumber,
> OD.OrdererEmailAddress,
> OD.SalePersonID,
> OD.Status,
> OD.OnHold,
> OD.DueDate,
> OD.DueTime,
> OD.ProofDate,
> OD.OrderNotes,
> OD.SalesTotal,
> EMP.EmployeeID,
> EMP.FirstName
> FROM "Order.DB" OD, "Employee.DB" EMP
> WHERE EMP.EmployeeID = OD.SalePersonID
> AND OD.Status NOT LIKE \'Closed\'
> AND OD.Status NOT LIKE \'Voided\'
> AND OD.Status NOT LIKE \'Built\'
> AND OD.Status NOT LIKE \'Picked-Up\'
> AND EMP.FirstName LIKE \''.$_SESSION['CSR'].'\'
> ORDER BY OD.DueDate, EMP.FirstName, OD.OrderID';
>
>
> $qry = odbc_prepare($db, $qry);
> $exe_id = odbc_execute($qry);
>
>
> while(odbc_fetch_row($qry)){
>
> // Define vars
> //----------------------
> $OrderID = odbc_result($qry, 'OrderID');
> $CustomerID = odbc_result($qry, 'CustomerID');
> $CustomerName = odbc_result($qry, 'CustomerName');
> $OrderNotes = odbc_result($qry, 'OrderNotes');
> $Description = odbc_result($qry, 'Description');
> $OrdererFirstName = odbc_result($qry, 'OrdererFirstName');
> $OrdererLastName = odbc_result($qry, 'OrdererLastName');
> $OrdererPhoneNumberArea = odbc_result($qry, 'OrdererPareaCode');
> $OrdererPhoneNumber = odbc_result($qry, 'OrdererPhoneNumber');
> $OrdererFaxNumber = odbc_result($qry, 'OrdererFaxNumber');
> $OrdererEmailAddress = odbc_result($qry, 'OrdererEmailAddress');
> $Status = odbc_result($qry, 'Status');
> $DueDate = odbc_result($qry,'DueDate');
> $DueTime = odbc_result($qry,'DueTime');
> $ProofDate = odbc_result($qry,'ProofDate');
> $SalePersonID = odbc_result($qry, 'SalePersonID');
> $SalePerson = odbc_result($qry, 'FirstName');
> $SalesTotal = odbc_result($qry, 'SalesTotal');
>
> var_dump($DueDate);
> var_dump($DueTime);
> }
>
You could offcourse:
while($array = odbc_fetch_array($qry)){
extract($array);
}
That's a lot shorter, but not the problem atm :-).
What does odbc_field_type($qry,16) say?
Unfortunately, there's a chronical shortage of descriptions of your used
database on the web, so I would not know where exactly this is coming from,
but it seems you have a field of type date, and your database starts
date-times around 01-01-1900, and subtracts something due to the actual
value in the field (usually stored as integer), and only returns the date.
I could unfortunately not find out wether this is possible in Paradox, but
when using MySQL I'd try:
DATE_FORMAT('%H:%i',OD.DueTime) AS 'DueTime'),
Maybe that helps...
--
Rik Wasmus