Array queries

[John Hunt]

Good afternoon to you all.
I'm a new user of phpScheduleit and have installed (sucessfully, in the main) on msSQL, IIS, windows server 2003, PHP and Pear.
I added the records in the database manually, using the msSql manager and query tool. I can make reservations and carry out simple admin tasks such as adding resources, announcements and accessories. Unfortunately I cannot run anything which uses an array to generate a display on a html page, the system will return a message such as the one below:

There was an error executing your query:
DB Error: unknown error SELECT res.*, resusers.*, rs.name, rs.rphone, rs.location FROM reservations as res INNER JOIN resources as rs ON rs.machid=res.machid INNER JOIN reservation_users as resusers ON resusers.resid=res.resid WHERE resusers.memberid='sc14694b40523e8d' AND (res.start_date>=1184799600 OR (res.start_date<=1184799600 AND res.end_date>=1184799600)) AND res.is_blackout <> 1 AND owner = 1 ORDER BY start_date ASC, res.start_date, rs.name, res.starttime [nativecode=169 - A column has been specified more than once in the order by list. Columns in the order by list must be unique.]

And all in red as well.
From the last sentence something beleives that the res.start_date is the same as the res.starttime. As I'm not really a programmer could anyone point me in a sensible direction, please?

Beautiful software, however.

john Hunt.

[Nick]

This is in 1.2.7?  This bug was reported before but I thought I released the fix into the code.

[John Hunt]

I had a look through the older messages and did not see a mention (possibly my eyesight).

Thanks,

John.

[Nick]

Is this on the control panel?

[John Hunt]

This happens for all users after they have logged on to the system (this dsplays the text I posted originally) but not under any other page.

In the administrator control panel similar text displays when attempting to access the 'Manage Users' window. When I'm doing this the array appears to be complaining that l.lname and l.fname are the same (at least that is what I beleive is happening).

Thanks fro your time,

John Hunt.

[Nick]

I'll get this fix in before the 1.2.8 release.  Thanks!

[Neologist]

Hi,

I recently installed 1.2.8 and I get exactly the same error as mentioned above.

DB Error: unknown error SELECT l.* FROM login as l ORDER BY lname ASC, l.lname, l.fname [nativecode=169 - A column has been specified more than once in the order by list. Columns in the order by list must be unique.]

Can you help me please?

[alricthemad]

Looks like the problem witht he query is here:
AND owner = 1 ORDER BY start_date ASC, res.start_date, rs.name, res.starttime,
Note the use of start_date twice.
If you can find he query (not sure where it is located) and remove one of the start date entries from the ORDER BY clause.

IIRC MS SQL Server does not recognize aliased column names in an ORDER BY clause.

you might also try adding the table name in front of the first start_date entry if it really si from another table.

[Neologist]

I already searched for the query but I am not sure which one is the right one as they are splitted up in different variables.
Perhaps the developer can give me a hint how to solve the problem?
He wrote that he already fixed the problem before 1.2.8 Perhaps he had messed up the code with the new release by mistake?!?

[EDIT]
Now I found a function called "function get_user_reservations()" in the DBEngine.class.php:

There is already a comment like "// Clean out the duplicated order so that MSSQL is OK" but obv that doesn't work well.

Some lines beyond there is the code:

Code:

. " ORDER BY $order $vert, res.start_date, rs.name, res.starttime";

I just wiped out ", res.start_date, rs.name, res.starttime" and the errors with the reservations were gone. I hope that will not effect any other functions.

Unfortunately I cannot find the other function affecting the user-management not to work.

[ANOTHER EDIT]
I found the next function, it's called search_users()" in the AdminDB.class.php.
Find the code:

Code:

. ' ORDER BY ' . $order . ' ' . $vert . ', l.lname, l.fname';

and wiped out " . ', l.lname, l.fname'" This is working well for me.

[Nick]

Thanks for the post.  The only side effect of doing that is that the default table sort order will be used if no columns are clicked.

[spamking]

I was having the same issue on my MSSQL installation . . . thanks for working on finding what the cause was.  I made your suggested edits and the errors went away on my end as well.