'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id'

2
votes

Hi: I'm having trouble debugging some MySQL & PHP code.

Background

  • Downloaded http://ajaxlivesearch.com/ source code
  • Followed instructions and enabled debugging
  • Created a MySQL db with the following columns: list_ADDRESS, list_COMPANY, list_WEBSITE, list_INDUSTRY
  • I know the db + connection work (I set up a small test file to connect & spit out the tables that're in the db)

Issue

  • The search doesn't work and returns a 'Something went wrong' error
  • With debugging enabled, the error_log file shows the following:

[26-Aug-2015 06:37:42 UTC] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'field list'' in /public_html/inz/class/handler.php:85 Stack trace:

0 /public_html/inz/class/handler.php(85): PDO-prepare('SELECT COUNT(id...')

1 /public_html/inz/ajax/process_livesearch.php(21): Handler::get_result('Telecom', 1, 5)

2 {main}

  • handler.php (around line 85) contains the following code:

$db = DB::getConnection();

//this is line 85// $stmt = $db->prepare('SELECT COUNT(id) FROM ' . Config::USER_TABLE . ' WHERE ' . Config::SEARCH_COLUMN . ' LIKE :query'); $search_query = $query.'%';

$stmt->bindParam(':query', $search_query, PDO::PARAM_STR);

$stmt->execute();

$number_of_result = $stmt->fetch(PDO::FETCH_COLUMN);

  • For ref, process_livesearch.php line 21 contains the following:

            $result = json_encode(Handler::get_result($_POST['ls_query'], (int) $_POST['ls_current_page'], (int) $_POST['ls_items_per_page']));

Does anyone know why it seems to be looking for a column called 'id' ? That's not specified anywhere in the config file, nor elsewhere in the code that i can see other than being mentioned on line 85 of handler.php...

1
phpmysql
add an id column to the table and set it to be unique - Ohgodwhy
The error is telling you everything you need to know: column id is missing. Add it :) - Tobias Golbs
Show your table 'Config::USER_TABLE' Whats columns it included? - SarDau Mort
Thanks @Ohgodwhy - the search now works! New problem: it doesn't find items that are in the database. I will do some more digging and see what happens... - George R

1 Answers

2
votes

To eliminate this particular error you could change count(id) to count(1) in your query.