How to display distinct records

mpsn · June 29, 2011

Hi, I am building a simple online buying website and for the admin php script, I want to display the same product type (in this case the same kind of cell phone) in an html table (I have the code all made below), but on the shopping public site, i want to display an html table with only UNIQUE entries and to just add up the quantity of the duplicates of a given product.

I know it's something like: SELECT DISTINCT etc, but please read my code and let me know, thanks. And then I have another field only in the shoppers page (the second set of code I posted on this page) that shows the quantity, but I don't know the mysql query to count up all the duplicates, let me know, thanks. :rtfm:

admin page (where admin adds new and multiple of an item for shoppers to buy)

<?php session_start();
include_once('config.php'); //NB: this includes the constant: domain
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml/DTD/xhtml1-strict.dtd"> 
  
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Bell Mobility - By Andy 2</title>
<link rel="stylesheet" type="text/css" href="BellATuCSS.css" />
<script type="text/javascript" src="BellATuJS.js"></script>

</head>

<body>
    <img src="Bell_Logo.jpg" title="Welcome to Bell Mobility!"/>
          
    <div>
        <form>  
            <input type="button" onclick="location='<?php echo DOMAIN?>/bellInsertForm.php'" value="Insert new entry" />
            <input type="button" onclick="window.open('<?php echo DOMAIN?>/bellPublicHome.php')" value="Public" />
            <!--<input type="button" onclick="window.open('<?php echo DOMAIN?>/purchaseditem.php')" value="View Customer Database" />-->
        </form>
    </div>  
           
    <hr />

<form method="POST" action=""> <!--NB: checkbox form for multiple delete/update/insert-->
    <table border="border"> 
           
           <input type='submit' value='DELETE' name='delete0' /> <!--name has suffix 0 to not get get mixed up with the submit name in update/delete php-->
           <!--<input type="submit" value="UPDATE" name="update0" />-->
<?php
    
    require 'bellSessionDisconnect.inc';
    require 'bellConnect.inc.php';      
                              
    //NB: only show DELETE NOW to confirm admin wishes to delete all checked entries 
    echo "<div id='confirmDelete' style='display:none'>
            <form method='post'>
              <input type='submit' value='DELETE NOW' name='deleteNow' />
              <input type='button' name='Cancel' value='Cancel' onclick='window.location='http://localhost/bell/display.php'' />
            </form>
          </div>"; 
       
    //NB: if DELETE button pressed, then delete all the entries with the ID's captured from the $_POST array named
    //  modifyArray (this is name attribute of checkbox input type)
    if(isset($_POST['delete0']) && !empty($_POST["modifyArray"]))//when admin presses DELETE button and there is at least one 
    //  entry selected, then show the confirmation to delete 
    {    
      $modifyArray = $_POST["modifyArray"];
      echo "<strong>Are you sure you want to delete ";
      foreach ($modifyArray as $curCheckBoxItem) 
      {
            $result=mysql_query("SELECT Manufacturer, Name FROM bellProducts WHERE ID='$curCheckBoxItem'");
            $row=mysql_fetch_assoc($result);
            echo $row['Manufacturer']." ".$row['Name'].", ";
            
      }//END FOR EACH loop
      echo "? </strong>";
    
      echo "<script type='text/javascript'>  
        document.getElementById('confirmDelete').style.display='visible';    
            </script>";
            	  
	  //NB: Only actually delete checked entries iff user clicks DELETE NOW button
	  if($_POST['deleteNow'])
	  {
        //NB: traverse the checked items to delete
        foreach ($modifyArray as $curItem) 
        {
          $sql = "DELETE FROM bellProducts WHERE ID='$curItem";
          $result = mysql_query($sql);

        }//END FOR EACH loop
      
        //NB: show Deleted status
        if($result)
        {
          echo "Deleted successfully.";
        }
        else if(!$result) //NB: how do I only display the error messages I want, not php error messages...
          echo "No items selected to delete.";
      }//END BIG IF for DELETE NOW button 
        
    }//END BIG IF for DELETE button


  //NB: outputting a nice html table of db table 
   $result=mysql_query("SELECT ID, Image, Name, Manufacturer, Price, Description, SimSupport FROM bellProducts");
    //phpinfo(); 

    //NB: print table headings
    if(mysql_num_rows($result))//if there is at least one entry in bellProducts, make a table
    {
        //$counter+=1;
        print "<table border='border'>";
        print "<tr>
        <th>ID</th>
        <th>Select entries to delete</th> 
        <th>Image</th>
        <th>Name</th>
        <th>Manufacturer</th>
        <th>Price</th>
        <th>Description</th>
        <th>Sim Support</th>
        <th colspan='2' align='center'>Modify</th>
           </tr>";

        //NB: now output each row of records
        while($row=mysql_fetch_assoc($result))//NB: while this row still has fields to go through
        {
            //extract($row);
                    
                print
                "<tr align='center'>
                    <td>$row[iD]</td>
                    <td><input type='checkbox' name='modifyArray[]' value='$row[iD]' id=\"modifyArray[]\"/></td>
                    <td>";
                    if(!empty($row['Image']))
                    {
                        $curImage=$row['Image'];
                    }
                    else if(empty($row['Image']))
                    {
                        $curImage='fileUploadIcon.jpg';
                        //$title="please upload item image";
                    }
                       print "<img src=$curImage width='70px' height='90px' title='please upload product image' />
                    </td>
                    <td> $row[Name] </td>
                    <td> $row[Manufacturer] </td>
                    <td> $$row[Price]</td>
                    <td align='left'>$row[Description]</td>
                    <td>$row[simSupport]</td>
                    <td><a href='bellUploadForm.php?ID=$row[iD]'>UPLOAD IMAGE</a></td>
                    <td><a href='bellUpdateForm.php?ID=$row[iD]'>UPDATE</a></td>
                  </tr>"; 
            //}//END INNER IF
        }//END WHILE
             
  }//END BIG IF

