CrossEyedMonk has asked for the wisdom of the Perl Monks concerning the following question:

I have to generate a HTML table from two MySQL db tables.
It has to display all of the items available to the user.

But one column in the display table has to do with the
users history with a particular row. Either he has
some kind of history, or he has none.

So...
I have 4 rows total in the item control dbtable.
I always have to display the total amount of rows in the
item control table (4 at this time).
items.it_ID
items.it_Name
etc...

Yet my user has a history with only 1 of the items and
this data is kept in the user_item_history dbtable.
user_item_history.ID
items.it_ID
user.UserID
etc....

So my HTML output would have to look like...

ITEM TITLEUSER ITEM HISTORY
---------------------------
Item OneNo User History
Item TwoClick for History
Item ThreeNo User History
Item FourNo User History

It would be easy if I only had to show the items
related to, or not to the user. But I need to show both.

I'm looking at 2 different queries, array's and while loops.
I've tried a number of different things but none seem
to do what I want.

I'm stuck.

Thanks,
The Stuck Newbie

Replies are listed 'Best First'.
Re: Newbie can't see the forest
by Malach (Scribe) on Mar 06, 2002 at 20:16 UTC

    As a rule, if you provide some sample code, people will be a lot happier to help you (it shows you've put some work in yourself, and you're not getting other people to do it for you).

    (And, if you do provide code, you really have to use strict; and use warnings; and (in this case) use CGI;, otherwise you tend to get jumped on.)

    Looking at your question, I'm not entirely sure about your database structure, and how it ties in to the sample table.

    It looks to me like you need to select it_ID, it_Name from items, and then loop inside that (with a while loop) selecting user_ID from (table name) where it_ID = (the result from the earlier query), and if you get a non-zero result, display the "click here for" link with a link to the history page.

    I could be more clear, with code examples, but to be honest it's too early in the morning for me, and I haven't had a coffee yet :)

    Hope that helps.

    --
    Malach
    So, this baby seal walks into a club.....

[reply]
      As a rule, if you provide some sample code, people will be a lot happier to help you (it shows you've put some work in yourself, and you're not getting other people to do it for you).

      I'll try and not take offense, I've been busting my butt on this problem.

      I left out the code to keep it simple. It has to be a SQL problem and I thought that since the use of SQL in Perl programming is used so much, that there would be many Perl programmers with good SQL experience.

      (holding his tounge)

      thanks for your helpful advice (!)

[reply]

        If you took offence, I'm sorry, it wasn't my intent to cause it.

        It's somewhat of a canon around here that posting code shows that you're actually working on the problem, and honestly stuck rather than lazy (or trying to get people to do your homework for you). Please note that I'm not suggesting that you're doing this, I'm just letting you know what seems to be considered standard here.

        Glad that someone could provide more help than I could at that time of the morning :)

        --
        Malach
        So, this baby seal walks into a club...

[reply]
Re: Newbie can't see the forest
by dws (Chancellor) on Mar 06, 2002 at 20:50 UTC
    I'm stuck.

    The first step to getting a problem fixed is in stating the problem in ways that others can understand unambiguously. One diagnostic of how stuck you are is in the lack of clarity in your problem statement. I've read over your post several times, but to make sense of it I have to fill in everal gaps with assumptions and guesses. That's a warning flag. I say this to give you honest feedback, not to be mean.

    If you repost the problem, please provide complete SQL DDL for both tables, along with notes of what the keys and foreign keys are.

    I suspect that you're going to need a query like 

    SELECT
  items.it_ID, items.it_Name, user_item_history.UserID
FROM
  items
  LEFT JOIN user_item_history
    ON items.it_ID = user_item_history.it_ID
WHERE user_item_history.UserID = ?
ORDER BY items.it_ID

    [download]
    This will give you a table with one row per item.it_ID, ordered by it_ID (which I assume is linearly ascending, and thus keeps it_Name in a sensible order). Thanks to the LEFT JOIN, the UserID column will be non-NULL if and only if the user has a history for the corresponding item.

    Getting the results of the query into Perl data structures is left as an exercise.

[reply]
[d/l]
Re: Newbie can't see the forest
by perrin (Chancellor) on Mar 06, 2002 at 20:27 UTC
    You just need to use an outer join in your SQL query.
[reply]
      Found it!

      From my SQL ref book: "... useful to be able to match all the rows from one or more tables along with any matching rows from realted tables...OUTERJOIN"

      Yea buddy, all I needed was a hint on where to look!

      Thanks, CEM

[reply]
Re: Newbie can't see the forest
by grummerX (Pilgrim) on Mar 06, 2002 at 20:32 UTC
    Assuming I understand what you're asking, this is really a SQL question rather than a Perl question. That caveat aside, it looks like you need to use an outer join, e.g. something like this: 
    SELECT items.it_name, user_item_history.ID 
FROM items
LEFT JOIN user_item_history 
	ON (user_item_history.it_ID = items.it_ID)
    This will give you all items, and include the ID of any associated history items. Any item that has no history will still return a row with a NULL user_item_history.ID.

    -- grummerX

[reply]

Back to Seekers of Perl Wisdom