Avatar of rlb1
rlb1 asked on

MySQL - How do I Display query results in 3 columns

Experts,

How do I display query results in 3 columns?  I have several of the results showing up over and over again.  Here are the results and the query:  (I have tried DISTINCT and some other things and cannot get just one result of each category.)  Thanks for your help!!
RESULTS:
Cable Management Panels & Racks Cable Management Panels & Racks Cable Management Panels & Racks Cable Management Panels & Racks Modular & Telephone Modular & Telephone Cable Ties - Standard Bulk Speaker Wire Bulk Speaker Wire Bulk Speaker Wire Bulk Speaker Wire Cat6 - PVC (Solid & Stranded) Composite (Multi-Signal) Modular & Telephone Modular & Telephone Modular & Telephone Modular & Telephone Bulk Speaker Wire Bulk Speaker Wire Bulk Speaker Wire Bulk Speaker Wire Bulk Speaker Wire Bulk Speaker Wire RG6 RG6 RG6 RG6 RG6 RG6 RG6 RG6 RG6 RG6 Cable Ties - Screw-Mountable Cable Ties - Screw-Mountable Wraps Wraps Flat Ribbon Flat Ribbon Multi-Conductor Multi-Conductor Multi-Conductor Multi-Conductor Multi-Conductor Multi-Conductor

QUERY:  
$SearchResult=mysql_query("SELECT DISTINCT * FROM `products` WHERE `Category` = '$SearchString'") or die(mysql_error());

While($row2 = mysql_fetch_object($SearchResult)) {
Echo $row2->Subcategory . "  ";
}

<form name="form" action="cat.php" method="get">
<!--<input type="hidden" name="p" value=''/>-->
 
 
 
 
<SELECT NAME=p>
<option>SELECT CATEGORY</option>
<option value='Adapters & Couplers'>Adapters & Couplers</option>
<option value='Audio Video Cables'>Audio Video Cables</option>
<option value='Audio Video Devices'>Audio Video Devices</option>
<option value='Bulk Cable'>Bulk Cable</option>
<option value='Cat5e & Cat6 Cables'>Cat5e & Cat6 Cables</option>
<option value='Connectors'>Connectors</option>
<option value='DVI'>DVI</option>
<option value='Fiber Cables'>Fiber Cables</option>
<option value='HDMI'>HDMI</option>
<option value='Home Distribution'>Home Distribution</option>
<option value='KVM Cables & Switches'>KVM Cables & Switches</option>
<option value='Monitor Cables'>Monitor Cables</option>
<option value='Network Cables'>Network Cables</option>
<option value='Network Devices'>Network Devices</option>
<option value='PC Cables'>PC Cables</option>
<option value='Power Cords & Chargers'>Power Cords & Chargers</option>
<option value='Retail Items'>Retail Items</option>
<option value='SCSI'>SCSI</option>
<option value='Tools & Testers'>Tools & Testers</option>
<option value='USB'>USB</option>
<option value='VGA'>VGA</option>
<option value='Wall Plates'>Wall Plates</option>
</select>
<input type="submit" name="Submit" value="Search" />
</form>
 
 
 
 
 
 
 
<?
//Connect to DB
mysql_connect("       ","         ","        ") or die("Unable to connect to SQL server");
mysql_select_db("         ") or die("Unable to SELECT DB");
 
$Limit = 10; //Number of results per page
$SearchString=$_POST["p"]; // Get the search term
If($SearchString == "") $SearchString=$_GET["p"]; // Get the search term
If($SearchString == "") {
Echo"No results found for $SearchString <BR> Please enter a valid search term";
exit();
}
$page=$_GET["page"]; //Get the page number to show
If($page == "") $page=1; //If no page number is set, the default page is 1
 
 
 
// NEED ASSISTANCE HERE
 
 
$SearchResult=mysql_query("SELECT DISTINCT * FROM `products` WHERE `Category` = '$SearchString'") or die(mysql_error());
 
While($row2 = mysql_fetch_object($SearchResult)) {
Echo $row2->Subcategory . "  ";
}
 
 
 
 
 
//Get the number of results
$SearchResult=mysql_query("SELECT * FROM `products` WHERE `Category` = '$SearchString'") or die(mysql_error());
$NumberOfResults=mysql_num_rows($SearchResult);
 
 
 
//Get the number of pages
$NumberOfPages = (ceil($NumberOfResults/$Limit)>=20) ? 8 : ceil($NumberOfResults/$Limit);
 
