Get data from database using php,ajax

Question

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,

Answer 1

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]);

Answer 2

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.

Answer 3

The single table looks OK.

(The rest of this Answer is not complete, 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 comfortable in.