Getting huge data from sql server with php

Question

I have a table call Access with 200 thounsand entries and I need get all the data and display it on a map. The schema of the data base is:

long    |  lat    |  service
12.00   | 34.33   |    3
133.32  | 32.4213 |    5
-58.3   | -3.3233 |    10

If I try the query:

SELECT long, lat, service FROM Access;

on SQL SERVER Console I get all the data, and works great, but if I try it with this php code:

$result = mssql_query($query) or die('Query Failed ' . mssql_error());

I get nothing. No errors and no data. I increased the memory limit on my php.ini and I set it to 1024M but I'm getting the same output,no errors and no data.

There are any way to get thousands of entries from a data base on a php array? What are I'm doig bad?

Thanks for your help.

error_reporting(E_ALL); ini_set('display_errors', 1); - still getting no errors? As a side note, 200,000 points on one map? I can't believe that would be much use to anyone... — DaveRandom
– DaveRandom, Commented Jun 6, 2012 at 16:12
You should never read huge data structures into memory, regardless of the programming language being used, and especially not with PHP which uses a lot of memory for a given data structure. Use LIMIT and OFFSET to read the data in chunks. — GordonM
– GordonM, Commented Jun 6, 2012 at 16:13
@GordonM SQL Server has no LIMIT or OFFSET, although the principle of what you say is sound. See this and others. — DaveRandom
– DaveRandom, Commented Jun 6, 2012 at 16:18
DaveRandom, I try error_reporting and ini_set right now. By the way, I'm using clustered map javascript to display all points. GordonM, sql server has no LIMIT. Thanks for your awswers. — Pipeline
– Pipeline, Commented Jun 6, 2012 at 16:19

Jocelyn · Accepted Answer · 2012-06-06 17:59:41Z

1

mssql_query() doesn't return any data, it returns a result resource on success. Have a look to the documentation: http://php.net/mssql_query
You must also use another function such as http://php.net/mssql_fetch_assoc to retrieve all your data.

mssql_connect(...); // put valid server/login/password here
$result = mssql_query($query) or die('Query Failed ' . mssql_error());
while($r = mssql_fetch_assoc($result))
{
    // use $r['long'], $r['lat']...to access the data of the fetched record
}

The while() loop is neccessary to fetch all records, one record for each call of mssql_fetch_assoc().

answered Jun 6, 2012 at 17:59

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting huge data from sql server with php

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related