I have a SQL ordering problem

ppowell

Using this SQL statement:

SELECT id, placement_name FROM placement ORDER BY upper(placement_name) asc, upper(right(placement_name, 2))

produces results like these:

Placement #1 Edit Delete
Placement #10 Edit Delete
Placement #2

This is totally not right, it should be Placement #1, #2, ..., #10, etc. I thought my SQL statement would accomplish this, but I was wrong. What do I do?

Thanx
Phil

cheese

Hi ppowell,

Aren't #1, #2, #10 etc text......

So if you try to order them they will come out

#1
#10
#2

as this is the correct text order. The numbers would actually need to be numbers in an INT field or similar.

Regards,

Neil.

ppowell

That's just it, how do I manage to extract an integer form from text using MySQL? I have to be able to sort using MySQL I would think without having to resort to PHP pattern matching and reordering the resultset.

Phil

cheese

Hi again,

OK Maybe I am missing something here......

You appear to have a column in a database that contains;

Placement #1 Edit Delete
Placement #10 Edit Delete
Placement #2

Why not have a table structure something like this

PlacementNumber (INT)
Editable (VARCHAR) enter Y or N
Delete (VARCHAR) enter Y or N


define (NL,"\n");  // makes the page think that .NL is /n for a new line

$query = mysql_query("SELECT * FROM yourtable ORDER BY PlacementNumber DESC ");

print "<table>".NL;
while ($row = mysql_fetch_array($query, MYSQL_NUM))
{
print "<tr>".NL;
print "<td>Placement # " . $row[0] . "</td>".NL;
print "<td>";
if ($row[1] == 'Y')
{
print "<a href='youreditpage.php?number=". $row[0]."'>Edit</a>;
}
else
{
print "&nbsp;";
}
print "</td>".NL;
print "<td>";
if ($row[2] == 'Y')
{
print "<a href='yourdeletepage.php?number=". $row[0]."'>Delete</a>}
else
{
print "&nbsp;";
}
print "</td>".NL;
print "</tr>".NL;
}
print "<table>".NL;

I know that my code is always crude, but I hope this is close to what you need.

I'm sure someone else will come up with another way of doing it

Cheers,

Neil.

ppowell

I'm sorry I wasn't more clear. This is your table, "placement"

id placement_name
1 Placement #2
2 Placement #10
3 Placement #1
4 Some other placement name I want to use

THAT is what I have to order but it must be ordered in logistical sequential order referencing placement_name.

Phil