Smarty Cross Matching Results

[adamjblakey]

Hi,

 

I have 2 tables both with a country field in.  I am doing a query on the first table to pull out all the results and then i want to do another query on table 2 which checks each result to see if the country field matches any on the loop of table 1.

 

function selects($select) {
$this->result = mysql_query($select,$this->connection);
$this->row = mysql_fetch_assoc($select);
}


function getRes() {
while ($res = mysql_fetch_array($this->result)) {
$this->reser[] = $res;
   }
return $this->reser;
  }


//Latest 5 load results
$current = new database;
$current->selects("SELECT * FROM loads ORDER BY collect_date");
$cresults = $current->getRes(); 

    // assign your db results to the template
    $smarty->assign('cresults', $cresults);


//match results
$match = new database;
$match->selects("SELECT * FROM vehicles WHERE region = '$cresults[region]'");
$matches = $match->getRes();
$countrows = $match->countrows(); 
//Countrows
$smarty->assign('countrows', $countrows);

 

 

HTML Side

 

 
{foreach from=$cresults item=cresult name=cresults}
                 {$cresult.to_city} - Matched Results {$countrows} 
  {/foreach}

 

But {$countrows} shows 0 on all the rows, but some of them have 2, 3 matchs.

 

Any ideas?

 

Cheers,

Adam

[adamjblakey]

Can anyone help with this?

[lemmin]

I don't even see the loop where you compare the two queries, but if you are trying to do what I think you are trying to do, it would probably be easier with a JOIN clause. You could probably even do it without the join clause and just query both tables in your query.

 

Here is the JOIN documentation:

http://dev.mysql.com/doc/refman/5.1/en/left-join-optimization.html

[adamjblakey]

How would i go about doing this as i am unsure?