PHP-Mysql: Sort by number in string

Today i had to solve one little bug in one web site. So i want to share it, may be it will be needed by you in the future.
I will explain it in example.
So, we have a table called “LessonParts” and one of its columns “Part” column contains elements as “Test 1”, “Quiz 2”, “Exercise 14”, “Test 3”, “Quiz 24” and etc. And let’s consider that they all are parts of lessons.(Lesson N contains [Test N, Quiz N, Exercise N…])
If you want to see it in action, you can use sample table.

DROP TABLE IF EXISTS `LessonParts`;
CREATE TABLE `LessonParts` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) NOT NULL,
  `Part` varchar(30) NOT NULL,
  `Content` text NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO `LessonParts` (`ID`, `Name`, `Part`, `Content`) VALUES
(1,	'Name 1_1',	'Exercise 1',	'Content here'),
(2,	'Name 1_2',	'Test 1',	'Content here'),
(3,	'Name 1_3',	'Quiz 1',	'Content here'),
(1,	'Name 2_1',	'Exercise 2',	'Content here'),
(2,	'Name 2_2',	'Test 2',	'Content here'),
(3,	'Name 2_3',	'Quiz 2',	'Content here'),
(1,	'Name 3_1',	'Exercise 3',	'Content here'),
(2,	'Name 3_2',	'Test 3',	'Content here'),
(3,	'Name 3_3',	'Quiz 3',	'Content here');

And now we need to get some sorted information about this table. For example:
1. We need to get maximum number of lessons.

$qnet=mysql_query("select CAST(SUBSTRING_INDEX(`Part`,' ',-1) AS UNSIGNED INTEGER) as numb from LessonParts ORDER BY numb desc limit 1");
$result=mysql_result($qnet,0,'numb');
echo $result;

2. We need to get lesson 4 parts for fetching it.

$qnet=mysql_query("SELECT Name,Part,Content,SUBSTRING_INDEX(Part,' ',-1) as alfa FROM LessonParts WHERE alfa=4  ;");
echo '<h3>Lesson 4</h3>';
while ($row=mysql_fetch_array($qnet))
  {
  echo '<p>'.$row[0].'</p>'.$row[2].'<br><br>';
  }
$result=mysql_result($qnet,0,'numb');

That’s all. Of course it is better to keep part number and part type not in one column(“Exercise 1”), it is better to create two columns and to keep it separated. But if no way, if the table already exists, you just have to get result, then our example will solve your problem.

2 comments on “PHP-Mysql: Sort by number in string

  1. string functions and type conversions in sql query are quite expensive so

    …in my next lesson I will show you how easy is it to add a new column and populate it with part number using just one update query 🙂

  2. PHP framework with a very impact developed for PHP developers who need a simple and fashionable program set to create full-featured web programs and i am also perform on codeIgniter.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.