Jump to content

Pull specific data from SQL and group it (best way I can think to put it)


geeknout

Recommended Posts

Hey Guys,

 

I can get the data from sqlsrv but I can't figure out how to format similar to a crystal report.. i.e.

 

something like:

 

Find a specific customer number

Print out the number, plus the customer name then:

  find all the rows that have the customer number and print them.

 

I can get all the information obviously, but I'm not good enough with PHP yet to find similar to the above.  Here is how I pull my data out if your curious:

 

while( $obj = sqlsrv_fetch_object( $stmt)) {
                                             
                                                $reqshipdt = date("d M Y", strtotime($obj->req_ship_dt));
                                                $popstdt = date("d M Y", strtotime($obj->start_dt));
                                                $reqshipdt1 = date(strtotime($obj->req_ship_dt));
                                                $popstdt1 = date(strtotime($obj->start_dt));
                                                $datediff = ($reqshipdt1 - $popstdt1);
                                                $days = round($datediff / 86400); //gives how many days
                                                echo "<tr>";
                                                echo "<td>". $obj->item_no."</td>";
                                                echo "<td>".$obj->item_desc_1."</td>";
                                                echo "<td>".$obj->ord_no."</td>";
                                                echo "<td>".$obj->qty_ordered."</td>";
                                                echo "<td>".$obj->mfg_ord_no."</td>";
                                                echo "<td>".$reqshipdt."</td>";
                                                echo "<td>".$obj->oe_po_no."</td>";
                                                echo "<td>".$obj->prod_cat."</td>";
                                                echo "<td>".$popstdt."</td>";
                                                echo "<td>".$days." Day(s)</td>";
                                                echo "<td>".$obj->planning_lead_tm."</td>";
                                                echo "</tr>";
                                         }
                                        

 

Hope this makes sense on what I'm trying to shoot for. 

 

Thanks Guys and Gals.

Link to comment
Share on other sites

Ok not sure why this was moved to SQL Server since its a PHP question, but hey whatever...  :confused:

 

I'm getting close can someone elaborate:

 

<?php
            include("includes/dbconn.php");
        
           if ($conn === 'false')
           {
               echo "Ooops!";
           }
           else
           {
               $query = "SELECT * from customers";
               
               $result = mysql_query($query) or die(mysql_error());
               
               while($row=mysql_fetch_assoc($result))
               {
                   echo "<br />";
                   echo $row['customerNumber']; //displays all the customer numbers
                   
                   foreach($row as $cusno)
                        {
                            // This doesnt seem to be running...
                            $query2 = "SELECT * FROM orders WHERE customerNumber = '%s'";
                            mysql_real_escape_string($cusno);
                            $result2 = mysql_query($query2) or die(mysql_error());
                            
                            while($row2=mysql_fetch_object($result2))
                                {
                                    echo "<br />";
                                    echo "Got this far."; //not getting this far just here to see if I can as I run my page.
                                }
                            
                        } 
               }
           }
          mysql_close($conn);
          unset($row);
        ?>

Link to comment
Share on other sites

Do one query that returns the data you need, then group it in php for display.

 

ex a query like

select 
*
from customers
inner join orders on customers.customerNumber=orders.customerNumber
order by customers.customerNumber

 

then process it like:

$customers=array();
while ($row=/*fetch row*/){
$custNo = $row['customerNumber'];
if (!isset($customers[$custNo])){
	$customers[$custNo]=array(
		'name' => $row['customerName'],
		/* .. other customer info fields */
		'orders' => array()
	);
}

$customers[$custNo]['orders'][] = $row;
}

print_r($customers);

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.