Fetch data from database in JS file

Question

I've created a tag input for my user in my site, for that purpose I coded a tag function with dropdown help. So my problem is that, I want to fetch data from data base in JavaScript file.
Js

var FormSamples = function () {


    return {
        //main function to initiate the module
        init: function () {

            // use select2 dropdown instead of chosen as select2 works fine with bootstrap on responsive layouts.
            $('.select2_category').select2({
                placeholder: "Select an option",
                allowClear: true
            });

            $('.select2_sample1').select2({
                placeholder: "Select a State",
                allowClear: true
            });

            $(".select2_sample2").select2({
                placeholder: "Type to select an option",
                allowClear: true,
                minimumInputLength: 1,
                query: function (query) {
                    var data = {
                        results: []
                    }, i, j, s;
                    for (i = 1; i < 5; i++) {
                        s = "";
                        for (j = 0; j < i; j++) {
                            s = s + query.term;
                        }
                        data.results.push({
                            id: query.term + i,
                            text: s
                        });
                    }
                    query.callback(data);
                }

            });
            function format(item) {
                opt = $(item.element);
                sel = opt.text();
                og = opt.closest('optgroup').attr('label');
                return og+' | '+item.text;

                }
                $("select").select2({
                formatSelection: format,
                escapeMarkup: function(m) { return m; }
            });


            $(".select2_sample3").select2({
                tags: ['Karachi','Lahore']
            });

        }

    };

}();

In the end of JS file you'll see:

$(".select2_sample3").select2({
     tags: ['Karachi','Lahore']
});

Instead of "Karachi","Lahore" I want to fetch tags from data base.
I am fetching data like this:

    $conn = mysqli_connect($servername, $username, $password, $dbname);
    $sql = "SELECT * FROM tags";
    $result = mysqli_query($conn, $sql);
    mysqli_query ($conn,"set character_set_results='utf8'"); 
    $row = mysqli_fetch_assoc($result);

Any body please help me that how can I fetch data in JS by PHP.

Answer 1

You can use json_encode in php:

$ar = array('Karachi','Lahore');
echo json_encode($ar);

and in javascript:

<script type="text/javascript">
// pass PHP variable declared above to JavaScript variable
var ar = <?php echo json_encode($ar) ?>;
</script>

output:

['Karachi','Lahore']

Answer 2

You are almost there. Now you can access the relevant data members of $row by selecting based on column name. For example you can look at the value of ˚$row["id"]. Also fetch_assoc type functions work row by row, so you will have to run it for each row, not each column, when you have multiple results. You can store the results in a php array but you will have to output them to the javascript portion of your file, or store them in a file javascript can access, before ending the php portion of your script. Below I write a little about each of your options.

1. Save data obtained form php data query to csv file, from which javascript can read.

Take a look at this SO post to learn how you can read data from csv. So in this example, your php script could read data from a database and then generate, via php, a csv file. Your javascript could then read from the csv file.

2. Alternately you can write from php directly to the javascript encoded in the same page assuming you can use your script in the same web page and not as a .js include. In that case, you can use json_encode to print your php arrays to javascript arrays as described in this SO post.

For example, to create two arrays accessible in Javascript of cities and countries I would do this:

   <?php
    ...

    $city = array();
    $country = array();
    while($row = mysqli_fetch_assoc($result)){
       array_push($city, $row['city']);
       array_push($country, $row['country']);
    }
    ...?>

    <script>
       var city = <?php echo json_encode($city); ?>;
       var country = <?php echo json_encode($country); ?>;
     ...
    </script>

Here I am assuming you stored the data in a database with column names 'city' and 'country'

You should also consider using PDO objects for safer SQL manipulation.