Can't prepare a query and no error output

Question

today I have some problems with my script, I can't prepare the query and the script doens't retrun any error:

Table structure(oops wrong table,corrected):

CREATE TABLE IF NOT EXISTS `razorphyn_support_users` (
`id`                BIGINT(11)    UNSIGNED    NOT NULL AUTO_INCREMENT,
`name`              VARCHAR(50)               NOT NULL,
`mail`              VARCHAR(50)               NOT NULL,
`password`          VARCHAR(200)              NOT NULL,
`reg_key`           VARCHAR(260)              NOT NULL,
`tmp_password`      VARCHAR(31)               NULL,
`ip_address`        VARCHAR(50)               NOT NULL,
`status`            ENUM('0','1','2','3','4') NOT NULL    DEFAULT '3',
`holiday`           ENUM('0','1')             NOT NULL    DEFAULT '0',
`assigned_tickets`  INT(5)        UNSIGNED    NOT NULL    DEFAULT 0,
`solved_tickets`    BIGINT(11)    UNSIGNED    NOT NULL    DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY(`mail`)) 
ENGINE=MyISAM
DEFAULT CHARSET=utf8 
AUTO_INCREMENT=55;

Unpreparable string:

$query = "SELECT `id` FROM ".$SupportUserTable." 
WHERE `status`='2' AND `holiday`='0' AND MIN(`assigned_tickets`) 
ORDER BY `solved_tickets` ASC" ;

Full code(in reality this is only part of it, the $stmt is already starterd and working,basically it's already connected):

file_put_contents('ok.txt','');
$query = "SELECT `id`  FROM ".$SupportUserTable." 
          WHERE `status`='2' AND `holiday`='0' AND MIN(`assigned_tickets`) 
          ORDER BY `solved_tickets` ASC" ;
$prepared = $stmt->prepare($query);
if($prepared){
file_put_contents('ok2.txt','');
if($stmt->execute()){
    file_put_contents('ok3.txt','');
    $stmt->store_result();
    $result = $stmt->bind_result($id);
    file_put_contents('eafv.txt','id: '.$id);
    if($stmt->num_rows>0){
        $query = "UPDATE ".$SupportTicketsTable." SET operator_id=? 
                  WHERE id=? ";
        if($prepared = $stmt->prepare($query)){
            if($stmt->bind_param('ii', $id,$tkid)){
                if($stmt->execute()){
                    echo json_encode(array(0=>'Created'));
                }
                else
                    echo json_encode(array(0=>mysqli_stmt_error($stmt)));
            }
            else
                echo json_encode(array(0=>mysqli_stmt_error($stmt)));
        }
        else
            echo json_encode(array(0=>mysqli_stmt_error($stmt)));
    }
    else
        echo json_encode(array(0=>'No Operator Available'));
}
else
    echo json_encode(array(0=>mysqli_stmt_error($stmt)));
}
else
    echo json_encode(array(0=>$stmt->error));

Can someone at least motivate the minus one or I have to die ignorant? — Razorphyn, May 14 '13 at 22:15
The table you have given the structure does not have `status`, `holiday`, `assigned_tickets`, `solved_tickets` fields. Might your $SupportUserTable have a value like "razorphyn_support_list_tickets"? Or did you show us another table? — zkanoca, May 14 '13 at 22:22
I have just noticed, stupid error, I have just inserted the right table — Razorphyn, May 14 '13 at 22:24
I don't think you want MIN(`assigned_tickets`) in the WHERE part of the query. What are you trying to achieve? — Pier-Luc Gendreau, May 14 '13 at 22:33
I would like to select the operator with the minimum number of open ticket and if there are multiple operator with the samevalue => select the one with less solved tickets — Razorphyn, May 14 '13 at 22:37
Hey @Dheed. I'm responsible for down, and close-vote. The code you've *dropped* here shows 0% effort. Note that SO is not a place for that. And note that in *cool* programs a line has maximal 80 chars — hek2mgl, May 14 '13 at 22:43
As you know you are free to express your ideas: '0% Effort' if you mean that I haven't tried to solve the problem by myself, I must disagree,I'm not so lazy, if you mean the presentation of the code, you're right. Thanks for the 80 characters suggestion — Razorphyn, May 14 '13 at 23:03

score 2 · Accepted Answer · edited May 23 '17 at 12:13

You can't put MIN() expressions in the WHERE clause in SQL. You can put MIN() expressions only in the select-list, the HAVING clause, and the ORDER BY clause.

What you want is:

I would like to select the operator with the minimum number of open ticket and if there are multiple operator with the same value [then] select the one with less solved tickets

Here's a solution to get this result:

SELECT `id` FROM razorphyn_support_users 
WHERE `status` = 2 AND `holiday` = 0 
ORDER BY `assigned_tickets` ASC, `solved_tickets` ASC
LIMIT 1

Re your comments:

For the LIMIT syntax, see https://stackoverflow.com/a/3325580/20860 or the MySQL manual which says:

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

Using string-delimiters is good if the type is a string or an enum. I made the assumption that the columns were integers (I didn't double-check your table definition above), and removed the string delimiters because they're not necessary for integers.

Thank you, just a couple of questions:is it LIMIT 1 or LIMIT 0,1 and should I use the single quote to incapsulate 2 and 0, because the table field is ENUM? — Razorphyn, May 14 '13 at 23:00

Can't prepare a query and no error output

1 Answers1