Sorting problem

[paulorv]

I am working with a 3rd party open source project management app that makes use of PHP and MySQL (http://collabtive.o-dyn.de). The database layout for the projects is pretty simple; there is a table labeled 'projekte' for the project data such as name, project number, start and end dates. There is also a table labeled 'projekte_assigned' that's used to assign these projects to certain users, with the fields referencing the IDs of project and user tables.

 

There is a page that loads in the app that lists the active projects for the logged in user, but there is no sorting implemented. I would like to show this page sorted by project number (a field in the 'projekte' table) but have had little success in doing this with ORDER BY statement in the MySQL query. I have found the function for listing the projects by user, that I believe needs to be modified.

 

 

    /**
     * Lists all projects assigned to a member
     *
     * @param int $user Unique Membership Number
     * @param int $status Processing status of projects (1 = open Project)
     * @return array $myprojekte Projects of the member
     */
    function getMyProjects($user, $status = 1)
    {
        $user = mysql_real_escape_string($user);
        $status = mysql_real_escape_string($status);
        $user = (int) $user;
        $status = (int) $status;

        $myprojekte = array();
        $sel = mysql_query("SELECT projekt FROM projekte_assigned WHERE user = $user");

        while ($projs = mysql_fetch_row($sel))
        {
            $projekt = mysql_fetch_array(mysql_query("SELECT ID FROM projekte WHERE ID = $projs[0] AND status=$status"), MYSQL_ASSOC);
            if ($projekt)
            {
                $project = $this->getProject($projekt["ID"]);
                array_push($myprojekte, $project);
            }
        }

        if (!empty($myprojekte))
        {
            return $myprojekte;
        }
        else
        {
            return false;
        }
    }

 

I've attempted changing the second query to something like

 

$projekt = mysql_fetch_array(mysql_query("SELECT ID FROM projekte WHERE ID = $projs[0] AND status=$status ORDER BY projnum ASC"), MYSQL_ASSOC);

 

but this gives the same output. 'projnum' is the field from 'projekte' I would like the results to be sorted by. Thank you in advanced.

 

MySQL Server version: 5.0.67-0ubuntu6

 

[ignace]

$projekt = mysql_fetch_array(mysql_query("SELECT ID FROM projekte WHERE ID = $projs[0] AND status=$status ORDER BY projnum ASC"), MYSQL_ASSOC);

does not work because you pass a certain value and are sorting according to this. You should start your sorting once after the while loop.

 

Second it's dangerous to write:

 

$user = mysql_real_escape_string($user);
        $status = mysql_real_escape_string($status);
        $user = (int) $user;
        $status = (int) $status;

 

Because if there is no active connection to the database mysql_real_escape_string returns false and emits a warning meaning that both $user and $status will equal 0

 

Also not entirely sure but is this query:

 

SELECT ID
FROM projekte t1 JOIN projekte_assigned t2 ON t1.ID = t2.projekt
WHERE status = $status

 

Not the same as

 

$sel = mysql_query("SELECT projekt FROM projekte_assigned WHERE user = $user");

        while ($projs = mysql_fetch_row($sel))
        {
            $projekt = mysql_fetch_array(mysql_query("SELECT ID FROM projekte WHERE ID = $projs[0] AND status=$status"), MYSQL_ASSOC);

 

If this is the case then you can:

 

SELECT ID
FROM projekte t1 JOIN projekte_assigned t2 ON t1.ID = t2.projekt
WHERE status = $status
ORDER BY projnum ASC

[Mchl]

The change you've mad should be working fine. The problem is in PHP code.

 

Notice that this query is run in a loop (ugly solution) that fetches rows from "SELECT projekt FROM projekte_assigned WHERE user = $user" query.

 

I would do it this way:

 

function getMyProjects($user, $status = 1)
    {
        $user = (int) $user;
        $status = (int) $status;

        $myprojekte = array();

	$sel = mysql_query("SELECT p.ID FROM projekte AS p INNER JOIN projekte_assigned AS pa ON p.ID = pa.projekt WHERE pa.user = $user AND p.status = $status ORDER BY p.projnum");

	while ($projs = mysql_fetch_assoc($sel))
        {
            $project = $this->getProject($projs["ID"]);
            array_push($myprojekte, $project);
        }

        if (!empty($myprojekte))
        {
            return $myprojekte;
        }
        else
        {
            return false;
        }
}

 

[paulorv]

Wow! What a quick response!

 

ignace: I agree the mysql_real_escape_string() is careless, but it is all over the code and I haven't found the time to clean it up.

 

Mchl: Interesting solution, something I would have never thought of.

 

Thanks to you both 

[Mchl]

Both values are cast to int anyway, so mysql_real_escape_string is unnecessary in this case.

[ignace]

@Mchl Your query confirms what I thought

 

@OP I feel that your code may even require further optimization like

 

$project = $this->getProject($projs["ID"]);

 

What code goes behind this?

[Mchl]

Ignace: You could probably rewrite whole application in more efficent way, but are you actually willing to do it?