I have the below PHP function that queries some custom database tables and outputs the result. This generally works great, except for when I put an apostrophe in the query; for example, "Men's" or "Women's" causes errors like below. How can I solve this? I'm a bit of a SQL novice, so I'm sure I'm doing something obviously wrong.
[03-Oct-2024 16:12:10 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's%' OR `description` LIKE '%Men\\'s%' OR `code` LIKE '%Men\\'s%') ORDER BY `titl' at line 1 for query SELECT * FROM `83kg5a_activity_posts` WHERE ( `title` LIKE '%Men\\'s%' OR `description` LIKE '%Men\\'s%' OR `code` LIKE '%Men\\'s%') ORDER BY `title` ASC, `code` ASC made by do_action('wp_ajax_activity_list'), WP_Hook->do_action, WP_Hook->apply_filters, ActivityPlugin\ajax_activity_list, activity_include, include('/plugins/activity-plugin/views/activity-list.php'), ActivityPlugin\get_activities /** * Get activities, optionally with a query * * @param array<mixed> $options * @return array|null */ function get_activities(array $options = []): ?array { global $wpdb; /** * Get the settings */ $settings = get_settings(); /** * Convert query */ if (array_key_exists("q", $_GET) && $_GET["q"]) { $options[] = [ [ "key" => "title", "operator" => "LIKE", "value" => "%" . $wpdb->esc_like($_GET["q"]) . "%", ], [ "key" => "description", "operator" => "LIKE", "value" => "%" . $wpdb->esc_like($_GET["q"]) . "%", ], [ "key" => "code", "operator" => "LIKE", "value" => "%" . $wpdb->esc_like($_GET["q"]) . "%", ], ]; } /** * Convert terms */ foreach ($_GET["terms"] as $data) { $options[] = [ "key" => $data["name"], "operator" => "=", "value" => $data["value"], ]; } /** * Convert DOW */ if (array_key_exists("dow", $_GET) && count($_GET["dow"]) < 7) { $options[] = array_map(function($day) use ($wpdb) { return [ "key" => "schedule", "operator" => "LIKE", "value" => "%" . $wpdb->esc_like($day) . "%", ]; }, $_GET["dow"]); } /** * Prepare the query */ $query = "SELECT * FROM `{$wpdb->prefix}activity_posts`"; /** * Append the options to the query */ if ($options) { $query .= " WHERE"; /** * Append the options */ foreach ($options as $data) { if (array_key_exists(0, $data)) { $query .= " ("; foreach ($data as $value) { $query .= " `{$value["key"]}` {$value["operator"]} '{$value["value"]}' OR"; } $query = rtrim($query, " OR"); $query .= ") AND"; } else { $query .= " `{$data["key"]}` {$data["operator"]} '{$data["value"]}' AND"; } } $query = rtrim($query, " AND"); } /** * Convert age */ $queried_min = (array_key_exists("age_minimum", $_GET) ? intval($_GET["age_minimum"]) : $settings["age_limit_min"]) * 12; $queried_max = (array_key_exists("age_maximum", $_GET) ? intval($_GET["age_maximum"]) : $settings["age_limit_senior"]) * 12; $min_query = ""; $max_query = ""; if ($queried_min !== $settings["age_limit_min"] * 12) { $min_query = " `age_minimum` >= {$queried_min}"; } if ($queried_min !== $settings["age_limit_min"] * 12 || $queried_max !== $settings["age_limit_senior"] * 12) { if ($queried_max >= $settings["age_limit_senior"] * 12) { $max_query .= " `age_maximum` >= {$queried_max}"; } elseif ($queried_max >= $settings["age_limit_adult"] * 12) { $max_query .= " `age_maximum` >= {$queried_max} AND `age_maximum` <= " . ($settings["age_limit_senior"] * 12); } elseif ($queried_max > $settings["age_limit_min"] * 12) { $max_query .= " `age_maximum` <= {$queried_max}"; } } if ($min_query || $max_query) { $query .= $options ? " AND" : " WHERE"; if ($min_query && $max_query) { $query .= " ({$min_query} AND {$max_query})"; } elseif ($min_query) { $query .= $min_query; } elseif ($max_query) { $query .= $max_query; } } /** * Set the order */ $query .= " ORDER BY `title` ASC, `code` ASC"; /** * Log the query */ if (WP_DEBUG_LOG) { error_log($query); } /** * Get all cached activities from the database */ return $wpdb->get_results($query); } 
