[SOLVED] Joining tables

[rvdb86]

Hi, i have the following code that joins two tables (tbl_customers and tbl_sites):

 

$query = "SELECT * FROM tbl_customers, tbl_sites ".
							"WHERE tbl_customers.customer_site = tbl_sites.site_id";

 

this selects all the records from the database. how would i modify the query to join only the fields where customer_site and site_id = 25 for example?

 

Thanks for any suggestions!

[allworknoplay]

Hmm well this is a shot in the dark..

 

 

$query = "SELECT * FROM tbl_customers, tbl_sites WHERE tbl_customers.customer_site = tbl_sites.site_id AND tbl_sites.site_id = '25' ";

[rvdb86]

thanks for trying to help but this only gives me the results from the tbl_sites table 

[allworknoplay]

Right, you never said what table you want the 25 on...

 

I don't think you can just say site_id=25, you would get an ambiguous error...

[sasa]

thanks for trying to help but this only gives me the results from the tbl_sites table 

are you try allworknoplay's code?

 

[rvdb86]

sorry posted that before i saw your post.

 

yeh i tried allworknoplay's code and it just gave me the results of the tbl_sites.

 

the values are the same in both tables ($customer_site = 25 AND $site_id = 25)

[revraz]

Provide a table structure for both tables as well as sample data.

[rvdb86]

tbl_customers:

 

customer_id SMALLINT (primary)

customer_name VARCHAR (50)

customer_email VARCHAR (100)

customer_site SMALLINT

 

tbl_sites:

 

site_id SMALLINT (primary)

site_name VARCHAR (50)

site_template VARCHAR (50)

 

|  customer_id  |  customer_name  |  customer_email  | customer_site  |

|        1        |        james          | james@test.com |      25          |

 

|  site_id  |  site_name  | site_template  |

|    25    |    mysite    |      sample      |

 

Hope this helps!

[revraz]

SELECT tbl_customers.customer_name, tbl_sites.site_name, tbl_sites.site_template FROM tbl_customers JOIN tbl_sites ON (tbl_customers.customer_site = tbl_sites.site_id) WHERE tbl_customers.customer_site = 25;

[rvdb86]

hey,

 

just tried that code, but again its not showing the results from the tbl_customers ???

 

i really appreciate the time you are taking to try and help me.

 

at the moment i use the following code that gets me the results:

 

$query = "SELECT * FROM tbl_customers WHERE customer_email = 'james@test.com'";
$result = mysql_query($query)
   or die ("Couldn't get customer details");
   while ($row = mysql_fetch_array($result))
     {
      extract($row);

       query1 = "SELECT * FROM tbl_sites WHERE site_id = '$customer_site'";
       $result1 = mysql_query($query1)
            or die ("Couldn't get site details");
            while ($row1 = mysql_fetch_array($result1))
             {
                echo $customer_name;
                echo $site_template;
              }
       }

 

this works but i wasn't sure if it is the correct way to do things. maybe i was wrong and i will carry on like this or maybe there is a better solution?

[allworknoplay]

Honestly, I'm not sure why my SQL doesn't work for you. It should give you all the info from both tables where the site_id = 25...

 

Not sure why it would just output only ONE of the tables.........

[revraz]

I don't think it's the SQL that's the problem, I think it's how you are actually displaying it that's the problem.

 

while ($row1 = mysql_fetch_array($result1))

            {

                echo $customer_name;

                echo $site_template;

              }

 

You don't even use the $row1 array that you created there.  Are you familiar with how to actually display the data you are pulling out of the DB?

 

So the code you used when you tried my SQL.

[rvdb86]

sorry revraz, i went back and checked again and the script was not actually printing it correctly (i had checked it but missed a obvious mistake)

 

thank you so much for your patience and it works like a dream!