-
new to table joins?
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
-
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!
-
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.
-
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..
-
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?
-
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...)
-
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);
}
?>
-
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..
-
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.
-
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..
-
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?
-
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.
-
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..
-
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|