Asked  7 Months ago    Answers:  5   Viewed   31 times

I have an html table that loads everything in a mySQL database table. I have dropdowns that relate to columns of that mySQL table - when the user selects one of the dropdowns it uses AJAX to query the database.

I need to figure out how to build the query dynamically because sometimes the dropdowns will be empty (i.e. they don't want to filter by that column).

What is the best way to do this?

Currently I have something like this:

    $stationFilter = $_GET['station'];
    $verticalFilter = $_GET['vertical'];
    $creativeFilter = $_GET['creative'];
    $weekFilter = $_GET['week'];    

    $result = mysql_query("SELECT * FROM $tableName WHERE STATION_NETWORK = '$stationFilter' AND VERTICAL = '$verticalFilter' AND CREATIVE = '$creativeFilter'  AND WK = '$weekFilter'");   
    $data = array();
    while ($row = mysql_fetch_row($result) )
        {
        $data[] = $row;
        }   
    $finalarray['rowdata'] = $data;

Which you can imagine doesn't work because if any of those fields are empty - the query fails (or returns nothing, rather).

Obviously creating such a 'static' query like that really makes it difficult if certain variables are empty.

What is the best way to dynamically create that query so that it only enters the ones that are not empty get added to the query so it can successfully complete and display the appropriate data?

 Answers

23

Just check if the variables contain a value and if they do, build the query like so:

unset($sql);

if ($stationFilter) {
    $sql[] = " STATION_NETWORK = '$stationFilter' ";
}
if ($verticalFilter) {
    $sql[] = " VERTICAL = '$verticalFilter' ";
}

$query = "SELECT * FROM $tableName";

if (!empty($sql)) {
    $query .= ' WHERE ' . implode(' AND ', $sql);
}

echo $query;
// mysql_query($query);
Wednesday, March 31, 2021
 
Tucker
answered 7 Months ago
57

This is what you need. We need set time for ajax auto reload. Don't put everything in one page. Because you must reload page to refresh data. That is bad solution.

Call jQuery Ajax Request Each X Minutes

Wednesday, March 31, 2021
 
saad
answered 7 Months ago
21

That sounds like something that jQuery would excel at:

<script type='text/javascript' src='jquery-1.4-min.js'></script>
<script type="text/javascript">
    $(document).ready(function() {
        document.title = $('h2:first').text();
    });
</script>

To modify the meta data, you would do more of the same. I strongly recommend jQuery - Novice to Ninja as an amazing way to get into great depth with jQuery.

<html>
<head>
<meta description="" />
<script type="text/javascript" src="jquery.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('meta:first').attr('description', $('h2:first').text());
        alert($('meta:first').attr('description'));
    });
</script>
</head>
<body>
<h2>Testing 123</h2>
</body>
</html>
Saturday, May 29, 2021
 
devo
answered 5 Months ago
94

You're missing quotes around your string value:

$result = $dbact->interact("UPDATE person SET first_name = ".$first_name." WHERE idperson = ".$user_id, true);
                                                         ^^^^^^^^^^^^^^^^^^^
                                                                HERE

Should be:

$result = $dbact->interact("UPDATE person SET first_name = '".$first_name."' WHERE idperson = ".$user_id, true);

FYI, htmlentities() does not protect you against SQL injections. Your code is vulnerable.

Saturday, May 29, 2021
 
IcedAnt
answered 5 Months ago
58

There are some mistakes in this code, let me help you line by line.

echo "<td> <img id='tblimg' 
onclick='like('" . $row['Username'] . "');' 
src='like.jpg' alt='like/dislike image' 
width='80px' height='30px'></td>";

The javascript function is:

Escape your quotes for the onclick event first

    function like(user) 
    {

        $.ajax({
            url: "update.php",
            type: "POST",
            data: { 'username': user, 'liked': '1' },                   
            success: function()
                        {
                            alert("ok");                                    
                        }
        });
    }

add { and } to the ajax call

Remove the quotes from table name and fields

$sql = "UPDATE followers SET Liked = '$Liked' WHERE Username = '$Username'";

in ajax success and after the function begins, you can always print a message to see if your function is being called, and if php script is returning some error, use an alert for that

UPDATE

success: function(data){
   alert(data); // this will print you any php / mysql error as an alert                                    
}

UPDATE 2

Write your onclick option like this.

echo "<img onclick="like('" . $row['Username']. "');" 
src='like.jpg' alt='like/dislike image' 
width='80px' height='30px' />";
Thursday, August 5, 2021
 
Bitmap
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :