PHP Select with Join - Order By problem.

[arleym]

I am displaying some records using a JOIN, the following Select statement works like a charm:

 

$result = "SELECT regjobs.jobtitle, applyjob.empnum, applyjob.startdate, applyjob.username, regjobs.jobid, regjobs.district, regjobs.timetype	 ". 
"FROM applyjob, regjobs ".
"WHERE (regjobs.jobid = applyjob.jobtitle)";

 

Ideally I want the client to be able to choose how to Order the results... but none of the following attempts work at all!

 

$result = "SELECT regjobs.jobtitle, applyjob.empnum, applyjob.startdate, applyjob.username, regjobs.jobid, regjobs.district, regjobs.timetype, regjobs.id	 ". 
"FROM applyjob, regjobs ".
"WHERE (regjobs.jobid = applyjob.jobtitle) ".
[color=orange]//"ORDER BY " .$cat.);[/color]
[color=orange]//"ORDER BY timetype");[/color]
"ORDER BY regjobs.timetype");

 

Any ideas how I can dynamically Order this Select?

 

 

 

 

 

[themistral]

You don't state how the user select the order?

 

You will need to use a form with a select box probably, then use the posted variable from the form in your select statement.

[arleym]

ORDER BY " .$cat.); is going to be my ORDER BY that will dynamically let the user pick. It doesn't work... even hard coding isn't working. I wonder if special syntax is needed in a JOIN like this? It's my first JOIN double table query.

[themistral]

Where does $cat get it's value from?

Can you post your code where you get the user input and assign it to $cat?

[arleym]

Sure, there will be a line about the query results like

 

Sort By Order | Job Number | Job Title | Start Date | Username | District | Work Load

 

<p>Sort By                                                       
<a href="jobstatus2.php?cat=id">Order</a> | 
<a href="jobstatus2.php?cat=jobid">Job Number</a> | 
<a href="jobstatus2.php?cat=jobtitle">Job Title</a> | 
<a href="jobstatus2.php?cat=startdate">Start Date</a> | 
<a href="jobstatus2.php?cat=username">Username</a> | 
<a href="jobstatus2.php?cat=district">District</a> | 
<a href="jobstatus2.php?cat=timetype">Work Load</a></p>

 

 

But these don't work. I wanted to see if I could make it work by hardcoding the column name like applyjob.timetype (aka Work Load), but even that simple thing doesn't work.

 

I have also tried ASC and DESC, but they make no difference.

 

The screen just renders as white - no code in it.

 

Poor me.

 

[arleym]

oh, and of course between those links above and the Select I have $cat = $_GET['cat'];

[craygo]

What happens if they don't hit anything??

 

<?php

if(isset($_GET['cat'])){
$cat = $_GET['cat'];
} else {
$cat = "timetype";
}

$sql = "SELECT regjobs.jobtitle, applyjob.empnum, applyjob.startdate, applyjob.username, regjobs.jobid, regjobs.district, regjobs.timetype
          FROM applyjob JOIN regjobs ON
          applyjob.jobtitle = regjobs.jobid
          ORDER BY $cat";
?>

 

Ray

[cooldude832]

now I don't know everything in php but since when does php have select with join and order by as functions shouldn't this be a mysql problem?

[craygo]

He's is using php to create his sql statement. Those are not php functions. you don't have to keep your sql statement all on the same line. As long as you don't close the double quotes, php ignores the whitespaces and line breaks. Just formatted like that for organization. Doesn't it look much better!?!?!

 

 

[arleym]

Craygo, that is some great code, I put it in and the page at least renders now... I do get an error:

 

You have an error in your SQL syntax near 'ON applyjob.jobtitle = regjobs.jobid ORDER BY id' at line 1

 

I hope to figure it out and will post the fix when I find it...

 

I have never had so much help on any forum before!

[arleym]

Brilliant, I got it working... the thing missing was INNER JOIN. Thanks all,

 

$result = "SELECT applyjob.jobtitle, regjobs.jobtitle, applyjob.empnum, applyjob.startdate, applyjob.username, regjobs.jobid, regjobs.district, regjobs.timetype, regjobs.id 
FROM applyjob INNER JOIN regjobs 
ON applyjob.jobtitle = regjobs.jobid 
ORDER BY $cat";