php4 to php5

[raa]

Hello guys,

 

I'm helping someone who has needed to go from Debian/php4 to Ubuntu/php5 on their webserver. Its a small business who had their page made some years back(like 10 maybe). Since their OS/php version has changed there are some PostgreSQL queries that don't seem to work anymore. I'm used to MySql so PostgreSQL is completely new to me. I've tried searching the interweb and didn't find much if anything.

 

 

Here is the function that has the query in it.

 

function get_all_days_products($order_day, $order_month, $order_year)
{
        $conn = connectEfesDB();
        if (!$conn){
                noDBConn($conn);
                exit;
        }

if($order_day < 10)
	$order_day = "0$order_day";
if($order_month < 10)
	$order_month = "0$order_month";

$query = "SELECT distinct P.product_id, product_code
	FROM products as P, order_items as OI, orders as O
	WHERE P.current_product='t'
	and O.order_id = OI.order_id
	and OI.product_id = P.product_id
	and O.delivery_date like '$order_year-$order_month-$order_day%'
	order by P.product_id";

        $products = pg_query($conn, $query);
        
if (!$products){
                return;
        }
        for ($i=0; $i<pg_numrows($products); $i++){
                $product_list[$i]["id"]   = pg_result($products, $i, 0);
                $product_list[$i]["code"] = pg_result($products, $i, 1);
        }
        return $product_list;
}

 

Now all I know so far is it returns nothing. I don't have PostgreSQL on my personal webhost so I haven't been able to test around too much.

Do you see anything in there that would stop working going from php4 to php5?

 

Thanks so much any help..

[btherl]

Hi,

 

Does the query execute but return nothing?  If that's the case the steps I would take in order are:

 

1.  Display the query (eg echo $query)

2.  var_dump($products) - see if this is "false" or if it's a resource.  If it's false the query failed, and you should check pg_last_error()

3.  If $products is a resource then check if pg_num_rows($products) == 0.  If it does, then you have an empty result set.  Probably because the query is not what you expect.  In which case you need to go back through the code finding where the input data comes from.

[raa]

var_dump return was false.

 

pg_last_error returns;

 

"ERROR: operator does not exist: date ~~ unknown LINE 6: and O.delivery_date like '2012-05-07%' ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts."

 

 

Now I didn't have access to this script before the "upgrade" to php5 but I'm assuming this query must have ran fine in php4 cuz the customer said everything was peachy..

 

 

[btherl]

That's very much a postgres issue, it's probably not related to the php version.  But the postgres version may also have changed.  You can try these solutions:

 

	and O.delivery_date::text like '$order_year-$order_month-$order_day%'

 

That casts the delivery date to text before doing the like.  It'll work but it won't use any indexes on that column (unless the index happens to be in that exact form, which I doubt).  Another option:

 

	and O.delivery_date >= '$order_year-$order_month-$order_day' AND O.delivery_date < '$order_year-$order_month-$order_day'::date + '1 day'::interval

 

Making it a range like that using the native type of the column can give you better optimization.  I'm guessing the column is a timestamp, because there's no need to use "like" if it's a date.

 

Or you could calculate what the following day is and put it into the query directly, which may give better optimization.  It really depends on your indexes and which version of postgres you have.

[raa]

thank you so much for everyones replies/help..

 

The column isn't a timestamp so I just went ahead and used = instead of LIKE.  Seemed to solve it..

 

[btherl]

thank you so much for everyones replies/help..

 

The column isn't a timestamp so I just went ahead and used = instead of LIKE.  Seemed to solve it..

 

Too easy