[API DB] MySQL query fails : Unknown column in where clause.

697 Posts

Jul Reply #1, 17 years, 9 months ago

Hi,

I’m currently writing a snippet and the code requires getting the id of a TV named "match_id".
From phpMyAdmin I could check that this TV exists in table "myprefix_site_tmplvars".
The "match_id" string is stored in the "name" column.
However I get the error below. Does anyone have an idea about what I’m doing wrong ?

$table_prefix='myprefix';
$table = $table_prefix.'_site_tmplvars';
$req = "SELECT id FROM $table WHERE name=match_id";
$res = $modx->db->query($req);

« MODx Parse Error »MODx encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - Unknown column ’match_id’ in ’where clause’ »
SQL: SELECT id FROM myprefix_site_tmplvars WHERE name=match_id

N.B. I tried replacing "SELECT id" by "SELECT name", to put `` quotes around match_id, to replace = by ==, a.s.o., but nothing helped. undecided

I observed that "name" is of type varchar(50). Should I take this into account and trim the field value ?
Many thanks for help.

2,310 Posts

bunk58 Reply #2, 17 years, 9 months ago

$table_prefix='myprefix';
$table = $table_prefix.'_site_tmplvars';
$req = "SELECT `id` FROM $table WHERE `name` =\'match_id\'";
$res = $modx->db->query($req);

Should work.

697 Posts

Jul Reply #3, 17 years, 9 months ago

Thanks a lot. Your code was not working but helped me a lot to catch that column names had to be quoted with `` . grin

This works :

$req = "SELECT `id` FROM ".$table."  WHERE `name`='match_id'";

As you can see, I was obliged to extract $table from the string and insert it by concatenation as it was not replaced when passed in the SQL query.
I also had to remove the backslashes.

Cheers.

2,310 Posts

bunk58 Reply #4, 17 years, 9 months ago

Glad you got there laugh