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,

phpMyAdmin is a database front-end, not a database. You don't get data from it. — tadman
– tadman, Commented Jul 17, 2019 at 19:35
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
– tadman, Commented Jul 17, 2019 at 19:35
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
– tadman, Commented Jul 17, 2019 at 19:36
update your question and add the expected result .. (use text ..not image) — ScaisEdge
– ScaisEdge, Commented Jul 17, 2019 at 19:36

ScaisEdge · Accepted Answer · 2019-07-24 05:20:36Z

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

edited Jul 24, 2019 at 5:20

answered Jul 20, 2019 at 16:15

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

2 Comments

The Dead Man Over a year ago

Hi scaris stil when I click a button All available industries and click algeria and clicking confirm I get [0,0] :( am about to give up now but when I select a another sector eg construction or power or oil I get the result I want :( except for the all available industries btn :(

The Dead Man Over a year ago

And what about ajax? maybe I need to add if the condition for All available industries? because the dataall.php I am using to get data if a user at the end clicks a button All available countries then the dataall.php is called and it returns me exactly what I want,

IceMetalPunk · Accepted Answer · 2019-07-17 19:37:07Z

4

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.

answered Jul 17, 2019 at 19:37

IceMetalPunk

5,6074 gold badges21 silver badges29 bronze badges

7 Comments

The Dead Man Over a year ago

Hi thanks, I will check out, IceMetalPunk I wish I could be able to make this as a single table but I can't figure out how for what I have do you think is possible to have a single table?

IceMetalPunk Over a year ago

Absolutely, it's possible. Like I suggested, these all have the same three columns: Countries, Sum of No of Projects, and Sum of Total Budget Value ($m). So if you just create a table with those three columns, plus a "Type" column, you can put all the data in that one table, with "Type" specifying power, construction, etc. as needed. (Side note: column names really shouldn't have spaces or special characters like parentheses in them; it could cause code problems later if you're not very careful. Maybe change them to Countries, Project_Total, and Budget_Sum_M?)

The Dead Man Over a year ago

I tried different combination but :( If you don't mind can you show me in excel??? really I tried I couldn't figure out

Dharman Over a year ago

Use of implicit joins in SQL is not recommended. You should use JOIN ON clause

IceMetalPunk Over a year ago

@Dharman As long as you're including a sufficient WHERE clause to ensure the join is as expected, is there any reason not to use implicit joins?

|

Rick James · Accepted Answer · 2019-07-20 05:08:36Z

1

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.

answered Jul 20, 2019 at 5:08

Rick James

144k15 gold badges144 silver badges255 bronze badges

9 Comments

The Dead Man Over a year ago

Hi thanks for your answer but am confused little bit I have two php files data.php and dataall.php do I need to add another php file like allindsturies.php, ? am not good in php, can you please put your answer in a clear way , that is why I added a bount of 100 because I needed a clear answer ,

Rick James Over a year ago

@user9964622 - Only 1 php file for handling industries (all or some) and countries (all and some). Note the test for "all". The code in the one .php interprets the url to decide what SQL to generate. I wrote out about half the code.

Dharman Over a year ago

You should not silence PHP errors with the @. Errors and warnings are your friends.

Rick James Over a year ago

@Dharman - That one use of @ is an easy and concise way to say "this argument is optional and I will check for it.

Dharman Over a year ago

It's neither easy nor needed. Either use null-coalescing operator or isset as you do in your if.

|

Collectives™ on Stack Overflow

Get data from database using php,ajax

3 Answers 3

2 Comments

7 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

7 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related