$SearchResult=mysql_query("SELECT * FROM `products` WHERE `Category` = '$SearchString' LIMIT " . ($page-1)*$Limit . ",$Limit") or die(mysql_error());
 
 
 
 
 
 
 
Echo $NumberOfResults . "  ";
Echo "results for";
Echo "  ";
Echo $SearchString;
Echo "<BR><BR>";
 
 
 
 
$Nav="";
If($page > 1) {
$Nav .= "<A HREF=\"cat.php?page=" . ($page-1) . "&p=" .urlencode($SearchString) . "\"><< Prev </A>&nbsp;&nbsp;&nbsp;";
}
For($i = 1 ; $i <= $NumberOfPages ; $i++) {
If($i == $page) {
$Nav .= "<font size=+1><B>&nbsp;$i&nbsp;</B></FONT>";
}Else{
$Nav .= "<A HREF=\"cat.php?page=" . $i . "&p=" .urlencode($SearchString) . "\"> $i </A>";
}
}
If($page < $NumberOfPages) {
$Nav .= "&nbsp;&nbsp;&nbsp;<A HREF=\"cat.php?page=" . ($page+1) . "&p=" .urlencode($SearchString) . "\">Next >> </A>";
}
 
Echo $Nav;
Echo "<BR><BR>";
 
 
While($row = mysql_fetch_object($SearchResult)) {
 
 
 
Echo "<form name='form' action='product.php' method='GET'>";
Echo "<input type='hidden' name='UPC' value=".$row->UPC." />";
Echo $row->ProductName . "  ";
Echo $row->Sku . "  ";
Echo $row->UPC . "  ";
Echo "<img src=";
Echo "http://www.cablestogo.com/assets/product_images/category_favorites_images/";
Echo $row->PictureSku;
Echo ".jpg>";
Echo "<input type='submit' name='Submit' value='More Info' />";
Echo "<HR width=500>";
Echo "</form>";
}
 
Echo "<BR><BR>" . $Nav;
 
?>

Open in new window

PHP

Avatar of undefined
Last Comment
rlb1

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Terry Woods

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
mankowitz

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
rlb1

TerryAtOpus & mankowitz,
Both of your solutiions worked!  Thanks!  Below is the table structure...  I think I need some type of array??  to list the results in 3 columns without duplicating?
Thanks!!




Echo "<TABLE><TR>";

$SearchResult=mysql_query("SELECT DISTINCT `Subcategory` FROM `products` WHERE `Category` = '$SearchString'") or die(mysql_error());

While($row2 = mysql_fetch_object($SearchResult)) {
Echo "<TD>";   // Not sure what to do right here??
Echo $row2->Subcategory . "  ";
Echo "</TD>";

}

Echo "</TR></TABLE>";
ASKER
rlb1

OK, I Here is my revised code.  This is very close to being right, but I am getting a blank page when  I run the code.  Please let me know if I have structured something wrong.  Thanks!!!






print "<table width=490 border=1 cellspacing=0 cellpadding=0>";
print "<tr><td valign=top>";

// SELECT * FROM products WHERE `Category` = '$SearchString' ORDER BY 'Subcategory'


$SearchResult2=mysql_query("SELECT DISTINCT `Subcategory` FROM `products` WHERE `Category` = '$SearchString' ") or die(mysql_error());
$num_result=mysql_num_rows($SearchResult2);

if($num_result>0)
{
      $i=1;
      $column=1;
      while($record=mysql_fetch_array($SearchResult2))
      {
            if($i == 10)
            {
                  if($column==4)
                  {
                        print "</td></tr><tr><td valign=\top\>";
                        $column=1;
                  }
                  else
                  {
                        print "</td><td valign=\top\>";
                        $column++;
                  }
                  $i=1;
            }
            print "<b>$record[Subcategory]</b><br>\";
            $i++;
      }
}

Echo "</td></tr></table>";

mankowitz

a few things
1.  I don't see that you are incrementing $column, so it will never equal 4. Similarly, you are incrementing $i within a block that begins with if($i==10). It will never get into that block
2. I am not sure why you are putting slashes before and after the word "top"
3. If you want to include an array subscript in a print, then you have to enclose the whole thing in curly braces, like this
      print "<b>{$record[Subcategory]}</b><br>\";

You probably want something like this
print "<table>";
$i=0;
while($record=mysql_fetch_array($SearchResult2))
{
if (($i % 3) == 0) print "<tr>";
 print "<td><b>{$record[Subcategory]}</b></td>";
if (($i % 3) == 0) print "</tr>";
$i++;
}
print "</table>";

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
rlb1

