Treat query result as array - How? 1

[casabella]

I have entered the world of arrays and I am revising some of the PHP scripts I've written and I want to use MD Arrays.

So far, I have ran queries and display the results from _var where the query results are dumped.

I have come to a point where this is not the best situation for me and I figure that I can solve my problem if I could treat my query's result as a MD Array.

Say my query returns 1500 rows and each row has 10 columns. I want to point to any row + column dynamically using numbers in lieu of aliases.

I tried

$rows = array(mysql_fetch_array($query));

but when I try to print values off the array by using

print $rows[10][10];

nothing prints. I get no errors nor content on screen.

What say you?

Thanks,


Jose

 

[vacunita]

You have to build the array as you extract the results. mysql_fetch_array will only return an array containing one row of your results at any one point. You need to keep calling mysql_fetch_array progressively to dump all rows into an array.

example:

$myarray=array();
$i=0;
while($row=mysql_fetch_array($resultset)){
$myarray[$i]=$row;
$i++;
}

This will loop through your results and place them into an array for later use.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[casabella]

vacunita,

Thanks for the suggestion. I did not want to use the loop as I thought there was a built-in function to handle that for me. I am afraid of what the performance will be like as tables increase in size and more and more users get on the system.

Regards,


Jose

 

[Kozusnik]

Then don't use an array for the entire thing. Do whatever you want inside the while loop.

$query = "SELECT * FROM table";
$result = mysql_query($query)

while($row=mysql_fetch_row($result)){
echo $row[0] . "\n"; //lists 1st column 
//perform row calculations/manipulations
}

Or use mysql_fetch_assoc rather than mysql_fetch_row and refer to the fields as $row['column_name'].

Mark

 

[vacunita]

I'm not sure I understand? you want a function that will automatically just spit out an array with all the results? Even if it was built into PHP, it would still take the same amount of time as if you constructed it yourself.

So I'm not sure what you want?

Once the array is built, you can do exactly what you want. point to a row and retrieve the data. However, maybe you could just retrieve the ID of the row you want and then generate a query for that particular row when you needed. So you store an array of ID's and generate a query from that.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

actually there is such a function built into php. but you need to be using the pdo extension to use it. pdo supports mysql and also supports prepared statements inherently (therefore making it more secure). I have switched to it for all my apps.

you don't save much on lines though...

$stmnt = $pdo->prepare($query);
$stmnt->execute();
$results = $stmnt->fetchAll();

the fetchAll is (very) fast as it's a direct C API call. it is quicker than constructing a programmatic, interpreted loop.

 

[vacunita]

I'm curious.. how much of a performance increase is there using that function instead of using a while loop, to put everything into an array?

I would think it should be about the same right?

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

i've noticed in general that pdo is a bit quicker all round. i have not done any proper tests though.

 

[vacunita]

ohh, o.k thanx.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[casabella]

vacunita,

I did end up doing as you suggested. I found no other work around it. With the few hundred records I have it flies, but this is just testing single user mode with a small number of records.

I think that we must always concern ourselves with performance as we develop our applications.


jpadie,

Where do I go to learn more about this PDO?


Thanks,


Jose

 

[jpadie]

http://www.php.net/pdo

 

[vacunita]

When you're dealing with 1500 records, really performance is not too big of a deal. its when you get up to say 100,000 200,000 records that's starts to be a problem, but can still be handled easily.
When you are handling millions of records, and thousands of requests, its when optimization, and your actual hardware come into play.

In any case... how much do you for see it growing in the short term? you might want to consider a better backbone for your development if you plan on serving a large amount of records continuously.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[casabella]

I wish I had that kind of problem! It appear then that I would not run into performance issues. I am intending to target small to medium size businesses which can build up to 80K to 120K in any given table over a number of years. Of course, not to be reached in short time ... it could take 3 to 5 years ... and some will never get there.

I should then rest easy ...

Regards,


Jose

 

[vacunita]

120,000 records can be easily handled by mysql with little or no noticeable delay.

you should not have problems in that scenario.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

another factor to take into account with performance is the table structure you employ. inefficient schemas and overly complex joins/unions can make a big performance hit. the mysql forum can assist better however.