Re: [PHP] need help to build a query
On Tue, 10 Oct 2006 14:22:54 -0500, Richard Lynch wrote: On Mon, October 9, 2006 2:58 pm, John Wells wrote: On 10/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: but I know there must be much better solution then this one. You're right: Simply JOIN your queries... SELECT order_id, order_date, order_status, file_name FROM orders JOIN uploaded_files AS uf ON orders.order_id = uf.order_id ORDER BY orders.order_id DESC LIMIT 100 This will miss all the orders with no upload at all, unlike the original. As specified elsewhere in this thread, use outer join to prevent losing orders with no uploads. AND it limits you to the 100 uploaded files, not 100 orders. This is a lot more subtle problem than it seems at first glance, eh? To fetch all uploaded filenames in one result row, and solving the limit problem all at once, see group_concat(). Mind you, works only with MySQL 4.1... Ivo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] need help to build a query
On Mon, October 9, 2006 2:44 pm, [EMAIL PROTECTED] 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
Re: [PHP] need help to build a query
On Mon, October 9, 2006 2:58 pm, John Wells wrote: On 10/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: but I know there must be much better solution then this one. You're right: Simply JOIN your queries... SELECT order_id, order_date, order_status, file_name FROM orders JOIN uploaded_files AS uf ON orders.order_id = uf.order_id ORDER BY orders.order_id DESC LIMIT 100 This will miss all the orders with no upload at all, unlike the original. AND it limits you to the 100 uploaded files, not 100 orders. This is a lot more subtle problem than it seems at first glance, eh? -- 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
Re: [PHP] need help to build a query
On Mon, October 9, 2006 3:09 pm, [EMAIL PROTECTED] wrote:
But, in this case I will have repeating order_date and order_status
info?
idorder_datestatusfile_name
122006-10-09live file1.jpg
122006-10-09live file2.jpg
122006-10-09live file3.jpg
132006-10-09live file1.jpg
142006-10-09live test.gif
Yes.
But what you print out and what you have in each result row need not
be exactly one-to-one.
$last_order_id = '';
while (... mysql_fetch_row($result)){
if ($last_order_id != $order_id){
echo $order_id $order_date $statusbr /\n;
$last_order_id = $order_id;
}
echo $file_namebr /\n;
}
Here we only print out the order_id etc when the order_id changes, not
for every uploaded file.
--
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
Re: [PHP] need help to build a query
sure it is! ;) On Mon, October 9, 2006 2:58 pm, John Wells wrote: On 10/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: but I know there must be much better solution then this one. You're right: Simply JOIN your queries... SELECT order_id, order_date, order_status, file_name FROM orders JOIN uploaded_files AS uf ON orders.order_id = uf.order_id ORDER BY orders.order_id DESC LIMIT 100 This will miss all the orders with no upload at all, unlike the original. AND it limits you to the 100 uploaded files, not 100 orders. This is a lot more subtle problem than it seems at first glance, eh? -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] need help to build a query
ok. got the point.
On Mon, October 9, 2006 3:09 pm, [EMAIL PROTECTED] wrote:
But, in this case I will have repeating order_date and order_status
info?
idorder_datestatusfile_name
122006-10-09live file1.jpg
122006-10-09live file2.jpg
122006-10-09live file3.jpg
132006-10-09live file1.jpg
142006-10-09live test.gif
Yes.
But what you print out and what you have in each result row need not
be exactly one-to-one.
$last_order_id = '';
while (... mysql_fetch_row($result)){
if ($last_order_id != $order_id){
echo $order_id $order_date $statusbr /\n;
$last_order_id = $order_id;
}
echo $file_namebr /\n;
}
Here we only print out the order_id etc when the order_id changes, not
for every uploaded file.
--
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
Re: [PHP] need help to build a query
In MySQL, you can do explain $query to see what MySQL estimates will be the workload. Interpreting that output is more art than science, but with practice, you can at least avoid SOME querie that will drive your server to its knees. I did work on a PostgreSQL search engine once where we did a similar thing in the program and just bailed out if the search was clearly going to take far too long. Kind of nifty, really... On Mon, October 9, 2006 3:50 pm, [EMAIL PROTECTED] wrote: while I was trying again I did something wrong (?) and my server is now busy and looks like it went down?!? The qestion is how can I check first query before I apply it to be sure I'm not goig to read every record in my DB or get into loop? thanks. -afan On 10/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: but I know there must be much better solution then this one. You're right: Simply JOIN your queries... SELECT order_id, order_date, order_status, file_name FROM orders JOIN uploaded_files AS uf ON orders.order_id = uf.order_id ORDER BY orders.order_id DESC LIMIT 100 HTH, John W thanks for any help. -afan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- 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
Re: [PHP] need help to build a query
On 10/10/06, Richard Lynch [EMAIL PROTECTED] wrote: This is a lot more subtle problem than it seems at first glance, eh? Yup, sure is. Thanks for the detailed response, so helpful as always. -John W -- 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
[PHP] need help to build a query
hi to all,
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.
thanks for any help.
-afan
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] need help to build a query
On 10/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: but I know there must be much better solution then this one. You're right: Simply JOIN your queries... SELECT order_id, order_date, order_status, file_name FROM orders JOIN uploaded_files AS uf ON orders.order_id = uf.order_id ORDER BY orders.order_id DESC LIMIT 100 HTH, John W thanks for any help. -afan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] need help to build a query
[EMAIL PROTECTED] wrote:
hi to all,
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.
thanks for any help.
-afan
Perhaps something like this: (not sure how this would play with the
limit key word, but you could play around with it...)
If you can guarantee that a record (order_id) will appear in both
tables, a simple join will work...
but if a record in table A exists but not in table B, a join will not
return that record, which is why there is a left outer join.
select o.order_id, o.order_date, o.order_status, uf.file_name
from orders o left outer join uploaded_files uf on uf.order_id = o.order_id
order by o.order_id desc
Not sure if mysql supports this..??
select * from (select o.order_id, o.order_date, o.order_status, uf.file_name
from orders o left outer join uploaded_files uf on uf.oerder_id = o.order_id
order by o.order_id desc ) LIMIT 100
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] need help to build a query
But, in this case I will have repeating order_date and order_status info? idorder_datestatusfile_name 122006-10-09live file1.jpg 122006-10-09live file2.jpg 122006-10-09live file3.jpg 132006-10-09live file1.jpg 142006-10-09live test.gif right? On 10/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: but I know there must be much better solution then this one. You're right: Simply JOIN your queries... SELECT order_id, order_date, order_status, file_name FROM orders JOIN uploaded_files AS uf ON orders.order_id = uf.order_id ORDER BY orders.order_id DESC LIMIT 100 HTH, John W thanks for any help. -afan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] need help to build a query
while I was trying again I did something wrong (?) and my server is now busy and looks like it went down?!? The qestion is how can I check first query before I apply it to be sure I'm not goig to read every record in my DB or get into loop? thanks. -afan On 10/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: but I know there must be much better solution then this one. You're right: Simply JOIN your queries... SELECT order_id, order_date, order_status, file_name FROM orders JOIN uploaded_files AS uf ON orders.order_id = uf.order_id ORDER BY orders.order_id DESC LIMIT 100 HTH, John W thanks for any help. -afan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] need help to build a query
Yes you are right, but I guess you can decide which is worse: sending extra data in one query or send extra queries. I guess it depends on how many records we're talking about... Brad also brings up a good point I hadn't considered. I do think an OUTER join is possible, perhaps depending on your mysql version... Which brings up something I had meant to say before, which is that this isn't actually a PHP question... :) John W On 10/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: But, in this case I will have repeating order_date and order_status info? idorder_datestatusfile_name 122006-10-09live file1.jpg 122006-10-09live file2.jpg 122006-10-09live file3.jpg 132006-10-09live file1.jpg 142006-10-09live test.gif right? On 10/9/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: but I know there must be much better solution then this one. You're right: Simply JOIN your queries... SELECT order_id, order_date, order_status, file_name FROM orders JOIN uploaded_files AS uf ON orders.order_id = uf.order_id ORDER BY orders.order_id DESC LIMIT 100 HTH, John W thanks for any help. -afan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] need help to build a query
On Monday 09 October 2006 16:50, John Wells wrote: Yes you are right, but I guess you can decide which is worse: sending extra data in one query or send extra queries. I guess it depends on how many records we're talking about... It will vary with your problem, but in general, fewer queries == A Good Thing. If you can do with a join or subselect what would take n queries otherwise, do so. Also, JOIN is faster than sub-queries. Be aware, though, that MySQL doesn't support sub-queries until version 4.1. -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
