• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

ORDER by, for this many to many query

I've been getting help with the following query and PHP and completely stumped on how to add a working order filter to it.

I would like the result of $sql to be ordered by "projects.project_position" which is in the line
$sql = "SELECT DISTINCT projects.project_synopsis, projects.project_name, projects.project_short_name, projects.project_position
FROM projects

Open in new window

but can't find the right place for it.


    // FIRST PART
if (!empty($_GET["cat"]))
{
    $arr = explode('|', $_GET["cat"]);
    $section1 = 'WHERE (';
    foreach ($arr as $x)
    {
        $section1 .= " c.category_name = '$x' OR";
    }
    $section1 = rtrim($section1, ' OR');

    // SECOND PART
$arr = explode('|', $_GET["cat"]);
    $section2 = 'WHERE NOT';
    foreach ($arr as $x)
    {
        $section2 .= " c.category_name = '$x' AND NOT";
    }
    $section2 = rtrim($section2, ' AND NOT');
	
	// THIRD PART
$arr = explode('|', $_GET["cat"]);
    $section3 = 'WHERE';
    foreach ($arr as $x)
    {
        $section3 .= " c.category_name = '$x' OR";
    }
    $section3 = rtrim($section3, ' OR');
}
else {$section1 = "WHERE ( c.category_name = 'All_Project'";
$section2 = "WHERE NOT c.category_name = 'All_Project'";
$section3 = "WHERE c.category_name = 'All_Project'";
}


    // THE QUERY
    $sql = "SELECT DISTINCT projects.project_synopsis, projects.project_name, projects.project_short_name, projects.project_position
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section1 ) AND NOT projects.project_visibility =0
UNION
SELECT DISTINCT projects.project_synopsis, projects.project_name, concat( projects.project_short_name, '_grey' )
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id 
$section2 AND NOT projects.project_visibility =0
AND projects.project_id NOT
IN (
SELECT DISTINCT projects.project_id
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section3 )";

Open in new window

0
sany101
Asked:
sany101
  • 15
  • 8
  • 8
  • +1
2 Solutions
 
KendorCommented:
what about the real end of the query?
... $section3 ) ORDER BY projects.project_position"; (add also ASC or DESC...)
doesn't this work?
0
 
SharathData EngineerCommented:
try this sql.
$sql = "SELECT * FROM (SELECT projects.project_synopsis, projects.project_name, projects.project_short_name, projects.project_position
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section1 ) AND NOT projects.project_visibility =0
UNION
SELECT projects.project_synopsis, projects.project_name, concat( projects.project_short_name, '_grey' )
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id 
$section2 AND NOT projects.project_visibility =0
AND projects.project_id NOT
IN (
SELECT DISTINCT projects.project_id
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section3 )) AS t1 ORDER BY project_position";

Open in new window

0
 
sany101Author Commented:
Sorry there I missed out an update to the query:

    $sql = "SELECT DISTINCT projects.project_synopsis, projects.project_name, projects.project_short_name, projects.project_position
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section1 ) AND NOT projects.project_visibility =0
UNION
SELECT DISTINCT projects.project_synopsis, projects.project_name, projects.project_position, concat( projects.project_short_name, '_grey' )
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id 
$section2 AND NOT projects.project_visibility =0
AND projects.project_id NOT
IN (
SELECT DISTINCT projects.project_id
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section3 ) ";

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sany101Author Commented:
Hi Kendor, no that gives me the following error: Unknown column 'projects.project_position' in 'order clause'
0
 
sany101Author Commented:
Hi Sharath_123, yep that works a treat however I get entries with 11, 12, 13 etc. showing before 2,3,4 etc. if I order it by that row directly in PHP My Admin it does display in the right order any ideas?
0
 
KendorCommented:
is it a CHAR column? then you probably want to change it to INT?
0
 
SharathData EngineerCommented:
Do you have non-numerical values project_posiiton? If not, you can convert that value to int and then order on those values.
$sql = "SELECT * FROM (SELECT projects.project_synopsis, projects.project_name, projects.project_short_name, projects.project_position
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section1 ) AND NOT projects.project_visibility =0
UNION
SELECT projects.project_synopsis, projects.project_name, concat( projects.project_short_name, '_grey' )
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id 
$section2 AND NOT projects.project_visibility =0
AND projects.project_id NOT
IN (
SELECT DISTINCT projects.project_id
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section3 )) AS t1 ORDER BY cast(project_position as int)";

Open in new window

0
 
HainKurtSr. System AnalystCommented:
just add this before running your query

$sql = "select * from ("  + $sql + ") as x order by project_position";

is it . instead of +???

$sql = "select * from (".$sql.") as x order by project_position";
0
 
sany101Author Commented:
Yeah it was originally but I then changed it to an INT and still getting the same result?
0
 
SharathData EngineerCommented:
Did you try my last query (casting to INT)?
0
 
HainKurtSr. System AnalystCommented:
try

$sql = "select * from (".$sql.") as x order by cast(project_position as int)";
0
 
sany101Author Commented:
@ Sharath_123 yes I get syntax error message..... for the right syntax to use near 'int)' at line 18
0
 
sany101Author Commented:
PS yes the project position values are all numerical
0
 
SharathData EngineerCommented:
Thats starnge. Can you try this?
    $sql = "SELECT * FROM (
    SELECT projects.project_synopsis, projects.project_name, projects.project_short_name, cast(projects.project_position as int) project_position
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section1 ) AND NOT projects.project_visibility =0
UNION
SELECT projects.project_synopsis, projects.project_name, cast(projects.project_position as int) project_position, concat( projects.project_short_name, '_grey' )
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id 
$section2 AND NOT projects.project_visibility =0
AND projects.project_id NOT
IN (
SELECT projects.project_id
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section3 )) AS t1 ORDER BY  project_position";

