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 7 of 7

Thread: if statement

  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts

    if statement

    Have this code that pulls the info and puts in a list, it is a very simple list, now I want to expand and add info to it but when I do the last code is what get implemented.

    What do I use to make it all work?

    This is the very simple code:

    PHP Code:
        $sql "SELECT * FROM nudges WHERE sender='$clientID' AND s_status IN (1,0) AND accepted=1
                        UNION 
                        SELECT * FROM nudges WHERE receiver='$clientID' 
                        ORDER BY posted DESC "
    ;
        
    $result=$mysqli->query($sql);
                                        
        while(
    $r $result->fetch_assoc()) {
            
    $sender=$r['sender'];
            
    $receiver=$r['receiver'];
            
        if(
    $receiver == $clientID) {
            echo 
    "<br>Received $receiver - $sender";
        }
        
        if(
    $sender == $clientID){
            echo 
    "<br>Sent $sender - $receiver";
        } 
    What I get is:
    Sent test - harkly
    Received test - fassbender
    Received test - theRory

    So now I add more details and it will only do the last if statement, I sort of get why but what I can't figure out is what I need to do to fix it

    More detailed code:
    PHP Code:
        $sql "SELECT * FROM nudges WHERE sender='$clientID' AND s_status IN (1,0) AND accepted=1
                        UNION 
                        SELECT * FROM nudges WHERE receiver='$clientID' 
                        ORDER BY posted DESC "
    ;
        
    $result=$mysqli->query($sql);
                                        
        while(
    $r $result->fetch_assoc()) {
            
    $sender=$r['sender'];
            
    $receiver=$r['receiver'];
            
        if(
    $receiver == $clientID) {
            
                
    $sql $mysqli->query("SELECT * FROM nudges WHERE receiver='$clientID' ORDER BY posted DESC");
                                        
                while(
    $r $sql->fetch_assoc()) {
                    
    $posted date(" F j",$r[posted]);
                    
    $id=$r['id'];
                    
    $sender=$r['sender'];
                    
    $receiver=$r['receiver'];
                    
    $r_status=$r['r_status'];
                    
    $s_status=$r['s_status'];
                    
    $accepted=$r['accepted'];
                    
    $result $mysqli->query("SELECT city, state,    birth_date,
                                                                        CURDATE(),
                                                                        (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)<RIGHT(birth_date,5))
                                                                        AS age FROM user WHERE userID = '$sender'"
    );
                        while (
    $r=$result->fetch_assoc()) {
                            
    $city $r['city'];
                        
                            echo 
    "<br>Received $receiver - $sender $city";
                        }
                }
            }

            if(
    $sender == $clientID){
            
                
    $sql $mysqli->query("SELECT * FROM nudges WHERE sender='$clientID' ORDER BY posted DESC");

                while(
    $r $sql->fetch_assoc()) {
                    
    $posted date(" F j",$r[posted]);
                    
    $id=$r['id'];
                    
    $sender=$r['sender'];
                    
    $receiver=$r['receiver'];
                    
    $result $mysqli->query("SELECT city, state,    birth_date,
                                                                        CURDATE(),
                                                                        (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)<RIGHT(birth_date,5))
                                                                        AS age FROM user WHERE userID = '$receiver'"
    );
                    while (
    $r=$result->fetch_assoc()) {
                        
    $age $r['age'];
                        
    $city $r['city'];

                        echo 
    "<br>Sent $sender - $receiver $city";
                
                    }
                }
            }

        } 
    // END while 
    What I get from this is:
    Sent test - harkly Winston Salem
    Sent test - charlieMain Winston Salem


    How can I pull info for both if statements??

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    I'm not sure a union is what you want to do here (unions do not consider relations). And you definitely don't want to stack up the queries within a while, so you need to join. What I'm not sure of yet is if you want to use a union of joins or if you can get away with just joins.

    What is the overall picture you have here? Are you simply wanting to take the city, state, birth_date, and age for each user and associate the sender and receiver?
    If so, to me this appears what you want to do is:
    Code:
    SELECT s.city AS sender_city, s.state AS sender_state, s.birth_date AS sender_birth_date, YEAR(CURDATE()) - YEAR(s.birth_date) AS sender_age, r.city AS receiver_city, r.state AS receiver_state, r.birth_date AS receiver_birth_date, YEAR(CURDATE()) - YEAR(r.birth_date) AS receiver_age, n.sender, n.receiver, n.r_status, n.s_status, n.accepted, n.id
    FROM nudges n
    INNER JOIN user s ON s.userID = n.sender
    INNER JOIN user r ON r.userID = n.receiver
    WHERE n.sender = '$clientID' AND n.s_status IN (0, 1) AND n.accepted = 1
    ORDER BY n.posted ASC
    Where it gathers the city, state, birth_date and age of the sending user, as well as the associated sender, reciever, r_status, s_status, accepted and id from the nudges table.

    When you run that query does it give you all the data you expect to receive?

  • #3
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    I am using the Union to pull out users based on the 2 different credentials


    I need to pull out all users where
    PHP Code:
    WHERE sender='$clientID' AND s_status IN (1,0) AND accepted=
    and
    PHP Code:
    sender='$clientID' AND s_status IN (1,0) AND accepted=

    table layout:

    nudge
    ID sender receiver posted s_status r_status accepted


    Then I want to pull their info from another table (users)


    I am not exactly sure what is going on with the SELECT you did, not familiar with that layout - still using the simple versions

    I know that this "s.city AS sender_city" is changing the name to sender_city but how does it know what s.city is? I know this is probably a stupid question

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    The condition you have only applies to the first query in the union. The second query doesn't have that condition. You can use a select. . . (select . . . union. . . select). . . where query to take the where condition result of the two select statement's unioned together. Not really ideal since you need to iterate the entire recordset.

    Easy enough to add that refinement to the where clause of a join to put both sender and receiver. The 's' comes from the inner join on the user table: inner join user s is identical to inner join user AS s, so the s is now the aliased name to the table user. Again, a join is performed on user AS r, so that aliases the user to r. The s and r were chosen to represent "sender" and "receiver" based on their ON conditions. Since this table joins user table into it twice, you'll need to provide aliasing to determine which table it draws its results as well as which table and fields it uses for its where clauses. Without an alias it would likely trigger an ambiguous error.

  • #5
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    I've played with the SELECT all weekend and was able to get to work.

    I used a UNION so I could pull based on the 2 different WHERE options I needed.

    Before I go and add all the other stuff to it I just want to make sure I am on the right track

    Both of the SELECTs are identical except the WHERE clause

    PHP Code:
    SELECT 
        s
    .city AS sender_city
        
    s.state AS sender_state
        
    r.city AS receiver_city
        
    n.sender
        
    n.receiver
        
    n.r_status
        
    n.s_status
        
    n.accepted
        
    n.posted
        
    n.id

    FROM nudges n

    INNER JOIN user s ON s
    .userID n.sender
    INNER JOIN user r ON r
    .userID n.receiver

    WHERE sender
    ='$clientID' AND s_status IN (1,0) AND accepted

    UNION

    SELECT 
        s
    .city AS sender_city
        
    s.state AS sender_state
        
    r.city AS receiver_city
        
    n.sender
        
    n.receiver
        
    n.r_status
        
    n.s_status
        
    n.accepted,
        
    n.posted
        
    n.id

    FROM nudges n

    INNER JOIN user s ON s
    .userID n.sender
    INNER JOIN user r ON r
    .userID n.receiver

    WHERE receiver
    ='$clientID' 

    ORDER BY posted DESC 

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    I still see no reason for a union here.
    Code:
    SELECT 
        s.city AS sender_city, 
        s.state AS sender_state, 
        r.city AS receiver_city, 
        n.sender, 
        n.receiver, 
        n.r_status, 
        n.s_status, 
        n.accepted, 
        n.posted, 
        n.id
    
    FROM nudges n
    
    INNER JOIN user s ON s.userID = n.sender
    INNER JOIN user r ON r.userID = n.receiver
    
    WHERE (s.userID='$clientID' AND n.s_status IN (1,0) AND n.accepted=1) OR r.userID='$clientID'
    Will return where the receiver is clientID OR (sender is cliendID AND statis is 1 or 0 AND accepted is 1 [which I added since you didn't have a condition for it]).

  • #7
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    Your right! I just couldn't get the correct wording for the WHERE clause to do both conditions.


  •  

    Posting Permissions

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