Ajax: 500 Internal Server Error

Question

I am using ajax to get events from my database. Retrieving the results does not work, nothing is displayed, and in the console I get the following error message:

POST http://www.example.com/system/live_filter.php 500 (Internal Server Error) jquery.min.js:4

Here is my HTML/JS:

<div id="results">

<script type="text/javascript">

    // 1. When user comes on page from homepage, results will be fetched with ajax
    function updateResults() { 

        // 2. Create array with values of all filter fields
        var value_town_id = $('#town_id').val();
        var value_type = $('#filter_type').val();
        var value_date = $('#filter_date').val();
        var array_filter_values = new Array(value_town_id, value_type, value_date);
        array_filter_values.join(', ');
        query_value = array_filter_values;

    // 3. Start ajax
    $.ajax({
            type: "POST",
            url: "system/live_filter.php",
            data: { query: query_value },
            cache: false,
            success: function(html){
                $("#results").html(html);
            }
        });

    };

    // 4. FIRE FUNCTION!
    updateResults();

</script>

</div>

Here is my live_filter.php to which the values are sent via Ajax:

require_once 'db.php';

// Define Output HTML Formating
$html = '';
$html .= '<div class="event">';
$html .= '<h3>titleString</h3>';
$html .= '<p>typeString</p>';
$html .= '<p>dateString</p>';
$html .= '</div>';

// Get values
$values_string = $_POST['query'];

// Explode to array
$values_array = explode(',', $values_string);
$town_id = $values_array[0];
$type = $values_array[1];
$date = $values_array[2];

// Prepare values for database results query
$town_id = $db->real_escape_string($town_id);
$type = $db->real_escape_string($type);
$date = $db->real_escape_string($date);


// Build Query
$query = "SELECT * FROM events WHERE towns_id=$town_id AND type='$type' AND date>=$date";

// Do Search
$results = $db->query($query);
while($result = $results->fetch_assoc()) {

            // Format Output Strings And Hightlight Matches
            $display_title = $result['title'];
            $display_type = $result['type'];
            $display_date = $result['date'];

            // Insert title
            $output = str_replace('titleString', $display_title, $html);
            // Insert type
            $output = str_replace('typeString', $display_type, $output);
            // Insert date
            $output = str_replace('dateString', $display_date, $output);

            // Output
            echo($output);
}

Anyone has an idea where the problem is?

I get the following error message: 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 'AND type='' AND date>=''' at line 1

Answer 1

Can you print the Post value if the variable $town_id is empty then only sql generate error

SELECT * FROM events WHERE towns_id = AND type='$type' AND date>='$date'

in case if you want to execute the query with towns_id is empty. append single with all variables

SELECT * FROM events WHERE towns_id = '$town_id' AND type='$type' AND date>='$date'

Answer 2

Let's tidy this up and simplify it a bit...

function updateResults() { 
    query = {"town_id": $('#town_id').val(),
             "value_type": $('#filter_type').val(),
             "value_date": $('#filter_date').val()
            }

    $.ajax({
        type: "POST",
        url: "system/live_filter.php",
        data: query,
        cache: false,
        success: function(html){
            $("#results").html(html);
        }
    });

};

and then in the PHP:

require_once 'db.php';

// Define Output HTML Formating
$html = '';
$html .= '<div class="event">';
$html .= '<h3>titleString</h3>';
$html .= '<p>typeString</p>';
$html .= '<p>dateString</p>';
$html .= '</div>';

$town_id = $_REQUEST['town_id'];
$type = $_REQUEST['value_type'];
$date = $_REQUEST['value_date'];

// Prepare values for database results query
$town_id = $db->real_escape_string($town_id);
$type = $db->real_escape_string($type);
$date = $db->real_escape_string($date);


// Build Query
$query = "SELECT * FROM events WHERE towns_id='$town_id' AND type='$type' AND date>='$date'";

/*Should it definitely be towns_id and not town_id?*/

// Do Search
$results = $db->query($query);
while($result = $results->fetch_assoc()) {
            // Insert title
            $output = str_replace('titleString', $result['title'], $html);
            // Insert type
            $output = str_replace('typeString', $result['type'], $output);
            // Insert date
            $output = str_replace('dateString', $result['date'], $output);

            // Output
            echo($output);
}

Of course if you're willing to move the template, it gets even simpler...

require_once 'db.php';

$town_id = $db->real_escape_string($_REQUEST['town_id']);
$type = $db->real_escape_string($_REQUEST['value_type']);
$date = $db->real_escape_string($_REQUEST['value_date']);

$query = "SELECT * FROM events WHERE towns_id='$town_id' AND type='$type' AND date>='$date'";

$results = $db->query($query);
while($result = $results->fetch_assoc()) {
    $html  = '<div class="event">';
    $html .= '<h3>{$result['title']}</h3>';
    $html .= '<p>{$result['type']}</p>';
    $html .= '<p>{$result['date']}</p>';
    $html .= '</div>';
    echo $html;
}

As to why it's erroring...

Firstly, are you sure the variables are being populated? The error you gave in comments would occur if town_id was missing. Since you're not quoting that field, it would result in broken SQL. It also makes the escaping pointless as the output expects to be in quotes.

I'd also check that the format of the date coming from your form is one that your database understands...

Try changing the PHP to be as follows:

require_once 'db.php';

$town_id = $db->real_escape_string($_REQUEST['town_id']);
$type = $db->real_escape_string($_REQUEST['value_type']);
$date = $db->real_escape_string($_REQUEST['value_date']);

$query = "SELECT * FROM events WHERE towns_id=$town_id AND type='$type' AND date>=$date";

echo $query;

Then take the SQL it gives you and copy/paste it into your database admin tool and see what happens. Once you've fixed the syntax errors there, you'll know how to fix the query in your PHP