0

I have unique 16-byte keys and I need to get record ID by them. This is my example table:

CREATE TABLE `prob` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`key` VARBINARY(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci;
INSERT INTO `prob` ( `key` ) VALUES ( 0x1234 );

I make recordings successfully:

$key = pack( "H*", '01020304' );
if( $stmt = $con->prepare("INSERT INTO prob VALUES ( DEFAULT, ? )") ) {
    $stmt->bind_param( "s", $key );
    $stmt->execute( );
    $stmt->close( );
} else { $err = $con->error; echo "Err $err<br>"; }

This is the code, I'm looking for the ID with the same key:

if( $stmt = $con->prepare("SELECT id FROM prob WHERE key='?'") ) {
    if( !$stmt->bind_param( "s", $key ) ) {echo 'Err bind_param.<br>'; die( );}
    if( !$stmt->execute( ) ) {echo 'Err execute.<br>'; die( );}
    if( !$stmt->bind_result( $id ) ) {echo 'Err bind_result.<br>'; die( );}
    $rv = $stmt->fetch( );
    $stmt->close( );
    echo 'Result: '.print_r( $rv ).'<br>';
} else { $err = $con->error; echo "Err $err<br>"; }

I get this error with it:

Err You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key=' at line 1

I could not find a solution.

I tried with this code too and again the same error:

if($result = $con->query( "SELECT id FROM prob WHERE key=$key" )) {
    if( $count = $result->num_rows == 1 ) {
        $row = $result->fetch_row( );
        $rv = $row[0];
        $result->close( );
    }
} else { $err = $con->error; echo "Err $err<br>"; }

I also tried with 0x1234, '0x1234', ('0x1234'), ..., no, no and no, same error...

Ted
  • 68
  • 7
  • Don't quote your bindings. It should be `SELECT id FROM prob WHERE key=?`. Using a straight query, you *do* need to quote the variable. – aynber Jul 20 '18 at 16:23
  • Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) – aynber Jul 20 '18 at 16:24
  • This is not a problem. I have put them in my endless attempts to avoid the error. Please see the error, that MySQL gives me... I think that it don't wants to accept varbinary in WHERE. – Ted Jul 20 '18 at 16:27
  • 1
    @Rainmx93 is correct about the reserved word, but the proper syntax would be `where \`key\` = ?`. https://dev.mysql.com/doc/refman/5.7/en/keywords.html has a list of reserved words – aynber Jul 20 '18 at 16:33
  • Oo, boy! You are right! Lost 3 hours!!! Thank you! It works! The problem was in that reserved word 'key'. I changed it to 'kay' and everything works! – Ted Jul 20 '18 at 16:33

0 Answers0