Forum Moderators: coopster
I know this is probably a bit of a newbie question, but I can't figure it out.
I have some code that output a list of numbers (10 in this case). The problem is that they are coming out in a 1,10,2,3,4,5,6,7,8,9 fashion instead of 1,2,3,4,5,6,7,8,9,10. What do I do to fix this? Here is my code snipet:
//*****Choose another session.
// generate and execute query
// AND type = 'Handout'
$query = "SELECT * FROM level_session WHERE date <= '$session_current' ORDER BY session ASC";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
// if records present
if (mysql_num_rows($result) > 0) {
?>
<p>Please choose a session to view:<br/>
<form method="post" action="index.php">
<input type="hidden" name="user" value="<? echo $user;?>">
<input type="hidden" name="pw" value="<? echo $pw;?>">
<select name="session_id">
<?
// iterate through resultset
// print title with links to edit and delete scripts
while($row = mysql_fetch_object($result))
{
?>
<option value="<? echo $row->session;?>">Session <? echo $row->session;?></option>
<?
}
?>
</select>
<input type="submit" value="View Session">
</form>
<?
}
// if no records present
// display message
else
{
?>
<p>
<font size="-1">That session could not be located in our database.</font>
<?
}
Thanks for your help!
I don't use MySql, so I'm not sure if it can sort after casting, but if it can then you need to cast your column to an integer type and sort by it.
something like:
... order by my_column_name::int;
or
... order by (int)my_column_name;
Check the MySql docs on how to cast types.
Is ading another ORDER command to the sql query going to work for scalability (more sessions may be added later...)
You could either ALTER the column to type numeric, or you could use a conversion mechanism when retrieving the data. MySQL automatically converts numbers to strings as necessary, and vice-versa.
CAST()would be nice, and is preferable, but it is unavailable before MySQL 4.0.2.
You may have to use something else to force a numeric column type, such as something like ABS perhaps:
$query = "SELECT * FROM level_session WHERE date <= '$session_current' ORDER BY ABS(session)";
...or better yet, simply let MySQL do the conversion for you...
$query = "SELECT * FROM level_session WHERE date <= '$session_current' ORDER BY session * 1";
Otherwise, you can just retrieve the columns and use PHP's natcasesort [php.net] function.
Personally, if the session column is going to be numeric, I would ALTER the table and make the column of numeric type. Otherwise, I would use the multiplication CAST-workaround noted above.
ORDER BY CAST(fieldname as int)
Thanks again!
Erik
Aren't the numbers integers (they are in the example, but I don't know if that's true for all cases)?
As long as the largest integer does not exceed 4294967295 you can use an unsigned integer column. For values larger than that, I guess you would need to use a varchar column and then type cast.
Integer cols would take up less disk space and I would guess that searches and indexing would be much faster.
[edit]I was over-generalizing a bit. I should have said that an integer col would take up more space than a single or double digit num in a varchar, the same as a three digit num, and less than anything larger. SMALLINT, however, would take the same space as a single-digit number in a varchar (2 bytes) and would allow values up to 65535.
[/edit]
Tom
[edited by: ergophobe at 5:21 pm (utc) on April 15, 2004]
[dev.mysql.com...]
