php+freetds:This runs Itself, but it dont as as included cod

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sugar
Forum Newbie
Posts: 8
Joined: Mon Mar 26, 2007 2:13 pm

php+freetds:This runs Itself, but it dont as as included cod

Post 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
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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."
sugar
Forum Newbie
Posts: 8
Joined: Mon Mar 26, 2007 2:13 pm

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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

Code: Select all

error_report(E_ALL);
and adding

Code: Select all

or die(odbc_error());
to your odbc calls to see if we can get some more information
sugar
Forum Newbie
Posts: 8
Joined: Mon Mar 26, 2007 2:13 pm

Post 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...
sugar
Forum Newbie
Posts: 8
Joined: Mon Mar 26, 2007 2:13 pm

Post 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);
}


????
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
sugar
Forum Newbie
Posts: 8
Joined: Mon Mar 26, 2007 2:13 pm

Post 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!
sugar
Forum Newbie
Posts: 8
Joined: Mon Mar 26, 2007 2:13 pm

Post 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
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
sugar
Forum Newbie
Posts: 8
Joined: Mon Mar 26, 2007 2:13 pm

Post 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...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I think what your looking for is a "JOIN" query.
sugar
Forum Newbie
Posts: 8
Joined: Mon Mar 26, 2007 2:13 pm

Post by sugar »

may I join a mysql table with 1 mssql table and then with another mssql table?
Post Reply