javascript - Get data from database using php,ajax - Stack Overflow

admin管理员组

文章数量:1392099

I have a simple section in which I am displaying data from the database, my database looks like this.

Now I have four buttons looks like this

When a user clicks one of the above buttons it displays this

So now when user eg select construction and next select eg Egypt' in the console and clicks buttonconfirmdisplays [855,599075], user can select multiple countries, this works as expected forconstruction ,power,oil`,

Now I want if user eg clicks All available industries button in those four buttons and next select eg Egypt and click confirm it should display the sum of egypt total projects in construction, oil, power sector 855+337+406 =1598 and the sum of total budgets in both sectors 1136173

Here is my solution

HTML

<div id="interactive-layers">
    <div buttonid="43" class="video-btns">
        <span class="label">Construction</span></div>
    <div buttonid="44" class="video-btns">
        <span class="label">Power</span></div>
    <div buttonid="45" class="video-btns">
        <span class="label">Oil</span></div>
    <div buttonid="103" class="video-btns">
        <span class="label">All available industries</span>
    </div>
</div>

Here is js ajax

$("#interactive-layers").on("click", ".video-btns", function(){
    if( $(e.target).find("span.label").html()=="Confirm" ) {

        var selectedCountries = [];

        $('.video-btns .selected').each(function () {
            selectedCountries.push( $(this).parent().find("span.label").html() ) ;
        });

        if( selectedCountries.length>0 ) {
            if(selectedCountries.indexOf("All available countries")>-1) {
                selectedCountries = [];
            }


        } else {

            return;
        }

        var ajaxurl = "";
        if(selectedCountries.length>0) {
            ajaxurl = "data.php";
        } else {
            ajaxurl = "dataall.php";

        }

        $.ajax({
            url: ajaxurl,
            type: 'POST',
            data: {
                    countries: selectedCountries.join(","),
                    sector: selectedSector
            },
            success: function(result){
                console.log(result);
                result = JSON.parse(result);
                $(".video-btns").each(function () {
                    var getBtn = $(this).attr('buttonid');
                    if (getBtn == 106) {
                        var totalProjects = $("<span class='totalprojects'>"+ result[0] + "</span>");
                        $(this).append(totalProjects)
                    }else if(getBtn ==107){
                        var resultBudget = result[1]
                        var totalBudgets = $("<span class='totalbudget'>"+ '&#36m' +" " + resultBudget +"</span>");
                        $(this).append( totalBudgets)
                    }
                });
                return;
              }
        });
    }
});

Here is php to get all dataall.php

$selectedSectorByUser = $_POST['sector'];
 $conn = mysqli_connect("localhost", "root", "", "love");
 $result = mysqli_query($conn, "SELECT * FROM meed");
 $data = array();

 $wynik = [];
$totalProjects = 0;
$totalBudget = 0;

 while ($row = mysqli_fetch_array($result))
 {
    if($row['Sector']==$selectedSectorByUser ) {
     $totalProjects+= $row['SumofNoOfProjects'];
     $totalBudget+= $row['SumofTotalBudgetValue'];
    }
 }
 echo json_encode([ $totalProjects, $totalBudget ] );
exit();
?>

Here is data.php

<?php

$selectedSectorByUser = $_POST['sector'];
$countries = explode(",", $_POST['countries']);

//var_dump($countries);
 $conn = mysqli_connect("localhost", "root", "", "meedadb");
 $result = mysqli_query($conn, "SELECT * FROM meed");
 $data = array();

 $wynik = [];
$totalProjects = 0;
$totalBudget = 0;

 while ($row = mysqli_fetch_array($result))
 {
    if($row['Sector']==$selectedSectorByUser && in_array($row['Countries'],$countries ) ) {
    // array_push($data, $row);
     $totalProjects+= $row['SumofNoOfProjects'];
     $totalBudget+= $row['SumofTotalBudgetValue'];
    }
 }

 // array_push($wynik, $row);
 echo json_encode([ $totalProjects, $totalBudget ] );
//echo json_encode($data);
exit();
?>

Now when the user clicks All available industries btn and selects a country I get [0,0] on the console.

What do I need to change to get what I want? any help or suggestion will be appreciated,

