SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Showing data in specific order

    I am stuck and would appreciate any help with this problem. I would like to display my sub categories and my products as such;

    Subcategory1
    Product1
    Product2

    Subcategory2
    Product1
    Product2

    So far, my code displays it as this;

    Subcatergory1
    Subcategory2

    Product1
    Product2

    Any ideas on how I can acheive this or any tutorials which show you how?

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Location
    New York
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is not enough info to be able to help you... Please post your code.
    www.forkaya.com - Web Development, PHP Scripting

  3. #3
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, here's the code. I have a routine which keep the column count to 4 and then creates a new row if it passes the maximum amount per row.

    <?php

    $query = mysql_query("SELECT subcatName FROM subcategory where cid='$categorytarget' ORDER BY subcatName ASC") or die("SELECT error: " . mysql_error());
    $num_rows = mysql_num_rows($query);
    $count = $num_rows;

    echo '<table cellspacing="5" cellpadding="5">';

    while($row = mysql_fetch_array($query))
    {

    $i = $row['subcatName'];
    echo '<td>' . $i . '</td></tr>';

    }


    $result = mysql_query("SELECT merchant_name, thumb_nail, subcatName FROM merchant, subcategory WHERE merchant.msubcid = subcategory.subcid AND cid= '$categorytarget'") or die("SELECT error: " . mysql_error());
    $num_rows = mysql_num_rows($result);
    //$result = mysql_query("SELECT subcatName FROM subcategory where cid='$categorytarget' ORDER BY subcatName ASC") or die("SELECT error: " . mysql_error());
    $sql = mysql_query($result);

    $columns_counter=4;


    echo '<tr>';


    while($row = mysql_fetch_array($result))
    {
    //echo '<td>' . $row['subcatName'] . '</td></tr>';
    echo '<td align = "center">';
    echo '<img src='.$row['thumb_nail'].' /><br />' . ($row['merchant_name']);
    //echo '</td><td>';
    echo '</td>';
    $columns_counter--;
    if(!$columns_counter)
    {
    echo '</tr><tr>';
    $columns_counter=4;
    }
    }
    echo '</tr></table>';

    //echo '</tr></table>';
    //echo show_merchant_list ($categorytarget);
    ?>

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that query inside the while loop should be eliminated

    a single join query is a lot more efficient

    sorry to butt in on your thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem with butting in. Thanks for the suggestion. I think the problem may be with the way my database has been set out. I have got a few ideas to try but if anyone else has any thoughts then please give them to me.

  6. #6
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I give in, I need help again. Can anyone suggest a script or tutorial where I can achieve the result I want. I believe that the way to go is via an Adjacency List but how would I go about creating the kind of output I want. Namely printing the products out that belong to the subcategory under the sub category heading.

    Any ideas?

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your SQL statement shoud join the 2 tables like this:
    Code:
    SELECT
    	s.subcatName
    	,m.merchant_name
    	,m.thumb_nail
    FROM
    	subcategory s
    LEFT JOIN
    	merchant m
    ON
    	m.msubcid = s.subcid
    ORDER BY
    	s.subcatName
    ASC

    Then when you loop through the results, just set check for /set the category:
    Code PHP:
    $currentCat = '';
    while($row = mysql_fetch_array($query)) {
    	extract($row);
    	if ($currentCat != $subcatName) {
    		//if a new category, write category head
    		echo "<tr><td colspan=\"2\"><h1>$subcatName</h1></td></tr>";
    		$currentCat = $subcatName;
    	} else {
    		echo "<tr><td>$merchant_name</td><td>$thumb_nail</td></tr>";
    	}
    }

    Does that help you out any?

  8. #8
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you funkdaddy. I will try that out today and let you know how I get on.

  9. #9
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your solution works. Thanks funkdaddy, however it is missing out the first merchant record. This happens whatever subcategory the merchant belongs to. Example, 4 merchants assigned to subcat 1, only three display. 3 merchants assigned to subcat 2, only 2 display. It's always the first record for the subcategory. Thanks for your help.

    Is this a looping problem? Here's my amended code including your solution;

    <?php

    echo '<table cellspacing="5" cellpadding="5" align="center">';

    $result = mysql_query("SELECT s.subcatName, m.merchant_name, m.thumb_nail FROM subcategory s LEFT JOIN merchant m ON m.msubcid = s.subcid WHERE cid='$categorytarget' ORDER BY s.subcatName ASC") or die("SELECT error: " . mysql_error());

    $columns_counter=5;

    echo '<tr>';

    $currentCat = '';
    while($row = mysql_fetch_array($result)) {
    $subcatName = $row['subcatName'];
    $merchant_name = $row['merchant_name'];
    $thumb_nail = $row['thumb_nail'];

    extract($row);
    if ($currentCat != $subcatName) {
    //if a new category, write category head
    echo "<tr><td colspan=\"2\"><h4>$subcatName</h4></td></tr>";
    $currentCat = $subcatName;
    } else {
    echo '<td align = "center">';
    echo '<A HREF="'.$config['thissite'].'/voucher-code/'.makeurlsafe($merchant_name).'"><img src='.$thumb_nail.' /></A><br />' . '<H4><A HREF="'.$config['thissite'].'/voucher-code/'.makeurlsafe($merchant_name).'">'.$merchant_name.'</A></H4>';
    }

    echo '</td>';
    $columns_counter--;
    if(!$columns_counter)
    {
    echo '</tr><tr>';
    $columns_counter=5;
    }
    }

    echo '</tr></table>';


    ?>
    Last edited by freakystreak; Mar 26, 2009 at 06:58.

  10. #10
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whoops... just remove the else statement from the cat checker:
    Code PHP:
    echo '<table cellspacing="5" cellpadding="5" align="center">';
     
    $result = mysql_query("SELECT s.subcatName, m.merchant_name, m.thumb_nail FROM subcategory s LEFT JOIN merchant m ON m.msubcid = s.subcid WHERE cid='$categorytarget' ORDER BY s.subcatName ASC") or die("SELECT error: " . mysql_error());
     
    $currentCat = '';
    while($row = mysql_fetch_array($result)) {
    	$subcatName = $row['subcatName'];
    	$merchant_name = $row['merchant_name'];
    	$thumb_nail = $row['thumb_nail'];
     
    	extract($row);
    	if ($currentCat != $subcatName) {
    		//if a new category, write category head
    		echo "<tr><td><h4>$subcatName</h4></td></tr>";
    		$currentCat = $subcatName;
    	}
    	echo '<tr><td align = "center"><A HREF="'.$config['thissite'].'/voucher-code/'.makeurlsafe($merchant_name).'"><img src='.$thumb_nail.' /></A><br />' . '<H4><A HREF="'.$config['thissite'].'/voucher-code/'.makeurlsafe($merchant_name).'">'.$merchant_name.'</A></H4></td></tr>'; 
    }
     
    echo '</table>';

    (Not sure why you had the columns_counter in there if you're putting everything in one table cell...)

  11. #11
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Funkdaddy you're a life saver. Thanks so much. If I could give kudos, I would.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •