SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    How do you limit the records returned by a query with a join in it. This is what I have:

    $sql="
    SELECT clients.id, clients.name, clients.address, contact_info.name
    FROM clients, contact_info
    WHERE clients.id=contact_info.id
    ";

    I only want the record where name="John Smith". Also, after these lines:

    $sql_result=mysql_query($sql, $connection);

    $row=mysql_fetch_array($sql_result);

    How do I distinguish the "name" field in the "clients" table from the "name" field in the "contact_info" table when using the array key. For, instance what will

    echo $row['name'];

    refer to? In other words, what are the array key values?

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you need to use aliases.


    Select clients.name as clients_name, contact_info.name as contact_name ....
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for the response....what about limiting the records returned by the query to the row where the
    "name" field = "John Smith"

  4. #4
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $sql="
    SELECT clients.id, clients.name, clients.address, contact_info.name
    FROM clients, contact_info
    WHERE clients.id=contact_info.id
    AND client.name = 'John Smith'
    ";

    You can make it more dynamic by doing this:
    AND client.name = $user

    Make sure you do the aliasing per aspen's suggestion.

  5. #5
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for the quick responses. I really appreciate the help. I tried adding a compound condition in the WHERE clause before, but it didn't work for some reason, so I went back and tried again and now I got it to work. Thanks! And yes, I actually want the dynamic form

    AND clients.id = "$name"

    but I simplified my question for the post.

    Yep, aliasing is a go. Thanks again!

  6. #6
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try putting each where clause in () so it look like this:

    WHERE (clients.id=contact_info.id)
    AND (client.name = 'John Smith')

  7. #7
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    After I query with joins using this select statement:

    SELECT clients.id, clients.name, contact_info.address, contact_info.email
    FROM clients, contact_info
    WHERE (clients.id=contact_info.id) AND (clients.name='$client')

    and then use

    $row=mysql_fetch_array();

    on the result identifier, I find that the length of the $row array is twice as long as the number of fields I query. So, I must be getting some data I don't need. For greater efficiency, can I get the exact number of fields I need when using joins?

  8. #8
    SitePoint Wizard
    Join Date
    Apr 2000
    Posts
    1,483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can also add "LIMIT 1" to the end of the SQL statement to only receive one record.

  9. #9
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    James, I believe the problem is too many columns in the result set.

    Go back to aspen's suggestion and use aliases. I've got a hunch, but no experience or proof, that might solve your problem. BTW, I haven't heard about needing to use () but nothing suprises me and they won't be doing any harm.

    SELECT clients.id AS id, clients.name AS name, contact_info.address AS address, contact_info.email AS email
    FROM clients, contact_info
    WHERE (id=contact_info.id) AND (name='$client')

  10. #10
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I believe the aliases are only for name conflicts, so that shouldn't be what is causing the extra data. Also, you are right, the paranthesis aren't necessary.

    I know with joins you get all kinds of permutations on what you query if you are not careful. I was just wondering if there was a way to limit that effect, or whether you have to live with that when using joins.

  11. #11
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something is not right. My belief is that the result set *should* most definitely only contain those four columns specified in your SELECT clause.

  12. #12
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I actually have 18 fields I am querying, 5 from one table and 13 from the other. The SELECT statement is just like in my last post but with more fields--I am not using aliases. After my query with the join in it, I have these lines:

    $sql_result=mysql_query($sql, $connection);
    $row=mysql_fetch_array($sql_result);
    echo count($row);

    and the ouput is: 36. That means I get an array back that has 18 extra fields somehow. Here is my full SELECT:

    $sql="SELECT clients.id, clients.name, clients.abn, contact_info.cont_name, contact_info.address, contact_info.email, contact_info.phone, contact_info.misc, clients.budget, clients.requ, clients.quote, clients.design, clients.hosting, clients.domain, clients.maint, clients.log, clients.price, clients.costs FROM clients, contact_info WHERE (clients.id=contact_info.id) AND (clients.name='$client')";
    Last edited by 7stud; May 9, 2001 at 14:09.

  13. #13
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well how fascinating. I just wrote some test code to try and work out what's going on. Here is the code and the results:
    PHP Code:
    <?php
    include("../dbconnect.php");
    $sql "SELECT Email, Password FROM Members";
    $result mysql_query($sql);

    // fetch row
    $row mysql_fetch_row($result);
    echo 
    count($row) . '<br>';
    foreach (
    $row AS $element) {
       echo 
    $element '<br>';
    }

    // fetch aassoc. rray
    $row mysql_fetch_array($result);
    echo 
    count($row) . '<br>';
    foreach (
    $row AS $element) {
       echo 
    $element '<br>';
    }
    ?>
    Results:
    2
    member1
    boo
    4
    member2
    member2
    poo
    poo
    ------------------

    If you read the manual you will get a hint at what is going on:
    "mysql_fetch_array*--* Fetch a result row as an associative array, a numeric array, or both. " http://www.php.net/manual/en/functio...etch-array.php

    So what appears to be happening is that PHP creates two name/value pairs for each column of data; one with the column name as the name and one with the index number as the name! Tricky PHP!

    BTW, if you want to access those values in the associative array using their column names then you will have to creates aliases in your SELECT clause. Freddydoesphp pointed out that PHP will not handle the "." kindly in something like $row["clients.id"]

  14. #14
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Yep, I knew that mysql_fetch_array() returns both an associative array and a numerical array, and I always wondered how one array could have two sets of indexes. Now I know!
    BTW, if you want to access those values in the associative array using their column names then you will have to creates aliases in your SELECT clause. Freddydoesphp pointed out that PHP will not handle the "." kindly in something like $row["clients.id"]
    You don't have to use aliasing unless you have name conflicts. The field referred to by "clients.id" can be accessed in the associative array by using the simple field name like this:

    echo $row['id'];

    Try it, it works. The reason you have to use aliasing when two fields with the same name are in different tables is because I think they will overwrite each other in the associative array(which makes me wonder if they are both present in the numerical array).

    Thanks for the help and discovering the cause of the double length array. I think I am all squared away now.
    Last edited by 7stud; May 9, 2001 at 22:24.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •