While Loops

[menwn]

Hi all

I have two tables which connect to each other with a columns. One table has text and the other the Title of the law containing the text.One Title has many texts. What I want is to search inside the texts and retrive the Titles who have texts containing my search terms. I have tried inner, outer joins but nothing happens. I keep receiving the Title more than one time if the search terms exist in more than one  text. 

here is the code:

$rs1 = $db->Execute("SELECT articles.LawID, articles.ArticleID, articles.ArticleNo, articles.Title as artiletitle, laws.Title as lawstitle, CreationDate as date, laws.Name as name
FROM articles RIGHT JOIN laws ON
articles.LawID= laws.LawID
WHERE CONTAINS (articles.Content, '$search') ORDER BY CreationDate DESC");

while (!$rs1->EOF)
{

$thelawid = $rs1->Fields['LawID']->Value;

$rs2 = $db->Execute("SELECT Title as lawstitle, CreationDate as date, Name as name, LawID FROM laws WHERE LawID = '$thelawid' ORDER BY CreationDate DESC");

while (!$rs2->EOF)
{

$title=$rs2->Fields['lawstitle']->Value;
//$article_titles = $rs2->Fields['artiletitle']->Value;
if ($title ==""){$title ="No title";};
 
  echo '<tr>';
  echo '<td nowrap="nowrap">'.$rs2->Fields['date']->Value.'</td>';
  echo '<td nowrap="nowrap">'.$rs2->Fields['name']->Value.'</td>';
  echo '<td nowrap="nowrap"> <a href=arthro.php?law='.$rs2->Fields['LawID']->Value."&searchstring=".urlencode($search)."&lawtitle=".urlencode($title).'>'.$title.'</a><br />';
  echo '</tr>';
 
 
    $rs2->MoveNext();
} $rs2->Close(); # optional

$rs1->MoveNext();

}

Any ideas on how to fix this?
thanks in advance

[suzzane2020]

put in a DISTINCT keyword in the second query
$rs2 = $db->Execute("SELECT DISTINCT Title as lawstitle, CreationDate as date, Name as name, LawID FROM laws WHERE LawID = '$thelawid' ORDER BY CreationDate DESC");

[menwn]

Hi

thanks for the answer. Unfortunately I use sql server and the text is in ntext (was not my choice grrrr) and so I get the following error:
Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image data type cannot be selected as DISTINCT.'!

any ideas?
thanks

[suzzane2020]

hmm..sorry hve no idea bout sql server..but i guess te u need to use a particular keyword tht gives u distinct titles and does nt repeat them..somethn equivqalent to DISTINCT in sql server

[menwn]

Ιτ works! With a "minor" problem! In order to make it work I had to change the first query not writing the join:

$rs1 = $db->Execute("SELECT LawID, ArticleID, ArticleNo, Title as artiletitle FROM articles
WHERE CONTAINS (Content, '$search') ");

while (!$rs1->EOF)
{

$thelawid = $rs1->Fields['LawID']->Value;

$rs2 = $db->Execute("SELECT DISTINCT cast(Title as nvarchar(4000)) as lawstitle, CreationDate as date, cast(Name as nvarchar(400)) as name, LawID FROM laws WHERE LawID = '$thelawid' ORDER BY CreationDate");

$rs1->MoveNext();
while (!$rs2->EOF)
{

$title=$rs2->Fields['lawstitle']->Value;
//$article_titles = $rs2->Fields['artiletitle']->Value;
if ($title ==""){$title ="No title";};
 
  echo '<tr>';
  echo '<td nowrap="nowrap">'.$rs2->Fields['date']->Value.'</td>';
  echo '<td nowrap="nowrap">'.$rs2->Fields['name']->Value.'</td>';
  echo '<td nowrap="nowrap"> <a href=arthro.php?law='.$rs2->Fields['LawID']->Value."&searchstring=".urlencode($search)."&lawtitle=".urlencode($title).'>'.$title.'[/url]
';
  echo '</tr>';
 
 
    $rs2->MoveNext();
}  $rs2->Close(); # optional

//$rs1->MoveNext();

}

now it does not sort the results by date? Why is this?

menwn

[menwn]

Finally got it... I used a join in the first while and a distinct!

The code is the following:

$rs1 = $db->Execute("SELECT DISTINCT articles.LawID, CreationDate as date FROM articles INNER JOIN laws ON articles.LawID = laws.LawID
WHERE CONTAINS (Content, '$search') ORDER BY CreationDate DESC ");

while (!$rs1->EOF)
{

$thelawid = $rs1->Fields['LawID']->Value;

$rs2 = $db->Execute("SELECT DISTINCT cast(Title as nvarchar(4000)) as lawstitle, CreationDate as date, cast(Name as nvarchar(400)) as name, LawID FROM laws WHERE LawID = '$thelawid' ORDER BY CreationDate");

$rs1->MoveNext();
while (!$rs2->EOF)
{

$title=$rs2->Fields['lawstitle']->Value;
//$article_titles = $rs2->Fields['artiletitle']->Value;
if ($title ==""){$title ="No title";};
 
  echo '<tr>';
  echo '<td nowrap="nowrap">'.$rs2->Fields['date']->Value.'</td>';
  echo '<td width="16%">'.$rs2->Fields['name']->Value.'</td>';
  echo '<td nowrap="nowrap"> <a href=arthro.php?law='.$rs2->Fields['LawID']->Value."&searchstring=".urlencode($search)."&lawtitle=".urlencode($title).'>'.$title.'';
  echo '</tr>';
 
 
    $rs2->MoveNext();
}  $rs2->Close(); # optional

//$rs1->MoveNext();

}