Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jan 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Select Statement - Two Tables

    My apologies if the answer to this is here somewhere. I have searched for days and tried writing it 500 different ways trying to figure this out but cannot get it to work just as i want it to. I have two tables, prefix_users and prefix_recipes. I need for the statement to look at and compare the id (user's id) from table prefix_users with the user_id and id (recipe's id) from the table prefix_recipes and if they are the same, show the recipe's name on the page. It is the user's profile page that I am trying to include the recipes that the user has submitted on their specific page. Any help would be great.

    table: prefix_users
    id (user's id)

    table: prefix_recipes
    id (recipe's id)
    user_id
    name (recipe's name)

    Smiles,
    Lana

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    This is actually an SQL question, not a PHP one.
    I don't understand what you're trying to do with this though. From the sounds of you're rules, you're looking to lookup WHERE users.id = recipes.id = recipes.user_id?
    Anyway, simple where clauses for those:
    Code:
    SELECT `name` FROM `prefix_recipes` WHERE `id` = {id} AND `user_id` = {id}
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    New to the CF scene
    Join Date
    Jan 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks so much for the time you took Fou-Lu. I knew it was something simple and I have little knowledge with sql statements and for some reason, could not wrap my brain around it.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    np, is that what you're looking for though? The logic of you're table schema seems to apply the same foreign key to the values of two seemingly unrelated fields, which will likely provide inaccurate results. I'm thinking you're only wanting to compare on a user id, so you can fetch all recipes owned by the specified user. This would indicate a surrogate key for the value of you're recipe id (a surrogate is a key that is automatically generated for you, so you're field for recipe id would be an auto_increment int).
    Another design approach would be to use a composite key between the recipe id and the user id. They are still unrelated (in that you only want the user id), but are unique and can be used as an easy count between each user. This would have data like so:
    recipe id, userid, name
    1, 1, 'Cake'
    2, 1, 'Cheese Cake'
    1, 2, 'Shortbread'
    etc

    Both are equally usable, though the surrogate approach does provide easier extension into additional tables for you're recipes (the latter approach would require both the recipe id and the user id to lookup a single recipe, while the former would require only the recipe id).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    should it not be

    Code:
    select
      pu.name
    , pr.recipe_name
    from prefix_users pu
    inner join
    prefix_recipes pr
    on pr.user_id = pu.id
    table structure should be (I reckon anyway)

    table: prefix_users
    user_id not null auto_increment primary key

    table: prefix_recipes
    user_id int not null
    name (recipe's name) varchar(99)

    bazz
    Last edited by bazz; 02-20-2009 at 02:57 AM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •