Solved

ORDER by, for this many to many query

Posted on 2011-03-14
33
324 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
  • 15
  • 8
  • 8
  • +1
33 Comments
 
LVL 5

Expert Comment

by:Kendor
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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
 

Author Comment

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

Author Comment

by:sany101
Comment Utility
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
Comment Utility
is it a CHAR column? then you probably want to change it to INT?
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
Yeah it was originally but I then changed it to an INT and still getting the same result?
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Did you try my last query (casting to INT)?
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
try

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

Author Comment

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

Author Comment

by:sany101
Comment Utility
PS yes the project position values are all numerical
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
did you try 35131678?

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

Author Comment

by:sany101
Comment Utility
@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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
@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
Comment Utility
@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 51

Expert Comment

by:HainKurt
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
What is your MySQL version?
0
 

Author Comment

by:sany101
Comment Utility
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
Comment Utility
Here you go MySQL.jpg
0
 

Author Comment

by:sany101
Comment Utility
HainKurt, Just tried again in case and can confirm it definitely doesn't work
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
Tried it directly in PHPMyAdmin and got following:


error.jpg
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
use

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

Accepted Solution

by:
HainKurt earned 250 total points
Comment Utility
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 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Wow, thanks for bearing with me guys now works like a charm.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now