<pre> 
#!/usr/bin/env python3
#===============================================================================
# List all orders
#===============================================================================
import config
import COOKIE
import WEB
import DB

msg     = ''                                #global variables
sort    = ''
data    = []                                #2dim list to hold query data 

#config.RUN_MODE='test'                     #turn on test mode
print('Content-type: text/html')            #required HTTP response header
if config.RUN_MODE=='test': print('\n')     #if mode='test', end the headers so all output will print

#===============================================================================
# Read all orders and build a 2 dimensional array
#===============================================================================
def read_data():
    global search, sort, data, msg;   
 
    search2 = "%" + search + "%"                        #wrap the search in % wildcards
    search2 = search2.replace(' ','%')                  #for multi-words, insert % wildcards                                             

    if not sort:                                        #if no sort order is provided 
        sort = 'order_id-desc'                          #sort by order_id descending
    
    sort  = sort.split('-')                             #split on '-'
    field = sort[0] 
    seq   = sort[1] 
 
    sql = F""" SELECT order_id, fname, ship_fname AS first, ship_lname AS last, ship_addr AS address,
                      GROUP_CONCAT(prod_desc,'--',qty,'--',price ORDER BY 1) AS products,  
                      tax, credit_card AS cc, order_date AS date 
                 FROM shop_customer JOIN shop_order        USING(cust_id)
                                    JOIN shop_order_detail USING(order_id) 
                WHERE cust_id = {cust_id}             
                  AND (lower(ship_fname) LIKE '{search2}'
                   OR  lower(ship_lname) LIKE '{search2}'
                   OR  lower(ship_addr)  LIKE '{search2}'
                   OR  prod_desc         LIKE '{search2}'
                   OR  credit_card       LIKE '{search2}'
                   OR  order_id          LIKE '{search2}') 
                GROUP BY order_id,fname,ship_fname,ship_lname,ship_addr,tax,credit_card        
                ORDER BY {field} {seq} """

    msg  = ''
    conn = DB.connect('demo2','demo2','demo2','mysql')
    if isinstance(conn,str): msg=conn; return           #connection error 

    result = DB.select(sql)             
    if (not result):       
        msg = F"There are no orders for customer {cust_id}"
        return        
    if isinstance(result,str): msg=result; return       #database error 

    conn.close()                                        #close the connection
    
    for row in result:                                  #loop through all rows
        data.append(row)                                #append to 2dim list