?>
        
      </table>
    </form>
</body>

</html>

<?php mysql_close();?>
[\code]

Here is the shopper public php script:
[code]
<?php 
  include_once('config.php');
  require 'bellConnect.inc.php';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Bell Mobility by Andy 2: Public</title>
</head>

<body>
<form method="POST" action="buyTester.php"><?php
$result=mysql_query("SELECT ID, Image, Name, Manufacturer, Price, Description, SimSupport FROM bellProducts");
    //phpinfo(); 

    //NB: print table headings
    if(mysql_num_rows($result))//if there is at least one entry in bellProducts, make a table
    {
        //$counter+=1;
        print "<table border='border'>";
        print "<tr>     
        <th>Select products to buy</th> 
        <th>Available Quantity</th>
        <th>Enter quantity</th>
        <th>Image</th> 
        <th>Name</th>
        <th>Manufacturer</th>
        <th>Price</th>
        <th>Description</th>
        <th>Sim Support</th>   
           </tr>";

        //NB: now output each row of records
        while($row=mysql_fetch_assoc($result))//NB: while the current db table's row still has fields to extract 
        {
            //extract($row);
            
                print
                "<tr align='center'>
              
                    <td><input type='checkbox' name='modifyArray[]' value='$row[iD]' id=\"modifyArray[]\" /></td>
                    <td style='text-align:center'> 3 </td>
                    <td style='text-align:center'><input type='text' name='updateQuantity' size='2' value=1 /></td>
                    <td>";
                    if(!empty($row['Image']))
                    {
                        $curImage=$row['Image'];
                    }
                    else if(empty($row['Image']))
                    {
                        $curImage='fileUploadIcon.jpg';
                        //$title="please upload item image";
                    }
                       print "<img src=$curImage width='70px' height='90px' title='please upload product image' />
                    </td>
                    <td> $row[Name] </td>
                    <td> $row[Manufacturer] </td>
                    <td> $$row[Price]</td>
                    <td align='left'>$row[Description]</td>
                    <td>$row[simSupport]</td>
                  </tr>";
            //}//END INNER IF
        }//END WHILE
             
  }//END BIG IF

?>
         
</table>
<input type='submit' value='Proceed to checkout' name='checkout' />
    </form>

</body>
</html>
[\code]

mpsn · June 29, 2011

I got it to work, just a simple: SELECT ID, Name, Manufacturer etc GROUP BY Name, Manufacturer to not show duplictates!

vbconz · June 30, 2011

I got it to work, just a simple: SELECT ID, Name, Manufacturer etc GROUP BY Name, Manufacturer to not show duplictates!

To get a count add SELECT ID, Name, Manufacturer count(1) as numberOfItemsInThisCategory etc GROUP BY Name, Manufacturer to not show duplictates!

ebmigue · June 30, 2011

SELECT DISTINCT Name, Manufacturer, etcc...FROM tableName

could to the trick, and dispense with the GROUP BY clause.

Sign In

How to display distinct records

Recommended Posts

mpsn

Link to comment

Share on other sites

mpsn

Link to comment

Share on other sites

vbconz

Link to comment

Share on other sites

ebmigue

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information