SQLSTATE[42S22]: Column not found: 1054 Unknown column
You are encountering a classic PHP-to-SQL boundary error. The message SQLSTATE[42S22]: Column not found: 1054 Unknown column 'John' in 'field list' is screaming that your database schema does not have a column named John. While the error points to the database, the root cause lies entirely within how you are constructing your SQL query string before it ever reaches the server.
In your code snippet, you are manually interpolating variables directly into an SQL statement using single quotes and concatenation logic that is dangerously close to syntax hell. Look closely at this line:
$sql = "INSERT INTO `saved_holidays` (`subscriberID`, `link`, `pubDate`, `title`, `description`)
VALUES (`John`, `$currentFav->link`, `$currentFav->pubDate`, `$currentFav->title`, `$currentFav->description`)";
Here, you have hardcoded the string John inside single quotes within the SQL query. In MySQL, anything enclosed in single quotes is treated as a literal text value (a string). When PDO executes this statement, it sees an attempt to insert the literal text "John" into the subscriberID column. If your table definition expects an integer or if you simply haven't created a column named "John", and worse, if you intended $check_value (which contains the subscriber ID) to go there, you have failed to pass that variable correctly.
The error occurs because the parser sees VALUES ('John', ...) and looks for a column named 'John'. If your table actually has an integer ID column, or a column named user_id, the database throws the "Unknown column" error because the query structure itself is malformed regarding which values map to which columns.
You are also mixing string literals with dynamic data without proper escaping. In the example above, $currentFav->link and other XML values are being injected directly into the SQL string. If a holiday link contains a single quote character (e.g., title="Holiday's Fun"), it will break the SQL syntax immediately. This is why you are seeing errors; the query structure is brittle.
The correct approach involves two distinct steps: separating data from schema, and using parameter binding. By using placeholders like ? or named parameters like :id, you allow PDO to handle the escaping and type coercion automatically. Here is how that specific insertion should look:
$sql = "INSERT INTO `saved_holidays` (`subscriberID`, `link`, `pubDate`, `title`, `description`)
VALUES (:subscriber_id, :link, :pub_date, :title, :description)";
$stmt = $db->prepare($sql);
$stmt->execute([
'subscriber_id' => $check_value,
'link' => (string)$currentFav->link,
'pub_date' => (string)$currentFav->pubDate,
'title' => (string)$currentFav->title,
'description' => (string)$currentFav->description
]);
Notice that $check_value is now mapped to the :subscriber_id placeholder. This ensures that whatever integer or string resides in your $_POST['chk'] array is actually inserted into the correct column. Additionally, casting the XML properties to strings prevents type mismatches and ensures clean data handling.
There is another subtle issue in your loop structure. You are calling $db = null; inside the inner foreach loop. While this doesn't cause the specific "Unknown column" error, it resets your database connection reference after every single row insertion. This defeats the purpose of a prepared statement cache and can lead to performance bottlenecks if you are inserting hundreds of records. It is better to prepare the statement once outside the loop and reuse it, or simply let the connection persist until the entire batch is processed.
Finally, consider that relying on simplexml_load_file to parse XML and then immediately dumping it into a database without validation exposes your application to potential injection vectors if the data isn't strictly typed. While PDO prevents SQL injection in the code above, you should still validate that the XML data conforms to expected formats before persisting it.
If you are looking for a more robust framework to handle these kinds of database operations with built-in migrations and type safety, Laravel offers a powerful ORM and query builder that abstracts away much of this manual string manipulation. You can explore their documentation at https://laravelcompany.com to see how they handle Eloquent models and migrations to avoid these low-level pitfalls entirely.