Re: need help to build a query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



afan@xxxxxxxx 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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux