Asked  2 Years ago    Answers:  5   Viewed   180 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

2

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, November 9, 2022
4

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

Sunday, December 11, 2022
 
2

Note Your question title is inconsistent with your example. In the title you ask for column 1 as the key, but your example uses column 2 as the key. I've used column 2 here...

It isn't clear what MySQL API you are using to fetch, but whichever it is, use the associative fetch method and create new array keys using the pattern $arraytable[$newkey] = $newvalue. This example would be in object-oriented MySQLi:

$arraytable = array();

while($row = $result->fetch_assoc()) {
  // Create a new array key with the 'link' and assign the 'state'
  $arraytable[$row['link']] = $row['state'];
}
Saturday, September 3, 2022
3

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.

Monday, December 12, 2022
 
1

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' />";
Friday, August 12, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages