CODEWITHSUNDEEP
javascriptphpmysqlsqlvariables
Freddie Barr-Smith
Freddie Barr-Smith

Reputation: 69

Fetch 5 Columns and their value from Mysql query and translate into PHP

I am unable to find a simple and elegant solution to solve this after several tries.

In essence I would like a way to fetch the 5 fields taken from this query and assign them to PHP variables then latterly Javascript variables.

<?php
    include_once('connect.php');
    $result = mysqli_query($conn, " SELECT DISTINCT IPdestport as destport,COUNT(*) as count FROM PACKETSFORIP GROUP BY destport ORDER BY count DESC LIMIT 5;");  

    while($row[] = mysqli_fetch_array($result))
    {
        $destport1 = $row[1][ 'destport' ];
        $destport2 = $row[2]['destport'];
        $destport3 = $row[3]['destport'];
        $destport4 = $row[4]['destport'];
        $destport5 = $row[5]['destport'];
        $count1 = $row[1]['count'];            
        $count2 = $row[2]['count'];
        $count3 = $row[3]['count'];
        $count4 = $row[4]['count'];
        $count5 = $row[5]['count']; 

    }
    ?>

Here is more code of how I will eventually translate these PHP variables into Javascript variables in order to process them into a google chart

var destport1 = "<?php echo $destport1 ?>";
var destport2 = "<?php echo $destport2 ?>";
var destport3 = "<?php echo $destport3 ?>";
var destport4 = "<?php echo $destport4 ?>";
var destport5 = "<?php echo $destport5 ?>";
var count1 = "<?php echo $count1 ?>";
var count2 = "<?php echo $count2 ?>";
var count3 = "<?php echo $count3 ?>";
var count4 = "<?php echo $count4 ?>";
var count5 = "<?php echo $count5 ?>";

Any help would be very much appreciated. Thanks.

Upvotes: 1

Views: 397

Answers (2)

Freddie Barr-Smith
Freddie Barr-Smith

Reputation: 69

For anyone searching for this in future this is the solution i eventually utilised. Found it in a previous web application i'd coded. 

       <?php
                     include_once('connect.php');
      $result = mysqli_query($conn, " SELECT DISTINCT IPdestport as destport,COUNT(*) as count FROM PACKETSFORIP GROUP BY destport ORDER BY count DESC LIMIT 5;");  
          $portsarray = Array();
          $countarray = Array();
            while($row = mysqli_fetch_array($result))
              {
        $portarray[] = array('destport'=>$row['destport'], 'count'=>$row['count']);
            echo $row['count'];
              }

          $portarray = json_encode($portarray);


    ?>

The above code fetches each port and the count of packets going to that port and this is latterly converted into javascript to be fed into a google chart, but i'll just show the part that fetches the PHP array and puts the variables contained within into javascript variables.

                        var obj = <?php echo $portarray; ?>;

                    for(var i= 0; i < obj.length; i++) {
                    var json = obj[i];
                    destport = json.destport;
                    numberofpackets = json.count;


                    destport = destport.toString();
                    numberofpackets= parseInt(numberofpackets);

Upvotes: 1

George Cummins
George Cummins

Reputation: 28906

You can save a lot of repetitive code by using arrays.

To simplify the first step (assign them to PHP variables) just convert the result set into an array:

$destPorts = mysqli_fetch_all($result);

$destPorts will contain all of the rows from the result set. If you want to access the 'count' column of the second row, use:

$destPorts[1]['count']

Or to access the 'destport' column of the fourth row, use:

$destPorts[3]['destport']

Since the values are stored in the $destPorts array, there is no need to convert them into separate variables.

You can do something similar in JavaScript. Instead of creating separate variables, store the data in a JavaScript object:

var destPorts = <?php echo json_encode($destPorts); ?>

Now you can access the values stored in the destPorts JavaScript variable. To access the 'count' column of the second row, use:

destPorts[1].count

Or to access the 'destport' column of the fourth row, use:

destPorts[3].destport

When you store your result rows in arrays and objects instead of simple numerically indexed variables, your code is less repetitive and more flexible. You no longer need to know how many results are in the database; you can simply loop over the array or object and access all of the data.

Upvotes: 1

Related Questions