# Database Discussions > MySQL >  new to table  joins?

## lukeurtnowski

I have an activity table which has A_ID (PK), C_NUMBER (FK), A_DATE, A_DESC, and a MEDIVAS field. When I perform a search by date, all the dates in that column show up in a combo box, when a cerrtain date is selected, I'm trying to output all the info for that activity (description, medivas) as well as the coresponding company information (C_NUMBER) would be its unique identifier.

how do I do this using table joins?
thanks

----------


## oraclable

select a.A_fields, c.C_fields from activity a, company c
where a.A_DATE in (set of date) 
and a.C_number=c.C_number

Good luck!

----------


## lukeurtnowski

when this is run and I echo tha variable, I get "Resource id #2", and not three seperate numbers
?php
$hostname = "10.0.0.127";
$username = "lukeu";
$password = "blues7";
$database = "test";
$connection = mysql_connect($hostname , $username , $password)
or die("cannot make connection");
$db = mysql_selectdb($database , $connection)
or die("cannot find database");
$sql="Select * From COMPANY As C Inner Join ACTIVITY AS A On A.C_NUMBER = C.C_ID
Where A.A_DATE = '$date'";
$result = mysql_query($sql) or die(mysql_error());
//select a.A_fields, c.C_fields from activity a, company c
//where a.A_DATE in '$date'
//and a.C_number=c.C_number
//$numofrows = mysql_num_rows($result); 
echo $result;
?>

Thanks.

----------


## oraclable

do not echo the variable $result
this should be the rest  of your script:


if you have only one row result:
$row = mysql_fetch_object($result)
echo($row->field_name1);
echo($row->field_name2);
....
More than one row ressult:
while ($row = mysql_fetch_object($result)){
echo($row->field_name1);
echo($row->field_name2);
......
}

Good luck..

----------


## lukeurtnowski

I see, this is what I did
while ($row = mysql_fetch_object($result)){
echo($row->C.C_ID);
echo($row->A.C_NUMBER);
}

I just get C_IDC_NUMBER
the values of those fields each time the loop is run,
also is it ok if I were to run a query on each of those numbers?

----------


## oraclable

I did not get the last question..

Did you try to execute the query below in MYSQL panel?
Select * From COMPANY As C Inner Join ACTIVITY AS A On A.C_NUMBER = C.C_ID
Where A.A_DATE ='a_date'
If everything works well use the same  field_names that appear in MYSQL while using your php script ($row->filed_name...)

----------


## lukeurtnowski

Sorry if I wasn't clear.  Here's my php code

<?php
$hostname = "10.0.0.127";
$username = "lukeu";
$password = "blues7";
$database = "test";
$connection = mysql_connect($hostname , $username , $password)
or die("cannot make connection");
$db = mysql_selectdb($database , $connection)
or die("cannot find database");
$sql="Select * From COMPANY As C Inner Join ACTIVITY AS A On A.C_NUMBER = C.C_ID Where A.A_DATE ='".$date."'";
$result = mysql_query($sql) or die(mysql_error());
$numofrows = mysql_num_rows($result);
while ($row = mysql_fetch_object($result)){
echo($row->C.C_ID);
echo($row->A.C_NUMBER);

}

?>

----------


## oraclable

I think the problem is in these lines:
echo($row->C.C_ID);
echo($row->A.C_NUMBER);

for instance 
$row->C.C_ID  // php  translates it like a concatenation with  "$row->C" & "C_ID"

To avoid that, what you should do is:
1) do not use the alias,  just put $row->C_ID  
2) or if the alias is a mandatory do not use " * " just put the field names in your select
then you will have to rename these fields in your query like 
select A.C_number n, C.C_id i from .....

Give it try, it should work...

Note: there should be better solutions than those..

----------


## lukeurtnowski

