SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot zbing's Avatar
    Join Date
    Jun 2002
    Location
    Lisbon, Portugal
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Help writing a query!

    I'm trying to write a query that searches through a database using 2 tables, e.g:

    table1 [people] & table2 [friends]

    I'm doign the search through a text string inputed through a HTML form, and the QUERY will retrieve the NAME, EMAIL, COUNTRY, DATE fiels from tables. So what i wrote was:

    PHP Code:
    SELECT p_namef_namep_emailf_email
           
    p_countryf_countryp_datef_date
      FROM people
    friends
     WHERE people
    .p_name OR friends.f_name
      LIKE 
    "%$tring%"

    // [B]$string[/B] is a PHP variable passed on by the HTML form 
    My problem is that with this query i get the correct result from the people table, and all the entries from the friends table.

    I've also tried:

    PHP Code:
    SELECT p_namef_namep_emailf_email
           
    p_countryf_countryp_datef_date
      FROM people
    friends
     WHERE people
    .p_name AND friends.f_name
      LIKE 
    "%$tring%"

    // [B]$string[/B] is a PHP variable passed on by the HTML form 
    But this outputs an empty result because any person is either in the people or the friends table.

    I've also also tried:

    PHP Code:
    SELECT p_namef_namep_emailf_email
           
    p_countryf_countryp_datef_date
      FROM people
    friends
     WHERE people
    .p_name=friends.f_name
       
    AND people.p_name
      LIKE 
    "%$string%"

    // [B]$string[/B] is a PHP variable passed on by the HTML form 
    But that, as i read it, again, it expects that there will be a entry for the same name in both tables.

    I'm having a lot of dificulty understanding JOINS, even after reading the MySQL manual chapter on the subject and Kevin Yank's book's chapter on the same subject...

    So if any of you could give me a "jump start" i would be very gratefull...

    Cheers
    zbing

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aren't friends people too?

    ** people
    id
    name
    email
    etc...

    ** people_being_friends
    id
    person1_id
    person2_id
    (adding a unique index for person1_id, person2_id is a good idea)

    Find a person:
    select name, email ...
    from people
    where name like '%$string%'

    Find my friends:
    select distinct p1.name, p1.email
    from people p1
    inner join people_being_friends pbf
    on p1.id = pbf.person1_id or p1.id = pbf.person2_id
    inner join people p2
    on pbf.person2_id = p2.id or pbf.person1_id = p2.id
    where p2.name = 'jofa' and p1.name <> 'jofa'

  3. #3
    SitePoint Zealot zbing's Avatar
    Join Date
    Jun 2002
    Location
    Lisbon, Portugal
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ?????????????????



    Now i feel really bogled...

    I do have ID columns in both tables... p_id & f_id but as i undertstood yer query, it will look for people that are in both tables, and this does not happen... there any person that is a friend or vice-versa....

    Maybe its just simpler to do 2 queries...
    zbing

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My point was: Why have people and friends in different tables, when you can have one people table and one table for the friendships (where you associate two id:s with each other)?

    And; with only one table with people (and some of them are friends) it's easier to search for a name - no join needed


    Joining a table with itself can be confusing the first time you see it, but think about it for a while and you will see why this method is better than people / friends in two tables

    I'm sure the join to get a person's friends can be done in a more efficient way...

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to search two tables anyway, you can use UNION to add results from table 2 to results from table 1

  6. #6
    SitePoint Zealot zbing's Avatar
    Join Date
    Jun 2002
    Location
    Lisbon, Portugal
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Thank you very much... The UNION funtion wuz just what i needed coz i really need to have people and friends on separate tables...

    zbing


Bookmarks

Posting Permissions

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