i just started developing website and i new to wordpress,php,mysql,jquery, hopefully someone can kind enough to help me solve the below problem which i have tried to resolve for months, thank you
Objective:To enable users to type in job title in a search box and search box would auto populate similar job titles from database in drop down just like google search
Problem: Search bar Drop down keep populating all job titles in Database table regardless of what text typed in search bar
Environment: Custom page on Wordpress,XAMPP,php,html,MYSQL database name 'jobsdatabase'
Developer's programming competency: Beginner, just started developing website for 5 months, have tried to solve this problem for 6 weeks
Relevant Files and location:
C:\xampp\htdocs\wordpress\wp-admin\admin-ajax.php
C:\xampp\htdocs\wordpress\wp-content\themes\twentysixteen\page_search_job_form V6.php
C:\xampp\htdocs\wordpress\wp-content\themes\twentysixteen\functions.php
Following is the code for my wordpress custom search page----> page_search_job_form V6.php
<?php
get_header();
?>
<!DOCTYPE html>
<html>
<head>
<style>
ul{
background-color:#eee;
cursor:pointer;
}
li{
padding:12px;
}
</style>
<script type="text/javascript" >
jQuery.noConflict();
jQuery(document).ready(function()
{
jQuery('#searchform_jobtitle').keyup(ajaxsubmit);
function ajaxsubmit()
{
var searchform_jobtitle = jQuery(this).val();
if (searchform_jobtitle.length >= 3)
{
jQuery.ajax({
url:'/wordpress/wp-admin/admin-ajax.php',
method:"POST",
data:{'action':'searchform_jobtitle'},
success:function(data)
{
console.log(data);
jQuery("#jobtitle").fadeIn();
jQuery("#jobtitle").html(data);
},
error:function(errorThrown)
{
console(errorThrown);
}
});
}
}
jQuery(document).on('click', 'li', function(){
jQuery('#searchform_jobtitle').val(jQuery(this).text());
jQuery('#jobtitle').fadeOut();
});
});
</script>
</head>
<body>
<form action="/" form type="post" id="searchform_jobtitle1" style="width:500px;">
<input type="text" id="searchform_jobtitle" name="s" placeholder="Search...">
</form>
<div id="jobtitle"></div>
</body>
</html>
Following is the code that i included in wordpress function.php
add_action('wp_ajax_searchform_jobtitle','searchform_jobtitle');
add_action('wp_ajax_nopriv_searchform_jobtitle','searchform_jobtitle');
function searchform_jobtitle()
{
$search_jobtitle = ucfirst($_POST["action"]);
$con = mysqli_connect('127.0.0.1','root','');
if(!$con)
{
echo 'Not connected to server';
$con->print_error();
}
if(!mysqli_select_db($con,'jobsdb'))
{
echo 'Database not selected';
$con->print_error();
}
if(isset($_POST["action"]))
{
$output = '';
$query = "SELECT * FROM job_info WHERE job_title like '%".$_post["action"]."%' ";
$result = mysqli_query($con, $query);
$output = '<ul class="list-unstyled">';
if(mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_array($result))
{
$output .= '<li>'.$row["job_title"].'</li>';
}
}
else
{
$output .= '<li>Job Not Found</li>';
}
$output.= '</ul>';
echo $output;
mysqli_close($con);
die();
}
}
You've chosen a complex feature for a beginner!
Where to start troubleshooting this? Start with this line of php:
$query = "SELECT * FROM job_info WHERE job_title like '%".$_post["action"]."%' ";
1. I think $_post should be uppercase $_POST.
2. I know you have a browser side limit suppressing AJAX requests when the user hasn't pushed more than three letters. But put that same limit in your php program. If your action variable is empty you'll get this query; I think that's why you get all the rows of your table back in the autocomplete.
SELECT * FROM job_info WHERE job_title like '%%'
3. What if your user types junk'; DROP TABLE job_info; -- ' into your search box? In that case the SQL that gets executed will be
SELECT * FROM job_info WHERE job_title like '%junk'; DROP TABLE job_info; --'%'
It seems unlikely that's what you want. That's called SQL injection. You need to deal with it before your project goes live on the internet.