Filter SQL with button

Question

I'm making a website that lets you submit information into a form and view it on a separate page. On that page, I want the user to be able to filter the data by language. A button with a dropdown menu selects a language:

    <button class="btn btn-default dropdown-toggle" type="button" data-toggle="dropdown">
      Select a language <span class="caret"></span>
    </button>

    <ul class="dropdown-menu">
      <li><a class="selectlang" id="arabic" data-language="arabic" href="#">Arabic</a></li>
      ...
      ...
      <li><a class="selectlang" id="spanish" data-language="spanish" href="#">Spanish</a></li>
    </ul>

  </div>

This is the jQuery I have to do this:

          $(".selectlang").click( function() {
            var language = $(this).data('language'); //this gets the selected language from the dropdown. I want to use this var in the PHP to complete the query "SELECT * FROM slangdata WHERE language = ?"

            $.ajax({
              url:  "test.php",
              type: "post",
              data: language,
              success: function() {
                $("#fill").load("test.php");
              }
            });
          });

On the PHP side (test.php), I really have no idea where to start. I found this page that I think might be helpful (PHP: Filtering SQL query with select options) but I don't know how to begin to implement it.

This is what I have written so far — it's copied from another example which uses a function to sanitize the PHP and originally had a table, so I tried to convert it to work with my code but I really have no idea what I'm doing so it's just a mess.

<?php include 'database.php'; ?>


<?php
function sanitizeMySQL($conn, $var) {
    $var = strip_tags($var);
    $var = mysqli_real_escape_string($conn, $var);
    return $var;
}
if ( isset($_POST['language']) ) {
?>

<?php
    $language = sanitizeMySQL($conn, $_POST['language']);
    $query = "SELECT * FROM slangdata WHERE language = ?";
    // code runs only if the statement was prepared
    if ($stmt = mysqli_prepare($conn, $query)) {
        mysqli_stmt_bind_param($stmt, 's', $language); 
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $id, $language, $word, $pronunciation, $translation, $notes, $example, $nsfw);
        // handle the data we fetched with the SELECT statement ...
        while (mysqli_stmt_fetch($stmt)) {

                printf ("<div class='%s'>", $nsfw);
                printf ("<p><span class='word'>%s</span>", stripslashes($word));
                printf ("<span class='pronounce'> %s </span></p>", stripslashes($pronunciation));
                printf ("<p class='translation'>%s</p>", stripslashes($translation));
                printf ("<p id='notes'>%s</p>", stripslashes($notes));
                printf ("<p id='ex'>%s</p>", stripslashes($example));
                printf ("<p class='%s'></p><hr></div>", $nsfw);
        } // end while-loop

        mysqli_stmt_close($stmt);
    }
    mysqli_close($conn);
} else {
?>


<p>Error</p>


<?php
}  // end of if-else isset
?>

(Alternatively, I have this code, which I think could replace the while loop I already have — not sure which is better):

        <?php while( $row = mysqli_fetch_assoc($run)) :  ?>

        <div class="<?php echo stripslashes($row['nsfw']); ?> entry <?php echo stripslashes($row['language']); ?>">
          <p><span class="word"><?php echo stripslashes($row['word']); ?></span> <span class="pronounce"> <?php echo stripslashes($row['pronunciation']); ?> </span></p>
          <p class="translation"><?php echo stripslashes($row['translation']); ?></p>
          <p id="notes"><?php echo stripslashes($row['notes']); ?></p>
          <p id="ex"><?php echo stripslashes($row['example']); ?></p>
          <p class="<?php echo stripslashes($row['nsfw']); ?>"></p>
        </div>

         <?php endwhile;  ?>

Right now, when I pick a language it just comes back with error. I'm so sorry this is a really long post but I've been stuck on this for two days and really have no idea what to do. How can I write the PHP to make it do what I want?

Edit: deleted comments

"when I pick a language it just comes back with error." - Being what exactly? — Funk Forty Niner
– Funk Forty Niner, Commented Jan 31, 2016 at 2:51
To show detailed errors on web pages, add error_reporting(E_ALL); ini_set('display_errors', 'on'); at beginning of your code. Otherwise, can check your apache error log. — fusion3k
– fusion3k, Commented Jan 31, 2016 at 2:54
@Fred-ii- I mean it's running the "else" part of my PHP that tells it to display <p>Error</p> — Adriana Barbat
– Adriana Barbat, Commented Jan 31, 2016 at 4:20

Asad Ali · Accepted Answer · 2016-01-31 05:14:28Z

0

On client side you can use like this

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<select id="combo">
  <option value="english">English</option>
  <option value="arabic">Arabic</option>
</select>
<input id="button1" type="button" value="Click Me!" />

and jquery as like this

$(document).ready(function(){ 
  $('#button1').click(function(){     
    $.post("somepage.php",
    {
        language: $('#combo :selected').text(),
    },
    function(data, status){
        alert("Data: " + data + "\nStatus: " + status);
    });

  });
});

and in Server Side Code you can use this

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";        
    $conn = new mysqli($servername, $username, $password, $dbname);
    $language = $_POST['language'];
    $sql = "SELECT * FROM slangdata WHERE language = '$language'";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row or whatever you want to do
    }
?>

answered Jan 31, 2016 at 5:14

Asad Ali

1112 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adriana Barbat Over a year ago

I tried that and it pops an alert saying: localhost says: data: Status: success. Any idea what that means?

fusion3k Over a year ago

@AdrianaBarbat it's the result of your jQuery code (take a look at function(data, status)). Sorry, but you will not get anything by randomly copy-and-paste. Furthermore, it's really hard to learn three languages (javascript, php, sql) at once. I suggest you to take a break and restart from some tutorial (step-by-step, not by copy-and-paste)...

Asad Ali Over a year ago

well it means that my code is running fine - but you need to learn these languages - not copy paste without basic knowledge

trank · Accepted Answer · 2016-01-31 05:17:00Z

0

in your ajax, set language like this: data: { language: language } because the $_POST var need the key language.
in your test.php, just echo the content regarding the language and set it in your success function:

success: function(response) {
    $("#fill").html(response);
}

hope this helps (:

answered Jan 31, 2016 at 5:17

trank

1,06212 silver badges8 bronze badges

Collectives™ on Stack Overflow

Filter SQL with button

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related