asked on

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

Kendor

what about the real end of the query?
... $section3 ) ORDER BY projects.project_position"; (add also ASC or DESC...)
doesn't this work?

Sharath S

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

sany101

ASKER

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

sany101

ASKER

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

sany101

ASKER

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?

Kendor

is it a CHAR column? then you probably want to change it to INT?

Sharath S

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

HainKurt

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

sany101

ASKER

Yeah it was originally but I then changed it to an INT and still getting the same result?

Sharath S

Did you try my last query (casting to INT)?

HainKurt

try

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

sany101

ASKER

@ Sharath_123 yes I get syntax error message..... for the right syntax to use near 'int)' at line 18

sany101

ASKER

PS yes the project position values are all numerical

Sharath S

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

HainKurt

did you try 35131678?

just add this line before running the query...

sany101

ASKER

@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

Sharath S

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

sany101

ASKER

@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

sany101

ASKER

@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

HainKurt

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

HainKurt

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

Sharath S

What is your MySQL version?

sany101

ASKER

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

sany101

ASKER

Here you go MySQL.jpg

sany101

ASKER

HainKurt, Just tried again in case and can confirm it definitely doesn't work

Sharath S

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

HainKurt

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...

sany101

ASKER

Tried it directly in PHPMyAdmin and got following:

error.jpg

HainKurt

use

"signed" or "unsigned" instead of "int"

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sany101

ASKER

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

sany101

ASKER

Wow, thanks for bearing with me guys now works like a charm.