SQLSTATE[42S22]: Column not found: 1054 Unknown column PhP PDO MariaDB10

Question

I found myself in a really weird situation in PDO. A query doesn't want to execute when called from PhP but it does when called from HeidiSQL.

The error is in title.

SQL query from statement debugDumpParams :

SELECT s_id AS id,
 s_title AS title,
 genre.g_name AS genreName,
 accounts.ac_public_name AS producerName,
 s_price AS price, 
 DATE_FORMAT(s_last_modified_date, '%d/%m/%Y %H:%i:%s') AS lastModifiedDate, 
 DATE_FORMAT(s_added_date, '%d/%m/%Y %H:%i:%s') AS addedDate,
 s_downloads AS downloads,
 s_sales AS sales,
 s_rating AS rating, 
 s_status AS STATUS
FROM song
JOIN accounts ON accounts.ac_id = song.s_producer
JOIN genre ON genre.g_id = song.s_genre
WHERE 1=1 AND genre.g_id = '1'
ORDER BY s_status ASC, s_added_date DESC
LIMIT 0, 5;

Next is the part where I add genre.g_id = :id in the query string

 if(isset($filterData["genreId"]) && $filterData["genreId"] !== ""){
    $queryString .= " AND genre.g_id = :genreId";
 }

And where I bind it

 if(isset($filterData["genreId"]) && $filterData["genreId"] !== ""){
    $genreParam = $filterData["genreId"];
    $stmt->bindParam('genreId', $genreParam);
 }

In both cases $filterData["genreId"] is set and it have a value, so no problems with the if. And if $filterData["genreId"] would be empy or not set there would be no problem with the query.

And the error : SQLSTATE[42S22]: Column not found: 1054 Unknown column \'genre.g_id\' in \'where clause\'

But wait, there is more. If I put genre.g_id2 instead of genre.g_id it will look like this:

 if(isset($filterData["genreId"]) && $filterData["genreId"] !== ""){
    $queryString .= " AND genre.g_id2 = :genreId";
 }

Now it won't even reach $stmt->debugDumpParams(); And all it gives out it's this error: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'genre.g_id2\' in \'where clause\ , no query like before.

The column 100% exists in the table. Similar problem whith this query:

SELECT s_id AS id,
 s_title AS title,
 genre.g_name AS genreName,
 accounts.ac_public_name AS producerName,
 s_price AS price, 
 DATE_FORMAT(s_last_modified_date, '%d/%m/%Y %H:%i:%s') AS lastModifiedDate, 
 DATE_FORMAT(s_added_date, '%d/%m/%Y %H:%i:%s') AS addedDate,
 s_downloads AS downloads,
 s_sales AS sales,
 s_rating AS rating, 
 s_status AS STATUS
FROM song
JOIN accounts ON accounts.ac_id = song.s_producer
JOIN genre ON genre.g_id = song.s_genre
WHERE 1=1 AND accounts.ac_id = '999999'
ORDER BY s_status ASC, s_added_date DESC
LIMIT 0, 5;

It seems the problem is only with columns that are from the tables that table song JOIN with.

Next query works perfectly.

SELECT s_id AS id,
 s_title AS title,
 genre.g_name AS genreName,
 accounts.ac_public_name AS producerName,
 s_price AS price, 
 DATE_FORMAT(s_last_modified_date, '%d/%m/%Y %H:%i:%s') AS lastModifiedDate, 
 DATE_FORMAT(s_added_date, '%d/%m/%Y %H:%i:%s') AS addedDate,
 s_downloads AS downloads,
 s_sales AS sales,
 s_rating AS rating, 
 s_status AS STATUS
FROM song
JOIN accounts ON accounts.ac_id = song.s_producer
JOIN genre ON genre.g_id = song.s_genre
WHERE 1=1 AND s_status = '0'
ORDER BY s_status ASC, s_added_date DESC
LIMIT 0, 5;

2 days on this and no solutons. Most solutions I find are to check again if the column really exists :| Does any one have a better solution for this specific problem ?

The goal is to select the data where the g_id is equal with the value I pass to it.

How I create the query string:

 $queryString = "SELECT s_id as id,
                            s_title as title,
                            genre.g_name as genreName,
                            accounts.ac_public_name as producerName,
                            s_price as price, 
                            DATE_FORMAT(s_last_modified_date, '%d/%m/%Y %H:%i:%s') as lastModifiedDate,
                            DATE_FORMAT(s_added_date, '%d/%m/%Y %H:%i:%s')  as addedDate,
                            s_downloads as downloads,
                            s_sales as sales,
                            s_rating as rating,     
                            s_status as status
                            FROM song 
                            JOIN accounts on accounts.ac_id = song.s_producer
                            JOIN genre on genre.g_id = song.s_genre
                            WHERE 1=1 ";

then in a function I add this

 if(isset($filterData["genreId"]) && $filterData["genreId"] !== ""){
    $queryString .= " AND genre.g_id = :genreId";
 }

then I do this

$queryString .= " ORDER BY s_status asc, s_added_date desc";
$queryString .= " LIMIT :offset, :limit;";

and finally

  $stmt = $dbConnector->getConnection()->prepare($queryString);

This is the part that is related to the problem. I can't post the entire function, it's really long.

This is used to search data based on some inputs or combinations of inputs. The entire DAO class is really big :)

OS: Windows 10, PhP version: 7.2.19, Apache version: 2.4.35, MariaDB version: 10.4 Heidi SQL: 10.2.0.5599

