Jump to content

Sorting the results of a query issue

Go to solution Solved by Nightasy,

Recommended Posts

Greetings all,


So I'm starting to play around with incorporating databases into dynamic pages and I've come across an issue that was only vaguely covered during my PHP course in college. The script below works great except that it needs a natsort and I'm not sure how to do a natsort and still make the while loop work. I'm not even sure how to go about writing it.


The script queries the database for the video names, video descriptions and then turns them into links (with a little css and java help). But after video 9 it starts to order incorrectly. It goes ...1, 10, 11, 12, 2, 3, 4, 5.... you get the idea, needs a natsort. I just don't have a clue how to go about doing that and keeping the while loop working. I tried a couple things but I'm just not figuring this out.


require ('masters/connect.php');
// Define the query:
$q = "SELECT * FROM videofiles WHERE folder='" . $sect . "' ORDER BY vidname";
$r = @mysqli_query ($connect, $q);

// Count the number of returned rows:
$num = mysqli_num_rows($r);

if ($num > 0) {

	// Table header:
	echo '<h1>Section Lessons</h1>';	
	echo '<table id="link-table">';
	// Set up array to remove beginning url from database section names.
	$sectpattern = $sect;
	$patterns = array();
	$patterns[0] = '/videos/';
	$patterns[1] = '/\//';
	$patterns[2] = '/' . $sectpattern . '/';
	$patterns[3] = '/\\.[^.\\s]{3,4}$/';
	$replacements = array();
	$replacements[0] = 'Lesson ';
 // Fetch and print all the records:
	while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
 		echo '<tr>
		<td>' . "<a href=\"javascript:create_window('".$row['vidname']."',640,360)\">Click Here</a>" . '</td>
		<td align="left">
		<div id="tabledataimage1">
			<img src="'. $row['vidimage'] .'" width="100"/>
		<div id="tabledatatitle1">
			<h4>' . preg_replace($patterns, $replacements, $row['vidname']) . '</h4>
		<div id="tabledatadesc1">
			' . $row['viddesc'] . '

		echo '</table>';
		mysqli_free_result ($r);
	} else {

// Inform that no entries were returned from the query.
echo '<p>There are currently no videos in this section.</p>';

// Close database connection:


As always any help is greatly appreciated. I'm having a lot of fun learning PHP but I still have a long ways to go.


Best Regards,


Edited by Nightasy
Link to comment
Share on other sites


Didn't seem to do the trick. I did this:

$q = "SELECT * FROM videofiles WHERE folder='" . $sect . "' 
		ORDER BY vidname REGEXP '^[A-Za-a]+$'
		,CAST(vidname as SIGNED INTEGER)
        ,CAST(REPLACE(vidname,'-','')AS SIGNED INTEGER)

 It's giving me the same output though.

Link to comment
Share on other sites

  • Solution

I got it working. Doing some more google searches (gotta love google), I found a forum written in a foreign language which had the solution. Well, at least it works for me.

The forums is here: http://www.php.net.my/forum/abs-mysql


They talked about using 'abs' which is absolute value. I used Google translate to read it, I don't speak that language.


I did the following:

$q = "SELECT * FROM videofiles WHERE folder='" . $sect . "' ORDER BY abs(vidname)";

And now it sorts naturally. Works great!


Figured I'd put the solution I found on here in case it helps anyone else.


Thanks for taking a look.


For more on abs take a look here: http://php.net/manual/en/function.abs.php

Edited by Nightasy
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.