[SOLVED] calculation...

[bea]

Hi,
it seems, that I'm out of Fabrik....

I need to calculate the order of records with same date and same shift, because I need it to get the right data from another database. There will be always 3 records within a shift and they should numbered like 1,2,3.
I've no idea how to write the calc ...
Cheers,
Bianka

Elements:
$shift = $data['fab_cp1_line_tour___shift_raw'];
$date = $data['fab_cp1_line_tour___date_raw'];
$id = $data['fab_cp1_line_tour___id_raw'];

 

[bea]

Hi,
I've solved myself with 2 different scripts. Don't know, which one is better...
update: I had to add the date element, but in second script it doesn't work (error)
Cheers

$db = JFactory::getDbo();
$date ='{fab_cp1_line_tour___date_raw}';
$shift = (int)'{fab_cp1_line_tour___shift_raw}';
$id = '{fab_cp1_line_tour___id_raw}';
$query = $db->getQuery(true);
$query
  ->select('MAX(id)')
  ->from('fab_cp1_line_tour')
  ->where('date= ' . $db->quote($date))
  ->where('shift = ' . $db->quote($shift));
$db->setQuery($query);
$maxid  = $db->loadResult();
$query = $db->getQuery(true);
$query
  ->select('MIN(id)+1')
  ->from('fab_cp1_line_tour')
  ->where('date= ' . $db->quote($date))
  ->where('shift = ' . $db->quote($shift));
$db->setQuery($query);
$midid  = $db->loadResult();
$query = $db->getQuery(true);
$query
     ->select('MIN(id)')
     ->from('fab_cp1_line_tour')
  ->where('date= ' . $db->quote($date))
  ->where('shift = ' . $db->quote($shift));
$db->setQuery($query);
$minid  = $db->loadResult();
if ($minid == $id) {
return '1';}
if ($midid == $id) {
return '2';}
if ($maxid == $id) {
return '3';}
else {return '';}

$db = JFactory::getDbo();
$date ='{fab_cp1_line_tour___date_raw}';
$shift = '{fab_cp1_line_tour___shift_raw}';
$id = '{fab_cp1_line_tour___id_raw}';
$db = & JFactory::getDBO();
$query = "SELECT MAX(id) FROM `fab_cp1_line_tour` WHERE `shift` = $shift AND `date` = $date";
$db->setQuery($query);
$maxid =  $db->loadResult();
$query = "SELECT MIN(id)+1 FROM `fab_cp1_line_tour` WHERE `shift` = $shift AND `date` = $date";
$db->setQuery($query);
$midid =  $db->loadResult();
$query = "SELECT MIN(id) FROM `fab_cp1_line_tour` WHERE `shift` = $shift AND `date` = $date";
$db->setQuery($query);
$minid =  $db->loadResult();
if ($minid == $id) {
return '1';}
if ($midid == $id) {
return '2';}
if ($maxid == $id) {
return '3';}
else {return '';}

 

[bea]

Friendly bump

 

[troester]

You didn't quote $date in your 2nd script.

 

[cheesegrits]

IMHO, it's always better to use the query builder rather than construct queries by hand, unless you need to do something the query builder can't handle.

And as you are querying the same table with the same conditions, you could do it in a single query instead of three:

$db = JFactory::getDbo();
$date = '{fab_cp1_line_tour___date_raw}';
$shift = (int)'{fab_cp1_line_tour___shift_raw}';
$id = '{fab_cp1_line_tour___id_raw}';

$query = $db->getQuery(true);
$query
   ->select('MAX(id) AS maxid')
   ->select('MIN(id)+1 AS midid')
   ->select('MIN(id) AS minid')
   ->from('fab_cp1_line_tour')
   ->where('date= ' . $db->quote($date))
   ->where('shift = ' . $db->quote($shift));
$db->setQuery($query);
$results = $db->loadObject();

if ($results->minid == $id) {
   return'1';
}
else if ($results->midid == $id) {
   return'2';
}
else if ($results->maxid == $id){
   return'3';
}
else {
   return'';
}

-- hugh

 

[bea]

Many thanks!

 

[cheesegrits]

N/p, apologies for the delay in answering.

-- hugh