My response below....
"leegold" <[EMAIL PROTECTED]> wrote on 10/12/2004 03:11:05 PM:
> I have been trying to explain my question, I'm trying to detail it as
> exactly as I can, I can not tell you how much this forum has helped with
> my project. I hope I am clear, he's some invaluable SQL i got from a
> poster here, it's what I'm currently playing with as my
> search/query[w/php]:
>
> $query = "SELECT page.*, url_pages.* FROM `page` LEFT JOIN `keywords`
> USING(`page_id`) LEFT JOIN URL_PAGES USING (`page_id`) WHERE
> MATCH(`keywords`.`keyword_txt`) AGAINST ('$keyword'IN BOOLEAN MODE)";
>
> Basically I got three tables, I got a keyword table, the fulltext finds
> the word in the keywords table
> and relates the page_id field to identical fields in the page table and
> the url table. Very schematically here's the structure, I search for
> "foo":
>
> keyword table
> page_id keyword
> 1 foo
>
> page table
> page_id title description
> 1 my urls It' my webpage
>
> url table
> page_id url
> 1 goglle1.com
> 1 goglle2.com
>
> I want *one reord* rendering like this:
>
> my urls It' my webpage goglle1.com goglle2.com
>
> ie. the user sees one record as a result.
But there isn't only one answer, there are two answers to the question you
are posing to the database. How you format the response from the database
to show the user is limited only by your skills as a PHP programmer.
>
> BUT in my mysql/php attempts i get this rendering:
> my urls It' my webpage goglle1.com
> my urls It' my webpage goglle2.com
That is the correct answer. You have two rows of data that meet your
requirements. That's why you get two records back.
>
> What's the strategy to combine into *one* record? Is the easiest way to
> split things up
> into two separate querys? The first query searches keywords table *and*
> thus finds the correct record in the page table, I then have the PK
> page_id and can pull all the matches in the url table with another
> query. Then do the php to render it. But I think my understanding of
> this is a bit superficial. I need help. Thanks, Lee
You are confusing data retrieval with SQL with data formatting for your
users. If you want to display it all on one line then do so. If I were
better at PHP I could give you a more correct example. This is guaranteed
to NOT work but I hope you can see the idea I am trying to illustrate with
this PHP-ish pseudocode:
$conn = get_connectionn("...connection string ...");
$query = "SELECT .... WHERE ...";
$results = get_query ($conn, $query);
$rowcount = get_rowcount($results);
print get_field($results,"title") + " " +
get_field($results,"description") + " ";
for($i=0,$i<=$rowcount, $i++){
print get_field($results, "url") + " ";
}
If you want to turn a list of values into a single space-separated string
of values, do it. How you format the results of the query is completely up
to you.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
