Help with listing stuff - $5 reward

I have the following code, basically I want to sort by id, the highest would be the newest. I know I could change the query to something like ORDER by link_id ASC or something (well i dont know mysql but i know thats possible) .. however, when doing that, my links like /index.php?sortby=link_added&order=DESC do not work, I had someone change the mysql query so i could try it and I got mysql errors. Can anyone come up with a fix so the following automatically displays the newest links first and the things like /index.php?sortby=link_added&order=DESC, /index.php?sortby=link_visitors&order=DESC etc work.

I will reward you with $5 + a positive rep.

<?

                // Include database

                require '/home/proxygd/public_html/proxies/config.php';

                // Number of items per page

                $perpage = 10;

                

                // I am currently drunk.  This page works fine.  The code may not be the greatest, but it works.

                // It's still better than Pixabit, bwuahahaha.

                

                // Base query

                $q = "SELECT * from links WHERE confirmed = 'yes'";

                

                // Other modifiers

                if ($_GET['type']) {

                    $type = $_GET['type'];

                    $q .= " WHERE link_type = '$type' && confirmed = 'yes'";

                }

                

                // Check if sorting

                

                if ($_GET['sortby']) {

                    $sortby = $_GET['sortby'];

                    $q .= " ORDER BY $sortby";

                }

                

                // Check sort order

                if ($_GET['order']) {

                    $order = $_GET['order'];

                    $q .= " $order";

                }

                

                $total = mysql_num_rows(mysql_query($q));

                

                // Check limit per page

                if ($_GET['page']) {

                    $pp = $_GET['page'];

                    if ($pp == 1) {

                    $start = 0;

                    }

                    if ($pp > 1) {

                    $start = $perpage * ($pp - 1);

                    }

                    $q .= " LIMIT $start, $perpage";

                }

                else {

                $q .= " LIMIT $perpage ";

                }

                $result = mysql_query($q) or die("Error in query " . mysql_error());

                $numb = mysql_num_rows($result);

                if ($numb > 0) {

                while ($row = mysql_fetch_assoc($result)) {

                // THIS IS THE PART WHERE YOU CAN MODIFY THE LINK OUTPUT

                // DO NOT MODIFY ANY THING OUTSIDE OF THE FOLLOWING 4 LINES OR YOU WILL BREAK SOMETHING

                echo "<p class=\"box\"><a href=\"$siteurl/$row[link_id]/\">$row[link_title]</a><br />";

                echo "Description: " . $row['link_description'] . "<br />";

                echo "Visitors: " . $row['link_visitors'] . "<br />";

                echo "Type: " . $row['link_type'];

                echo "</p>";

                }

                if ($pp > 1) {

                $ts = 0;

                foreach ($_GET as $key => $value) {

                    if ($key == "page") { $value--; }

                    if ($ts == 0) {

                    $ns .= "?" . $key . "=" . $value;

                    $ts++;

                    }

                    else {

                    $ns .= "&" . $key . "=" . $value;

                    $ts++;

                    }

                    }

                    echo "<a href=\"view_proxies.php" . $ns . "\">PREVIOUS</a>";

                }
                // Fix for pagination.
                // PP was set to 0, causing equations to evaluate wrong.
                if (($perpage * $pp) == "0") { $pp = "1"; }
                // Fix for the pagination part 2
                unset($ns);
                if ($total > ($perpage * $pp)) {
                    $ts = 0;
                        foreach ($_GET as $key => $value) {
                            if ($key == "page") { $value++; }
                            if ($ts == 0) {
                            $ns .= "?" . $key . "=" . $value;
                            $ts++;
                            }        
                            else {
                            $ns .= "&" . $key . "=" . $value;
                            $ts++;
                            }
                        }
                        if (empty($ns) and $total > ($perpage * $pp)) { echo "<a href=\"view_proxies.php?page=2\">NEXT</a>"; }
                        if (!empty($ns) and $total > ($perpage * $pp)) { echo "<a href=\"view_proxies.php" . $ns . "\">NEXT</a>"; }

                    }

                }

                else {

                echo "There are no links for the criteria specified";

                }

                ?>

PHP:

 

when you put code (php var) in your mysql queries it should be like :

select * from asd where type = '$type' 
// just an example 

//so your lines here : 
$q .= " ORDER BY $sortby";

$q .= " $order";

$q .= " LIMIT $perpage ";

//should be :

$q .= " ORDER BY '$sortby' "; //you can add ASC or DESC at the end

$q .= " '$order' ";

$q .= " LIMIT '$perpage' ";

PHP:

 

Try this:

require '/home/proxygd/public_html/proxies/config.php';

$perpage = 10;

$q = 'SELECT * FROM links WHERE confirmed = \'yes\'';

if (!empty($_GET['type'])) {
   $q .= ' AND link_type = \''. addslashes($_GET['type']) .'\'';
}

if (!empty($_GET['sortby'])) {
   $q .= ' ORDER BY \''. addslashes($_GET['sortby']) .'\'';
}

if (!empty($_GET['order']) AND $_GET['order'] == 'DESC') {
   $q .= ' DESC';
}

        $total = mysql_num_rows(mysql_query($q));								

if (!empty($_GET['page'])) {
   if ($_GET['page'] == 1) {
	    $start = 0;
	 }
	 else {
	    $start = $perpage * ($_GET['page'] - 1);
	 }
	 $q .= ' LIMIT '. $start .', '. $perpage;
}
else {
   $q . = ' LIMIT '. $perpage;
}

$result = mysql_query($q) or die("Error in query " . mysql_error());

[...]

PHP:

