Sorting the results of a query issue

[Nightasy]

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.

<?php

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>
		<div id="tabledatatitle1">
			<h4>' . preg_replace($patterns, $replacements, $row['vidname']) . '</h4>
		</div>	
		<div id="tabledatadesc1">
			' . $row['viddesc'] . '
		</div>
		</td>
		</tr>';
		}

		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:
mysqli_close($connect);


?>

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,

Nightasy

Edited October 2, 2013 by Nightasy

[sKunKbad]

see http://stackoverflow.com/questions/17354217/natural-sorting-sql-order-by

 

the sql fiddle seems to work

[Nightasy]

see http://stackoverflow.com/questions/17354217/natural-sorting-sql-order-by

 

the sql fiddle seems to work

 

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)
        ,vidname";

 It's giving me the same output though.

[Nightasy]

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 October 2, 2013 by Nightasy