SitePoint Sponsor

User Tag List

Results 1 to 24 of 24

Thread: Weird Query

  1. #1
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Weird Query

    Can anyone try to figure out why this query keeps failing with the following error:

    #1054 - Unknown column 'atm_area_ride.ride_id' in 'where clause'

    The query I am using is:

    SELECT ride_name, area_name
    FROM atm_rides, atm_areas
    WHERE atm_rides.ride_id =34
    AND atm_area_ride.ride_id =34

    All the table and field names are correct.


    Please help.

    Thanks

    Neil

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you do not have a table called atm_area_ride

    instead of atm_area_ride.ride_id, it should be atm_areas.ride_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As a matter of fact, I do have a table called atm_area_ride

    basically i have these 3 tables:

    atm_rides

    atm_area_ride

    atm_areas



    atm_rides and atm_areas hold the actual details where as atm_area_ride is just a linking table between the two.

    But it still does not work.

  4. #4
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should mention the table in FROM

    Code MySQL:
    SELECT ride_name, area_name
    FROM atm_rides, atm_area_ride
    WHERE atm_rides.ride_id =34
    AND atm_area_ride.ride_id =34

  5. #5
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, Thanks for that. After all of that the outcome was not relaly what I expected.

    heres what I want the query to show:

    Hex | The Towers

    I have 3 tables:

    atm_rides

    Thsi table is as follows:

    ride_id | ride_name
    34 | Hex



    atm_areas:

    area_id | area_name
    8 | The Towers


    atm_area_ride:

    ride_id | area_id
    34 | 8



    How can this be achieved?

    Thanks

    Neil

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select r.ride_name
         , a.area_name
      from atm_rides as r
    inner
      join atm_area_ride as ar
        on ar.ride_id = r.ride_id
    inner
      join atm_areas as a
        on a.area_id = ar.area_id
     where r.ride_id = 34
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don;t understand what all the r's, a's and ar's are?

  8. #8
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another approach
    Code:
    SELECT ride_name, area_name
    FROM atm_rides, atm_areas, atm_area_ride 
    WHERE atm_rides.ride_id =34
    AND atm_rides.ride_id = atm_area_ride.ride_id
    AND atm_areas.area_id = atm_area_ride.area_id
    @rudy
    which one is better?

  9. #9
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From the two choices. I would prefer to use the second one as i can understand how it's working.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    JOIN syntax is much better

    you will learn this as you write more complex queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rctneil View Post
    I don;t understand what all the r's, a's and ar's are?
    These are table aliases, after defining aliases you can use them instead of fulltable names.

    Visit the link http://www.1keydata.com/sql/sqlalias.html

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rctneil View Post
    I don;t understand what all the r's, a's and ar's are?
    those are table aliases -- they make your query easier to read

    compare this --
    Code:
    select r.ride_name
         , a.area_name
      from atm_rides as r
    inner
      join atm_area_ride as ar
        on ar.ride_id = r.ride_id
    inner
      join atm_areas as a
        on a.area_id = ar.area_id
     where r.ride_id = 34
    with this --
    Code:
    select atm_rides.ride_name
         , atm_areas.area_name
      from atm_rides
    inner
      join atm_area_ride
        on atm_area_ride.ride_id = atm_rides.ride_id
    inner
      join atm_areas 
        on atm_areas.area_id = atm_area_ride.area_id
     where atm_rides.ride_id = 34
    you don't recall our previous threads on these topics?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do recall them and never understood what they are no matter the amount of times you explained them to me. Let me try and understand them now.

  14. #14
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I get the aliases, sort of. I really do not understand joins even reading the example above and reading through tutorials on them. I'll use the and queries for now and when i understand the need for joins, I can easily convert them.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you will understand the need for JOIN syntax the moment you need an outer join -- rows from one table with or without rows from another, in those instances where not every row has a match
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,
    Can anyone help me create a query to show up the names of all rides which are in a certain area when given the area_id usingt he 3 tables posted in a previous post? Please use "and"s instead of joins until I understand them better.

  17. #17
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rctneil View Post
    Hello,
    Can anyone help me create a query to show up the names of all rides which are in a certain area when given the area_id usingt he 3 tables posted in a previous post? Please use "and"s instead of joins until I understand them better.
    Same query Rudy wrote for you, with an area ID as the WHERE condition instead of a ride ID.

  18. #18
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, got the query working in phpmyadmin but when i put it into my page i just get the below error:

    Resource id #9

    Heres the code i'm using:

    <?php
    $sql2=mysql_query("SELECT ride_name
    FROM atm_rides, atm_areas, atm_area_ride
    WHERE atm_areas.area_id = '$areaid'
    AND atm_rides.ride_id = atm_area_ride.ride_id
    AND atm_areas.area_id = atm_area_ride.area_id");

    echo $sql2;
    ?>

    Can anyone help?

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if your query works correctly outside of your php code, then your problem isn't a mysql problem, it's a php problem

    moving thread to the php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, Can anyone help me figure out why the query output is not showing up on my page within the php code?

  21. #21
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't echo the return value of mysql_query because it's a ressource. You have to fetch the data from the ressource, like

    PHP Code:
    while($arr mysql_fetch_assoc($sql2)) {
      echo 
    nl2br(print_r($arrtrue));

    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  22. #22
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,898
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that. That does echo out the ride name sbut along with a load of php code which i do not want. I ahve tried the below code:

    $getridelist=mysql_fetch_array($sql2);

    echo $getridelist['ride_name'];

    but using that I get the first ride but none of the others. How can i adapt this code to output the entire list?

  23. #23
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    use while loop
    PHP Code:
    while($getridelist=mysql_fetch_array($sql2))
    {
    echo 
    $getridelist['ride_name'] . "<br>";

    Barbara

  24. #24
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rctneil View Post
    Thanks for that. That does echo out the ride name sbut along with a load of php code which i do not want. I ahve tried the below code:

    $getridelist=mysql_fetch_array($sql2);

    echo $getridelist['ride_name'];

    but using that I get the first ride but none of the others. How can i adapt this code to output the entire list?
    Do multiple people use your SitePoint username? I thought we had helped you with more complex queries and the code to retrieve the results many times in the past here and in the PHP forum... you seem to have forgotten *all* of it, and can't find any of your old code to see how!


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
  •