CODEWITHSUNDEEP
phpmysqlregex
klox
klox

Reputation: 2093

count and search string from data array

I have a database table as follows:

line     model        serial     range serial    
fa01     kd-g335ud   105x0001    105x0001-105x0200
fa01     kd-g335ud   105x0002    105x0001-105x0200 
fa01     kd-g335ud   105x0003    105x0001-105x0200 
fa02     kd-r311ed   105x0001    105x0001-105x0500
fa02     kd-r311ed   105x0002    105x0001-105x0500

When I query it, I want the result to be like this:

line     model       qty        qty range
fa01     kd-g335ud   3            200
fa02     kd-r311ed   2            500

I have written the following query, however I am still still confused as to how to show qty range.

 //this just show until qty
    SELECT line, model, COUNT(serial) as qty FROM inspection GROUP BY line, model

    //this for search string
    $sql_result=array(//what must i fill?);
    foreach($sql_result as $row)
    {
       preg_match_all('/\d{4}/',$row,$matches);
       echo intval($matches[0][1])-intval($matches[0][0])+1;
    }

Please help, as I am still unsure as to the second syntax

Upvotes: 0

Views: 191

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

Assuming that qty range is the last three characters of range_serial

SELECT line, 
       model, 
       COUNT(serial) as qty,
       RIGHT(range_serial,3) as qty_range 
  FROM inspection 
 GROUP BY line, 
          model,
          range_serial

EDIT

$sqlQuery = 'SELECT line, model, COUNT(serial) as qty,range_serial as qty_range 
               FROM inspection 
              GROUP BY line, model, range_serial';

$sqlResult = mysql_query($sqlQuery); 

while ($row = mysql_fetch_assoc($sqlResult)) {
   preg_match_all('/\d{4}/',$row['qty_range'],$matches); 
   echo intval($matches[0][1])-intval($matches[0][0])+1; 
}

Upvotes: 1

Related Questions