Filtering large array from SQL result in WordPress

I have an SQL query that returns 14 rows per userID.

It pulls a lot of data to verify different fields, but specifically returned I need the first and last name. The first name I can get from $firstName[]=$row2[0]; but I can’t get the last name value out of the array. I can see its position, but I’m not sure how to call that position in the loop.

echo '<table border=1><tr><td>Student ID</td><td>Full Name</td><td>Class</td></tr>';


    //Build Student Info1 table
    foreach($userID as $val) {
    $user_id= $val;
    $query="SELECT * FROM wp_usermeta WHERE meta_key ='purchased_instances' AND user_id ='$user_id'";
    $query2="SELECT meta_value FROM wp_usermeta WHERE user_id ='$user_id'";

    $result = $link->query($query)or die($link->error);
    $result2 = $link->query($query2)or die($link->error);



    if($result->num_rows > 0){
    /* fetch associative array */

         $i=0;
         while ($row = mysqli_fetch_array($result)) {
             while($row2 = mysqli_fetch_array($result2)) {

                $details[$i][]=$row2;
                $firstName[]=$row2[0];
                $lastName[]=$row2[0];  //Can't get this value right, need the first element of the 2 indexed array 

             }

            $result1[]=$row;
            $studentID[$i]=$row[1];
            $classDetails[$i]=$row[3];

            echo'<tr><td>';
            print_r($studentID[$i]);
            echo '</td><td>';
            print_r($firstName[$i]);
            echo ' ';
            print_r($lastName[$i]);
            echo'</td><td>';
            print_r($classDetails[$i]);
            echo '</td></tr>';


            $i++;
        }
/* free result set */
mysqli_free_result($result);
}
}
echo '</table>';

This is what print_r($details) produces:

==============================================================================

New working code:

    echo '<table border=1><tr><td>Full Name</td><td>Class Purchased</td></tr>';


    //Build Student Info1 table
    foreach($userID as $val) {
    $user_id= $val;
    $queryA="SELECT meta_value FROM wp_usermeta WHERE meta_key ='first_name' AND user_id ='$user_id'";
    $queryB="SELECT meta_value FROM wp_usermeta WHERE meta_key ='last_name' AND user_id ='$user_id'";
    $queryC="SELECT meta_value FROM wp_usermeta WHERE meta_key ='purchased_instances' AND user_id ='$user_id'";

    $resultA = $link->query($queryA)or die($link->error);
    $resultB = $link->query($queryB)or die($link->error);
    $resultC = $link->query($queryC)or die($link->error);

    $numPurchases = $resultC->num_rows;

    if($numPurchases > 0){

        $i=0;
        foreach($resultA as $val){

                $fName[$i]=$val['meta_value'];;
                $i++;
        }

        $i=0;
        foreach($resultB as $val){

                $lName[$i]=$val['meta_value'];  
                $i++;

        }

        $i=0;
        foreach($resultC as $val){

                $classDetails[$i]=$val['meta_value'];   
                $i++;

        }

        for($i=0; $i < $numPurchases; $i++){
        echo '<td>';
        print_r($fName[$i]);
        echo ' ';
        print_r($lName[$i]);
        echo '</td><td>';
        print_r($classDetails[$i]);
        echo '</td></tr>';  


        }

/* free result set */
mysqli_free_result($resultA);
mysqli_free_result($resultB);
mysqli_free_result($resultC);
}

}
echo ”;

Post Views: 1

1 comment

Some important notes:

Validate any user input
NEVER execute SQL in a loop
Use one query that fetches everything you need (you’re using the same table)
Fetch an associative array, this will help you a lot
Specify exactly what you need in your projection (not * after SELECT, exactly state each field to fetch like SELECT foo, bar, â¦)

Here is some code to help you get started with the above tips:

<?php

// Validate input, please note that prepared statements would be best.
// Also note that we don't need mysql escape calls because we validate
// that this is an actual integer value which can't contain anything
// dangerous. If we'd be working with any kind of string, date, ... the
// world would look different.
if (!is_array($userID)) {
  if (!is_int($userID)) {
    throw new RuntimeException;
  }
  $userIds = "= {$userID}";
}
else {
  $userIds = null;
  $c       = count($userID);
  for ($i = 0; $i < $c; ++$i) {
    if (!is_int($userID[$i])) {
      throw new RuntimeException;
    }
    $userIds .= $userIds ? "," : "IN(";
    $userIds .= $userID[$i];
  }
  $userIds .= ")";
}

// One query to rule them all and nice formatting for readability.
$result = $link->query(
  "SELECT *
  FROM `wp_usermeta`
  WHERE `meta_key` = 'purchased_instances'
    AND `user_id` {$userIds}"
);

// Now we fetch everything within a single loop.
while ($row = mysqli_fetch_assoc($result)) {
  print_r($row);
}

// That's it!
mysqli_free_result($result);

?>

Filtering large array from SQL result

Leave a Reply Cancel reply

1 comment

Social Network

Related posts

Leave a Reply Cancel reply

1 comment

Social Network