Member Avatar for Samyx

I have the query below:

$insert2query="INSERT INTO output_search(ref_code, title, author, channel_id) VALUES
            (select  refcode,title, authors, $idquery as cid from chemoJ where title LIKE '%$search%' and refcode NOT IN (select exc_refid_fk FROM exclusions))";

It is giving me an error near the parenthesis, what is wrong with my sintax? I ran the different pieces of the query individually so I could see if the expected results were being returned, and it seems fine. But when I put them all together, it doesn't work.

Thanks in advance.

  • 2 Contributors
  • 1 Reply
  • 220 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by hericles

Dani AI

Generated

The syntax error comes from using VALUES with a SELECT subquery. VALUES is for literal tuples; inserting rows returned by a query requires the INSERT ... SELECT form. was right to suggest removing VALUES. Also confirm the SELECT returns exactly the same number of expressions (and compatible types) as the target column list (ref_code, title, author, channel_id) — column names do not have to match, but the ordering and types do.

Correct pattern (parameterized example for PostgreSQL):

INSERT INTO output_search (ref_code, title, author, channel_id)
SELECT refcode, title, authors, $1
FROM chemoJ
WHERE title LIKE '%' || $2 || '%'
  AND refcode NOT IN (
    SELECT exc_refid_fk FROM exclusions WHERE exc_refid_fk IS NOT NULL
  );

Notes and troubleshooting tips:

  • If $idquery is a literal value for all inserted rows, pass it as the first parameter ($1) and the search text as the second ($2). Prefer prepared statements / pg_query_params or PDO to avoid quoting bugs and SQL injection.
  • NOT IN will behave unexpectedly if the exclusions list contains NULL; the WHERE ... IS NOT NULL guard or switching to NOT EXISTS avoids that pitfall.
  • Verify the SELECT actually returns rows (a quiet no-op inserts nothing). If the SELECT returns more/fewer columns than the INSERT list, PostgreSQL raises an error.
  • For case-insensitive matches use ILIKE. Leading wildcards ('%term%') prevent normal index use — consider full-text search or trigram indexes for large tables.

Mention for : check that refcode/ref_code and authors/author correspond in order and type, and switch to parameterized queries to eliminate PHP string-assembly issues.

Member Avatar for hericles

Try ti without the VALUES keyword, just the select statement.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.