Display results in ascending order?

[careerbarn]

I have the following code, which queries a mysql database and displays all results random, I would like the results to be displayed in ascending order but I'm new to this and not sure how to proceed. Thanks for any help.

<?

$day = date(d);
$month = date(m);
$year = date(Y);

$del = "delete from job_post where EXday = \"$day\" and EXmonth = \"$month\" and EXyear = \"$year\" ";
$rdel = mysql_query($del) or die(mysql_error());

if(!empty($_POST))
{
extract($_POST);
}

if(!empty($_GET))
{
extract($_GET);
}

$sch = array();
if (!empty($position))
{
$sch[] = "position like '%$position%'";
}
if (!empty($country))
{
$sch[] = "CompanyCountry = \"$country\" ";
}
if (!empty($state))
{
$sch[] = "CompanyState = '$state' ";
}
if (!empty($JobCategory))
{
$sch[] = "JobCategory like '%$JobCategory%' ";
}
if (!empty($careerlevel))
{
$sch[] = "j_target = '$careerlevel'";
}
if (!empty($kw))
{
$sch[] = "description like '%$kw%'";
}


if (!$ByPage) $ByPage=25;
if (!$Start) $Start=0;

if(empty($sm))
{
$sm = "and";
}

if($sm == 'or')
{
$qs = "select * from job_post ".(($sch)?"where ".join(" or ", $sch):"")." limit $Start,$ByPage";
$qss = "select * from job_post ".(($sch)?"where ".join(" or ", $sch):"");
}
elseif($sm == 'and')
{
$qs = "select * from job_post ".(($sch)?"where ".join(" and ", $sch):"")." limit $Start,$ByPage";
$qss = "select * from job_post ".(($sch)?"where ".join(" and ", $sch):"");
}
$rqs = mysql_query($qs) or die(mysql_error());
$rqss = mysql_query($qss) or die(mysql_error());

$rr = mysql_num_rows($rqss);

if($rr == '0')
{
echo "<br><br><center><span class=TNA>No results found.</span></center>";
include_once('../footer.php');
exit;
}
elseif($rr == '1')
{
echo "<br><center><span class=HeaderClass>Your search returned one result.</span></center>";
}
elseif($rr > '1')
{
echo "<br><center><span class=HeaderClass>Your search returned $rr results.</span></center>";
}

$col = "cococo";

echo "<table align=center width=460 cellspacing=0 class=BlackText>
<tr class=TableHead>
<td width=220>Position </td><td width=190>Job Category </td><td width=60 align=center>Expire in</td></tr>";

while($as = mysql_fetch_array($rqs))
{
//$ex13 = date('d', mktime(0,0,0, $as[EXmonth] - date(m), $as[EXday] - date(d), $as[EXyear] - date(Y)));

$day = date(d);
$month = date(m);
$year = date(Y);

$EXdate = "$as[EXyear]"."-"."$as[EXmonth]"."-"."$as[EXday]";
$dnes = "$year"."-"."$month"."-"."$day";

$qd = "select to_days('$EXdate') - to_days('$dnes')";
$rqd = mysql_query($qd) or die(mysql_error());
$ex13 = mysql_fetch_array($rqd);

if($col == "cococo")
{
$col = "dddddd";
}
else
{
$col = "cococo";
}
echo "<tr bgcolor=\"$col\">
<td><a class=BlackLink href=\"JobInfo.php?job_id=$as[job_id]\"> $as[position] </a></td><td> $as[JobCategory] </td><td align=center><b> $ex13[0] days</b></td>
</tr>";
}


if($sm == 'or')
{
$qs2 = "select * from job_post ".(($sch)?"where ".join(" or ", $sch):"");
}
elseif($sm == 'and')
{
$qs2 = "select * from job_post ".(($sch)?"where ".join(" and ", $sch):"");
}
$rqs2 = mysql_query($qs2) or die(mysql_error());
$rr2 = mysql_num_rows($rqs2);

echo "</table>";

echo "<table width=470 align=center><tr>";


if ($rr2 <= $ByPage && $Start == '0')
{

}

if ( $Start > 0 )
{
$nom1 = $Start - $ByPage;
echo "<td align=left><a class=TNA href=\"JobSearch3.php?sm=$sm&position=$position&CompanyCountry=$CompanyCountry&CompanyState=$CompanyState&JobCategory=$JobCategory&careerlevel=$careerlevel&target_company=$target_company&relocate=$relocate&country=$country&city=$city&kw=$kw&Start=$nom1\">previous</a></td>";
}

if ($rr2 > $Start + $ByPage || ($Start == 0 && $rr2 > $ByPage))
{
$nom = $Start + $ByPage;
echo "<td align=right><a class=TNA href=\"JobSearch3.php?sm=$sm&position=$position&CompanyCountry=$CompanyCountry&CompanyState=$CompanyState&JobCategory=$JobCategory&careerlevel=$careerlevel&target_company=$target_company&relocate=$relocate&country=$country&city=$city&kw=$kw&Start=$nom\">next</a></td>";
}

echo "</tr></table>";


?>

[GaryS]

add "ORDER BY the_date" (or what ever you want to order by) to the end of your query.

[The Little Guy]

$del = "delete from job_post where EXday = '$day' and EXmonth = '$month' and EXyear = '$year' ORDER BY EXyear ASC";

[GaryS]

Not sure why you'd ORDER a delete... unless there's intended to be a LIMIT?

[The Little Guy]

$example = "SELECT * from job_post where EXday = '$day' and EXmonth = '$month' and EXyear = '$year' ORDER BY EXyear ASC";

How about that

[GaryS]

Beautiful :)

[careerbarn]

Thank you for the quick responses. I tried both pieces of code above and neither worked. It still displayed the same as before. Do I need to make a modification futher down the code?

Thanks again

[GaryS]

Could you echo the offending query/queries and post it/them back here. Thanks.

Could you also post the database field types of the field you are sorting by. Taa.

[The Little Guy]

could you please edit your fist post by putting the PHP inside [ PHP] code here [/PHP] PHP square braces? (with out the space).

2 reasons

1. It makes me need to scroll left and right when its not
2. It is easier to read

[careerbarn]

Sorry about the first post not being in the [ PHP] code here [/PHP]

[GaryS]

I think I can answer my own question: just noticed that you've enclosed EXyear (etc) values in quotes... which means (afaik) that the fields are "text" (varchar?) rather than "number" (e.g., int).

If the fields are text they will be sorted as text. To make them sort as numbers, use the "+0" trick:


ORDER BY (EXyear+0)

[careerbarn]

That didn't work. Here is the test page that calls the code above.

http://www.careerbarn.com/careerbarnpost/jobseekerstest/JobSearch.php

click on any category to see how the items are displayed (This is the page where I'm trying to get the sort)

Don't know if this helps any and thank you for taking the time to help.

[GaryS]

Hold on... we've all been asleep here. This is never going to work as things stand. You have two options:


swap your day/month/year fields for a date or (better) timestamp field that you can ORDER BY.

Grab the results, then sort them (in you PHP code) as a separate operation.


Sorry it took me so long to wake up to this.

[careerbarn]

I have tried serveral things with no good results. I'm not really sure what I need to change. Thanks for any help.

[GaryS]

Did you change the way the date is stored in the database? (Or do you plan to.) Think we need to know that in order to help you.