44 users online (0 members and 44 guests)  


  Results 1 to 7 of 7

Related

  1. Replies: 0
  2. Replies: 1
  3. Replies: 6
  4. Multiple Select Issue    Forum: Javascript Forum
    Replies: 2
  5. MySQL Select statement    Forum: Databases
    Replies: 1
  1. #1
    antonyjoy34's Avatar
    New User

    Status
    Offline
    Join Date
    Jun 2009
    Posts
    4

    Exclamation Need Help : mysql advanced SELECT issue

    I am Joy new to this forum I really need a help,

    I have a mysql table

    Field Type
    match_id, int(10)
    match_date, date
    match_time, time
    match_round, int(10)
    home_team, varchar(50)
    away_team, varchar(50)
    prediction, varchar(50)
    home_score, int(3)
    away_score, int(3)
    form, varchar(50)

    and I am trying to retrieve data as the query mentioned below,

    PHP Code:
    if (isset($_GET['home_team'])) {
    $home_team mysql_real_escape_string($_GET['home_team']);
        
        }
        if (isset(
    $_GET['away_team'])) {
    $away_team mysql_real_escape_string($_GET['away_team']);
        
        }
        if (isset(
    $_GET['match_id'])) {
    $match_id mysql_real_escape_string($_GET['match_id']);
        
        }

    $query "SELECT  match_id, match_date, LEFT (match_time, 5)  match_time, home_team, away_team, home_score, away_score
     
              FROM premier_league2 
              WHERE    home_team ='" 
    .$home_team ."'
                      AND    match_id !='" 
    $match_id ."'
              ORDER BY match_date DESC LIMIT 5 "
    ;

              echo 
    "<table>";
               
    $result mysql_query($query);
            while(
    $row2 mysql_fetch_array$result )) {
                echo 
    "<tr><td>"
                echo 
    $row2['match_date'];
                echo 
    "</td><td>"
                echo 
    $row2['home_team'];
                echo 
    "</td><td>"
                echo 
    $row2['home_score'];
                echo 
    "</td><td>"
                echo 
    $row2['away_score'];
                echo 
    "</td><td>"
                echo 
    $row2['away_team'];
                echo 
    "</td><tr>"
                }

        echo 
    "</table>"
    up to here every thing works fine and I am getting the result as expected

    2009-05-31 teamA 0 1 teamB
    2009-05-17 teamA 1 1 teamC
    2009-05-03 teamA 2 0 teamD

    now what i trying to pull out all the matches played the teamA either as home_team or away_team the code is

    PHP Code:
    $query "SELECT  match_id, match_date, LEFT (match_time, 5)  match_time, home_team, away_team, home_score, away_score
     
              FROM premier_league2 
              WHERE    home_team ='" 
    .$home_team ."'
              AND     away_team ='" 
    $away_team ."'
              AND    match_id !='" 
    $match_id ."'
              ORDER BY match_date DESC LIMIT 5 "
    ;

              echo 
    "<table>";
               
    $result mysql_query($query);
            while(
    $row2 mysql_fetch_array$result )) {
                echo 
    "<tr><td>"
                echo 
    $row2['match_date'];
                echo 
    "</td><td>"
                echo 
    $row2['home_team'];
                echo 
    "</td><td>"
                echo 
    $row2['home_score'];
                echo 
    "</td><td>"
                echo 
    $row2['away_score'];
                echo 
    "</td><td>"
                echo 
    $row2['away_team'];
                echo 
    "</td><tr>"
                }

        echo 
    "</table>"

    this gives me nothing, in this code i put AND away_team ='" . $away_team ."' after WHERE home_team ='" .$home_team ."' and not even a signal line is echoed back, i am totally stuck here. so i am looking out here some help to figure out this.

    I am appreciated the time you taking to help me Thanking you!

    Regards
    Joy

  2. #2
    jthayne's Avatar

    Status
    Offline
    Join Date
    Aug 2008
    Location
    Texas
    Posts
    508

    Re: Need Help : mysql advanced SELECT issue

    On the line imediately following where you define the variable $query, add the following line:
    PHP Code:
    echo $query
    so it will look like:
    PHP Code:
    $query "SELECT  match_id, match_date, LEFT (match_time, 5)  match_time, home_team, away_team, home_score, away_score
     
              FROM premier_league2 
              WHERE    home_team ='" 
    .$home_team ."'
              AND     away_team ='" 
    $away_team ."'
              AND    match_id !='" 
    $match_id ."'
              ORDER BY match_date DESC LIMIT 5 "

    echo 
    $query
    If that looks like it should work, then you can PM me the query that prints out, or post it in the thread.

  3. #3
    antonyjoy34's Avatar
    New User

    Status
    Offline
    Join Date
    Jun 2009
    Posts
    4

    Re: Need Help : mysql advanced SELECT issue

    Thank you for you replay, but it just echoed back the variable query it not solves the problem. What I am looking for is I am trying to pull out the rows teamA played regardless as home_team or away_team – last five matches of teamA. The result I am expecting like this (in red teamA's away matches)

    2009-05-31 teamA 0 1 teamB
    2009-05-24 teamE 1 2 teamA
    2009-05-17 teamA 1 1 teamC
    2009-05-10 teamF 2 2 teamA
    2009-05-03 teamA 2 0 teamD

    for this kind of result (please ignore the text color) what the modification should I do in my code please help me, I am new to php and mysql so please ignore my lack of knowledge if I am wrong.

  4. #4
    jthayne's Avatar

    Status
    Offline
    Join Date
    Aug 2008
    Location
    Texas
    Posts
    508

    Re: Need Help : mysql advanced SELECT issue

    The code I gave you was not supposed to solve anything. It was supposed to print out the query so that you could see if there was anything wrong with the query itself.

    I did not understand what you were looking for earlier. However, now I do, and you need to make the following changes to your query in order to include the away games as well:

    Code:
    $query = "SELECT  match_id, match_date, LEFT (match_time, 5)  match_time, home_team, away_team, home_score, away_score
     
              FROM premier_league2 
              WHERE (home_team ='" .$home_team ."'
              OR away_team ='" . $away_team ."')
              AND    match_id !='" . $match_id ."'
              ORDER BY match_date DESC LIMIT 5 ";
    The primary difference is in the use of the OR keyword. To explain the difference, read the following demonstration:

    In order to determine the answer to a question, the definitions of OR and AND are as follows:

    AND -- In order to continue, BOTH the condition on the left and the condition on the right MUST be evaluated as true.
    Ex.: 2=2 AND 2=3. This expression will be evalutated as FALSE because only one of the expressions is true, not both.

    OR -- In order to continue, EITHER the condition on the left OR the condition on the right must be evaluated as true.
    Ex.: 2=2 AND 2=3. This expression will be evalutated as TRUE even though 2 does not equal 3 because only one of the expressions needs to be true, not both.

  5. #5
    antonyjoy34's Avatar
    New User

    Status
    Offline
    Join Date
    Jun 2009
    Posts
    4

    Re: Need Help : mysql advanced SELECT issue

    Thank you very much Jthayne problem solved, you are a good teacher too well explained AND, OR differences. I am also tried the OR statement but forgot to put the brackets what you did, that’s where I got stuck totally

    PHP Code:
    $query "SELECT  match_id, match_date, LEFT (match_time, 5)  match_time, home_team, away_team, home_score, away_score 
     
              FROM premier_league2 
              WHERE    (home_team ='" 
    .$home_team ."'
              OR    away_team ='" 
    .$home_team ."')  
              AND    match_id !='" 
    $match_id ."'
              ORDER BY match_date DESC LIMIT 5 "

    Now it make sense right forgive me that’s my ignorance and now I am getting the result what I am expected

    2009-05-31 teamA 0 1 teamB
    2009-05-24 teamE 1 2 teamA
    2009-05-17 teamA 1 1 teamC
    2009-05-10 teamF 2 2 teamA
    2009-05-03 teamA 2 0 teamD

    Once again Thank you very much Jthayne

  6. #6
    jthayne's Avatar

    Status
    Offline
    Join Date
    Aug 2008
    Location
    Texas
    Posts
    508

    Re: Need Help : mysql advanced SELECT issue

    Glad to be of assistance. It actually took me a while to figure out the brackets at first. I am just glad we were able to get it working.

  7. #7
    antonyjoy34's Avatar
    New User

    Status
    Offline
    Join Date
    Jun 2009
    Posts
    4

    Re: Need Help : mysql advanced SELECT issue

    yes it working well jthayne thank you very much.



Tags for this Thread