I have a simple section in which I am displaying data from the database, my database looks like this.

Now I have four buttons looks like this

When a user clicks one of the above buttons it displays this

So now when user eg select construction and next select eg Egypt' in the console and clicks buttonconfirmdisplays [855,599075], user can select multiple countries, this works as expected forconstruction ,power,oil`,

Now I want if user eg clicks All available industries button in those four buttons and next select eg Egypt and click confirm it should display the sum of egypt total projects in construction, oil, power sector 855+337+406 =1598 and the sum of total budgets in both sectors 1136173

Here is my solution

HTML

<div id="interactive-layers">
    <div buttonid="43" class="video-btns">
        <span class="label">Construction</span></div>
    <div buttonid="44" class="video-btns">
        <span class="label">Power</span></div>
    <div buttonid="45" class="video-btns">
        <span class="label">Oil</span></div>
    <div buttonid="103" class="video-btns">
        <span class="label">All available industries</span>
    </div>
</div>

Here is js ajax

$("#interactive-layers").on("click", ".video-btns", function(){
    if( $(e.target).find("span.label").html()=="Confirm" ) {

        var selectedCountries = [];

        $('.video-btns .selected').each(function () {
            selectedCountries.push( $(this).parent().find("span.label").html() ) ;
        });

        if( selectedCountries.length>0 ) {
            if(selectedCountries.indexOf("All available countries")>-1) {
                selectedCountries = [];
            }


        } else {

            return;
        }

        var ajaxurl = "";
        if(selectedCountries.length>0) {
            ajaxurl = "data.php";
        } else {
            ajaxurl = "dataall.php";

        }

        $.ajax({
            url: ajaxurl,
            type: 'POST',
            data: {
                    countries: selectedCountries.join(","),
                    sector: selectedSector
            },
            success: function(result){
                console.log(result);
                result = JSON.parse(result);
                $(".video-btns").each(function () {
                    var getBtn = $(this).attr('buttonid');
                    if (getBtn == 106) {
                        var totalProjects = $("<span class='totalprojects'>"+ result[0] + "</span>");
                        $(this).append(totalProjects)
                    }else if(getBtn ==107){
                        var resultBudget = result[1]
                        var totalBudgets = $("<span class='totalbudget'>"+ '&#36m' +" " + resultBudget +"</span>");
                        $(this).append( totalBudgets)
                    }
                });
                return;
              }
        });
    }
});

Here is php to get all dataall.php

$selectedSectorByUser = $_POST['sector'];
 $conn = mysqli_connect("localhost", "root", "", "love");
 $result = mysqli_query($conn, "SELECT * FROM meed");
 $data = array();

 $wynik = [];
$totalProjects = 0;
$totalBudget = 0;

 while ($row = mysqli_fetch_array($result))
 {
    if($row['Sector']==$selectedSectorByUser ) {
     $totalProjects+= $row['SumofNoOfProjects'];
     $totalBudget+= $row['SumofTotalBudgetValue'];
    }
 }
 echo json_encode([ $totalProjects, $totalBudget ] );
exit();
?>

Here is data.php

<?php

$selectedSectorByUser = $_POST['sector'];
$countries = explode(",", $_POST['countries']);

//var_dump($countries);
 $conn = mysqli_connect("localhost", "root", "", "meedadb");
 $result = mysqli_query($conn, "SELECT * FROM meed");
 $data = array();

 $wynik = [];
$totalProjects = 0;
$totalBudget = 0;

 while ($row = mysqli_fetch_array($result))
 {
    if($row['Sector']==$selectedSectorByUser && in_array($row['Countries'],$countries ) ) {
    // array_push($data, $row);
     $totalProjects+= $row['SumofNoOfProjects'];
     $totalBudget+= $row['SumofTotalBudgetValue'];
    }
 }

 // array_push($wynik, $row);
 echo json_encode([ $totalProjects, $totalBudget ] );
//echo json_encode($data);
exit();
?>

Now when the user clicks All available industries btn and selects a country I get [0,0] on the console.

What do I need to change to get what I want? any help or suggestion will be appreciated,

• javascript
• php
• jquery
• mysql
• ajax

