sorting results with sub categories

I was in a rush typing it before a final sorry.

I want to be able to sort my results, like I have now, but add display on dogs or cats

And give the option for all the subcategories

Ex.

show only black labs in dogs.

Hi,

Ok I'm struggling to get my head around this as I don't have a view of the DB and all your categories etc, but it looks to be me like you have the method of showing the categories and subcategories in dropdown boxes sorted. I suppose you want to leave your "Sort By:" fields as they are, but under them have a "Limit to:" heading with dropdowns for the categories and subcategories. Have you tried this?

It also looks like you have alreay added a WHERE clause to the SQL for: "p.category = c.cat_id"
and a variable to pick up a selected category: $category = $_POST['category'].
So after that i think all you'd need would be to change your SQL statement to look like:

$sql = "SELECT p.*, c.category FROM pet p, categories c WHERE p.category = $category ORDER BY p." . $sort . " $ascdesc LIMIT $from, $max_results";

And if you add a subcategory dropdown, change statment to:

$sql = "SELECT p.*, c.category FROM pet p, categories c WHERE p.category = $category AND p.subcategory = $subcategory ORDER BY p." . $sort . " $ascdesc LIMIT $from, $max_results";

I don't know, something like that! Oh and if you dont want to limit it I think that $category and $subcategory could be set to "%" and that would return all results...

Cheers! Ben

Hey man, that sounds like what I want to do! I will try implementing that and hopefully it will work! Ill get back to ya.

I just need to add a drop down for the categories right? If I made that while it would feed from the categories table righT?