mankowitz,
Thanks it works!  There is one problem though....  If I set ($i % 3),  I get this: (one on first row, two on second row, one on third row, two on forth row, etc.)

S-Video Cables
Composite Video Cables         Y-Cables and Splitter Cables

If I set ($i % 4),  I get this:  (one on first row, three on second row, one on third row, three on fourth row, etc.)

S-Video Cables
Composite Video Cables         Y-Cables and Splitter Cables     RCA Stereo Cables

How would you reccomend fixing this?   Thanks for your help!!


$SearchResult2=mysql_query("SELECT DISTINCT `Subcategory` FROM `products` WHERE `Category` = '$SearchString' ") or die(mysql_error());
$num_result=mysql_num_rows($SearchResult2);

print "<table>";
$i=0;
while($record=mysql_fetch_array($SearchResult2))
{
if (($i % 3) == 0) print "<tr>";
 print "<td><b>{$record[Subcategory]}</b></td>";
if (($i % 3) == 0) print "</tr>";
$i++;
}
print "</table>";
Terry Woods

Note I disagree with Mankowitz's comment that the $column and $i values weren't being incremented. Both were, though they obviously still weren't working as desired.

That aside, I'm confused about what you actually want as output - can you give us an example of the output you want?
ASKER
rlb1

Thanks TerryAtOpus!  Here is what I am looking for...  Just a regular table with 3 columns....  I just do not know how to format it with PHP/MySQL.

Desired Output:

Subcategory1    Subcategory2      Subcategory3

Subcategory4     Subcategory5      Subcategory6
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Terry Woods

Try this:
$SearchResult2=mysql_query("SELECT DISTINCT `Subcategory` FROM `products` WHERE `Category` = '$SearchString' ") or die(mysql_error());
$num_result=mysql_num_rows($SearchResult2);
 
print "<table>";
$i=0;
while($record=mysql_fetch_array($SearchResult2))
{
  if (($i % 3) == 0) print "<tr>";
  print "<td><b>{$record[Subcategory]}</b></td>";
  if (($i % 3) == 2) print "</tr>";
  $i++;
}
 
#If we finished the above while loop with $i%3 < 2 then print the final </tr> tag:
if ( $i > 0 && ($i-1) % 3 < 2) print "</tr>";
 
print "</table>";

Open in new window

ASKER
rlb1

Thanks TerryAtOpus!
EXACTLY How I wanted it!!!!!     This is above and beyond your code that worked....  Let's say that I have a product that does not have a Subcategory (or the Subcategory field is blank in the DB.)     Is there a way to not "print" any result that is blank?   Here is an example of what I mean.....

Digital Audio - S/PDIF                    Subwoofer Cables               **(Blank..... Product exists with no Subcat)**
Speaker Interconnect Cables        M1 Cables                           HDTV Breakout Cables & Cable Kits
BNC Cables                                   Pro-Audio Cables                 CCTV Cables (Siamese/Power)

Thanks again for your help!!!
Terry Woods

Something like the below should do it I think.

Just a quick note also that it would have been good to assign a split of the points to mankowitz as some of his code was used in the solution. To do this, you can re-open the question by posting a request in the community forum if you like (provide the url of your question):
https://www.experts-exchange.com/Community_Support/General/
$SearchResult2=mysql_query("SELECT DISTINCT `Subcategory` FROM `products` WHERE `Category` = '$SearchString' ") or die(mysql_error());
$num_result=mysql_num_rows($SearchResult2);
 
print "<table>";
$i=0;
while($record=mysql_fetch_array($SearchResult2))
{
  if(strlen($record[Subcategory])>0) {
    if (($i % 3) == 0) print "<tr>";
    print "<td><b>{$record[Subcategory]}</b></td>";
    if (($i % 3) == 2) print "</tr>";
    $i++;
  }
}
 
#If we finished the above while loop with $i%3 < 2 then print the final </tr> tag:
if ( $i > 0 && ($i-1) % 3 < 2) print "</tr>";
 
print "</table>";

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
rlb1

Thanks TerryAtOpus And Mankowitz,
I meant to split points, but I just did not scroll back up.  Sorry Mankowitz!!!!!!    I have requested that points be split with customer service.
Thanks again for all of your help  TerryAtOpus & Makowitz!!!!
ASKER
rlb1

TerryAtOpus & Mankowitz,
I really appreciate your help!!  You and this website have brought my skills A LONG WAY!!!
Thank you very much!!
RLB1