Share Improve this question Follow edited Jul 21, 2019 at 17:55 The Dead Man asked Jul 17, 2019 at 19:30 The Dead ManThe Dead Man 5,5763333 gold badges125125 silver badges226226 bronze badges 7

• phpMyAdmin is a database front-end, not a database. You don't get data from it. – tadman Commented Jul 17, 2019 at 19:35
• 1 Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface where missing a single i can cause trouble. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is largely an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code. – tadman Commented Jul 17, 2019 at 19:35
• 1 If you want to get data from multiple tables, either do multiple queries or use a JOIN. As a note, when dealing with countries it's often best to use numerical identifiers or standardized codes to avoid ambiguity or spelling errors. ISO-3166 specifies codes for every country. – tadman Commented Jul 17, 2019 at 19:36
• 2 update your question and add the expected result .. (use text ..not image) – ScaisEdge Commented Jul 17, 2019 at 19:36
• updated the question check out with bount 100 – The Dead Man Commented Jul 19, 2019 at 23:49

 |  Show 2 more ments

3 Answers 3

Sorted by: Reset to default 6 +100

in you dataAll.php

If you have select All available industries
you shold not check for sector because you need all sector (eventually you should check for countries ) so you should avoid the check for this condition

<?php

$conn = mysqli_connect("localhost", "root", "", "love");
$result = mysqli_query($conn, "SELECT * FROM meed");
$data = [];

$wynik = [];
$totalProjects = 0;
$totalBudget = 0;

while ($row = mysqli_fetch_array($result)) {
    $totalProjects += $row['SumofNoOfProjects'];
    $totalBudget += $row['SumofTotalBudgetValue'];
}
echo json_encode([$totalProjects, $totalBudget]);

You can use the SQL JOIN operator, or in this case an implicit join would be cleanest:

$result = mysqli_query($conn, "SELECT * FROM construction, power, oil_and_gas, industrial WHERE construction.Countries = power.Countries AND power.Countries = oil_and_gas.Countries AND oil_and_gas.Countries = industrial.Countries");

You need the WHERE conditions so it knows how the rows of each different table are related to each other. You can shorten it a bit with aliases for the tables:

$result = mysqli_query($conn, "SELECT * FROM construction as C, power as P, oil_and_gas as G, industrial as I WHERE C.Countries = P.Countries AND P.Countries = G.Countries AND G.Countries = I.Countries");

In this case, however, I think you may want to consider changing the structure of your database. It seems like you repeat columns quite a bit across them. Perhaps these can all be in a single table, with a "type" column that specifies whether it's power, construction, etc. Then you can query just the one table and group by country name to get all your results without the messy joins across 4 tables.

The single table looks OK.

(The rest of this Answer is not plete, but might be useful.)

First, let's design the URL that will request the data.

.../foo.php?industry=...&country=...

But, rather than special casing the "all" in the client, do it in the server. That is, the last button for industry will generate

    ?industry=all

and the PHP code will not include this in the WHERE clause:

    AND industry IN (...)

Similarly for &country=all versus &country=egypt,iran,iraq

Now, let me focus briefly on the PHP:

$wheres = array();

$industry = @$_GET['industry'];
if (! isset($industry)) { ...issue error message or use some default... }
elseif ($industry != 'all') {
    $inds = array();
    foreach (explode(',', $industry) as $ind) {
        // .. should test validity here; left to user ...
        $inds[] = "'$ind'";
    }
    $wheres[] = "industry IN (" . implode(',', $inds) . )";
}

// ... repeat for country ...

$where_clause = '';
if (! empty($wheres)) {
    $where_clause = "WHERE " . implode(' AND ', $wheres);
}
// (Note that this is a generic way to build arbitrary WHEREs from the data)

// Build the SQL:

$sql = "SELECT ... FROM ... 
           $where_clause
           ORDER BY ...";
// then execute it via mysqli or pdo (NOT mysql_query)

Now, let's talk about using AJAX. Or not. There were 2 choices:

• you could have had the call to PHP be via a GET and have that PHP display a new page. This means that PHP will be constructing the table of results.
• you could have used AJAX to request the data. This means that Javascript will be constructing the data of results.

Which choice to pick probably depends on which language you are more fortable in.

本文标签： javascriptGet data from database using phpAJAXStack Overflow