Here is my DB layout

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `cat_id` int(10) NOT NULL auto_increment,
  `category` varchar(50) NOT NULL default '',
  `parent_id` int(10) NOT NULL default '0',
  PRIMARY KEY  (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `critter`
--

CREATE TABLE `critter` (
  `id` int(11) NOT NULL auto_increment,
  `username` text NOT NULL,
  `password` varchar(32) NOT NULL default '',
  `name` text NOT NULL,
  `email` text NOT NULL,
  `address` text NOT NULL,
  `phone` text NOT NULL,
  `businfo` text NOT NULL,
  `website` text NOT NULL,
  `date` text NOT NULL,
  `ip` text NOT NULL,
  `actkey` varchar(40) NOT NULL default '',
  `activated` int(1) NOT NULL default '0',
  `zip` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `pet`
--

CREATE TABLE `pet` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(150) default NULL,
  `date` datetime default NULL,
  `name` varchar(255) NOT NULL default '',
  `price` varchar(10) NOT NULL default '',
  `description` text NOT NULL,
  `city` varchar(30) NOT NULL default '',
  `state` varchar(2) NOT NULL default '',
  `zip` varchar(5) NOT NULL default '',
  `username` varchar(255) NOT NULL default '',
  `category` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

I could do this:

<select name="parent">
    <?php
    $query = "SELECT cat_id, category FROM categories WHERE parent_id=0";
    $result = mysql_query($query) or die(mysql_error());
   
    while ($row = mysql_fetch_object($result))
    {
      ?><option value="<?php echo $row->cat_id; ?>"><?php echo $row->category; ?></option>
      <?php
    }
    ?>
    </select><br />

But alter it to only show the main categories on not the sub right? then make another one for the sub?

Here is my latest code with that while in there for the categories:

<?php
include 'config.php';
$domain = "http://www.rksdesignstudios.com/clients/critterclassifieds/pets.php";
?>


<p>
<form action="<?php echo $_SERVER['SCRIPT_NAME']; ?>" method="post" name="action">

    <label>Sort by:</label>
    <select name="sort">
        <option value="name">Name</option>
        <option value="date">Date</option>
        <option value="id">ID</option>
        <option value="username">Username</option>
        <option value="category">Category</option>
    </select><br />
    <label>Filter Categories:</label>
    <select name="parent">
    <?php
    $query = "SELECT cat_id, category FROM categories WHERE parent_id=0";
    $result = mysql_query($query) or die(mysql_error());
   
    while ($row = mysql_fetch_object($result))
    {
      ?><option value="<?php echo $row->cat_id; ?>"><?php echo $row->category; ?></option>
      <?php
    }
    ?>
    </select><br />
    <label>DESC or ASC</label>
    <select name="ascdesc">
        <option value="DESC">Descending</option>
        <option value="ASC">Ascending</option>
    </select><br />
    <label>Display</label>
    <select name="max_results">
        <option value="5">5</option>
        <option value="10" selected="selected">10</option>
        <option value="20">20</option>
        <option value="50">50</option>
    </select><br />
   
   
    <input name="action" type="hidden" value="go">
    <input name="submit" type="submit" style="float:right;" value="Submit!">
</form>

<br />
<label for="pages"><b>Page:</b></label>
</p>

<?php

if(!isset($_GET['page']))
{
      $page = 1;
}
else
{
      $page = $_GET['page'];
}


$max_results = 10;

$from = (($page * $max_results) - $max_results);

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM pet"),0);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
$total_pages = intval($total_results / $max_results);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
if($page > 1)
{
    $prev = ($page - 1);
    echo "<a href=\"$domain?page=$prev\">&laquo; Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++)
{
    if(($page) == $i)
      {
        echo "$i ";
        }else{
            echo "<a href=\"$domain?page=$i\">$i</a> ";
    }
}

if($page < $total_pages)
{
    $next = ($page + 1);
    echo "<a href=\"$domain?page=$next\">Next &raquo;</a>";
}

// alternating row colors
$color1 = "#f8f8f8";
$color2 = "#cccccc";
$row_count = 0;

if (isset($_POST['action']))
{
      echo "<h1>SORTING ENABLED</h1>";
    $sort = $_POST['sort'];
    $ascdesc = $_POST['ascdesc'];
    $max_results = $_POST['max_results'];
    $category = $_POST['category'];

$sql = "SELECT p.*, c.category FROM pet p, categories c WHERE p.category = $category ORDER BY p." . $sort . " $ascdesc LIMIT $from, $max_results";

print "first query - $sql</br>";

echo "<h2>" . $sql . "</h2>";
   $result = mysql_query($sql);
}
else
{
$sql = "SELECT p.*, c.category FROM pet p, categories c WHERE p.category = c.cat_id ORDER BY p.id DESC LIMIT $from, $max_results";

print "second query - $sql</br>";
    $result = mysql_query($sql) or die("Bad Query: " . mysql_error());
}

$num_rows  =  mysql_num_rows($result);
echo "<br /><b>Results returned:</b> $num_rows\n";
echo "</div><table width=\"100%\" cellpadding=\"2\" cellspacing=\"0\">";
if ($num_rows > 0)
{
    while ($row = mysql_fetch_array($result))
      {
    $row_color = ($row_count % 2) ? $color1 : $color2;
    echo '<tr bgcolor="'. $row_color .'">
          <td>      
          <h1><a href="pet-detial.php?id='. $row['id'] .'" target="_blank">'. $row['title'] .'</a></h1><br />
          <h2><b>Pet Name:</b> '.$row['name'].'</h2><br />
          <h2><b>Posted By:</b> <a href="profile.php?id='. $row['id'] .'" target="_blank">'.$row['username'].'</a></h2><br />

              <p>'.$row['description'].'</p>

              <b>Added on:</b> '. date("F j, Y, g:i a", strtotime($row[date])) .' &middot; <b>Category:</b> '.$row['category'].' &middot;
 
          </td>
              </tr>'."\n";
          $row_count++;
    }

}
else
{
      echo "No resources in this category.";
}
echo "</table>";
?>
</body>
</html>


I get this error, when I use the form:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/g/l/a/glanceatx/html/clients/critterclassifieds/pets.php on line 133

$num_rows  =  mysql_num_rows($result);

Would i need to alter both queries, I am looking at my code now, and am confused.

I would also have to add what you said:

I don't know, something like that! Oh and if you dont want to limit it I think that $category and $subcategory could be set to "%" and that would return all results...

Because when it loads we want all results correct?

Re that error, I think:

$category = $_POST['category'];

should be:

$category = $_POST['parent'];

when its in the 'sorting enabled' section.

I have to leave work now, and going out for a meal, so Good luck! If you are still having problems I'll check back either late tonight or first thing in the morning.

By the way, that wildcard % thing might not be a problem actually becasue when the first page is showed you use 'second query' which doesnt have any category selected does it?
Its only when people refine their search that they would have to select a category. Although if you always want them to be able to show all then we will have to use a wildcard.

Cheers!

I updated that change with parent, but it wont start selecing the categories, it sorts but not by the category type.

Is there a way to only show category X and then also sort it by that, i think to save time I just want to have show only cat X.

Thats fine. If you did check tomorrow or tonight, that would be a big help thank you!

That is correct, the second query is the default one, that has no sorting options.

Hi, how you getting on? I was just about to take a look again when I see that the page has changed and you've added some INNER JOINS etc. And any selection now only returns one result.

So whats the latest? I think the easiest way for me to help is if I set the page up here with your DB, will take 5 mins. If you still need help can you paste the whole of the pets.php. I'll use the DB structure you pasted above, so if thats changed could you paste the new structure too. I can just fill it with random stuff.

Alrighty, thanks for you help man! I appreciate it alot! You're a big help here is code for pets.hpp

<?php
include 'config.php';
$domain = "http://www.rksdesignstudios.com/clients/critterclassifieds/pets.php";
?>


<p>
<form action="<?php echo $_SERVER['SCRIPT_NAME']; ?>" method="post" name="action">

    <label>Sort by:</label>
    <select name="sort">
        <option value="name">Name</option>
        <option value="date">Date</option>
        <option value="id">ID</option>
        <option value="username">Username</option>
        <option value="category">Category</option>
    </select><br />
    <label>Show only:</label>
    <select name="parent">
    <?php
    $query = "SELECT cat_id, category FROM categories WHERE parent_id=0";
    $result = mysql_query($query) or die(mysql_error());
   
    while ($row = mysql_fetch_object($result))
    {
      ?><option value="<?php echo $row->cat_id; ?>"><?php echo $row->category; ?></option>
      <?php
    }
    ?>
    </select><br />
    <label>DESC or ASC</label>
    <select name="ascdesc">
        <option value="DESC">Descending</option>
        <option value="ASC">Ascending</option>
    </select><br />
    <label>Display</label>
    <select name="max_results">
        <option value="5">5</option>
        <option value="10" selected="selected">10</option>
        <option value="20">20</option>
        <option value="50">50</option>
    </select><br />
   
   
    <input name="action" type="hidden" value="go">
    <input name="submit" type="submit" style="float:right;" value="Submit!">
</form>

<br />
<label for="pages"><b>Page:</b></label>
</p>

<?php

if(!isset($_GET['page']))
{
      $page = 1;
}
else
{
      $page = $_GET['page'];
}


$max_results = 10;

$from = (($page * $max_results) - $max_results);

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM pet"),0);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
$total_pages = intval($total_results / $max_results);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
if($page > 1)
{
    $prev = ($page - 1);
    echo "<a href=\"$domain?page=$prev\">&laquo; Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++)
{
    if(($page) == $i)
      {
        echo "$i ";
        }else{
            echo "<a href=\"$domain?page=$i\">$i</a> ";
    }
}

if($page < $total_pages)
{
    $next = ($page + 1);
    echo "<a href=\"$domain?page=$next\">Next &raquo;</a>";
}

// alternating row colors
$color1 = "#f8f8f8";
$color2 = "#cccccc";
$row_count = 0;

if (isset($_POST['action']))
{
      echo "<h1>SORTING ENABLED</h1>";
    $sort = $_POST['sort'];
    $ascdesc = $_POST['ascdesc'];
    $max_results = $_POST['max_results'];
      $category = $_POST['parent'];

$sql = "SELECT p.*, c.category, U.ID as CritterID FROM pet p INNER JOIN categories c ON p.category = c.cat_id INNER JOIN critter U on p.username = U.username
ORDER BY p." . $sort . " DESC LIMIT $from, $max_results";

print "first query - $sql</br>";

echo "<h2>" . $sql . "</h2>";
   $result = mysql_query($sql);
}
else
{
$sql = "SELECT p.*, c.category, U.ID as CritterID FROM pet p INNER JOIN categories c ON p.category = c.cat_id INNER JOIN critter U on p.username = U.username
ORDER BY p.id DESC LIMIT $from, $max_results";
print "second query - $sql</br>";
    $result = mysql_query($sql) or die("Bad Query: " . mysql_error());
}

$num_rows  =  mysql_num_rows($result);
echo "<br /><b>Results returned:</b> $num_rows\n";
echo "</div><table width=\"100%\" cellpadding=\"2\" cellspacing=\"0\">";
if ($num_rows > 0)
{
    while ($row = mysql_fetch_array($result))
      {
    $row_color = ($row_count % 2) ? $color1 : $color2;
    echo '<tr bgcolor="'. $row_color .'">
          <td>      
          <h1><a href="pet-detial.php?id='. $row['id'] .'" target="_blank">'. $row['title'] .'</a></h1><br />
          <h2><b>Pet Name:</b> '.$row['name'].'</h2><br />
          <h2><b>Posted By:</b> <a href="profile.php?id='. $row['CritterID'] .'" target="_blank">'.$row['username'].'</a></h2><br />

              <p>'.$row['description'].'</p>

              <b>Added on:</b> '. date("F j, Y, g:i a", strtotime($row[date])) .' &middot; <b>Category:</b> '.$row['category'].' &middot;
 
          </td>
              </tr>'."\n";
          $row_count++;
    }

}
else
{
      echo "No resources in this category.";
}
echo "</table>";
?>
</body>
</html>

If there is anything else you need I will be at my computer, the good ole gmail notifier I love it. I will be headed back for home, but I should be back on around 2 I will answer any questions you have immeadiately.

Ryan

OK! can you give this a go? I have set it up so that you can 'show' all, or one of your categories.
I've also added/fixed the Ascending / Descending option.
There are loads of little tweaks that can be made but i'm not sure exactly what you want, and presumably you are going to move this all into a tarted up website once you've finished testing it.
I had to guess at the values if config.php, but dont think that will effect you when testing. And of course theres only so much of my companies time I can use to look at this :)

<?php
include 'config.php';
$domain = "http://www.rksdesignstudios.com/clients/critterclassifieds/pets.php";
?>


<p>
<form action="<?php echo $_SERVER['SCRIPT_NAME']; ?>" method="post" name="action">

    <label>Sort by:</label>
    <select name="sort">
        <option value="name">Name</option>
        <option value="date">Date</option>
        <option value="id">ID</option>
        <option value="username">Username</option>
        <option value="category">Category</option>
    </select><br />
    <label>Show:</label>
    <select name="parent">
            <option value="'%'">All</option>
    <?php
    $query = "SELECT cat_id, category FROM categories WHERE parent_id=0";
    $result = mysql_query($query) or die(mysql_error());
   
    while ($row = mysql_fetch_object($result))
    {
      ?><option value="<?php echo $row->cat_id; ?>"><?php echo $row->category; ?></option>
      <?php
    }
    ?>
    </select><br />
    <label>DESC or ASC</label>
    <select name="ascdesc">
        <option value="DESC">Descending</option>
        <option value="ASC">Ascending</option>
    </select><br />
    <label>Display</label>
    <select name="max_results">
        <option value="5">5</option>
        <option value="10" selected="selected">10</option>
        <option value="20">20</option>
        <option value="50">50</option>
    </select><br />
   
   
    <input name="action" type="hidden" value="go">
    <input name="submit" type="submit" style="float:right;" value="Submit!">
</form>

<br />
<label for="pages"><b>Page:</b></label>
</p>

<?php

if(!isset($_GET['page']))
{
      $page = 1;
}
else
{
      $page = $_GET['page'];
}


$max_results = 10;

$from = (($page * $max_results) - $max_results);

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM pet"),0);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
$total_pages = intval($total_results / $max_results);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
if($page > 1)
{
    $prev = ($page - 1);
    echo "<a href=\"$domain?page=$prev\">&laquo; Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++)
{
    if(($page) == $i)
      {
        echo "$i ";
        }else{
            echo "<a href=\"$domain?page=$i\">$i</a> ";
    }
}

if($page < $total_pages)
{
    $next = ($page + 1);
    echo "<a href=\"$domain?page=$next\">Next &raquo;</a>";
}

// alternating row colors
$color1 = "#f8f8f8";
$color2 = "#cccccc";
$row_count = 0;

if (isset($_POST['action']))
{
    echo "<h1>SORTING ENABLED</h1>";
    $sort = $_POST['sort'];
    $ascdesc = $_POST['ascdesc'];
    $max_results = $_POST['max_results'];
    $category = $_POST['parent'];

$sql = "SELECT p.*, c.category, U.ID as CritterID FROM pet p INNER JOIN categories c ON p.category = c.cat_id INNER JOIN critter U on p.username = U.username
WHERE c.cat_id LIKE ".$category." ORDER BY p." . $sort . " " . $ascdesc . " LIMIT $from, $max_results";

print "first query - $sql</br>";

echo "<h2>" . $sql . "</h2>";
   $result = mysql_query($sql);
}
else
{
$sql = "SELECT p.*, c.category, U.ID as CritterID FROM pet p INNER JOIN categories c ON p.category = c.cat_id INNER JOIN critter U on p.username = U.username
ORDER BY p.id DESC LIMIT $from, $max_results";
print "second query - $sql</br>";
    $result = mysql_query($sql) or die("Bad Query: " . mysql_error());
}

$num_rows  =  mysql_num_rows($result);
echo "<br /><b>Results returned:</b> $num_rows\n";
echo "</div><table width=\"100%\" cellpadding=\"2\" cellspacing=\"0\">";
if ($num_rows > 0)
{
    while ($row = mysql_fetch_array($result))
      {
    $row_color = ($row_count % 2) ? $color1 : $color2;
    echo '<tr bgcolor="'. $row_color .'">
          <td>      
          <h1><a href="pet-detial.php?id='. $row['id'] .'" target="_blank">'. $row['title'] .'</a></h1><br />
          <h2><b>Pet Name:</b> '.$row['name'].'</h2><br />
          <h2><b>Posted By:</b> <a href="profile.php?id='. $row['CritterID'] .'" target="_blank">'.$row['username'].'</a></h2><br />

              <p>'.$row['description'].'</p>

              <b>Added on:</b> '. date("F j, Y, g:i a", strtotime($row['date'])) .' &middot; <b>Category:</b> '.$row['category'].' &middot;
 
          </td>
              </tr>'."\n";
          $row_count++;
    }

}
else
{
      echo "No resources in this category.";
}
echo "</table>";
?>
</body>
</html>

Hey man, first off, thank you very much, this really helps me out, i am learning PHP more and more everyday, thank you!

Thank you again!

I just uploaded it and it still shows only one result, when I go click submit I get:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/content/g/l/a/glanceatx/html/clients/critterclassifieds/pets.php on line 135

$num_rows  =  mysql_num_rows($result);


Any suggestions?

Ryan

Hi, Can you post the contents of config.php here please? I can't see why you'd only get one result, unless you only had 1 entry in the DB :)
Also I presume that you have pets of different categories in your DB? Here's the test DB that I used, maybe you can try the code with it:

-- Dumping data for table `categories`
INSERT INTO `categories` VALUES (1, 'Dogs', 0);
INSERT INTO `categories` VALUES (2, 'Cats', 0);
INSERT INTO `categories` VALUES (3, 'Birds', 0);

-- Dumping data for table `critter`
INSERT INTO `critter` VALUES (1, 'ben', 'ben', 'ben hutchings', 'hutch@myaddres.com', '123 street', '01234 5678910', 'Business Info', 'Website Address', 'The Date', 'The IP', 'Act Key', 0, 'ABC 1');

-- Dumping data for table `pet`
INSERT INTO `pet` VALUES (1, 'Mrs Sushi', '2007-04-03 16:10:03', 'Sushi', '£15', 'A highly intelligent animal', '', '', '', 'ben', '2');
INSERT INTO `pet` VALUES (2, 'Mrs Noodles', '2007-04-30 16:17:25', 'Noodles', '£20', 'A foolish cat', '', '', '', 'ben', '2');
INSERT INTO `pet` VALUES (3, 'Master Tibbles', '2007-02-05 16:22:07', 'Tibbles', '£35', 'A woofit if ever there was one', '', '', '', 'ben', '1');
INSERT INTO `pet` VALUES (4, 'S. Parrot', '2006-10-25 16:24:22', 'Sparrot', '£45', 'A psychopathic Parrot', '', '', '', 'ben', '3');

I had to guess at the field values, and think that it was done ok. I only used one 'critter' (presumably thats what you call humans?)

Ah... ok the problem is this:

To allow the sorting of ALL I was using this in my SELECT query:
...WHERE c.cat_id LIKE '%'...

However - it seems that your install of PHP has has 'magic_quotes_gpc = On'. This means that whenever a slash, single or double quote appears in GET or POST or COOKIE data, it is automatically back-slashed to make it safe to insert into a database. This means that where I have set the value of 'parent' to '%' in the dropdown box (for the ALL condition), when it's pulled out and placed in the SELECT query it ends up looking like this:

WHERE c.cat_id LIKE \'%\'

Note the unwanted backslashes?

One way to fix this is to 'stripslashes' from the $category varable like this (line 117):

replace: $category = $_POST['parent'];
with: $category = stripslashes($_POST['parent']);

Another way (preferable if possible in my opinion) is to turn magic-quotes OFF in your php.ini file.
You can find it in the DATA HANDLING section of the ini file, and can set it to:

magic_quotes_gpc = Off

But... even after that, it still looks like your DB only has one record :)

Hey man, thank you for your help, I never really knew what magic quotes did, but a simple tip to fix would be to add:

stripslashes?

I added that on the category POST and I still get only 1 results, but NO error, which is a good thing, haha.

Oh yeah, I like your method of turning magic quotes off, but with go daddy I dont think you can access/edit php.ini

Here is my latest code:

<?php
include 'config.php';
$domain = "http://www.rksdesignstudios.com/clients/critterclassifieds/pets.php";
?>


<p>
<form action="<?php echo $_SERVER['SCRIPT_NAME']; ?>" method="post" name="action">

    <label>Sort by:</label>
    <select name="sort">
        <option value="name">Name</option>
        <option value="date">Date</option>
        <option value="id">ID</option>
        <option value="username">Username</option>
        <option value="category">Category</option>
    </select><br />
    <label>Show:</label>
    <select name="parent">
            <option value="'%'">All</option>
    <?php
    $query = "SELECT cat_id, category FROM categories WHERE parent_id=0";
    $result = mysql_query($query) or die(mysql_error());
   
    while ($row = mysql_fetch_object($result))
    {
      ?><option value="<?php echo $row->cat_id; ?>"><?php echo $row->category; ?></option>
      <?php
    }
    ?>
    </select><br />
    <label>DESC or ASC</label>
    <select name="ascdesc">
        <option value="DESC">Descending</option>
        <option value="ASC">Ascending</option>
    </select><br />
    <label>Display</label>
    <select name="max_results">
        <option value="5">5</option>
        <option value="10" selected="selected">10</option>
        <option value="20">20</option>
        <option value="50">50</option>
    </select><br />
   
   
    <input name="action" type="hidden" value="go">
    <input name="submit" type="submit" style="float:right;" value="Submit!">
</form>

<br />
<label for="pages"><b>Page:</b></label>
</p>

<?php

if(!isset($_GET['page']))
{
      $page = 1;
}
else
{
      $page = $_GET['page'];
}


$max_results = 10;

$from = (($page * $max_results) - $max_results);

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM pet"),0);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
$total_pages = intval($total_results / $max_results);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
if($page > 1)
{
    $prev = ($page - 1);
    echo "<a href=\"$domain?page=$prev\">&laquo; Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++)
{
    if(($page) == $i)
      {
        echo "$i ";
        }else{
            echo "<a href=\"$domain?page=$i\">$i</a> ";
    }
}

if($page < $total_pages)
{
    $next = ($page + 1);
    echo "<a href=\"$domain?page=$next\">Next &raquo;</a>";
}

// alternating row colors
$color1 = "#f8f8f8";
$color2 = "#cccccc";
$row_count = 0;

if (isset($_POST['action']))
{
    echo "<h1>SORTING ENABLED</h1>";
    $sort = $_POST['sort'];
    $ascdesc = $_POST['ascdesc'];
    $max_results = $_POST['max_results'];
    $category = stripslashes($_POST['parent']);

$sql = "SELECT p.*, c.category, u.id as CritterID FROM pet p INNER JOIN categories c ON p.category = c.cat_id INNER JOIN critter u on p.username = u.username
WHERE c.cat_id LIKE ".$category." ORDER BY p." . $sort . " " . $ascdesc . " LIMIT $from, $max_results";

print "first query - $sql</br>";

echo "<h2>" . $sql . "</h2>";
   $result = mysql_query($sql);
}
else
{
$sql = "SELECT p.*, c.category, u.id as CritterID FROM pet p INNER JOIN categories c ON p.category = c.cat_id INNER JOIN critter u on p.username = u.username
ORDER BY p.id DESC LIMIT $from, $max_results";
print "second query - $sql</br>";
    $result = mysql_query($sql) or die("Bad Query: " . mysql_error());
}

$num_rows  =  mysql_num_rows($result);
echo "<br /><b>Results returned:</b> $num_rows\n";
echo "</div><table width=\"100%\" cellpadding=\"2\" cellspacing=\"0\">";
if ($num_rows > 0)
{
    while ($row = mysql_fetch_array($result))
      {
    $row_color = ($row_count % 2) ? $color1 : $color2;
    echo '<tr bgcolor="'. $row_color .'">
          <td>      
          <h1><a href="pet-detial.php?id='. $row['id'] .'" target="_blank">'. $row['title'] .'</a></h1><br />
          <h2><b>Pet Name:</b> '.$row['name'].'</h2><br />
          <h2><b>Posted By:</b> <a href="profile.php?id='. $row['CritterID'] .'" target="_blank">'.$row['username'].'</a></h2><br />

              <p>'.$row['description'].'</p>

              <b>Added on:</b> '. date("F j, Y, g:i a", strtotime($row['date'])) .' &middot; <b>Category:</b> '.$row['category'].' &middot;
 
          </td>
              </tr>'."\n";
          $row_count++;
    }

}
else
{
      echo "No resources in this category.";
}
echo "</table>";
?>
</body>
</html>

Hi, I'm a bit confused. I guess you missed my comments about stripslashes.
And are you saying it all works now or you still have a problem. Did you try the data from my DB?
Not a great deal of time today so sorry if you get rushed - to the point - replies.

Thats fine! I wont get rushed, thanks for your help

I got no errors with and without submiting form

I just still have only one result being displayed:

http://www.rksdesignstudios.com/clients/critterclassifieds/pets.php

And there are three in the database

All three rows have all information, mainly including the important CATEGORY ID.

So right as we stand we have:

Only displaying one of the three rows.
No errors but not sorting.

Thanks for your help man!

Ryan

Ok can you paste the data from your DB. I want to see what cat numbers are being used. Thanks.

Alternatively you can use my data (from post at 4.27.2007 at 08:18AM GMT) and tell me what happens.

I didnt even see that post! Let me try that I am right here at my computer, so I have gmail notifier so I get the emails right away.

Let me try that out!

Back within minutes.

Hi, Can you post the contents of config.php here please? I can't see why you'd only get one result, unless you only had 1 entry in the DB :)
Also I presume that you have pets of different categories in your DB? Here's the test DB that I used, maybe you can try the code with it:
------------------

that is right, I do have pets in different categories of my DB.

I am going to empty it and start new.

I will be back with results.

And I will dump lastest SQL and PHP

I started from scratch. Later I need to edit my add.php to work in my sub cats so I might need help with that, I can make a new post and give you points! haha

http://www.rksdesignstudios.com/clients/critterclassifieds/pets.php

I have three entires in the DB

http://www.rksdesignstudios.com/clients/critterclassifieds/pets.php

One dog, one cat, one bird, for testing puporses.

Here is my SQL DUMP:



-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `cat_id` int(10) NOT NULL auto_increment,
  `category` varchar(50) NOT NULL default '',
  `parent_id` int(10) NOT NULL default '0',
  `subcategory` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` VALUES (1, 'Dogs', 0, '');
INSERT INTO `categories` VALUES (2, 'Cats', 0, '');
INSERT INTO `categories` VALUES (3, 'Birds', 0, '');

-- --------------------------------------------------------

--
-- Table structure for table `critter`
--

CREATE TABLE `critter` (
  `id` int(11) NOT NULL auto_increment,
  `username` text NOT NULL,
  `password` varchar(32) NOT NULL default '',
  `name` text NOT NULL,
  `email` text NOT NULL,
  `address` text NOT NULL,
  `phone` text NOT NULL,
  `businfo` text NOT NULL,
  `website` text NOT NULL,
  `date` text NOT NULL,
  `ip` text NOT NULL,
  `actkey` varchar(40) NOT NULL default '',
  `activated` int(1) NOT NULL default '0',
  `zip` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `critter`
--

INSERT INTO `critter` VALUES (1, 'glanceatx', 'df745a6ecc79adb85f58b55dc66bdd3b', 'Ryan Coughlin', 'glanceatx@gmail.com', '3 Captain Sawyer Lane', '2075905241', 'College Student', 'http://www.rksdesignstudios.com', '19, 04 07', '216.93.157.21', 'a9d124839e56e89cec7af9fd8f9e0072fcce1239', 1, '');
INSERT INTO `critter` VALUES (2, 'ryangatto', '098f6bcd4621d373cade4e832627b4f6', 'Ryan Gatto', 'ryangatto@catonthecouchproductions.com', '117 meadow', '617-347-3313', 'test', 'http://www.catonthecouchproductions.com', '19, 04 07', '71.169.162.9', 'a1f53f7db2f20cb3ebfd7af4e01a678720d58b2d', 1, '');

-- --------------------------------------------------------

--
-- Table structure for table `pet`
--

CREATE TABLE `pet` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(150) default NULL,
  `date` datetime default NULL,
  `name` varchar(255) NOT NULL default '',
  `price` varchar(10) NOT NULL default '',
  `description` text NOT NULL,
  `city` varchar(30) NOT NULL default '',
  `state` varchar(2) NOT NULL default '',
  `zip` varchar(5) NOT NULL default '',
  `username` varchar(255) NOT NULL default '',
  `category` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `pet`
--

INSERT INTO `pet` VALUES (1, 'Pet for sale', '2007-04-27 05:18:03', 'Fluffy', '', 'This is a nice Cat', '04046', 'ME', '04046', 'glanceatx', '');
INSERT INTO `pet` VALUES (2, 'Tough guy for sale', '2007-04-27 05:18:45', 'Tough Guy', '', 'Bad Dog but sweet at the same time', '02262', 'MA', '02262', 'glanceatx', '');
INSERT INTO `pet` VALUES (3, 'New bird', '2007-04-27 05:19:29', 'Tucan Sam', '', 'Nice calm yellow parrot', '05401', 'VT', '05401', 'glanceatx', '');



PHP FOR PETS.php

<?php
include 'config.php';
$domain = "http://www.rksdesignstudios.com/clients/critterclassifieds/pets.php";
?>


<p>
<form action="<?php echo $_SERVER['SCRIPT_NAME']; ?>" method="post" name="action">

    <label>Sort by:</label>
    <select name="sort">
        <option value="name">Name</option>
        <option value="date">Date</option>
        <option value="id">ID</option>
        <option value="username">Username</option>
        <option value="category">Category</option>
    </select><br />
    <label>Show:</label>
    <select name="parent">
            <option value="'%'">All</option>
    <?php
    $query = "SELECT cat_id, category FROM categories WHERE parent_id=0";
    $result = mysql_query($query) or die(mysql_error());
   
    while ($row = mysql_fetch_object($result))
    {
      ?><option value="<?php echo $row->cat_id; ?>"><?php echo $row->category; ?></option>
      <?php
    }
    ?>
    </select><br />
    <label>DESC or ASC</label>
    <select name="ascdesc">
        <option value="DESC">Descending</option>
        <option value="ASC">Ascending</option>
    </select><br />
    <label>Display</label>
    <select name="max_results">
        <option value="5">5</option>
        <option value="10" selected="selected">10</option>
        <option value="20">20</option>
        <option value="50">50</option>
    </select><br />
   
   
    <input name="action" type="hidden" value="go">
    <input name="submit" type="submit" style="float:right;" value="Submit!">
</form>

<br />
<label for="pages"><b>Page:</b></label>
</p>

<?php

if(!isset($_GET['page']))
{
      $page = 1;
}
else
{
      $page = $_GET['page'];
}


$max_results = 10;

$from = (($page * $max_results) - $max_results);

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM pet"),0);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
$total_pages = intval($total_results / $max_results);

if ($total_pages%$perpage)
{
        // has remainder so add one page
            $total_pages++;
}
if($page > 1)
{
    $prev = ($page - 1);
    echo "<a href=\"$domain?page=$prev\">&laquo; Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++)
{
    if(($page) == $i)
      {
        echo "$i ";
        }else{
            echo "<a href=\"$domain?page=$i\">$i</a> ";
    }
}

if($page < $total_pages)
{
    $next = ($page + 1);
    echo "<a href=\"$domain?page=$next\">Next &raquo;</a>";
}

// alternating row colors
$color1 = "#f8f8f8";
$color2 = "#cccccc";
$row_count = 0;

if (isset($_POST['action']))
{
    echo "<h1>SORTING ENABLED</h1>";
    $sort = $_POST['sort'];
    $ascdesc = $_POST['ascdesc'];
    $max_results = $_POST['max_results'];
    $category = stripslashes($_POST['parent']);

$sql = "SELECT p.*, c.category, u.id as CritterID FROM pet p INNER JOIN categories c ON p.category = c.cat_id INNER JOIN critter u on p.username = u.username
WHERE c.cat_id LIKE ".$category." ORDER BY p." . $sort . " " . $ascdesc . " LIMIT $from, $max_results";

print "first query - $sql</br>";

echo "<h2>" . $sql . "</h2>";
   $result = mysql_query($sql);
}
else
{
$sql = "SELECT p.*, c.category, u.id as CritterID FROM pet p INNER JOIN categories c ON p.category = c.cat_id INNER JOIN critter u on p.username = u.username
ORDER BY p.id DESC LIMIT $from, $max_results";
print "second query - $sql</br>";
    $result = mysql_query($sql) or die("Bad Query: " . mysql_error());
}

$num_rows  =  mysql_num_rows($result);
echo "<br /><b>Results returned:</b> $num_rows\n";
echo "</div><table width=\"100%\" cellpadding=\"2\" cellspacing=\"0\">";
if ($num_rows > 0)
{
    while ($row = mysql_fetch_array($result))
      {
    $row_color = ($row_count % 2) ? $color1 : $color2;
    echo '<tr bgcolor="'. $row_color .'">
          <td>      
          <h1><a href="pet-detial.php?id='. $row['id'] .'" target="_blank">'. $row['title'] .'</a></h1><br />
          <h2><b>Pet Name:</b> '.$row['name'].'</h2><br />
          <h2><b>Posted By:</b> <a href="profile.php?id='. $row['CritterID'] .'" target="_blank">'.$row['username'].'</a></h2><br />

              <p>'.$row['description'].'</p>

              <b>Added on:</b> '. date("F j, Y, g:i a", strtotime($row['date'])) .' &middot; <b>Category:</b> '.$row['category'].' &middot;
 
          </td>
              </tr>'."\n";
          $row_count++;
    }

}
else
{
      echo "No resources in this category.";
}
echo "</table>";
?>
</body>
</html>

Small change haha. One more last thing! Where do I enter category? Is it in my pets.php

Thank you!

Ryan

Nevermind I see! But that means that, the add.php page isnt working, I will post another question for that, I am not sure why that isnt working.

Ryan

Ok... i'll see you over on the other side then:)
I can try and take a look at the add.php, but need to really spend the afternoon confusing myself with an actionscript problem... pff i hate Flash.