php: how to change string data become numeric data

Question

dear all.. can you tell how to change this result in php and mysql script:

  Model                  Class
Ball                        S
Book                        A
Spoon
Plate                       B
Box                         C

this is my DB:

CREATE TABLE IF NOT EXISTS `inspection_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Model` varchar(14) NOT NULL,
  `Serial_number` varchar(8) NOT NULL,
  `Lot_no` varchar(6) NOT NULL,
  `Line` char(5) NOT NULL,      
  `Class` char(1) NOT NULL,
  `Status` varchar(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Model` (`Model`,`Serial_number`,`Lot_no`,`Line`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ;

how if i want to show result like:

 Model           s       a       b       c
 Ball            1       0       0       0
 Book            0       1       0       0
 Spoon           0       0       0       0
 Plate           0       0       1       0
 Box             0       0       0       1

What's query to make this? thanks.

Answer 1

Your question is a little unclear, but I'm assuming you have the input data in an array mapping name to defect, and you want for each row a 1 in the appropriate column and a zero everywhere else. If so, it's just this:

$arr = array('blue' => 'S', 'red' => 'A', 'yellow' => null, 'green' => 'B', 'black' => 'C');

$defects = array_filter(array_unique(array_values($arr)));
echo "name\t";
echo implode("\t", $defects);
echo "\n";

foreach($arr as $name => $defect) {
    echo "$name";
    foreach($defects as $test) {
        echo "\t";
        echo $test == $defect ? 1 : 0;
    }
    echo "\n";
}

Answer 2

I am assuming you can already get the data from the database, so here is an example if you had the data in $rows as shown:

<?php

$rows = array(
              array('name'=>'blue', 'class'=>'S'),
              array('name'=>'red', 'class'=>'A'),
              array('name'=>'yellow', 'class'=>NULL),
              array('name'=>'green', 'class'=>'B'),
              array('name'=>'black', 'class'=>'C'),
            );

$vals = array('s'=>0, 'a'=>0, 'b'=>0, 'c'=>0);

$output = array();
foreach ($rows as $row) {
    $row_vals = $vals;
    if ($row['class']) $row_vals[strtolower($row['class'])] = 1;
    $output[$row['name']] = $row_vals;
}

print_r($output);

?>

OUTPUT:

Array
(
    [blue] => Array
        (
            [s] => 1
            [a] => 0
            [b] => 0
            [c] => 0
        )

    [red] => Array
        (
            [s] => 0
            [a] => 1
            [b] => 0
            [c] => 0
        )

    [yellow] => Array
        (
            [s] => 0
            [a] => 0
            [b] => 0
            [c] => 0
        )

    [green] => Array
        (
            [s] => 0
            [a] => 0
            [b] => 1
            [c] => 0
        )

    [black] => Array
        (
            [s] => 0
            [a] => 0
            [b] => 0
            [c] => 1
        )

)

Answer 3

Very crude example, you'd probably go with HTML tables in reality.

<?php // $rows = array(array('name' => 'blue', 'class_defect' => 'S'), ...); ?>

<pre>
name      s  a  b  c
<?php
foreach ($rows as $row) {
    printf('%-10s', $row['name']);  // padding with spaces
    foreach (array('s', 'a', 'b', 'c') as $col) {
        echo (strtolower($row['class_defect']) == $col) ? 1 : 0;
        echo '  ';  // just padding again
    }
}
?>
</pre>

Answer 4

SELECT `Model`,
IF(`Class`='S', 1, 0) AS `S`,
IF(`Class`='A', 1, 0) AS `A`,
IF(`Class`='B', 1, 0) AS `B`,
IF(`Class`='C', 1, 0) AS `C`
FROM `inspection_report`