Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: new to table joins?

  1. #1
    Join Date
    Jun 2005
    Location
    coronado
    Posts
    76

    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

  2. #2
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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!

  3. #3
    Join Date
    Jun 2005
    Location
    coronado
    Posts
    76
    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.

  4. #4
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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..

  5. #5
    Join Date
    Jun 2005
    Location
    coronado
    Posts
    76
    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?

  6. #6
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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...)

  7. #7
    Join Date
    Jun 2005
    Location
    coronado
    Posts
    76
    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);

    }

    ?>

  8. #8
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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..

  9. #9
    Join Date
    Jun 2005
    Location
    coronado
    Posts
    76
    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.

  10. #10
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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..

  11. #11
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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?

  12. #12
    Join Date
    Jun 2005
    Location
    coronado
    Posts
    76
    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.

  13. #13
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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..

  14. #14
    Join Date
    Jun 2005
    Location
    coronado
    Posts
    76
    K, ill do that, thanks

  15. #15
    Join Date
    Jun 2005
    Location
    coronado
    Posts
    76
    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
  •