Ok, I shortened it a lot, Here's my php code.
<?php
$hostname = "10.0.0.127";
$username = "*";
$password = "blues7";
$database = "test";
$connection = mysql_connect($hostname , $username , $password)
or die("cannot make connection");
$db = mysql_selectdb($database , $connection)
or die("cannot find database");
$sql="Select * From COMPANY As C Inner Join ACTIVITY AS A On A.C_NUMBER = C.C_ID Where A.A_DATE ='".$date."'";
$result = mysql_query($sql) or die(mysql_error());
$numofrows = mysql_num_rows($result);
?>
heres some more to display the results of the query in a table..
 while ($row = mysql_fetch_object($result)){ 
   	print "<TD>".$row->C_NAME."</TD>";
	print "<TD>".$row->C_CITY.", ".$row->C_STATE."</TD>";
	print "<TD>".$row->C_DESC."</TD>";
	print "<TD>".$row->RATIONALE."</TD>";
	print "<td>".$row->contact_NAME."</TD>";
	print "<td>".$row->contact_PHONE."</td>";
	print "<TD>".$row->A_DATE."</TD>";
	print "<TD>".$row->A_DESC."</TD>";
	print "<td>".$row->NEXT."</TD>";
	print "<td>".$row->MEDIVAS."</td>";
	print "</TR>\n"; 
			} 

for some reason, it returns only the results where C_NUMBER is equal in the ACTIVITY  table, not where A_DATEs' are equal.   Shouldn't the query return only instances where A_DATE's are equal.

----------


## oraclable

Did you run it in Mysql Panel?

Select * From COMPANY As C Inner Join ACTIVITY AS A On A.C_NUMBER = C.C_ID Where A.A_DATE ='some_date'

now I think it is a SQL issue..

Could you tell me what you would like to have in your query?
and send the table structures..

----------


## oraclable

I sent this in my first answer :

select a.A_fields, c.C_fields from activity a, company c
where a.A_DATE in (set of date) 
and a.C_number=c.C_number

Did you try it?

----------


## lukeurtnowski

Yes,. that doesn't return anything?
But I'll explain to you what im trying to do (If you dont mind)
I have a COMPANY table which holds info about a company, whose primary key is C_ID.  Then I have an ACTIVITY table which holds info about an activity, whose primary key is A_ID and its foreign key (C_NUMBER) which links it to a company.  I'm trying to perform a select by date function, so that once a certain date is selected, all the activity info and their coresponding company info would be listed.
Thank you soo much for helping me with this.

----------


## oraclable

I guess you are doing some like this:

drop table activity;
drop table  company;
create table company(
	c_id int(2) primary key,
	c_name varchar(30),
	c_desc varchar(60),
	c_state varchar(40),
	c_city varchar(40)
);
create table activity(
	a_id int(2) primary key,
	a_date date,
	a_desc varchar(60),
	a_c_id int(2) references company(c_id)

);

insert  company values(1,'comapany1','desc1','state1','city1');
insert  company values(2,'comapany2','desc2','state2','city2');
insert  company values(3,'comapany13','desc3','state4','city3');

insert activity values(11,'2005/6/02','company1 1st activities',1);
insert activity values(12,'2005/6/03','company1 2nd activities',1);
insert activity values(21,'2005/6/02','company2 1st activities',2);
insert activity values(31,'2005/6/07','company3  1st activities',3);

select * from company, activity
where c_id=a_c_id
and a_date='2005/6/02'


If it is not the case make some changes above and send it back we'll work on it..

----------


## lukeurtnowski

K, ill do that, thanks

----------


## lukeurtnowski

I have a question though, is it ok if I put a_date in the"mm/dd/yyyy" format
and  on tthat query, is it ok if I were to put the date in the php variable $date?

----------


## lukeurtnowski

Also, I thought both primary keys should be autonumbers if im going to add both companies and activities with a form

----------


## oraclable

you can manupulate your php variables as you want ...

I put a draft for you in this location: http://www.locatehomes.com/test/activities.php

if you need the connection.php code:

<?php
class DB {
	function DB() {
		$this->host = "your_host";
		$this->db = "your_db"
		$this->user = "user_name";
		$this->pass = "your_password";
		$this->link = mysql_connect($this->host, $this->user, $this->pass);
		mysql_select_db($this->db);
		register_shutdown_function($this->close);
	}
	function query($query) {
		$result = mysql_query($query, $this->link);
		return $result;
	}
	function close() {
		mysql_close($this->link);
	}
}
?>

----------

