user5307298 user5307298 - 7 months ago 11
SQL Question

How can I convert this untidy table header sorting code to well compact function?

I have written a code snippet to sort my table headers in PHP/MySQL. The HTML of table headers along with PHP embedded code is given by:

<tr>
<th><a href="?orderby=id<?php if($orderby == '`post_id`' && $order == 'asc') echo '&order=desc'; else echo '&order=asc'; ?><?php echo $q; ?>" title="<?php echo $title_th1; ?>">ID</a>&nbsp;<?php echo $icon_th1; ?></th>
<th><a href="?orderby=name<?php if($orderby == '`post_name`' && $order == 'asc') echo '&order=desc'; else echo '&order=asc'; ?><?php echo $q; ?>" title="<?php echo $title_th2; ?>">Name</a>&nbsp;<?php echo $icon_th2; ?></th>
<th><a href="?orderby=url<?php if($orderby == '`post_url`' && $order == 'asc') echo '&order=desc'; else echo '&order=asc'; ?><?php echo $q; ?>" title="<?php echo $title_th3; ?>">URL</a>&nbsp;<?php echo $icon_th3; ?></th>
<th><a href="?orderby=title<?php if($orderby == '`p`.`post_title`' && $order == 'asc') echo '&dir=desc'; else echo '&dir=asc'; ?><?php echo $q; ?>" title="<?php echo $title_th4; ?>">Title</a>&nbsp;<?php echo $icon_th4; ?></th>
</tr>


PHP code that handles the incoming GET request and decides what should be the correct table sorting order column and sorting direction (i.e. asc or desc) is given below:


// Sorting
if(isset($_GET['orderby']))
{
$orderby = $_GET['orderby'];

switch($orderby)
{
case "id":
$orderby = "post_id";
break;
case "name":
$orderby = "post_name";
break;
case "url":
$orderby = "post_url";
break;
case "title":
$orderby = "post_title";
break;
default:
$orderby = "post_name";
}
}

// Sort direction
if(isset($_GET['order']) && in_array($_GET['order'], array('asc', 'desc')))
{
$order = $_GET['order'];
}

// Get dynamic sort direction icon and anchor title
if($orderby == 'post_id')
{
if($order == 'asc')
{
$icon_th1 = '<img src="images/arrow_up.png" class="arrow_dir">';
$title_th1 = 'Click to sort results descending';
}
else
{
$icon_th1 = '<img src="images/arrow_down.png" class="arrow_dir">';
$title_th1 = 'Click to sort results ascending';
}
$icon_th2 = '';
$title_th2 = 'Click to sort results ascending';
$icon_th3 = '';
$title_th3 = 'Click to sort results ascending';
$icon_th4 = '';
$title_th4 = 'Click to sort results ascending';
}

elseif($orderby == 'post_name')
{
if($order == 'asc')
{
$icon_th2 = '<img src="images/arrow_up.png" class="arrow_dir">';
$title_th2 = 'Click to sort results descending';
}
else
{
$icon_th2 = '<img src="images/arrow_down.png" class="arrow_dir">';
$title_th2 = 'Click to sort results ascending';
}
$icon_th1 = '';
$title_th1 = 'Click to sort results ascending';
$icon_th3 = '';
$title_th3 = 'Click to sort results ascending';
$icon_th4 = '';
$title_th4 = 'Click to sort results ascending';
}

elseif($orderby == 'post_url')
{
if($order == 'asc')
{
$icon_th3 = '<img src="images/arrow_up.png" class="arrow_dir">';
$title_th3 = 'Click to sort results descending';
}
else
{
$icon_th3 = '<img src="images/arrow_down.png" class="arrow_dir">';
$title_th3 = 'Click to sort results ascending';
}
$icon_th1 = '';
$title_th1 = 'Click to sort results ascending';
$icon_th2 = '';
$title_th2 = 'Click to sort results ascending';
$icon_th4 = '';
$title_th4 = 'Click to sort results ascending';
}

elseif($orderby == 'post_title')
{
if($order == 'asc')
{
$icon_th4 = '<img src="images/arrow_up.png" class="arrow_dir">';
$title_th4 = 'Click to sort results descending';
}
else
{
$icon_th4 = '<img src="images/arrow_down.png" class="arrow_dir">';
$title_th4 = 'Click to sort results ascending';
}
$icon_th1 = '';
$title_th1 = 'Click to sort results ascending';
$icon_th2 = '';
$title_th2 = 'Click to sort results ascending';
$icon_th3 = '';
$title_th3 = 'Click to sort results ascending';
}
?>


This code is working without any issue. But what I am thinking is to convert it into something more tidy and compact may be into some function either. Because the code length increases as the number of table headers increase and number of columns we need to use in the sorting procedure. Currently we have only 4
<th>
participating in the sorting. But what if we had more than it may be 7, 8 or so? Because there are almost same code repeating in all lines, so I am wondering can we convert it into some function which can be called anywhere where we want to use sorting.

Secondly, I am also thinking to convert my
<tr><th>...</th></tr>
HTML code from static to dynamic using some array and loop. Is it possible? If not, then no problem, my main focus is to solve the first query I just discussed above.

Answer

See if this is what you are thinking of, it may be close:

function renderButton($raw_value,$title_value)
    {
        // Take the raw column name and prepend for later-comparison
        $to_orderby =   'post_'.$raw_value;
        // Check if orderby is not empty, assign it or assign a default
        $orderby    =   (!empty($_GET['orderby']))? 'post_'.$_GET['orderby'] : 'post_id';
        // Check if asc or desc, if neither, default asc
        $order      =   (!empty($_GET['order']) && in_array($_GET['order'], array('asc', 'desc')))?  $_GET['order'] : 'asc';
        // Choose by order
        $icn_dir    =   ($order == 'asc')? 'up' : 'down';
        // Choose by order
        $aTitle     =   ($order == 'asc')? 'ascending' : 'decending';
        // Combine string with variable
        $icn        =   '<img src="images/arrow_'.$icn_dir.'.png" class="arrow_dir">';
        // Combine string with variable
        $title      =   'Click to sort results '.$aTitle;
        // Do a comparison
        $matched    =   ($orderby == $to_orderby);
        // Create a cached string
        ob_start();
?>
        <a href="?orderby=<?php echo $raw_value; echo ($matched && $order == 'asc')? '&order=desc' : '&order=asc'; ?><?php //echo $q; ?>" title="<?php if($matched) echo $title; ?>"><?php echo $title_value; ?></a>&nbsp;<?php if($matched) echo $icn; ?>
<?php
        $data   =   ob_get_contents();
        ob_end_clean();
        // Return the cached string
        return $data;
    }

// Render all the buttons
echo renderButton('id','ID').PHP_EOL;
echo renderButton('one','One').PHP_EOL;
echo renderButton('two','Two').PHP_EOL;
echo renderButton('three','3').PHP_EOL;