MSSQL / mySQL not working as expected

Dev Shed Forums Sponsor:

November 2nd, 2012, 06:27 PM

Renigade

Registered User

Join Date: Nov 2012

Location: Indiana

Posts: 3

Time spent in forums: 1 h 14 m 51 sec
Reputation Power: 0

PHP5 - 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>';

November 2nd, 2012, 06:35 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,717

Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Months 1 Week 4 Days 7 h 21 m 39 sec
Reputation Power: 8969

Send a message via Google Talk to 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 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

__________________
Site rules and FAQ | Project Suggestions | mod_rewrite guide | PHP New User Guide and FAQ - PHP Stickies | Regex Resources

Last edited by requinix : November 2nd, 2012 at 07:20 PM.

November 2nd, 2012, 06:45 PM

Renigade

Registered User

Join Date: Nov 2012

Location: Indiana

Posts: 3

Time spent in forums: 1 h 14 m 51 sec
Reputation Power: 0

Quote:

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

November 2nd, 2012, 07:21 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,717

Time spent in forums: 5 Months 1 Week 4 Days 7 h 21 m 39 sec
Reputation Power: 8969

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.

November 2nd, 2012, 07:29 PM

Renigade

Registered User

Join Date: Nov 2012

Location: Indiana

Posts: 3

Time spent in forums: 1 h 14 m 51 sec
Reputation Power: 0

Quote:

Originally Posted by requinix

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!!

November 2nd, 2012, 09:26 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,717

Time spent in forums: 5 Months 1 Week 4 Days 7 h 21 m 39 sec
Reputation Power: 8969

Quote:

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.

Quote:

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.

PHP5 - MSSQL / mySQL not working as expected

Developer Shed Advertisers and Affiliates