Page 1 of 1
php+freetds:This runs Itself, but it dont as as included cod
Posted: Mon Mar 26, 2007 2:18 pm
by sugar
Hi, this code is prety simple, it just query for a Inventory quantity, using php+freetds+unixodbc, and works well as I provided here, the thing is that when I put this code inside a homemade php sofware... it just returns null, none, blank, nothing... how can I workaround this?
Thanks in advance.
Code: Select all
<?php
$code = '20005-FRD';
include "/usr/local/www/data/scripts/db_stuff/db_secrets.php";
$conn=odbc_connect($pos_host,$pos_user,$pos_pass);
$queryRMSqty1 = "SELECT Quantity FROM Custom_Piso_Qty WHERE SubDescription3 = '".$code."'";
$rs_RMS1=odbc_exec($conn,$queryRMSqty1);
$RMS_qty1=odbc_result($rs_RMS1,'Quantity');
echo $RMS_qty1;
odbc_free_result($rs_RMS1);
odbc_close($conn);
?>
Aldo
Posted: Mon Mar 26, 2007 2:41 pm
by Begby
Well since the code you posted works perfectly, perhaps you should post some of the code around it that causes it to fail. Or tell us how you are putting it inside a "homemade php sofware."
Posted: Mon Mar 26, 2007 2:51 pm
by sugar
Begby wrote:Well since the code you posted works perfectly, perhaps you should post some of the code around it that causes it to fail. Or tell us how you are putting it inside a "homemade php sofware."
may I put here the whole 370 lines of the php file?
Posted: Mon Mar 26, 2007 3:00 pm
by John Cartwright
Try and host it elsewhere and change the extension to .phps if you think it is critical to provide us with the entire code. I would recommend simply turning error reporting to E_ALL
and adding
to your odbc calls to see if we can get some more information
Posted: Mon Mar 26, 2007 3:30 pm
by sugar
this is the whole code:
http://site.crafta.com/suppliers.phps
I dont know where to include the error_on code you gave me...
Posted: Mon Mar 26, 2007 5:32 pm
by sugar
I located the error but can't figureout how to solve it, this is the code in wich the error is located, and this is the error msg: "Error rs_RMS1:[unixODBC][FreeTDS][SQL Server]Invalid cursor state"
Code: Select all
function query_to_rms1_qty($rms2_Qdesc3) // rms = calexico RMS
{
include "/usr/local/www/data/scripts/db_stuff/db_secrets.php";
$conn=odbc_connect($pos_host,$pos_user,$pos_pass);
if (!$conn) {return "Error conn:".odbc_errormsg(); }
$queryRMSqty1 = "SELECT Quantity FROM Custom_Piso_Qty WHERE SubDescription3 = '".$rms2_Qdesc3."'";
if (!$queryRMSqty1) {return "Error queryRMSqty1:".odbc_errormsg(); }
$rs_RMS1=odbc_exec($conn,$queryRMSqty1);
if (!$rs_RMS1) {return "Error rs_RMS1:".odbc_errormsg(); }
$RMS_qty1=odbc_result($rs_RMS1,'Quantity');
if (!$RMS_qty1) {return "Error RMS_qty1:".odbc_errormsg(); }
return $RMS_qty1;
odbc_free_result($rs_RMS1);
odbc_close($conn);
}
????
Posted: Mon Mar 26, 2007 8:07 pm
by Begby
This is because you are calling this code inside a loop on another recordset I believe. You have an active select that you are looping on (while (odbc_fetch_row($rsV))), and for each result you call this function, this craps out because of the way its looping, you can only loop on one recordset at a time per connection. On the outer loop try and fetch all of the results into an array, free the result, then loop through the array.
Note: When you find yourself getting a recordset, then looping on that recordset and doing a query for each row, thats usually a signal that you can solve the problem with an SQL join. Also it can be very inneficient especially if you are looping on a lot of records.
Posted: Mon Mar 26, 2007 8:16 pm
by sugar
I will check that in the code, another thing is, lets say I solve the php coding problem, I tested it in console and look what I get:
Code: Select all
$ isql pos-server *** *****
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>SELECT Quantity FROM Custom_Piso_Qty WHERE SubDescription3 = '20005-FRD'
[ISQL]ERROR: Could not SQLPrepare
It should return a query result... but it is returning an error... how can I handle it?
thanks!
Posted: Mon Mar 26, 2007 9:54 pm
by sugar
I have an Idea, I did this in another website: Create a single php query file, and call (implode or POST method) from the large page, and each time I need to request for a item inventory only call some url like this:
http://myserver.com/phpitem.php?sku=xxxx; it might load the server more than a internal query but does the work..
what you think??
Aldo
Posted: Tue Mar 27, 2007 1:57 pm
by Begby
sugar wrote:I have an Idea, I did this in another website: Create a single php query file, and call (implode or POST method) from the large page, and each time I need to request for a item inventory only call some url like this:
http://myserver.com/phpitem.php?sku=xxxx; it might load the server more than a internal query but does the work..
what you think??
Aldo
This is a terrible idea. You should learn how to solve the problem using either proper PHP or an SQL join. Looking into a join would be your best bet and the most efficient.
Posted: Thu Mar 29, 2007 2:17 pm
by sugar
okay I fixed the code, now my problem is this:
The main table is a mysql table, and the other two queries (qty1 and qty2) are MSSQL queries, so lets say I do a SELECT 'sku', 'name', 'qty' FROM 'items' in mysql then I have to do a individual query for each qty1 and qty2 on MSSQL for each item in the 'items' table...
I takes too long time to render the results like this:
sku,name,qty,qty1,qty2
---------------------------------
abc,the item a,1,4,3
def,the item d,3,6,1
ghi,the item h,0,2,1
etc...
how can I improbe this??
thanks in advance again...
Posted: Thu Mar 29, 2007 2:20 pm
by Benjamin
I think what your looking for is a "JOIN" query.
Posted: Thu Mar 29, 2007 2:22 pm
by sugar
may I join a mysql table with 1 mssql table and then with another mssql table?