Mysql Question posted in
The official MySQL documentation can be found here.

MySQL PHP query to break out table data as rows and columns

I have a table named employee with this data:

Name ID State
Bob 392 CA
Bob 392 ID
Bob 392 WA
Joe 393 CA
Joe 393 CO

I want to run a query so when I pull the report, it looks like this:

Name ID State
Bob 392 CA, ID, WA
Joe 393 CA, CO

I have tried many things, but this is all I have come up with so far:

//create arrays

$state1 = array();
$name1 = array();
$id1 = array();

// set query statement
$sql=mysqli_query($dbc,"SELECT * from employee WHERE name = 'Bob' OR name = 'Joe'");
while ($row = mysqli_fetch_array($sql,MYSQLI_ASSOC)){

$name1[] = $row['name'] . '<br>';
$id1[] = $row['id'] . '<br>';
$state1[] = $row3['state'];
$state =  join("','",$states1) . '<br>';

}

//delete name duplicates from **employee** table
$mlon = join(',',$name1);
$arr = explode(',', $mlon );
$arr = array_unique( $arr );
$name = implode(',' , $arr);

//delete id duplicates from **employee** table
$idn = join(',',$id1);
$arrn = explode(',', $idn );
$arrn = array_unique( $arrn );
$id = implode(',' , $arrn);

echo  '<table><th>Name<th>ID<th>States</th>
<tr><td>'. str_replace(",","",$name) . '<td>'. str_replace(",","",$id) .'<td>'. $state   . '<tr>';

I almost get what I want, but I can’t get the states to break out into their own line:

Name   ID   State
Bob    392
Joe    393  CA,ID,WA,CA,CO

What I want:

Name   ID   State
Bob    392  CA,ID,WA
Joe    393  CA,CO
Tags:

2

Answers


  1. 0

    I think that will do it. it is not testet but I think its ok.

    $id_save = 0;
$index = -1;
$sql=mysqli_query($dbc,"SELECT * from employee WHERE name = 'Bob' OR name = 'Joe' order by id");
while ($row = mysqli_fetch_array($sql,MYSQLI_ASSOC)){

    if $row['id'] <> $id_save { //when new id make new arrayentry
        $index += 1;
        $arr[$index][0] = $row['id'];
        $arr[$index][1] = $row['name'];
        $arr[$index][2] = $row['state'];
    } else {
        $arr[$index][2] = $arr[$index][2] .', '.$row['state'];  //add state
    }
}

var_dump($arr);
    Login or Signup to reply.
  2. 0

    MySQL has for that the aggregation function GROUP_CONCAT

    You can replace your function with the below

    If you want to have dynamic employy names use prepared statements

    SELECT * is bad style name your columns that you need, so that you can always know what you wanted from the query.

    CREATE TABLE employee (
  `Name` VARCHAR(3),
  `ID` INTEGER,
  `State` VARCHAR(2)
);

INSERT INTO employee
  (`Name`, `ID`, `State`)
VALUES
  ('Bob', '392', 'CA'),
  ('Bob', '392', 'ID'),
  ('Bob', '392', 'WA'),
  ('Joe', '393', 'CA'),
  ('Joe', '393', 'CO');

    Records: 5  Duplicates: 0  Warnings: 0

    SELECT `Name`, `ID`, GROUP_CONCAT(`State`) as State
  from employee
  WHERE name = 'Bob' OR name = 'Joe'
GROUP BY `Name`, `ID`
    Name ID State
    Bob 392 CA,ID,WA
    Joe 393 CA,CO

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search