PHP / MySQL - Create array of distinct values, query db table for data associated with those values, and loop for each

Question

I'm not much of a PHP programmer, so I hope someone can help me with this. What I'm trying to do is get the distinct values from the competitor column, create an array of them, retrieve the share1-share12 values for each of those distinct values based on a number of variables, and output the competitors and their share1-12 values.

Below is the format of my data table along with the mess of code I've been cobbling together:

state|bigcat|competitor|metric|share1|share2|share3|share4|share5|share6|share7|share8|share9|share10|share11|share12

<?php
    $product = $_GET['product'];
    $cat = $_GET['cat'];
    $state = $_GET['state'];
    $metric = $_GET['metric'];

    $table = $product ."_specs_states";

    $q = " SELECT distinct(competitor) as competitor from $table";
                $result = $dbc->query($q) or die("unable to execute the query<br />" . $dbc->errno . "<br />" . $dbc->error);
                $r = $result->fetch_array();
                $competitors = array();

                do {
                    $competitors[] = $r[0];

          } while ($r = $result->fetch_array());
          echo $competitors;


        foreach($competitors as $competitor){


                $q = "SELECT * FROM $table where state = '$state' && bigcat = '$cat' && product = '$product' && metric = '$metric' && competitor = '$competitor'";
                $result = $dbc->query($q) or die("unable to execute the query<br />" . $dbc->errno . "<br />" . $dbc->error);

                $r = $result->fetch_array();

                    $share1 = ($r[5]);
                    $share2 = ($r[6]);
                    $share3 = ($r[7]);
                    $share4 = ($r[8]);
                    $share5 = ($r[9]);
                    $share6 = ($r[10]);
                    $share7 = ($r[11]);
                    $share8 = ($r[12]);
                    $share9 = ($r[13]);
                    $share10 = ($r[14]);
                    $share11 = ($r[15]);
                    $share12 = ($r[16]);

    }//end loop

    ?>

    <?php
    $i=1;
    while($i<=#)
      {
    ?>

    <?= $competitor ?><br />
    <?= $share1 ?><br />
    <?= $share2 ?><br />
    <?= $share3 ?><br />
    <?= $share4 ?><br />
    <?= $share5 ?><br />
    <?= $share6 ?><br />
    <?= $share7 ?><br />
    <?= $share8 ?><br />
    <?= $share9 ?><br />
    <?= $share10 ?><br />
    <?= $share11 ?><br />
    <?= $share12 ?>


    <?php
      $i++;
      }
    ?>

Answer 1

I don't quite understand completely but I'm guessing you want to output all of the competitors and their shares but this is only outputting the last competitor? If so the way to fix that would be to put your echoes in your foreach loop instead of creating another while loop.

<?php
$product = $_GET['product'];
$cat = $_GET['cat'];
$state = $_GET['state'];
$metric = $_GET['metric'];

$table = $product ."_specs_states";

$q = " SELECT distinct(competitor) as competitor from $table";
            $result = $dbc->query($q) or die("unable to execute the query<br />" . $dbc->errno . "<br />" . $dbc->error);
            $r = $result->fetch_array();
            $competitors = array();

            do {
                $competitors[] = $r[0];

      } while ($r = $result->fetch_array());
      echo $competitors;


    foreach($competitors as $competitor){


            $q = "SELECT * FROM $table where state = '$state' && bigcat = '$cat' && product = '$product' && metric = '$metric' && competitor = '$competitor'";
            $result = $dbc->query($q) or die("unable to execute the query<br />" . $dbc->errno . "<br />" . $dbc->error);

            $r = $result->fetch_array();

                $share1 = ($r[5]);
                $share2 = ($r[6]);
                $share3 = ($r[7]);
                $share4 = ($r[8]);
                $share5 = ($r[9]);
                $share6 = ($r[10]);
                $share7 = ($r[11]);
                $share8 = ($r[12]);
                $share9 = ($r[13]);
                $share10 = ($r[14]);
                $share11 = ($r[15]);
                $share12 = ($r[16]);

                echo $competitor ."<br />";
echo $share1 ."<br />";
echo $share2 ."<br />";
echo $share3 ."<br />";
echo $share4 ."<br />";
echo $share5 ."<br />";
echo $share6 ."<br />";
echo $share7 ."<br />";
echo $share8 ."<br />";
echo $share9 ."<br />";
echo $share10 ."<br />";
echo $share11 ."<br />";
echo $share12;

}//end loop

?>