How to sort a list by date???

[rilana]

Is there anyone how could please give me a hint how I could sort this output by date?

 

<?

 

include_once "../conn.php";

 

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

 

$sch = array();

if (!empty($position))

{

$sch[] = "position like '%$_REQUEST[position]%'";

}

if (!empty($bereich))

{

$sch[] = "bereich like '%$_REQUEST[bereich]%'";

}

if (!empty($anstellung))

{

$sch[] = "anstellung like '%$_REQUEST[anstellung]%'";

}

if (!empty($region))

{

$sch[] = "region like '%$_REQUEST[region]%'";

}

 

 

 

 

if (!$ByPage) $ByPage=25; 

if (!$Start) $Start=0;

 

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><left> <font class=\"inhalt\">Ihre Suche ergab keinen Treffer.</left><br>";

exit;

}

elseif($rr == '1')

{

echo "<br><br><left> <font class=\"inhalt\">Ihre Suche ergab einen Treffer. </left><br>";

}

elseif($rr > '1')

{

echo "<br><br><left> <font class=\"inhalt\">Ihre Suche ergab $rr Treffer. </left><br>";

}

 

$col = "cococo";

 

echo "<br><table align=center width=500 cellspacing=0>

<tr bgcolor=#cccccc class=\"inhaltTitel\">

<td>Position </td><td width=80><font class=\"inhaltTitel\">Bereich </td><td width=80>Anstellung </td><td width=50><font class=\"inhaltTitel\">Datum</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]";

 

$today = date("d-m-Y");

 

$ERdate = "$as[ERday]"."-"."$as[ERmonth]"."-"."$as[ERyear]";

 

 

if($as[erfasst] == "fake")

    {

    $colin = "$today";

    }

  else

    {

    $colin = "$ERdate";

    }

 

$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 = "F2F2F2";

    }

        else

{

$col = "cococo";

}

echo "<tr bgcolor=\"$col\" style=\"font-size:12\">

<td class=inhalt><a class=TN href=\"JobInfo.php?job_id=$as[job_id]\" class=bmnu>  $as[position] </a></td><td class=inhalt> $as[bereich] </td><td align=left class=inhalt> $as[anstellung] </td> <td align=left class=inhalt> $colin  </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=500 align=center>

<tr><br></tr>

<tr>";

 

 

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

{

 

}

 

if ( $Start > 0 )

{

$nom1 = $Start - $ByPage;

echo "<td align=left class=inhalt><a class=TN href=\"JobSearch3.php?sm=$sm&position=$position&region=$region&bereich=$bereich&anstellung=$anstellung&datum=$datum&target_company=$target_company&relocate=$relocate&country=$country&city=$city&kw=$kw&Start=$nom1\">zurück</a></td>";

}

 

if ($rr2 > $Start + $ByPage  || ($Start == 0 && $rr2 > $ByPage))

{

$nom = $Start + $ByPage;

echo "<td align=right class=inhalt><a class=TN href=\"JobSearch3.php?sm=$sm&position=$position&region=$region&bereich=$bereich&anstellung=$anstellung&datum=$datum&target_company=$target_company&relocate=$relocate&country=$country&city=$city&kw=$kw&Start=$nom\">nächste Seite</a></td>";

}

 

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

 

 

?>

[roopurt18]

If you're not going to bother using code tags then I'm not going to bother reading your code.

 

But, my browser's search function would indicate you don't have a single ORDER BY in your queries.

[rilana]

Hi roopurt18

 

I read that you are from southern cali..... dame I wish I would be there now! I love SD!!!!

Anyway I bought that code a long time ago personaly I do have a lot of difficulties to learn coding! I dont evan understand what you wrote....

My problem is that I bout this package of a joblisting tool for http://www.smartpersonal.ch/. Since I bought it I have been making some changes.

But now this guy wants me to sort his job listings by date and I dont evan know where to start... I asume somewhere arround here:

 

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

{

 

}

 

if ( $Start > 0 )

{

$nom1 = $Start - $ByPage;

echo "<td align=left class=inhalt><a class=TN href=\"JobSearch3.php?sm=$sm&position=$position&region=$region&bereich=$bereich&anstellung=$anstellung&datum=$datum&target_company=$target_company&relocate=$relocate&country=$country&city=$city&kw=$kw&Start=$nom1\">zurück</a></td>";

}

 

if ($rr2 > $Start + $ByPage  || ($Start == 0 && $rr2 > $ByPage))

{

$nom = $Start + $ByPage;

echo "<td align=right class=inhalt><a class=TN href=\"JobSearch3.php?sm=$sm&position=$position&region=$region&bereich=$bereich&anstellung=$anstellung&datum=$datum&target_company=$target_company&relocate=$relocate&country=$country&city=$city&kw=$kw&Start=$nom\">nächste Seite</a></td>";

}

 

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

 

but I have no clue, I am looking for a hint so I can figure it out somehow.

 

Thanks anyway, and enjoy the warm weather I am freezing my a...... off here in switzerland!

[roopurt18]

The person you are changing the code for, do they wish to have click-able table headers that will cause the table to be sorted by that column in ascending or descending order?

[rilana]

the newest job should be on top of the list, as older the date as more towerd the end should the job be.

[roopurt18]

When selecting data from the database, you can tell MySQL to order it for you with ORDER BY.

 