One more edit:

If I add the columun in the queryString when I first declare it:

$queryString = "SELECT s_id as id,
                            s_title as title,
                            genre.g_name as genreName,
                            accounts.ac_public_name as producerName,
                            s_price as price, 
                            DATE_FORMAT(s_last_modified_date, '%d/%m/%Y %H:%i:%s') as lastModifiedDate,
                            DATE_FORMAT(s_added_date, '%d/%m/%Y %H:%i:%s')  as addedDate,
                            s_downloads as downloads,
                            s_sales as sales,
                            s_rating as rating,     
                            s_status as status
                            FROM song 
                            JOIN accounts on accounts.ac_id = song.s_producer
                            JOIN genre on genre.g_id = song.s_genre
                            WHERE 1=1 AND genre.g_id = :genreId";
        $queryString = $this->filterDataQuery($queryString, $filterData, "songs");
        $queryString .= " ORDER BY s_status asc, s_added_date desc";
        $queryString .= " LIMIT :offset, :limit;";
        $stmt = $dbConnector->getConnection()->prepare($queryString);
        $genreParam = $filterData["genreId"];
        $stmt->bindParam(':genreId', $genreParam);

and not inside $this->filterDataQuery($queryString, $filterData, "songs"); when I build the queryString based on selected filters it works perfectly. This is the select from the browser console now:

SELECT s_id AS id,

 s_title AS title,

 genre.g_name AS genreName,

 accounts.ac_public_name AS producerName,

 s_price AS price, DATE_FORMAT(s_last_modified_date, '%d/%m/%Y %H:%i:%s') AS lastModifiedDate, DATE_FORMAT(s_added_date, '%d/%m/%Y %H:%i:%s') AS addedDate,

 s_downloads AS downloads,

 s_sales AS sales,

 s_rating AS rating, 

 s_status AS STATUS
FROM song
JOIN accounts ON accounts.ac_id = song.s_producer
JOIN genre ON genre.g_id = song.s_genre
WHERE 1=1 AND genre.g_id = '1'
ORDER BY s_status ASC, s_added_date DESC
LIMIT 0, 5;

Isn't this one the same with the first? For it is. I am blind ?

Answer 1

After rereading your first post dozens of times and testing locally all sorts of normal things without getting the same error, I think the $stmt variable inside filterDataQuery() holds a total different query than posted here. And it just doesn't include the table genre as shown in the error message. If you're not using that function, seeing your second last code block, all works well (if I understand you correctly).

Also according to the code shown, I don't understand how you would call filterDataQuery() to add criteria to the querystring, and directly binding the params to the statement object that's prepared later on and thus is not available there (or not the one you would expect).

To prove my theory my complete test code, still using this db-fiddle:

ini_set('display_errors', 1);
error_reporting(E_ALL);

$DB_USER = '*****';
$DB_PASS = '*****';
$db = new PDO('mysql:host=localhost;dbname=testing.project', $DB_USER, $DB_PASS);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

$select = 'SELECT s_id AS id,
 s_title AS title,
 genre.g_name AS genreName,
 accounts.ac_public_name AS producerName,
 s_price AS price, 
 DATE_FORMAT(s_last_modified_date, "%d/%m/%Y %H:%i:%s") AS lastModifiedDate, 
 DATE_FORMAT(s_added_date, "%d/%m/%Y %H:%i:%s") AS addedDate,
 s_downloads AS downloads,
 s_sales AS sales,
 s_rating AS rating, 
 s_status AS STATUS
FROM song
JOIN accounts ON accounts.ac_id = song.s_producer
JOIN genre ON genre.g_id = song.s_genre';
$where = ' WHERE genre.g_id = :genreId';
$order = ' ORDER BY s_status ASC, s_added_date DESC';
$limit = ' LIMIT 0, 5';

// No problemo:
$sql = $select . $where . $order . $limit;
$stmt = $db->prepare($sql);
$genreId = 1;
$stmt->bindParam(':genreId', $genreId);
$stmt->execute();

// Trigger error column not found, $stmt containing an unexpected query:
$select = 'SELECT * FROM `song`';
$sql = $select . $where . $order . $limit;
$stmt = $db->prepare($sql);
$genreId = 1;
$stmt->bindParam(':genreId', $genreId);
$stmt->execute();

Answer 2

First, I don't pass the $stmt variable to filterDataQuery. I pass it to bindParams. bindParams function is called aftert filterDataQuery is done and after adding the strings for order by and limit.

I really have to thank you,Piemol , for trying to help me.

I checked Maria DB logs after a professor from my college adviced me to do so, and I found the problem there

In the same controller I was calling two DAO methods, one after another, one to bring the data, and the sencond to do a count.

  $responseMessage = json_encode(AdminDAO::getInstance()->getFilteredSongsList($message));
  $count = AdminDAO::getInstance()->getFilteredSongsListItemsCount($message); // this one was the problem

The problem was not in the one that was getting the data, the one I posted about. The problem was with the one doing the count. I did not added the joins there :| . Alose there where no indication from what method the error was comming so I focused on the wrong one(the one that was displayed in log). Something like this would have never happened if I would have used a logger like I do in Java.

Here are the queries from MariaDB log file: https://www.heypasteit.com/clip/0IUPWG First one is the one that fails and the 2nd one is the one that works.

I miss log4j from Java so much.

Thanks to everybody who tried to help me.

For those who have this kind of weird problems, check logs, put echos, don't focus on the first thing you see in a AJAX response log.