Entries Tagged 'PHP' ↓

I am stuck onto a problem and would appreciate some help on this! Here's the problem:

I am using aggregate functions (eg. SUM) of MySQL in a query where two tables are joined. I also need to filter the results based on some WHERE clause. Now the problem is, SUM sums up the values multiple times since one row from one table repeats due to the join.

Let me explain with an example. Suppose the two tables I am working with are transactions (T) and transaction_products (TP). T stores the details of the transaction (date, total price, discount, customerId etc) and TP stores line item details (productId, item price, item discount etc). And I want to get the total sales for an order where a particular product was sold.

Here's what I do:

Now that does not work correctly. And even when I simply want to get the total sales for all orders, the query becomes

And this is wrong! It joins the two tables, and repeats the totalPrice in the result, and sums up those values. Resulting in a much larger sales than the actual

How to solve this?

You can say that we can avoid the join where we want to SUM on a field from T, but that would not help in a condition like this:

If I sum up all the result rows of the above query, the result is about 3 times higher than this:

And the problem is that I need the category Id (or some other field from TP table)!

Any solutions to this?

I thought of derived tables (as mentioned here) but that does not solve the problem.

I thought of splitting the query in two parts. Storing the results of SUM in a temp table and then doing the WHERE clause - but that would fail outright since the results do not include the WHERE condition.

What else can I do? What do you think?

Here's a great thing I learned about MySQL today:

Will create a copy of the table along with structure and keys (primary keys, auto increments etc) but not the data.

This is perfect solution to create new tables based on an existing one - we use it to create a new database for every customer that signs up on a project. We go in a loop of the list of tables, and use the CREATE TABLE statement to duplicate the table in a new name. This works across databases too (assuming you have permissions).

Of course, if you wanted to get all the data, you can do something like:

The full CREATE TABLE syntax is here.

Been trying out different embedded databases for the past few days. And am not sure which one is the best. The background is this:

We have a desktop application in C#, and are sending data from desktop to the PHP backend on the server. Since the data is going to be large, we want to send only the changed data to the server. That too in the most optimized manner. We are using CSV file as data storage and doing a diff on it to create the delta that we can send on the server. We are even zipping the delta to make sure it's good on the bandwidth.

And here's the problem:

We want to pre-process the delta. So we convert the unified diff to CSV again. Now we want to push it to a temporary table, and update a few fields. E.g. we want to mark all new rows (+ in diff) as "I" (inserted), removed rows (- in diff) as "D" (deleted) and updated rows (- and + both in diff) as "U" (updated). To do this, we load the data into a DataTable and perform some queries on it.

But there's a catch. The .Net DataTable can't perform updates on CSV files. So we need a real database.

That's where our search for embedded databases began.

We want something free and something that does not restrict redistribution in a commercial application.

The options were limited. SQL Server Express, Firebird and SQLite (didn't want Access/MDB).

Frankly, it's not been a simple ride so far. We are considering only Firebird and SQLite (open source etc). Neither had enough ready samples / documentation that could solve the problem for us readily. Firebird was more difficult - with a lot of confusion about setting it up and opening the fdb file from a GUI database administrator application. SQLite was easier.

We have to do INSERT queries on the table, as we do not know how many fields will be there in the CSV file, and we couldn't figure out an easy way to load a CSV file into a new table.

At this stage, I am not sure which one to go for. We need something that will work fast. I know SQLite has an option to load CSV files directly into a table through command line, but we don't want to do a system call.

Any suggestions?

On a recent Flex project, we needed to take a snapshot of what's shown on the screen and save it as an image. I had seen somewhere that this was possible, but couldn't locate how exactly it was to be done. Turns out it's very easy to do such a screen capture / screen grab using Flex.

The essential steps are:

• Create a new BitmapData object.
• Copy the target component's pixel data into BitmapData object.
• Convert the BitmapData object to a PNG encoded ByteArray (using the PNGEnc library)
• Convert the ByteArray to a Base64Encoded string so that we can send the data safely to the backend.
• On the backend (PHP via WebORB in our case), decode the data and write it to a file.

Most of the piece come from two places. James Ward has described this technique in his Flex Paint sample. And Tinic Uro has written the PNG Encoder library. Let's see the code now.

Here's the Flex Code:

We use WebORB for backend connectivity. Assuming you have the connection in place, we can simply call a method passing the base 64 encoded string and save it on the server. If you don't have RemoteObject / Web Service, you can also use an HTTP POST. The reason to use Base 64 encoding is to get a string we can safely pass around, instead of a byte array.

Here's the PHP side of the code:

I could resize and do anything else with the image on the PHP side now.

Hope you find some creative use of this

I always have a tough time deploying a Flash/Flex based app that uses web services. And we use web services (or similar) on almost all projects! Every two or three months I come across a new problem with web service deployment and have to spend some long hours finding and fixing it. The post describes my latest encounter with webservice errors and the fundamental lessons I've learnt about debugging PHP web services used in Flash/Flex/OpenLaszlo.

So, my latest web service error message was:
Request implements version: http://schemas.xmlsoap.org/soap/envelope/ Response implements

Well, that's the only part I could read in the Alert message I had setup for faults in the web service. This was followed by the famous "could not connect to endpoint" error.

I was surprised looking at the error. First thing I did was check the WSDL file. (Yes, am using SOAP webservices). Tried different ways to generate the WSDL from Zend Studio (I use PHP5 web services and Zend Studio for development). Nothing worked. BTW, the problem came only on the live server. The development and staging servers were working perfectly fine with the same files!

After solving the problem, I re-learnt the biggest lesson in web service debugging.

Root cause of almost all web service related problems is some error / warning in your PHP (or other) code.

In this case, the server was not able to include classes and other files that the webservice was using. My PHP based test cases helped solve the problem since they showed up the include errors PHP was having. So let me give you the lesson # 2.

Make sure you write PHP test cases for your web services.

These test cases will use the SoapClient class to call a method on your webservice. Display all errors in the test cases and you will be able to nail the source pretty quickly.

This one is yet unsolved for me! I want to find out a good web service debugger. Something that allows me to pass arguments to the web service and see the entire request and response - including HTTP headers. The WebOrb admin panel does a great job at that, but I would like something that can easily handle complex data! Anything you know of?

Here are some earlier problems I have had with Webservices:

• Unable to connect to endpoint
• Procedure not found / Function not found problems
• WSDL Blues
• Debugging webservices with OpenLaszlo
• Unicode, Webservices and OpenLaszlo