Open in new window

0
 
HainKurtSr. System AnalystCommented:
did you try 35131678?

just add this line before running the query...
0
 
sany101Author Commented:
@Sharath_123 I get the following with your last comment:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int) project_position FROM projects INNER JOIN p_c ON p_c.project_id = project' at line 2

Note I had changed your last query by adding projects.project_position in line 7 should this altered in the new one too?


Last working query:

    $sql = "SELECT * FROM (SELECT projects.project_synopsis, projects.project_name, projects.project_short_name, projects.project_position
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section1 ) AND NOT projects.project_visibility =0
UNION
SELECT projects.project_synopsis, projects.project_name, projects.project_position, concat( projects.project_short_name, '_grey' )
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id 
$section2 AND NOT projects.project_visibility =0
AND projects.project_id NOT
IN (
SELECT DISTINCT projects.project_id
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section3 )) AS t1 ORDER BY project_position";

Open in new window

0
 
SharathData EngineerCommented:
can you try this?
    $sql = "SELECT * FROM (SELECT projects.project_synopsis, projects.project_name, projects.project_short_name, projects.project_position
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section1 ) AND NOT projects.project_visibility =0
UNION
SELECT projects.project_synopsis, projects.project_name, projects.project_position, concat( projects.project_short_name, '_grey' )
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id 
$section2 AND NOT projects.project_visibility =0
AND projects.project_id NOT
IN (
SELECT DISTINCT projects.project_id
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section3 )) AS t1 ORDER BY cast(project_position as integer)";

Open in new window

0
 
sany101Author Commented:
@HainKurt  yes I tried it did you mean like this:


$sql = "select * from (SELECT projects.project_synopsis, projects.project_name, projects.project_short_name, projects.project_position
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section1 ) AND NOT projects.project_visibility =0
UNION
SELECT projects.project_synopsis, projects.project_name, projects.project_position, concat( projects.project_short_name, '_grey' )
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id 
$section2 AND NOT projects.project_visibility =0
AND projects.project_id NOT
IN (
SELECT DISTINCT projects.project_id
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section3 )) as x order by cast(project_position as int)";

Open in new window


If so I get error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)' at line 18

Otherwise let me know where I'm getting it wrong
0
 
sany101Author Commented:
@Sharath_123 copy pasted your code and:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'integer)' at line 18

I've attached a grab of the projects table too in case Projects.jpg
0
 
HainKurtSr. System AnalystCommented:
what I say is do not change any code, just add this at the end  

$sql = "select * from (".$sql.") as x order by cast(project_position as int)";


to Line 55 on your original post
0
 
HainKurtSr. System AnalystCommented:
if your orinal sql works fine then adding this should run too, without any error



// THE QUERY
$sql = "SELECT DISTINCT ...
        ...
        $section3 )"; 
$sql = "select * from (".$sql.") as x order by cast(project_position as int)";

Open in new window

0
 
SharathData EngineerCommented:
What is your MySQL version?
0
 
sany101Author Commented:
Thanks HainKurt, Sorry miss understood.

just tried it with the original code I posted but get error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)' at line 18
0
 
sany101Author Commented:
Here you go MySQL.jpg
0
 
sany101Author Commented:
HainKurt, Just tried again in case and can confirm it definitely doesn't work
0
 
SharathData EngineerCommented:
Can you simply run this and let us know if you still get error?
$sql = "SELECT cast(project_position as int) project_position
FROM projects";

Open in new window

0
 
HainKurtSr. System AnalystCommented:
sorry about that, just to understand it correct...

you add this

$sql = "select * from (".$sql.") as x order by cast(project_position as int)";

it gives error, you remove it, it runs with wrong order!

are you saying this? or are you saying with or without it gives error... if this is the case you should check your logic... when it does not run, post the sql with the latest code you have...
0
 
sany101Author Commented:
Tried it directly in PHPMyAdmin and got following:


error.jpg
0
 
HainKurtSr. System AnalystCommented:
use

"signed" or "unsigned" instead of "int"
0
 
HainKurtSr. System AnalystCommented:
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast

CAST(expr AS type)

The type can be one of the following values:

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
0
 
SharathData EngineerCommented:
yes, used signed keyword as HainKurt mentioned.
    $sql = "SELECT * FROM (SELECT projects.project_synopsis, projects.project_name, projects.project_short_name, projects.project_position
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section1 ) AND NOT projects.project_visibility =0
UNION
SELECT projects.project_synopsis, projects.project_name, projects.project_position, concat( projects.project_short_name, '_grey' )
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id 
$section2 AND NOT projects.project_visibility =0
AND projects.project_id NOT
IN (
SELECT DISTINCT projects.project_id
FROM projects
INNER JOIN p_c ON p_c.project_id = projects.project_id
INNER JOIN c ON c.category_id = p_c.category_id
$section3 )) AS t1 ORDER BY cast(project_position as signed integer)";

Open in new window

0
 
sany101Author Commented:
Just so that it's absolutely clear
"it gives error, you remove it, it runs with wrong order!" yes that's correct.

See attached which is directly in PHPMyAdmin and top works bottom doesn't.


error-02.jpg
0
 
sany101Author Commented:
Wow, thanks for bearing with me guys now works like a charm.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 15
  • 8
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now