Viewing/Modifying./Deleting a reservation

[jwh]

PHP version: 4.3.2

Database server and version: MySQL 3.23.58

Web server and version: Apache 2.0

Browser vendor and version: Mozilla Firefox 2.0 and Microsoft Internet Explorer 7.0

Description of bug: Received error “There was an error executing your query:
DB Error: syntax error”

What you were doing at the time of the bug: Attempting to select and modify/delete an already existing reservation (while logged in as Administrator)

Any help would be greatly appreciated…

JWH

[Nick]

This is because MySQL 3.x isn't fully supported by phpScheduleIt.

If you can, let's try some debugging steps to identify where the problem is.  First, try commenting out lines 507-527 in lib/db/ResDB.class.php and see if you still see the issue.

Example

Code:

// $sql = 'SELECT ru.*, users.fname, users.lname, users.email FROM '
// . $this->get_table(TBL_RESERVATION_USERS) . ' as ru INNER JOIN ' . $this->get_table(TBL_LOGIN) . ' as users'
// . ' ON users.memberid = ru.memberid WHERE ru.resid=?'
// . ' UNION SELECT ru.*, au.fname, au.lname, au.email FROM '
// . $this->get_table(TBL_RESERVATION_USERS) . ' as ru INNER JOIN ' . $this->get_table(TBL_ANONYMOUS_USERS) . ' as au'
// . ' ON ru.memberid = au.memberid WHERE ru.resid=?';
//
// $result = $this->db->query($sql, array($resid, $resid));
//
// $this->check_for_error($result);
//
// if ($result->numRows() <= 0) {
// $this->err_msg = translate('That record could not be found.');
// return false;
// }
//
// while ($rs = $result->fetchRow()) {
// $return[] = $rs;
// }
//
// $result->free();

[jwh]

When I comment out the lines, there is no error message or info - all it shows is the empty table (background?). I have tried editing the code elsewhere to make it work with MySQL 3.x (like changing "LEFT JOIN"s to "INNER JOIN"s or just using a long "WHERE" statement), but I still can't get it to run properly. I'm going to try finding another server and database that will be more compatible. As a last effort today, we tried PHP 3.x to a 5.x MySQL database, but PHP couldn't connect properly - too much of a version difference.

I DO really want to use the program, though - it's exactly what we need if I can get it up and running! If you have any other suggestions, I'd really appreciate them, but it might be easier at this point to just beg my way onto another server; that will just take a few months.

Thanks for your help! And great program!

JWH

[Nick]

If the error is gone when commenting out those lines, try changing that whole section (507-527) to this:

Code:

$sql = 'SELECT ru.*, users.fname, users.lname, users.email FROM '
. $this->get_table(TBL_RESERVATION_USERS) . ' as ru INNER JOIN ' . $this->get_table(TBL_LOGIN) . ' as users'
. ' ON users.memberid = ru.memberid WHERE ru.resid=?';

$result = $this->db->query($sql, array($resid));

$this->check_for_error($result);

if ($result->numRows() <= 0) {
$this->err_msg = translate('That record could not be found.');
return false;
}

while ($rs = $result->fetchRow()) {
$return[] = $rs;
}

$sql = 'SELECT ru.*, au.fname, au.lname, au.email FROM '
. $this->get_table(TBL_RESERVATION_USERS) . ' as ru INNER JOIN ' . $this->get_table(TBL_ANONYMOUS_USERS) . ' as au'
. ' ON ru.memberid = au.memberid WHERE ru.resid=?';

$result = $this->db->query($sql, array($resid));

$this->check_for_error($result);

if ($result->numRows() <= 0) {
$this->err_msg = translate('That record could not be found.');
return false;
}

while ($rs = $result->fetchRow()) {
$return[] = $rs;
}

$result->free();

This code just breaks up the UNION sql statement into 2 queries.  If the problem was with the UNION statement, then you should be all set after this change.

[jwh]

That didn't work, either. However, when I changed lines 97 to 116 to the new code (see below), I got the error message "That record could not be found." I tried replacing this code because it was the only other part in the page that seemed to refer to retrieving information about a reservation.

Code:

$query = 'SELECT r.*, ru.memberid AS participantid, rem.reminder_time, rem.reminderid FROM ' . $this->get_table(TBL_RESERVATIONS) . ' r'

. ' LEFT JOIN ' . $this->get_table(TBL_RESERVATION_USERS) . ' ru ON r.resid = ru.resid AND ru.memberid = ? AND ru.owner = 0 AND ru.invited = 0'

. ' LEFT JOIN ' . $this->get_table(TBL_REMINDERS) . ' rem ON r.resid = rem.resid AND rem.memberid = ?'

. ' WHERE r.resid=?';

$result = $this->db->getRow($query, array($memberid, $memberid, $resid));

$this->check_for_error($result);

if (count($result) <= 0) {

$this->err_msg = translate('That record could not be found.');

return false;
}
return $this->cleanRow($result);

I've also noticed that my line numbers are very different from the ones you have given and I've seen elsewhere on this forum. I doubt that has anything to do with it, but if I refer to line numbers I'll include little snippets of code as a reference.

Thanks for the help so far!

JWH

[Nick]

What version of phpScheduleIt is this?

[jwh]

What version of phpScheduleIt is this?

phpScheduleIt v1.2.5

It was downloaded and installed about a week ago.

JWH

[Nick]

For some reason we seem to be looking at different line numbers in this file.  The change I was referring to would be in the get_res_users() function of lib/db/resdb.class.php.

[jwh]

For some reason we seem to be looking at different line numbers in this file.  The change I was referring to would be in the get_res_users() function of lib/db/resdb.class.php.

That was the section that I originally changed; all I get is a blank page (I tried it again just to be sure). I also tried changing the "LEFT JOIN"s in get_reservation($resid, $memberid) to "INNER JOIN"; then I got the error message, "That record could not be found."

The only trouble shooting I have done has been in get_reservation($resid, $memberid) and get_res_users($resid), but the closest I've gotten is recieving the message "That record could not be found."

JWH

[jwh]

Is there a previous version of phpScheduleIt that would work with MySQL 3.x? I wouldn't mind using an older version for a few months until I can find appropriate server space.

Thanks so much for your help!

JWH

[Nick]

My guess is that the latest 1.1 package should work fine.  I believe this issue was introduced with some of the advanced features of 1.2.

[jwh]

My guess is that the latest 1.1 package should work fine.  I believe this issue was introduced with some of the advanced features of 1.2.

I installed version 1.1.3 today, and so far it is working well. I'll be using that until our campus servers are updated in the summer.

Thanks so much for your help! And wonderful program!

JWH

[Nick]

I'll be posting any future updates to this problem in the forums.  I'm sure there is a workaround for this.