Results 1 to 4 of 4

Thread: MYSQL join 1 table issues

  1. #1

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17

    MYSQL join 1 table issues

    Hello.

    Can we have a "sticky" for useful MYSQL comamnds iN PHP ? Using Google & searching for tutorials, some 'basic' MYSQL commands do not work well on powweb, as Powweb MYSQL servers are very fussy.

    Issue :- (PHP & MYSQL)

    $sql="SELECT `t1.*`, `t2.*` FROM `names` AS t1
    JOIN `names` AS t2
    ON (`t1.DomainName` == `t2.DomainName`) AND (`t1.Email` == $email)";
    echo $sql."<br />\n";



    basically the names table contains only 2 fields.

    I want to

    :- search for a email address
    :- find which domain (or group) they belong to
    :- & List ALL users with that same group.

    (in the above example, its a doamin name - which will be the last part of an email address - but I'll want to use the same format / script for apples/pears etc - in another part of my site. )




    Error message i'm getting is a FOREACH prob. I'm echio'ing the result & nothings been retrieved from the MYSQL.

    EDIT: Ive double-checked both tables & my SQL comamnd should produce 2 lines of results.
    Last edited by PeterPan; 1-19-14 at 05:33 AM.

  2. #2

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    Example :-

    i have a small table with 2 x fields

    Domain / Email

    george.com / peter@george.com
    george.com / george@george.com
    sarah.com / sarah@sarah.com
    george.com / Susie@george.com
    sarah.com / martin@sarah.com

    ok, so i want to find martin@sarah.com

    it finds the DOMAIN colum as "sarah.com", & brings me a result containining all the records at sarah.com

    sarah.com / sarah@sarah.com
    sarah.com / martin@sarah.com

    Same process (different script) will work on another table :-

    TREE / apples
    GROUND / strawberries
    TREE / pears

    i enter "apples", & it finds the Group of TREES, & brings me the result containing 2 entries :-

    TREE / apples
    TREE / pears

    i think the problem resolvesa round quotes, i've tried NO quotes, single quotes, double quotes, & the weird quote on the 'tilde' key of my keyboard.

    so far, i am not getting any error messages that the MYSQL code is incorrect, but i'm getting zero / blank / no results (I've double checked & there should be at least 1 result (the email address i searched for)

  3. #3

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    Hello. Using https://mysqladmin.powweb.com SQL option, I have found the right code (that works), HOWEVER, my results are showing two lots of field names

    EG

    FIELD1, FIELD2, FIELD3, FIELD4, FIELD1, FIELD2, FIELD3, FIELD4

    (the field names are repeating in each "row" - Also in the data retrieved - It comes up twice.)

    CODE :-

    Code:
     $sql="
    SELECT t1. * , t2. *
    FROM `names` AS t1
    JOIN `names` AS t2 ON ( t1.DomainName = t2.DomainName )
    AND (
    t1.Email = '$email')";
    
    
    
    echo $sql."<br />\n";
    
    
     $result=mysql_query($sql);
    
    
    $i = 0;
    while ($i < mysql_num_fields($result)) {
        echo "Information for column $i:<br />\n";
        $meta = mysql_fetch_field($result, $i);
        if (!$meta) {
            echo "No information available<br />\n";
        }
        echo "<pre>
    blob:         $meta->blob
    max_length:   $meta->max_length
    multiple_key: $meta->multiple_key
    name:         $meta->name
    not_null:     $meta->not_null
    numeric:      $meta->numeric
    primary_key:  $meta->primary_key
    table:        $meta->table
    type:         $meta->type
    unique_key:   $meta->unique_key
    unsigned:     $meta->unsigned
    zerofill:     $meta->zerofill
    </pre>";
        $i++;
    }
    (I found the second part in Google)

    Hopefully i can fix it to only bring up ONE set of column titles/data

  4. #4

    Join Date
    Sep 2002
    Location
    Auckland
    Posts
    1,356
    Rep Power
    17
    YAY - HAPPY DANCE :-

    FIXED :- I've only selected t2.* and not t1.*, t2.*

    I'll prepare a text, & post to the new sticky thread. I've discovered a few probs & resolved them

    WORKING CODE for JOIN / SELECT on same table

    Code:
     $sql="
    SELECT t1.DomainName , t1.Email
    FROM `names` AS t1
    JOIN `names` AS t2 ON ( t1.DomainName = t2.DomainName )
    AND (
    t1.Email = '$email')";

Posting Permissions

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