php

mysql

sql

ajax

Create a dynamic mysql query using php variables

Viewed 185 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

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

mjdprv-703f32f472f9

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

kleopatra

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>

Thursday, December 8, 2022

thomasc

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

albertamg

JDBI is not very well suited for constructing dynamic queries. IMO the whole point of this library is to separate code and SQL queries as much as possible.

However, your particular case might be solved by means of SQL:

COALESCE(:foo, foo)

if 'foo' is the name of the column in the table, and :foo will resolve to NULL, then mysql SET will be effectively

SET foo=foo

i.e. it will do nothing (which is desired beaviour in your case). If :foo is not null, it will be equivalent to

SET foo=:foo

Sunday, August 7, 2022

elis_jones

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 :

php

mysql

sql

ajax

Create a dynamic mysql query using php variables

Answers

php,mysql,sql,ajax