Mysql error 1054 Unknown column

Asked

Viewed 10,030 times

4

I’m making an SQL that counts how many names in the table start with any letter but an error is being returned:

Column not found: 1054 Unknown column 'A' in 'Where clause'

To the following SQL:

SELECT COUNT(*) FROM author WHERE name LIKE 'A%';

Excommunicating directly in Mysql console works perfectly but when using in PDO error 1054 is returned.

$count = 'SELECT COUNT(*) FROM author WHERE name LIKE \'' . $consulta->letter . '%\'';
echo $count; //Resultado : SELECT COUNT(*) FROM author WHERE name LIKE 'A%'
$stmt = $conn->prepare($count);

Note: I am using PDO and Mysql.

mysql sql pdo

  • @Jorgeb. Amabas the forms didn’t work

  • Strange because I get results from echo and lap on the console and works

  • Makes echo "::$count::"; and put the result here.

  • @Jorgeb. ::SELECT COUNT(*) FROM Author WHERE name LIKE 'A%'::

  • I really don’t see...

4 answers

2


I believe the problem occurs because of the signal %, that can’t be there. Try using bindParam, thus:

$letter = $consulta->letter . "%"

$stmt = $conn->prepare('SELECT COUNT(*) FROM author WHERE name LIKE :letter');
$stmt->bindParam(':letter', $letter, PDO::PARAM_STR);

  • Generated the following error: Fatal error: Cannot pass parameter 2 by reference

  • I switched to: "$consulta->letter%" and it didn’t work

  • Sorry, the second parameter must be a variable, able to be passed by reference. I edited the answer.

  • 1

    Switching to bindValue() should work.

  • Using his answer I managed to get to full functioning, I believe the error is the presence of % inside the string that will be prepared by PDO, as I have already used concatenations inside the string that will be prepared without problems (concatenei clausulas LIMIT)

  • Fatal error: Cannot pass parameter 2 by reference this mistake left me very intrigued because I’ve done it as follows: $stmt->bindParam(':description',$logMessage->mensagem,PDO::PARAM_INT); and worked perfectly

  • 1

    It turns out that this argument needs to be a reference to a variable. Note that before I had done $name . "%", which is not a variable but a concatenation, so there is no reference to it. In the case of $logMessage->mensagem, it is possible.

Show 2 more comments

1

I’d do it like this and work here:

$count = "SELECT COUNT(*) FROM author WHERE name LIKE '{$consulta->letter}%'";

  • It doesn’t work, he tested.

  • very strange. something in the internal settings of php+Pdo, will be?

1

1054 Unknown column 'column name' in local/claudia

This error usually occurs when the column does not exist, was typed wrong, lacked quotes in a value that ended up being interpreted as a column or in the worst case the table ended up corrupting.

The ideal is to use Prepared statement and let joker (% or _) out sql statement and pass it only when making bind.

$stmt = $conn->prepare('SELECT COUNT(*) FROM author WHERE name LIKE ?');
$stmt->execute(array('A%'));

Jokers:

% - Qualquer caracter em qualquer quantidade.
_ - Um único caracter.

0

I was with the same problem and followed the answer given by the user "bigwebguy" here at this link: Getting raw SQL query string from PDO Prepared statements

Thus it is possible to identify which query and parameters are being executed. In my case it was necessary the use of crase `` for it to work.

$user = DB::getInstance()->get('users', array("\` name\`", '=', "joao"));

/**
 * Replaces any parameter placeholders in a query with the value of that
 * parameter. Useful for debugging. Assumes anonymous parameters from 
 * $params are are in the same order as specified in $query
 *
 * @param string $query The sql query with parameter placeholders
 * @param array $params The array of substitution parameters
 * @return string The interpolated query
 */
public static function interpolateQuery($query, $params) {
    $keys = array();

    # build a regular expression for each parameter
    foreach ($params as $key => $value) {
        if (is_string($key)) {
            $keys[] = '/:'.$key.'/';
        } else {
            $keys[] = '/[?]/';
        }
    }

    $query = preg_replace($keys, $params, $query, 1, $count);

    #trigger_error('replaced '.$count.' keys');

    return $query;
}

Browser other questions tagged mysql sql pdo

You are not signed in. Login or sign up in order to post.