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

Thread: Need Query Help

  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Query Help

    I've been trying to create a query that will match up a team's NFL abbreviation that i have in a database to an existing php variable, and then get the URL and place the result into a link.

    I currently have three php variables of these:

    AWAY_TEAM_ABBREVIATION
    HOME_TEAM_ABBREVIATION
    BYTEAM_ABBREVIATION

    I also have a table called nfl_teams, in which I have each teams abbreviation and there appropriate url.

    What I've tried is this (testing locally):
    Code:
    $user=          "root";
    $host=          "localhost";
    $password=      "";
    $databasename = "xxxxxxx";
    mysql_connect($host,$user,$password);
    mysql_select_db($databasename);
    $nfl = "SELECT nfl_website FROM nfl_teams WHERE abbreviation = AWAY_TEAM_ABBREVIATION";
    	$nflwebsite = mysql_query($nfl);
    I then tried to creat the link using the $nflwebsite result, and I know that is wrong, as I can't get it to work. But I also need to be able to have it compare to all three of the php variables. Is this something easy or complex?

  • #2
    New Coder
    Join Date
    May 2003
    Location
    UK
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check query syntax

    It could be that away_team_abbreviation is text?

    If it is then the query should read


    abbreviation = '$AWAY_TEAM_ABBREVIATION'


    for all 3 variables

    WHERE ((abbreviation = '$AWAY_TEAM_ABBREVIATION') OR (abbreviation = '$HOME_TEAM_ABBREVIATION') OR (abbreviation = '$BYTEAM_ABBREVIATION'))
    They only tell you its a stupid question when they don't know the answer!

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the help. Thats gotten me closer at least.

    Its looks to start, but stops at the first match from each one, so I think I need to loop it somehow. That, and the link doesn't want to work correctly. The link shows as 'Resource ID #2', and not the www.website.com that i have in my table.

    Once I get my highspeed connection fixed at home, I can hopefully research this alot faster too.

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I see two problems:
    - the select --> this conditions will return three rows, but how will you know which is the awway or home or bytteam?
    So i'm afraid you need to run it as three separated selects
    - the $nflwebsite = mysql_query($nfl);
    $nflwebsite will countain the resource identifier to the returned recordset. You need this resource identifier to get values from the recordsets variabels. But you don't show any code for that. You can for instance use
    http://be2.php.net/manual/en/functio...etch-array.php

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Guys, thanks for your help with this, and I think (or hope) I'm almost there. I've also picked up a few books, but still struggling a little

    Here's the thing, I'm using some software that uses templates to create some php webpages. The template creates an NFL football schedule that includes all the teams logos. A sample line of the template looks like this.
    {NFL_Schedule.Week.Games.AWAY_TEAM_ABBREVIATION}.gif
    . Now, I think thats a php variable, as I've never used the {}. That variable is creating the template using all the nfl team logos, so they look like SF.gif or NYG.gif, etc. I'm attempting to turn those images into links to each of their respective NFL official team sites. So I've create a sql table that is basically just 2 columns, called abbreviation and nfl_website, and I'm trying to get the logos' to their official nfl websites. So that when I have a abbrevation of SF in my sql table matching the AWAY_TEAM_ABBREVIATION, it will insert the correct web path into the url.

    So, all that said, here's what I've got
    PHP Code:
    $user=          "xxxxx";
    $host=          "localhost";
    $password=      "xxxxxx";
    $databasename "xxxxxx";
    mysql_connect($host,$user,$password);
    mysql_select_db($databasename);


    $query "SELECT * FROM nfl_teams";
         
    $result mysql_query($query);
         
    $row    mysql_fetch_array($result);

    while(
    $row=mysql_fetch_array($result)){
    echo 
    $row['nfl_website'];
    }

    $nflwebsite "(abbreviation = '$NFL_Schedule.Week.Games.AWAY_TEAM_ABBREVIATION')"
    I know I dont' need the echo $row['nfl_website']; but just have that to make sure I am at least reading it. But how do I get this to compare the two, so that it inserts correctly into the a href link?

    That link is trying to use the $nfl_website variable like this:

    <a href="'.$nfl_website.'"> There's more to that as it includes the <image> tag as well. But I just can't seem to get all quite right.

    Any Ideas? Am I least on the right path?
    Last edited by Switch17; 07-19-2003 at 01:19 AM.

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can anyone help? I'm still trying to figure this out, but I'm not the programming expert like the rest of you.

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm on my way to party, but maybe tomorrow.
    Can you tell me what you mean by

    I know I dont' need the echo $row['nfl_website']; but just have that to make sure I am at least reading it. But how do I get this to compare the two, so that it inserts correctly into the a href link?
    Are you selectoing the url from the db, and then want to set it as the value for href="" ?

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK. I read all that but i stille don't exactly get the setup.

    I suppose you need something like
    PHP Code:
    $logo="{NFL_Schedule.Week.Games.AWAY_TEAM_ABBREVIATION}.gif";  // or whatever other code that generates the file-adress to the teams logo
    echo ("<a href=\"" $row['nfl_website'] . "\"><img height=\"60\" width=\"150\" src=\"" $logo .  "\" border=\"1\" alt=\"$row['teamname-variable in db']\"></a>"); 
    No ?
    This will print the logo as a link, with $row['nfl_website'] as url, and $row['teamname-variable in db'] as alternative text if the image isn't found or can't be displayed in the browser.
    Last edited by raf; 07-19-2003 at 11:04 AM.

  • #9
    Regular Coder
    Join Date
    Jun 2002
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    raf.

    thansk for the help. My sites been down since last friday, and i'm hoping to get it back up this week. Once thats done, I'll try what you said.

    Thanks again
    Switch

  • #10
    Regular Coder
    Join Date
    Jun 2002
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, my sites back up and running, and i've stripped the entire report i'm trying to create down to just trying to get this to work.

    I've got this"
    PHP Code:
    $query "SELECT * FROM nfl_teams";
             
    $result mysql_query($query);
             
    $row    mysql_fetch_array($result);

    while(
    $row=mysql_fetch_array($result)){
    echo 
    $row['nfl_website'];
    }

    $logo="{NFL_Schedule.Week.Games.AWAY_TEAM_ABBREVIATION}.gif";  // or whatever other code that generates the file-adress to the teams logo

    echo ("<a href=\"" $row['nfl_website'] . "\"><img height=\"22\" width=\"30\" src=\""$logo "\" border=\"1\"></a>"); 
    Now, the echo $row['nfl_website']; is printing the web address for each teamn, so I know i'm finding the result.

    However, the link:
    <a href=\"" . $row['nfl_website'] . "\">
    is not showing up. The $logo is found no problem though

    I'm starting to think I may be able to do all this easier with an IF THEN statement. So that if AWAY_TEAM_ABBREVIATION is equal to the $logo name, then give me the nfl_website.

    I'm gonna look into it some more, but if anyone can help out a newbie at this, i'd be very grateful.

  • #11
    Regular Coder
    Join Date
    Jun 2002
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, still plugging along and learning here.

    I've gotten the url figured out, but can't seem to match it correctly to its correct row in the table.

    This here will at least give me one of the row's website paths, however it gives me the last row found, when i need to match up correctly:
    PHP Code:
    while ($row=mysql_fetch_array($result)){
    $nflwebsite $row['nfl_website'];

    So I tried this, but it doesn't seem to want to work:
    PHP Code:
    if ($row['abbreviation'] == {NFL_Schedule.Week.Games.AWAY_TEAM_ABBREVIATION}) {
       
    $nflwebsite $row['nfl_website'];
    } else {
       echo 
    "There is no match";

    Getting closer, but could always use anyone's help. Quess this is now definitely a php problem.

  • #12
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wait a minute.

    A few post up, you say the logo is found but the link isn't shown --> the image (the logo) is the link. If you click on the link, the url you select from 'nfl_website' will be requested.
    If you look at the outfuppet ftml (source in your browser), what do you get there then?

    I don't understand he thing about the comparison. You need to use the teamabreviation in your select (in the where clause, like in the reply to your first post).

    Also, that
    $row = mysql_fetch_array($result);
    should be left out

  • #13
    Regular Coder
    Join Date
    Jun 2002
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My apologies for the confusion. I had tried out what you posted earier, but coundn't get to work correctly. What was happening is that I would get the logo to show up, but when you moused over, there was no link showing.

    I've made some more progress, and now have the logo matching up to its link, but only for the first row of my table. I think now I just need to figure out how to get it to loop through the remainder of the table. This is where I currently stand, which works the first row of the table:
    PHP Code:
    $query "SELECT * FROM nfl_teams"
             
    $result mysql_query($query);

    while (
    $row=mysql_fetch_array($result)){
    $abbreviation $row['abbreviation'];
    $awaylogo = {NFL_Schedule.Week.Games.AWAY_TEAM_ABBREVIATION};
    $nflwebsite $row['nfl_website'];
    echo 
    $abbreviation;

    if (
    $abbreviation == $awaylogo){
        
    $logo="{NFL_Schedule.Week.Games.AWAY_TEAM_ABBREVIATION}.gif";
        
    $nflwebsite $row['nfl_website'];
        echo 
    '<a href="http://'.$nflwebsite.'"><img src='$nflicon $logo .' border=0 height=50 width=50></a>'
      } else {
        echo 
    'There were no matches found';
      }


    Any idea where to go next from here?

  • #14
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Why not simply
    PHP Code:
    $query="SELECT * FROM nfl_teams where abbreviation='" . {NFL_Schedule.Week.Games.AWAY_TEAM_ABBREVIATION} . "'";

    $result=mysql_query($query);
    if (
    mysql_num_rows($result)==0){   // so if the abbreviation is not found
      
    echo ("<br/>No rows found");
    }
    else_{
     while (
    $row=mysql_fetch_array($result)){    // each row will be processed
      
    echo $row['abbreviation'];  // which can be left off
      
    $logo$row['abbreviation']. ".gif";   
      echo (
    "<a href=\"http://".$row['nfl_website']."\"><img src=\". $nflicon . $logo ." border="0\" height=\"50\" width=\"50\"></a>");
     }

    The $logo line is also not necessary, but it makes the sourcevalue of the image more readable. The $nflicon is new ?

  • #15
    Regular Coder
    Join Date
    Jun 2002
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One more step closer. For some reason again, I couldn't seem to get your link to work correctly, and had to alter it very slightly (its something in the img tag). Anyway that line now looks like this:
    PHP Code:
    echo ("<a href=\"http://".$row['nfl_website']."\"> <img src="$nflicon $logo ." border=0 height=22 width=30></a>"); 
    FYI, the $nflicon is something new to you. I've been leaving it off the posts as to not cause additional confusion. I have an include file at the top off all my pages in which I point to all of my image directories. Thus the $nflicon is pointing to where I have all my logo's stored. The idea there is to let anyone use the template, and store there images where ever they want and only change one file.

    Anyway, moving on. When I use your query, it will stop at the first match, which is SF, which happens to show as the first game in week 1. I think it has to do with, once it finds a match, its stopping. So by removing the
    PHP Code:
    where abbreviation='" . {NFL_Schedule.Week.Games.AWAY_TEAM_ABBREVIATION} . "' 
    I can now at least show all of the teams, and there correct website paths.

    Now, I just need to figure out how to show all of this in the actual schedule.

    For example, the template I have that creates the schedule is a little complex in that it places each team (either away or home)correctly based upon its week. A sample of the home team line looks like this.

    <td align=left nowrap class="row{NFL_Schedule.Week.Games.CLASS}">&nbsp; at &nbsp; <?php echo '<img src='.$nflicon.'{NFL_Schedule.Week.Games.HOME_TEAM_ABBREVIATION}.gif border=0 height=22 width=30>';?> {NFL_Schedule.Week.Games.HOME_TEAM_LOCATION} {NFL_Schedule.Week.Games.HOME_TEAM_MASCOT} ({NFL_Schedule.Week.Games.HOME_TEAM_ABBREVIATION})</td>
    I know I'm alot closer now than I was. Just need to figure out how to pull it all together.

    If you want to see where I stand, my test page is Here. Of Course I'll be removing the logo's accross the top, and that table as well once I get it all working.
    Last edited by Switch17; 07-24-2003 at 05:11 PM.


  •  

    Posting Permissions

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