#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Indiana
    Posts
    3
    Rep Power
    0

    MSSQL / mySQL not working as expected


    Hello all... Thank you for your time in looking into my problem...I have some code that is not working as expected, I am trying to get the LAST record set from my DB and it seems to only get the first record. I am using the "Order by field desc" method.

    this works correctly when I return ALL of the rows but not if I just want to get the first row (in this case the last row written)

    Here is the code that I am using...
    Note: there are 2 connections to the same DB here getting different records but have the same dbfield name

    PHP Code:

    //          connect to a DSN "myDSN"
            
    $conn1 odbc_connect('dbname','uid','pw');
    //         the SQL statement that will query the database
            
    $sqlcbn="select * from tablename where fieldkey=" .$row["id"]. " order by id desc";
    //          perform the query
            
    $resul1t=odbc_exec($conn1$sqlcbn);
    //          Check if there are records
            
    if (odbc_num_rows($resul1t) != 0) {
    //          fetch tha data from the database
                
    $nerd=odbc_num_rows($resul1t);
    //            echo $nerd;
                
    while ($r1ow odbc_fetch_array($resul1t))
                    {
    $shofield $r1ow["dbfield"];}
            }else{
    $shofield=$row["dbfield"];}
            echo
    ' <td>' .$shofield'&nbsp;</td></tr>'
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,007
    Rep Power
    9398
    Your query is returning all the rows. All of them. The first one in the set is the oldest, the last one is the newest. Your loop goes all the way to the end and saves what it finds.

    LIMIT 1 the query and remove the loop.

    And why are you using a second connection? And a second query? Even though you didn't post any of the earlier code I'm pretty sure you only need one of each.

    Comments on this post

    • Renigade agrees : Thank you it has been helpful
    Last edited by requinix; November 2nd, 2012 at 07:20 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Indiana
    Posts
    3
    Rep Power
    0
    Originally Posted by requinix
    Your query is returning all the rows. All of them. The first one in the set is the oldest, the last one is the newest. Your loop goes all the way to the end and saves what it find.

    LIMIT 1 the query and remove the loop.

    And why are you using a second connection? And a second query? Even though you didn't post any of the earlier code I'm pretty sure you only need one of each.
    I am using a second connection (Not Sure)

    the 2nd query is accessing a different table in the db that may or may not have a relation to the first table in the first query
  6. #4
  7. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,007
    Rep Power
    9398
    It inherently has a relation because you're using the results from the first query ($row) in the second query. If that's all you're doing (again, you didn't post the code...) then the whole thing should be one connection and one query that uses a JOIN.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Indiana
    Posts
    3
    Rep Power
    0
    Originally Posted by requinix
    It inherently has a relation because you're using the results from the first query ($row) in the second query. If that's all you're doing (again, you didn't post the code...) then the whole thing should be one connection and one query that uses a JOIN.
    I am not using a join due to the fact that if there is no data in the 2nd table that relates to the first table then no rows will be returned.

    I did try the only one connection and it worked fine...
    I thought I read someplace that if you use one connection and try to access it the 2nd time it wipes out the first query... I am wrong in this since it does work. Thanks requinix you are my hero!!
  10. #6
  11. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,007
    Rep Power
    9398
    Originally Posted by Renigade
    I am not using a join due to the fact that if there is no data in the 2nd table that relates to the first table then no rows will be returned.
    That would be what an OUTER JOIN is for.

    Originally Posted by Renigade
    I thought I read someplace that if you use one connection and try to access it the 2nd time it wipes out the first query... I am wrong in this since it does work.
    There's actually a grain of truth to that but don't worry about it: for most uses a single connection is all you need.

IMN logo majestic logo threadwatch logo seochat tools logo