#===============================================================================
# Display all orders in an HTML table
# allow sorting of columns 
#===============================================================================
def display():
    global sort, data;
       
    print("""
        &lt;html>
        &lt;head>
        &lt;title>Retrieve data from database&lt;/title>
        &lt;style>
            a     {text-decoration:none; color:brown}
            table {font-family:arial; font-size:11pt; background-color:f9f9f9; border:2px solid brown}
            th,td {border-top:1px solid gray} 
        &lt;/style>
       &lt;/head>
       &lt;body bgcolor=lightyellow>
       &lt;h1>&lt;center>The Ice Cream Shop&lt;/center>&lt;/h1>
    """)

    if msg: print(F"&lt;h4>&lt;font color=red> {msg} &lt;/h4>"); exit()

    print(F"""&lt;form method=get >                
              Search for: 
              &lt;input type=text   name=q value='{search}'>
              &lt;input type=submit        value=Search>
              &lt;hr> \n """)

    ordr_seq = 'order_id-desc'  if (sort[0]=='order_id'  and sort[1]=='asc') else 'order_id-asc'
    name_seq = 'lname-desc'     if (sort[0]=='lname'     and sort[1]=='asc') else 'lname-asc'
    addr_seq = 'address-desc'   if (sort[0]=='address'   and sort[1]=='asc') else 'address-asc'
    prod_seq = 'prod_desc-desc' if (sort[0]=='prod_desc' and sort[1]=='asc') else 'prod_desc-asc'
    tax_seq  = 'tax-desc'       if (sort[0]=='tax'       and sort[1]=='asc') else 'tax-asc'
    card_seq = 'cc-desc'        if (sort[0]=='cc'        and sort[1]=='asc') else 'cc-asc'
    date_seq = 'date-desc'      if (sort[0]=='date'      and sort[1]=='asc') else 'date-asc'

    fname = data[0]['fname']
    print(F"&lt;h3>{fname}, your previous orders are below...&lt;/h3>")

    print(F""" &lt;table width=1052> 
               &lt;tr bgcolor=tan>
               &lt;th>&lt;a href=shopQuery.py?sort={ordr_seq}>Order Number&lt;/a> 
               &lt;th>&lt;a href=shopQuery.py?sort={name_seq}>Name&lt;/a>
               &lt;th>&lt;a href=shopQuery.py?sort={addr_seq}>Shipping Address&lt;/a>     
               &lt;th>&lt;a href=shopQuery.py?sort={prod_seq}>Products Ordered&lt;/a>     
               &lt;th>&lt;a href=shopQuery.py?sort={tax_seq}> Sales Tax&lt;/a>    
               &lt;th>&lt;a href=shopQuery.py?sort={card_seq}>Credit Card&lt;/a> 
               &lt;th>Total Price 
               &lt;th>&lt;a href=shopQuery.py?sort={date_seq}>Ordered On&lt;/a> 
    """)

    for row in data:                                        #for every row in the query result
        total=0
        order_id   = row['order_id']
        first      = row['first']
        last       = row['last']
        address    = row['address']
        products   = row['products']
        tax        = row['tax']
        creditCard = row['cc']
        ord_date   = row['date']
                
        address  = address.replace('\n', '&lt;br>')            #change all \n to &lt;br> 
        print("&lt;tr valign=top>")
        print(F"&lt;td align=middle>{order_id}&lt;td width=100>{first} {last}&lt;/td>&lt;td>&lt;i>{address}&lt;/i>&lt;/td>")
        print("&lt;td>&lt;table width=100%>")
        
        orders = products.split(',')                        #split the multiple product ordered using , 
        for order in orders:
            (prod_desc,qty,price) = order.split('--')       #split each product on prod_desc,qty,price using --             
            total += int(qty) * float(price) * (1+ float(tax)/100)
            print(F"&lt;tr>&lt;td> {prod_desc} &lt;td> Quantity:{qty} &lt;td> ${price} &lt;/tr>")
        total = '{:.2f}'.format(total)
        print("&lt;/table>")
        print(F"&lt;td align=middle>{tax}%&lt;/td>&lt;td>&lt;nobr>{creditCard}&lt;/td>&lt;th>${total}&lt;/td>&lt;td>{ord_date}&lt;/td>")
        print('&lt;tr height=20px>')

    print("""    
          &lt;/table> &lt;hr> \n
          &lt;center>
          &lt;a href=shopBrowse.py>   shop          &lt;/a> |
          &lt;a href=shopCheckout.py> checkout      &lt;/a> | 
                                   search orders      |
          &lt;a href=shopProf.py>     profile       &lt;/a> |
          &lt;a href=shop.py?out=y>   logout        &lt;/a>
          &lt;/center>
          &lt;/body>
          &lt;/html>
    """)
    
#===============================================================================
# main
#===============================================================================
cookies = COOKIE.getAll()                       #get the cookies
cust_id = cookies.get('cust_id')   
if not cust_id:                                 #if there is no cust_id cookie --> not logged in
    print("Location: shop.py")                  #redirect to login page
    print('\n')                                 #end of response headers
    exit()

input  = WEB.input()                             #obtain the input fields from screen
search = input.get('q')    or ""                 #get fields from web page             
sort   = input.get('sort') or ""                 

read_data()
print('\n')                                     #end of response headers
display()


#=== link to see the python code ================================================
import os
import sys
sys.path.insert(0,'/home/s/sultans/web/python/demo')
import zCode                          #import func to display the Python code
filename = os.path.abspath(__file__)  #get absolute file name 
zCode.display(filename)               #call it
#================================================================================
</pre>