Solved

ORDER by, for this many to many query

Posted on 2011-03-14
33
336 Views
Last Modified: 2012-05-11
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
Comment
Question by:sany101
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 8
  • 8
  • +1
33 Comments
 
LVL 5

Expert Comment

by:Kendor
ID: 35131383
what about the real end of the query?
... $section3 ) ORDER BY projects.project_position"; (add also ASC or DESC...)
doesn't this work?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35131403
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
 

Author Comment

by:sany101
ID: 35131455
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:sany101
ID: 35131475
Hi Kendor, no that gives me the following error: Unknown column 'projects.project_position' in 'order clause'
0
 

Author Comment

by:sany101
ID: 35131582
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
 
LVL 5

Expert Comment

by:Kendor
ID: 35131612
is it a CHAR column? then you probably want to change it to INT?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35131621
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
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35131630
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
 

Author Comment

by:sany101
ID: 35131653
Yeah it was originally but I then changed it to an INT and still getting the same result?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35131664
Did you try my last query (casting to INT)?
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35131678
try

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

Author Comment

by:sany101
ID: 35131740
@ Sharath_123 yes I get syntax error message..... for the right syntax to use near 'int)' at line 18
0
 

Author Comment

by:sany101
ID: 35131756
PS yes the project position values are all numerical
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35131852
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
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35131857
did you try 35131678?

just add this line before running the query...
0
 

Author Comment

by:sany101
ID: 35132007
@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
 
LVL 41

Expert Comment

by:Sharath
ID: 35132028
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
 

Author Comment

by:sany101
ID: 35132030
@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
 

Author Comment

by:sany101
ID: 35132112
@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
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35132136
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
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35132159
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35132164
What is your MySQL version?
0
 

Author Comment

by:sany101
ID: 35132167
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
 

Author Comment

by:sany101
ID: 35132270
Here you go MySQL.jpg
0
 

Author Comment

by:sany101
ID: 35132289
HainKurt, Just tried again in case and can confirm it definitely doesn't work
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35132302
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
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35132309
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
 

Author Comment

by:sany101
ID: 35132348
Tried it directly in PHPMyAdmin and got following:


error.jpg
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35132465
use

"signed" or "unsigned" instead of "int"
0
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 250 total points
ID: 35132472
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
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 35132501
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
 

Author Comment

by:sany101
ID: 35132527
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
 

Author Comment

by:sany101
ID: 35132559
Wow, thanks for bearing with me guys now works like a charm.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question