so I’ve tried creating a WordPress plugin that searches data in a different MySQL database and it works when I search by license number but when I search for any kind of name I get sent to a “Page not found” page and I can’t figure out how to fix it.
This is the important part of the plugin:
<div class="search">
<form class="search-form" method="get" action="">
<input type="text" name="name" placeholder="Enter Name">
<input type="text" name="license_number" placeholder="Enter License Number">
<input type="submit" value="Search">
</form>
<?php
if (isset($_GET['name']) || isset($_GET['license_number'])) {
$name = sanitize_text_field($_GET['name']);
$license_number = sanitize_text_field($_GET['license_number']);
// Construct the search query
$conditions = [];
if (!empty($name)) {
$conditions[] = $second_db->prepare("name LIKE %s", "%{$name}%");
}
if (!empty($license_number)) {
$conditions[] = $second_db->prepare("license_number LIKE %s", "%{$license_number}%");
}
if (empty($conditions)) {
echo '<p>Please enter a search query.</p>';
return ob_get_clean();
}
$where_clause = implode(' OR ', $conditions);
// Debug: output the constructed query
error_log("Constructed query: SELECT * FROM licenses WHERE {$where_clause}");
// Query the second database for license results
$license_results = $second_db->get_results("SELECT * FROM licenses WHERE {$where_clause}");
// Debug: output the number of license results found
error_log("Number of license results: " . count($license_results));
// If searching by license number, use the name from the license result to search applications and courses
if (!empty($license_number) && !empty($license_results)) {
$name = $license_results[0]->name;
}
// Query the second database for application and course results using the name
$application_results = $second_db->get_results($second_db->prepare("SELECT * FROM applications WHERE name LIKE %s", "%{$name}%"));
$course_results = $second_db->get_results($second_db->prepare("SELECT * FROM courses WHERE name LIKE %s", "%{$name}%"));
// Debug: output the number of application and course results found
error_log("Number of application results: " . count($application_results));
error_log("Number of course results: " . count($course_results));
// Display results
if ($license_results || $application_results || $course_results) {
?>
<div class="search-results">
<?php
if ($license_results) {
?>
<h3>Licenses</h3>
<table>
<thead>
<tr>
<th>Name</th>
<th>License Type and Level</th>
<th>License Number</th>
<th>Last Issued Date</th>
<th>Expiration Date</th>
<th>BPAT Skills</th>
<th>CE Hours</th>
</tr>
</thead>
<tbody>
<?php foreach ($license_results as $result) : ?>
<tr>
<td><?php echo esc_html($result->name); ?></td>
<td><?php echo esc_html($result->license_type_and_level); ?></td>
<td><?php echo esc_html($result->license_number); ?></td>
<td><?php echo esc_html($result->last_issued_date); ?></td>
<td><?php echo esc_html($result->expir_date); ?></td>
<td><?php echo esc_html($result->bpat_skills); ?></td>
<td><?php echo esc_html($result->ce_hours); ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<div class="page-break"></div>
<?php
}
if ($application_results) {
?>
<h3>Applications</h3>
<table>
<thead>
<tr>
<th>Name</th>
<th>Program</th>
<th>Type and Level</th>
<th>Application Type</th>
<th>Application Status</th>
<th>Application Review Date</th>
<th>Application Expire Date</th>
<th>Deficiency Letter Date</th>
<th>Total Hours</th>
</tr>
</thead>
<tbody>
<?php foreach ($application_results as $result) : ?>
<tr>
<td><?php echo esc_html($result->name); ?></td>
<td><?php echo esc_html($result->program); ?></td>
<td><?php echo esc_html($result->type_and_level); ?></td>
<td><?php echo esc_html($result->app_type); ?></td>
<td><?php echo esc_html($result->app_status); ?></td>
<td><?php echo esc_html($result->app_review_date); ?></td>
<td><?php echo esc_html($result->app_exp_date); ?></td>
<td><?php echo esc_html($result->deficiency_letter_date); ?></td>
<td><?php echo esc_html($result->total_hours); ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<div class="page-break"></div>
<?php
}
// Insert suggested courses here
$first_license_number = !empty($license_results) ? $license_results[0]->license_number : null;
$suggested_courses = get_suggested_courses($first_license_number);
if (!empty($suggested_courses)) {
?>
<h3>Suggested Courses <span style="font-size: 65%;">for License: <?php echo esc_html($first_license_number); ?></span></h3>
<table>
<thead>
<tr>
<th>Course Title</th>
<th>Start Date</th>
<th>Location</th>
<th>Register</th>
</tr>
</thead>
<tbody>
<?php foreach ($suggested_courses as $event) :
// Check if the necessary properties exist before accessing them
$course_title = isset($event->name) ? $event->name : 'N/A';
$summary = isset($event->summary) ? $event->summary : 'N/A';
$start_date = isset($event->startDate) ? (new DateTime($event->startDate))->format('m/d/Y') : 'N/A';
$location = isset($event->location) ? $event->location : 'N/A';
$register_url = isset($event->formUrl) ? $event->formUrl : '#';
?>
<tr>
<td><?php echo esc_html($course_title); ?></td>
<td><?php echo esc_html($start_date); ?></td>
<td><?php echo esc_html($location); ?></td>
<td><a href="<?php echo esc_url($register_url); ?>" target="_blank">Click Here to Register</a></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<div class="page-break"></div>
<?php
}
if ($course_results) {
?>
<h3>Courses</h3>
<table>
<thead>
<tr>
<th>Name</th>
<th>Program</th>
<th>Course Title</th>
<th>Course Code</th>
<th>Hours</th>
<th>Date</th>
<th>Provider</th>
</tr>
</thead>
<tbody>
<?php foreach ($course_results as $result) : ?>
<tr>
<td><?php echo esc_html($result->name); ?></td>
<td><?php echo esc_html($result->program); ?></td>
<td><?php echo esc_html($result->course_title); ?></td>
<td><?php echo esc_html($result->course_code); ?></td>
<td><?php echo esc_html($result->hours); ?></td>
<td><?php echo esc_html($result->date); ?></td>
<td><?php echo esc_html($result->provider); ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<div class="page-break"></div>
<?php
}
?>
</div>
<?php
} else {
echo '<p>No results found.</p>';
}
}
?>
</div>
<?php
return ob_get_clean();
}
?>
This is what the results should look like:
This is what I get when I search by any kind of name:
The field in the database is: “name” and the name format in the field is: “LastName, FirstName Middle Initial(if available)” or “Doe, John J”.
My error log is inconsistent with updating so that hasn’t been helpful.
Thank you for any help!