distinct result

[Raadha]

what i need is a single set of output for each procedures, but my this query is giving me 2-3 sets of answers, between this time period.. i need distinct set of answers..

if(isset($_POST['submit']))
{
$to= $_POST['to'];
$too = date("d/m/y",strtotime($_POST['to']));

$from= date('Y-m-d');;
echo " <br><h2> PROCEDURE REPORTS UPTO <b>'$too' </b> </h2>"; 

$p =array();
$query = mysqli_query($con,"SELECT COUNT(pro) FROM ivf WHERE DATE(epd) >='$from' and DATE(epd) <='$to' GROUP BY pro");
$result1 = mysqli_query($con,"SELECT * FROM ivf WHERE DATE(epd) >='$from' and DATE(epd) <='$to'GROUP BY pro");
while ($row=mysqli_fetch_array($result1))
{ 
$row1=mysqli_fetch_array($query); 
echo "<td align='center' width='200'>" . $row[2] . "</td>"; 
echo "<td align='center' width='100' >"  .$row1[0].  "</td>";
$p[]=$row1[0];
$a[]=$row[2];
echo"</tr>";
}
echo"</table>";

echo" <br><br><br><br>";
echo" <h2>STOCK REPORT</h2>";

$result = mysqli_query($con,"SELECT DISTINCT * FROM phar 
JOIN med
ON med.iname = phar.iname 
JOIN ivf
ON phar.pro = ivf.pro 
WHERE  DATE(ivf.epd) >='$from' and DATE(ivf.epd) <='$to'");

echo "<td align='center'>Medicine Name</td> 
      <td width='100' align='center'>Procedure</td> 
      <td width='100' align='center'>Stock in Hand</td>
      <td width='100' align='center'>Required Quantity</td></tr> "; 
echo "</tr>";

while($row2=mysqli_fetch_array($result))
{

echo "<tr>";
$inme =array();
$stk[]=$row2['stock']; 
$inme[]=$row2['iname'];
for ($i = 0; $i < count($inme); $i++)
{

echo "<td align='center' width='200'>" . $row2[1] . "</td>";  
echo "<td align='center' width='200'>" . $row2['pro'] . "</td>";
echo "<td align='center' width='50'>" . $row2['stock'] . "</td>";  

echo "</tr>";  

}
}
echo "</table>"; 

mysqli_close($con);
}

PROCEDURE REPORTS UPTO '31/05/14'

Procedure Name Total Procedures
Andrology Procedure 1
Ovum Pick up 5
Tesa Procedure 2
*****************************************************

5ml round bottom tube Andrology Procedure 6
Sample Container Andrology Procedure -47
14ml round bottom tube Ovum Pick up 250
14ml round bottom tube Ovum Pick up 250
14ml round bottom tube Ovum Pick up 250
14ml round bottom tube Ovum Pick up 250
14ml round bottom tube Ovum Pick up 250
4 well dish Ovum Pick up 0
4 well dish Ovum Pick up 0
4 well dish Ovum Pick up 0
4 well dish Ovum Pick up 0
4 well dish Ovum Pick up 0
5ml round bottom tube Tesa Procedure 6
5ml round bottom tube Tesa Procedure 6
Falcon(3002) Tesa Procedure 266
Falcon(3002) Tesa Procedure 266

[diafol]

I'm afraid there is so much wrong with this, I don't know where to start.

You start with 2 queries where only one if required, e.g.

//======================== INPUT VARIABLES ============================
$from = '2014-01-09';
$to = '2014-01-11';
//======================= END INPUT VARIABLES =========================

//======================= CONNECTION SCRIPT ===========================
$db = new mysqli('localhost', '#########', '########', 'daniweb');

if ($db->connect_error) {
    die('Connect Error (' . $db->connect_errno . ') '
            . $db->connect_error);
}
//==================== END CONNECTION SCRIPT ==========================

//================== INITIALIZE OUTPUT VARIABLES ======================
$summaryTable = "";
$detailsTable = "";
//================= END INITIALIZE OUTPUT VARIABLES ===================

//===================== GET SUMMARY TABLE OUTPUT ======================
$query = "SELECT pro, COUNT(pro) FROM ivf 
            WHERE DATE(epd) >= ? and DATE(epd) <= ? 
            GROUP BY pro";

if($stmt = $db->prepare($query))
{
    $stmt->bind_param('ss', $from, $to);
    $stmt->execute();
    $stmt->bind_result($procedure, $total);
    while ($stmt->fetch()) {
        $summaryTable .= 
            printf("<tr><td>%s</td><td>%d</td>\n", $procedure, $total);
    }
    $stmt->close();
}
//=================== END GET SUMMARY TABLE OUTPUT ====================

It gets really bizarre from row 26 onwards. Are you really creating a join on long text fields? What about your primary and foreign keys (usually integer)? Do you have any?

You use * in the SQL, which means that all fields will be returned in the dataset, regardless of the fields that you want to display in the html table - so it will probably not be DISTINCT at all! Explicitly name the fields you wish to extract in the SQL.

The html table is a mess as there is no open <table> tag, no opening <tr> table row tag. Your column headings - 4 of them are contained in <td> - you should use <th>, preferably the whole row being contained in <thead> tags.

Other html issues are the inline styling and layout attributes - these should be tucked away in CSS or <style> tags in the <head>.

for ($i = 0; $i < count($inme); $i++)

This will probably create rows of differing field numbers. Why do you need to do this? AFAIK, you want 4 fields.
However, you don't seem to want 4 fields, but 3:

echo "<td align='center' width='200'>" . $row2[1] . "</td>";  
echo "<td align='center' width='200'>" . $row2['pro'] . "</td>";
echo "<td align='center' width='50'>" . $row2['stock'] . "</td>";  

You seem to mix index and associative references to the array here, which is allowed with fetch_array, but why do you need both? Seems confused.
What happened to 'Required Quantity'?

Perhaps you could have another think about what you want to achieve and come back?