Try changing these lines:

to:

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):"");
}

 

to:

if($sm == 'or')
{
$qs = "select * from job_post ".(($sch)?"where ".join(" or ", $sch):"")." order by EXyear DESC, EXmonth DESC, EXday DESC limit $Start,$ByPage";
$qss = "select * from job_post ".(($sch)?"where ".join(" or ", $sch):"") . " order by EXyear DESC, EXmonth DESC, EXday DESC";
}
elseif($sm == 'and')
{
$qs = "select * from job_post ".(($sch)?"where ".join(" and ", $sch):"")." order by EXyear DESC, EXmonth DESC, EXday DESC limit $Start,$ByPage";
$qss = "select * from job_post ".(($sch)?"where ".join(" and ", $sch):"") . " order by EXyear DESC, EXmonth DESC, EXday DESC";
}

 

 

[rilana]

Hi again, thanks that sorting helped a lot! Its almost perfect now!

I had to change it to another variable, to ERyear etc. because this was the one I created a while ago to write into the database. But also created something that gives me a problem sorting now. This guy said that some jobs are allways available but he didnt wanted to give the impression that they are old. So I created a box for him where he can make the choice to set the date to the actual creation date or to always today. It look like that:

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]";

 

$today = date("d-m-Y");

 

$ERdate = "$as[ERday]"."-"."$as[ERmonth]"."-"."$as[ERyear]";

 

 

if($as[erfasst] == "fake")

    {

    $colin = "$today";

    }

  else

    {

    $colin = "$ERdate";

    }

 

$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 = "F2F2F2";

    }

        else

{

$col = "cococo";

}

echo "<tr bgcolor=\"$col\" style=\"font-size:12\">

<td class=inhalt><a class=TN href=\"JobInfo.php?job_id=$as[job_id]\" class=bmnu>  $as[position] </a></td><td class=inhalt> $as[bereich] </td><td align=left class=inhalt> $as[anstellung] </td> <td align=left class=inhalt> $colin  </td>

        </tr>";

}

 

So now I have the Problem that the dates work fine but of course it puts that fake created date that is allways today in order of the creation day.... I tryed to figure it out and tryed to sort it by ERdate but that does not work because I dont have ERdate in the database it's created in the php document.

 

I hope you can figure out my confusing writing :-) thank you verry much for your help!

[roopurt18]

You are determining which date (the DB date or today's date) to use based on another database field: erfasst.

 

You can just put that field in the beginning of the order by clauses:

if($sm == 'or')
{
$qs = "select * from job_post ".(($sch)?"where ".join(" or ", $sch):"")." order by erfasst, EXyear DESC, EXmonth DESC, EXday DESC limit $Start,$ByPage";
$qss = "select * from job_post ".(($sch)?"where ".join(" or ", $sch):"") . " order by erfasst, EXyear DESC, EXmonth DESC, EXday DESC";
}
elseif($sm == 'and')
{
$qs = "select * from job_post ".(($sch)?"where ".join(" and ", $sch):"")." order by erfasst, EXyear DESC, EXmonth DESC, EXday DESC limit $Start,$ByPage";
$qss = "select * from job_post ".(($sch)?"where ".join(" and ", $sch):"") . " order by erfasst, EXyear DESC, EXmonth DESC, EXday DESC";
}

 

If that doesn't work, replace erfasst with erfasst DESC.

[rilana]

thanks, it does put it in a different order, but it still isn't correct. erfasst only determins fake or real. Meaning it's only a variable which is set to fake or real in the backend. And if it's set to fake, thenk it takes allways todays date. So I would have to find a way to say all the erfasst that are set to fake should be in the beginning of the list. Is there a way I could do this? I guess I would have an If thing or so...???

 

Thanks a lot for your help, I apreciate it!

[roopurt18]

What are the possible values for `erfasst`?

 

If the only possible values are say "fake" and "real", using ORDER BY `erfasst` will put the fake ones before the real ones.  Did you see this part of my post:

If that doesn't work, replace erfasst with erfasst DESC.

[rilana]

OK Now I think I see the light! Without DESC works better, what thrue me off was the first 5 or so where totally out of order, but now I found out it's because they are not set to fake or real! Thank you so much I wished I could be thinking like that!!!!! do you know anything about CSS? I got a CSS Problem and I think I can only solve it by using javascript or so....

 

Hey Thank you verry much for your help!

[roopurt18]

What's the CSS problem?

[rilana]

I am trying to do a flexible layout with css.

http://www.rilana.biz/defacto/english/index.php

The Problem is that the buttom Layer is set to bottom: 0px;

but when I start resizing the screen, the one layer collapses with the

text layer and that looks ugly. It's two layer on top of each other.

I need to do something to eighter not let the people resize the window to small

or I need to script something that says if the screen gets to small, hide

that buttom layer.

 

I have been looking for a tutorial but didnt find anything that could help me.

[roopurt18]

Eh that's more than I can help with at the moment.  Your best shot is to ask in the CSS forums.

 

The other thing you can do is use FireFox with the web developer and firebug extensions.  Firebug will let you inspect page elements and the attached styles while web developer will let you edit your CSS on the fly.  It's easier than editing the file, reloading the page, etc.

[rilana]

thanks for the tip! take care and thanks again!

[rilana]

http://www.phpfreaks.com/forums/index.php/topic,118755.0.html