Weird Query

[rctneil]

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

[r937]

you do not have a table called atm_area_ride

instead of atm_area_ride.ride_id, it should be atm_areas.ride_id

[rctneil]

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.

[mwasif]

You should mention the table in FROM

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

[rctneil]

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

[r937]

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

[rctneil]

I don;t understand what all the r's, a's and ar's are?

[mwasif]

Another approach

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?

[rctneil]

From the two choices. I would prefer to use the second one as i can understand how it's working.

[r937]

JOIN syntax is much better

you will learn this as you write more complex queries

[mwasif]

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

[r937]

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

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

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?

[rctneil]

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.

[rctneil]

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.

[r937]

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

[rctneil]

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.

[Dan Grossman]

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.

[rctneil]

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?

[r937]

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

[rctneil]

So, Can anyone help me figure out why the query output is not showing up on my page within the php code?

[kleineme]

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

while($arr = mysql_fetch_assoc($sql2)) {
  echo nl2br(print_r($arr, true));
} 

[rctneil]

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?

[barbara1712]

use while loop

while($getridelist=mysql_fetch_array($sql2))
{
echo $getridelist['ride_name'] . "<br>";
} 

[Dan Grossman]

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!