database erorr

[nor-al3in]

Script URL: localhost
Version of script:
Version of PHP:EasyPHP1-7
Hosting company:
Have you searched THIS FORUM for your problem:
(if not please do before posting)
If so, what terms did you try:

Write your message below:

hi I need ur help please I download ur help desk program

& I edit the hesk_settings_inc.php

when I try to submit a new ticket

I have this masseg:

Couldn't execute SQL: SELECT * FROM `hesk_categories`
Please contact webmaster

remember that I want to try the program in esay php server (localhost)


could u help me???

thanx.....

[Klemen]

Have you installed MySQL tables using the install.php file?

Is the DEFAULT category still in the database or have you deleted it manually?

[nor-al3in]

thanx very mutch

thanx for ur help

[Klemen]

I presume it works now?

[jfisher]

Hello Klemen,
first: thanks for this very good script which can be easily modified even with my limited programming knowledge to fit many needs.

We use this script for an internal communication tool between 4 parties working on order fulfillment. It's not a publicly used application and I did some tweaks to make it work for us.

URL: https://www.med-service27.net/backoffice27/
(as I said it's not accessable for the public)
Hesk Version: 0.93b from July 5, 2005 (patch 0.93.1)
Server: Windows
PHP: Version 4.4.0
Host: Handynetworks
Database: MySQL 4.0.24-nt-max

Sometimes when logging in we receive the following error message:

"SQL-Anweisung nicht ausführbar: SELECT * FROM 'hesk_tickets' WHERE ( 'category'=Arra ) AND 'status'=1 ORDER by 'priority' ASC

MySQL-Meldung:
Unknown column 'Arra' in 'where clause'"

Obviously it doesn't retrieve the array in '$sql .= hesk_myCategories();' correctly.
Strangely it works correct most of the time but sometimes this error shows up.
A fix is to log out and then to log in again. But this is annoying.

Is there any way to fix this completely?

Best regards

J.Fisher

[Klemen]

Hi,

Well never had any problems like this reported with the original version, maybe it has something to do with your edited version? Paste the PHP code around the SQL that makes problems and I will look for any obvious problems.

Regards

[jfisher]

Klemen,
thanks for your offer.
In the do_login function I made the following change:

function do_login() {
global $hesklang;
$user=hesk_isNumber($_REQUEST['user'],$hesklang['select_username']);
$pass=hesk_input($_REQUEST['pass'],$hesklang['enter_pass']);

$sql = "SELECT * FROM `hesk_users` WHERE `id`=$user LIMIT 1";

Means I replaced 'POST' by 'REQUEST'.

BUT as far as I understand your script, the above described error doesn't originate from the login procedure itself but from the database query for the search form in the admin_main.php script, respectively in the script 'show_search_form.php' where you coded:

$sql_private = "SELECT * FROM `hesk_categories` WHERE ";
$sql_private .= hesk_myCategories('id');

$result = hesk_dbQuery($sql_private) or hesk_error("$hesklang[cant_sql]: $sql</p><p>$hesklang[mysql_said]:<br>".mysql_error()."</p><p>$hesklang[contact_webmsater] $hesk_settings[webmaster_mail]");
while ($row=hesk_dbFetchAssoc($result))
{
echo "
<option value=\"$row[id]\">$row[name]</option>
";
}

and here I didn't change anything.
As I said, the error occurs only sometimes. If it occurs it occurs several times in a row which is annoying. Then suddenly it works again.

That's how it is.

In all other regards the script is great and can be adapted to any requirements easily.

Jerome

[Klemen]

Hi,

I can't see no obvious reasons why that error should occur. Can you please give an EXACT example of all the values when this happens:

1. the content of your categories table
2. the content of your users table (passwords can be ****)
3. exactly what you try to do when you get the error and with which user

[jfisher]

Klemen,
I cant see an error either.
The content of the tables doesn't matter here. I assume it's a database connection error where the array of mycategories cant be retrieved quickly enough to avoid the error display. It's annoying because it uses to happen most frequently with my most important partner whose username is 'apotheek' - and his pw is +++++, so does this help further? Certainly not. I guess it's an issue with the database server not responding quickly enough to the request.
I just wanted to know if the login procedure can be rewritten to be more "forgiving" to slower database responses.

I am too dumb to do that - but it basically means now I can throw this helpdesk in the bin and setup a new one (pain in the a... with all the
existing tickets)

J. Fisher

[Klemen]

Jerome,

I don't think this has anything to do with database connection, the problem is somewhere else. If you can give me your Hesk URL I will try logging in myself and see if I can get the same error.

Have you tried uploading the ORIGINAL Hesk (without editing anything except settings file) and see if you can get the same error there? Just upload everything to a new folder/database, create same categories and a user with same username/password/categories as the one you are having problems with and let's see if the error can be repeated in original files.

[craftastic]

Oddly enough, I came here today to report the same exact problem. I've made a few changes to the script but basically just cosmetic ones.

One of the users I set up as staff says she gets this randomly when she logs in:

Can't execute SQL: SELECT * FROM `hesk_tickets` WHERE ( `category`=Arra ) AND `status`=1 ORDER BY `priority` ASC

MySQL said:
Unknown column 'Arra' in 'where clause'

I've tried logging in with her username and password and the error doesn't happen to me. Right now I'm waiting for her to give me some more details like perhaps it's a difference in OS/browser or something...

[craftastic]

OK. So it looks like it's either happening in print_tickets.inc.php or find_tickets.php since those are the only two files with the statement "SELECT * FROM `hesk_tickets`..." Digging down from there, it must be specifically happening in the function "hesk_myCategories" in common.inc.php.

I added a debugging statement to hesk_myCategories right before the return statement:

Code: Select all

        $handle = fopen("/home/craft/public_html/debug.txt", "a");
        fwrite($handle, $mycat_sql);
        fwrite($handle, " | ");
        fwrite($handle, gettype($mycat_sql));
        fwrite($handle, "\n");
        fclose($handle);

Then I had that staff member login and use hesk. She got the error to happen once. Here's the debugging output:

( `category`=1 OR `category`=2 ) | string
( `id`=1 OR `id`=2 ) | string
( `category`=1 OR `category`=2 ) | string
( `id`=1 OR `id`=2 ) | string
( `category`=Arra ) | string

I highlighted in red, the line that shows the problem happening.

I'm still not sure what's causing this or how to fix it but I'll keep looking.

[Klemen]

Hi,

Can YOU post the exact content of your "users" and "categories" tables here (you can change passwords to **** )? I haven't been able to recreate this error so I'm not exactly sure what is happening.

[craftastic]

Hi Klemen,

I have theory. It looks like for these two users who are having this problem, that in admin.php perhaps $_SESSION is not being set properly in do_login().

When I first installed hesk, I was having that problem that a lot of other people were having where whenever I tried to login I always got "Your session has expired, please login using the form below."

So I read in one of the threads here that if you comment out this line:
session_regenerate_id();

that it might fix that problem. So I did this, and it did solve the problem for me. But now I wonder if commenting out that line is causing this new problem.

So I've uncommented it and I'm going to see if it fixes the problem for my two staff members.

[Klemen]

Hi,

No, commenting out the session_regenerate_id(); shouldn't have any affect on sessions. The problem must be somewhere else but it also seems to be very rare (only two people reported it of thousands of people who downloaded Hesk). In my opinion it is some weird behaviour because of some special database content (don't ask me what), but I can't really say anything unless I am able to see the database content.

So, it would be really helpful if you could send me (here or send a PM) the content of tables of your Hesk database so I can try if I can recreate the error from my side and find out what's wrong.