On Mon, October 9, 2006 2:44 pm, afan@xxxxxxxx wrote:
> I have table orders with primary key order_id. I have table
> uploaded_files
> with primary key ufid and uploaded files are linked to orders by
> order_id
> column.
>
> I have to list all orders and uploaded files. this works fine:
>
> $query = mysql_query("
> select order_id, order_date, order_status
> from orders
> order by order_id desc
> limit 100");
> while($result=mysql_fetch_array($query))
> {
> echo "ID: ". $result['order_date']."|";
> echo "DATE: ". $result['order_date'] ."|";
> echo "STATUS: ". $result['order_status'] ."|";
> echo "UPLOADED FILES: ";
> $query2 = mysql_query("
> select uf.file_name
> from uploaded_files as uf
> where uf.order_id = $result['order_id']
> ");
> while($result2=mysql_fetch_array($query2))
> {
> echo $result2['file_name']."|";
> }
> echo "<hr>";
> }
>
> but I know there must be much better solution then this one.
This is really an SQL question...
But let's take the PHP performance test, to stay on topic.
Yes, you are sending 100 queries to the DB through mysql_query.
Does that negatively impact performance on YOUR server for YOUR web
app at your current or 5-year projected levels of usage?
If not, don't waste time trying to optimize it. K.I.S.S. and use the
queries that work just fine.
As a bonus, the answer to what you actually asked is:
select orders.order_id, order_date, order_status
from orders
left outer join uploaded_files on uploaded_files.order_id =
orders.order_id
order by orders.order_id desc
limit 100
Unfortunately, this limit now applies to the number of uploaded files
and/or orders with no files at all, rather than the number of orders.
AFAIK, there is no easy way to get the exact same limit of 100 orders
with however many uploaded files.
So if you NEED 100 orders, and who cares how many uploaded files, the
solution you have is CORRECT, no matter how wrong it looks.
Actually, you could *maybe* get some performance gain by running
through the first result, gathering up the IDs into an array, and then
splicing that into a single query like:
//outside the loop:
$order_ids_sql = implode(', ', $order_ids);
$result2 = mysql_query("select order_id, file_name from uploaded_files
where order_id in ($order_ids_sql) order by order_id");
You can then run through $result2 "in parallel" with $result1 and
compare the $order_id to make sure you print the right thing at the
right time.
Don't be surprised if the LEFT OUTER JOIN above, or the parallel
processing and the extra array to build the query with 100 IDs turns
out to be not any faster/better than your current solution. SQL
theory often turns out to be not-so-practical, in my experience.
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some starving artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