If that doesn't work, please post the error message here.

 

Parse error: syntax error, unexpected '=' in /home/proxygd/public_html/index.php on line 137

137 - $q . = ' LIMIT '. $perpage;

 

Ah yeah, one space too much.

Should be .= instead of . =

 

OK - Well that works, but it doesn't list them newest first automatically.

 

OK what's the name of the auto increment column

 

Use this:

if (!empty($_GET['sortby'])) {
   $q .= ' ORDER BY '. addslashes($_GET['sortby']);
	 if (!empty($_GET['order']) AND $_GET['order'] == 'DESC') {
      $q .= ' DESC';
	 }
}
else {
   $q .= ' ORDER BY link_id DESC';
}

PHP:

 

Where do I place that?

 

Replace the existing if clause for $_GET['sortby'].

 

OK that worked, however.

/index.php?sortby=link_title&order=DESC doesn't, but ASC does.

The DESC/ASC thing is the same for sortby=link_title, link_visitors and link_url.

 

Try this:

if (!empty($_GET['sortby'])) {
   $q .= ' ORDER BY '. addslashes($_GET['sortby']);
   if (!empty($_GET['order'])) {
      $q .= ' '. addslashes($_GET['order']);
   }
}
else {
   $q .= ' ORDER BY link_id DESC';
}

PHP:

 

Nope, that didn't work

 

Why don't you post an error message or the query? Would certainly make it easier.

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/proxygd/public_html/index.php on line 131
Error in query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC LIMIT 10' at line 1

When I visit index.php?sortby=link_visitors&order=DESC

 

Use echo $q; and post the output.

 

When I'm on index.php it's:
SELECT * FROM links WHERE confirmed = 'yes' ORDER BY link_id DESC LIMIT 10

/index.php?sortby=link_visitors&order=ASC :
SELECT * FROM links WHERE confirmed = 'yes' ORDER BY link_visitors ASC LIMIT 10

 

There is nothing wrong with that query from what I can see. Are you sure that's the correct one?

 

Yeah, well, my code is below.

Visit: http://www.proxy.gd/index.php?sortby=link_visitors&order=DESC and you'll see it doesn't work : (

<?

				// Include database

require '/home/proxygd/public_html/proxies/config.php';

$perpage = 10;

$q = 'SELECT * FROM links WHERE confirmed = \'yes\'';

if (!empty($_GET['type'])) {
   $q .= ' AND link_type = \''. addslashes($_GET['type']) .'\'';
}

if (!empty($_GET['sortby'])) {
   $q .= ' ORDER BY '. addslashes($_GET['sortby']);
   if (!empty($_GET['order'])) {
      $q .= ' '. addslashes($_GET['order']);
   }
}
else {
   $q .= ' ORDER BY link_id DESC';
}

if (!empty($_GET['order']) AND $_GET['order'] == 'DESC') {
   $q .= ' DESC';
}

        $total = mysql_num_rows(mysql_query($q));                        

if (!empty($_GET['page'])) {
   if ($_GET['page'] == 1) {
        $start = 0;
     }
     else {
        $start = $perpage * ($_GET['page'] - 1);
     }
     $q .= ' LIMIT '. $start .', '. $perpage;
}
else {
   $q .= ' LIMIT '. $perpage;
}

$result = mysql_query($q) or die("Error in query " . mysql_error());

				$numb = mysql_num_rows($result);

				if ($numb > 0) {

				while ($row = mysql_fetch_assoc($result)) {

				// THIS IS THE PART WHERE YOU CAN MODIFY THE LINK OUTPUT

				// DO NOT MODIFY ANY THING OUTSIDE OF THE FOLLOWING 4 LINES OR YOU WILL BREAK SOMETHING

				echo "<p class=\"box\"><a href=\"$siteurl/$row[link_id]/\">$row[link_title]</a><br />";

				echo "Description: " . $row['link_description'] . "<br />";

				echo "Visitors: " . $row['link_visitors'] . "<br />";

				echo "Type: " . $row['link_type'];

				echo "</p>";

				}

				if ($pp > 1) {

				$ts = 0;

				foreach ($_GET as $key => $value) {

					if ($key == "page") { $value--; }

					if ($ts == 0) {

					$ns .= "?" . $key . "=" . $value;

					$ts++;

					}

					else {

					$ns .= "&" . $key . "=" . $value;

					$ts++;

					}

					}

					echo "<a href=\"view_proxies.php" . $ns . "\">PREVIOUS</a>";

				}
				// Fix for pagination.
				// PP was set to 0, causing equations to evaluate wrong.
				if (($perpage * $pp) == "0") { $pp = "1"; }
				// Fix for the pagination part 2
				unset($ns);
				if ($total > ($perpage * $pp)) {
					$ts = 0;
						foreach ($_GET as $key => $value) {
							if ($key == "page") { $value++; }
							if ($ts == 0) {
							$ns .= "?" . $key . "=" . $value;
							$ts++;
            				}		
							else {
							$ns .= "&" . $key . "=" . $value;
							$ts++;
							}
						}
						if (empty($ns) and $total > ($perpage * $pp)) { echo "<a href=\"view_proxies.php?page=2\">NEXT</a>"; }
						if (!empty($ns) and $total > ($perpage * $pp)) { echo "<a href=\"view_proxies.php" . $ns . "\">NEXT</a>"; }

					}

				}

				else {

				echo "There are no links for the criteria specified";

				}

				?>

PHP:

 

Remove this:

if (!empty($_GET['order']) AND $_GET['order'] == 'DESC') {
   $q .= ' DESC';
}

PHP:

It's there twice.