setQuery($query);$row = $db->loadRowList();}else {$db =& JFactory::getDBO();$query = " SELECT upphandling.upphandlingsNamn, upphandling.iso, upphandling.AvtalsperiodStart, upphandling.AvtalsperiodStopp, upphandling.optionForl, avtal.forlangdTill, avtal.leverantor, avtal.skapadAv FROM upphandling, avtal WHERE upphandling.dnr = avtal.dnr AND avtal.id = $MyRow->id AND $whereStat; ";$db->setQuery($query);$row = $db->loadRowList();}?> " rel="{handler: 'iframe', size: {x: 600, y: 600}}"> "> ThanksMarkus"> Almost solved: Filter more than one table - Forums

Forums

Almost solved: Filter more than one table

PicoPaco 24 Aug, 2010
Hi,

I try to manage to do filter boxes that searchers two db-tables. I almost got it working, but my problem is that the posts in the tables that does not the search is empty, but their space in the results is visible. Here is some code and how it looks after I filter it.

Header
<?php

JHTML::_('behavior.modal');

$search_array = array('upphandlingsNamn','iso','leverantor', 'skapadAv','fritext'); 
foreach ( $search_array as $search ) {
  $value = JRequest::getVar($search, '' , 'post');
  if ( $value ) {
    if($search=='upphandlingsNamn' || $search=='iso'){
      $where[] = " upphandling.$search LIKE '%$value%' ";
      }
    else {
    $where[] = " avtal.$search LIKE '%$value%' ";
    }
  }
}

if ( !empty($where) ) {
  global $whereStat;
  $whereStat = implode(' AND ', $where);
}

?>
<div class="myform_filter" id="stylized_filter">
<div class="filterbox"><label>Avtalsområde</label><br /><input type="text" name="upphandlingsNamn" /></div>
<div class="filterbox"><label>Produkt</label><br /><input type="text" name="fritext" /></div>
<div class="filterbox"><label>ISO-kod</label><br /><input type="text" name="iso" /></div>
<div class="filterbox"><label>Leverantör</label><br /><input type="text" name="leverantor" /></div>
<div class="filterbox"><label>Ansvarig</label><br /><input type="text" name="skapadAv" /></div>
<div class="filterbox"><input class="button" type="submit" value="Sök" name="undefined" /></div>
</div>
<br />

<table class="upphTabell" width="100%">
  <thead>
    <tr>
      <th class="small" scope="col">Avtalsområde</th>
      <th class="small" scope="col">ISO-kod</th>
      <th class="small" scope="col">Avtalsperiod</th>
      <th class="small" scope="col">Förlängning</th>
      <th class="small" scope="col">Förlängd till</th>
      <th class="small" scope="col">Leverantör</th>
      <th class="small" scope="col">Ansvarig</th>
    </tr>
  </thead>


Body
<?php

global $whereStat;

if ( $whereStat=='' ) {
$db =& JFactory::getDBO();
$query = "
  SELECT upphandling.upphandlingsNamn, upphandling.iso, upphandling.AvtalsperiodStart, upphandling.AvtalsperiodStopp, upphandling.optionForl, avtal.forlangdTill, avtal.leverantor, avtal.skapadAv
    FROM upphandling, avtal
    WHERE upphandling.dnr = avtal.dnr AND avtal.id = $MyRow->id;
  ";
$db->setQuery($query);
$row = $db->loadRowList();
}
else {
$db =& JFactory::getDBO();
$query = "
  SELECT upphandling.upphandlingsNamn, upphandling.iso, upphandling.AvtalsperiodStart, upphandling.AvtalsperiodStopp, upphandling.optionForl, avtal.forlangdTill, avtal.leverantor, avtal.skapadAv
    FROM upphandling, avtal
    WHERE upphandling.dnr = avtal.dnr AND avtal.id = $MyRow->id AND $whereStat;
  ";
$db->setQuery($query);
$row = $db->loadRowList();
}

?>
  <tr>
    <td class="small"><a class="modal" href="http://7klovern.se.preview.binero.se/index.php?option=com_chronocontact&chronoformname=Avtal_detalj&task=extra&tmpl=component&id=<?php $row2['4'] ?>" rel="{handler: 'iframe', size: {x: 600, y: 600}}"><?php echo $row['0']['0']; ?></a></td>
    <td class="small"><?php echo $row['0']['1']; ?></td>
    <td class="small"><?php echo $row['0']['2'].'--'.$row['0']['3'];?></td>
    <td class="small"><?php echo $row['0']['4']; ?></td>
    <td class="small"><?php echo $forlangd; ?></td>
    <td class="small"><?php echo $row['0']['6']; ?></td>
    <td class="small"><a href="mailto:<?php echo $mail;?>"><?php echo $row['0']['7']; ?></a></td>
  </tr>





Thanks

Markus
nml375 24 Aug, 2010
Hi Markus,
The first thing that comes to mind, is that you are using Cartesian Joins. They can cause this kind of behaviour, if you're not careful. I havn't looked that deep into the code to either confirm or rule this matter out yet, but I'd say that's a good place to start.

I'll see about looking a little closer at the code once I get back on my workstation.
/Fredrik
nml375 24 Aug, 2010
Hi again,
Could you try printing the generated query for each row, and then test the output using phpmyadmin or some other sql-tool. Especially look for multiple rows in the result, and/or rows with NULL values for either upphandling or avtal.

/Fredrik
PicoPaco 20 Sep, 2010
Hi Fredrik

I have printed the output query in phpmyadmin, but only one row at a time when it actually loops through the table for each row in cc. The answer is one row for each question and it looks like it should. When I do this way, I find no multiple rows or NULL values. The query I print in phpmyadmin look like this:


SELECT upphandling.upphandlingsNamn, upphandling.iso, upphandling.AvtalsperiodStart, upphandling.AvtalsperiodStopp, upphandling.optionForl, avtal.forlangdTill, avtal.leverantor, avtal.skapadAv
    FROM upphandling, avtal
    WHERE upphandling.dnr = avtal.dnr AND avtal.id = 69;


Any suggestions?


Thanks for your time,

Markus
This topic is locked and no more replies can be posted.