Caching a MySQL query

again

[lorax]

I've got a query that pulls about 10 cols of data for each row. Then I want to save the result set for later use. I do the following to save the resulting rows to an array:

while ($record = mysql_fetch_array($result) ) {
$records[] = $record;
}

Two questions.

1. Should I use serialize/unserialize or simply save $records as a session var. I ultimately use the same page to output the data but I allow the user to sort the data by different columns and I allow them to reduce the result set by selective criteria (like 'county', 'state', 'color').

2. I'm tripping over outputting the array. The code I'm using is:

foreach ($records as $row) {
echo "<p>".$row[column1]."</p>";
}

The script outputs the letter 'A' once for each record and then outputs the column data as I expected it to. I'm feeling a bit dense but at the risk of embarrasment, where's the bloodly 'A' coming from!?

[coopster]

Not sure on this, but it may be caused by the mysql_fetch_array. What if you used mysql_fetch_assoc instead?

I guess I should explain my thought process here. In addition to storing the data in the associative indices of the result array, mysql_fetch_array also stores the data in numeric indices. It may be where your "A" is coming from...you're probably getting the "A" in "Array".

[edited by: coopster at 5:56 pm (utc) on Oct. 2, 2003]

[coopster]

Try running a print_r($records) right after you load it to possibly see where the "A" may be coming from.

while ($record = mysql_fetch_array($result) ) {
$records[] = $record;
}
print_r($records);

[edited by: coopster at 6:15 pm (utc) on Oct. 2, 2003]

[coopster]

Scrap both previous posts by me! :) I took your code and ran it on a table in one of my databases and it worked fine as is! You must be doing something in between that is muckity-mucking up your recordset.

[lorax]

And I was just about to post you that I still get the 'A'. :)

Stand by in the bunker...

[lorax]

Ok, here's the deal. I was intending to use sessions on this page and it seems they're some how affecting the output. I take out the session_start() call and the output works fine. I put it back in and even call session_unregister() and unset() and I get the 'A'.

This leads me to the first of my questions - serialize versus sessions?

[coopster]

I guess it would depend on the amount of information your are transferring in your recordset and how large it could grow to be? Have you considered executing a new query once key information is sent back via POST from the user? If so, and it's not an option, I guess I would try to use sessions.

Which would lead to question number 2 once again. And I don't have an answer or even a clue as to what is happening there...yet.

[lorax]

The recordset is less than 300 records and I don't expect it to get remotely close to 1000. I just went and actually counted the columns and I have 19.

I will execute a new query if need be. Sorting the data isn't a problem as much as getting the data initially.

Let's back up a bit and let me fill you in - you see I have three tables.

[T1]
records about businesses: biz name, owner name, address, phone, etc...

[T2]
business types - admin can add/edit/delete these at any time: engineering firm, interior decorator, seo experts

[T3]
biz2type matching table: ids from both T1 and T2 are paired here to give me a picture of what the business does

So when a query for the businesses is run (lets say all of them) the script queries T1 for everyone.

$query = "SELECT *
From biz";
$result = mysql_query($query);
$num_all = mysql_num_rows($result);

Then on the output, I had used a 'for' loop to output the query results like so:

for($i=0;$i<$num_all;$i++) {
$business = mysql_fetch_array($result);
output something
}

But in order to determine the 'type' of business I resorted to running another query in that for loop to search for the business type associated with the business. On 300 records the pause was noticeable.

I'm playing with a complex join to avoid the extra query but haven't been able to get the syntax right - if it's even possible. So that led me to the idea of caching the initial query and then doing the same with the second query. It's a stop gap method and I'd gladly drop it if I can get the join to work. My thinking is that the JOIN statement should eliminate all hoops I'm jumping through.

[coopster]

Is this the type of JOIN you are looking for?
# An inner join is required on the matching table
# because this info must exist to include.
# However, a business type is not required so use a left join.

$query =
"SELECT * FROM T1
INNER JOIN T3 ON (T1.ID = T3.ID)
LEFT JOIN T2 ON (T3.ID = T2.ID)
");

[lorax]

That's the format I've been fussing with. What I'm stuck on is the fact that T3 can have more than one biz type for each business. SO what I end up with is multiple outputs for the biz name - number of outputs is driven by the number of times the T1.ID shows up in the T3 table.

[coopster]

I'm not sure I understand your issue. So, you are getting the data you want, correct? I understand you have multiple/repeat rows since your business can have multiple business type records. Is it just that you don't want them displayed this way? Have a look at this thread [webmasterworld.com...] and see if it is the same as your situation.

Still in the bunker ;) -- coopster

[lorax]

That sounds like the same situation coopster. I've got the data. The output looks like:

biz name - biz type 1
biz name - biz type 2 etc..

and should look like

biz name - biz type 1, biz type 2

I've been trying to condition it within the context of my SELECT query. In that thread you're filtering the result output through PHP conditional statements. If that's correct - it's been a long day and I'm not thinking clearly - then I should be able to handle that!

[coopster]

If there is a better way, lorax, I want to be enlightened as well. Regards, coopster

[lorax]

That seems to be the best option. Certainly better than the direction I was headed. Thanks for your help coopster.

[anseljh]

I have a similar question.. I'd like to store the MySQL result resource in a session variable. This may not be possible at all - if anyone knows, please let me know!

I tried a bunch of stuff, including:

$_SESSION['result'] = serialize($result);

I'm checking the $_SESSION array right before my 1st page quits, and what gets stored in there is

i:0;

..which appears to be the representation for the integer 0. When I unserialize() it it gives me 0.

Has anyone run into this before?

Am I better off doing this some other way?

Thanks-

[lorax]

Welcome to WebmasterWorld anseljh,

>> Am I better off doing this some other way?

Depends upon what you're trying to do. As you can see above what I started out trying to do was the wrong way to go about it and I ultimately changed course (thanks to a dope slap from coopster) and used the method that better suited what I was trying to accomplish.

SO, what is it you're trying to do!?

[anseljh]

This is for a search system. The first page displays a form of about 10 fields the user can enter search criteria into, and click Submit. The next page shows the search results. This much works great.

What I would like to do is have the results page store the MySQL result resource in a session variable so that if the user clicks a column heading to do a sort, then PHP can do this without issuing a new MySQL query.

I'm beginning to think I'd be better off just chunking through the rows and storing them in the session instead of the MySQL result resource. Ideas? Thanks in advance.

[lorax]

Ah. A couple of things come to mind. While I haven't used serialize() my limited understanding of it is that it should work in the way you seek. But I am not sure.

Other thoughts. You could build a temporary table and place the results in that and then sort accordingly.

Alternately you could stuff everything into an array as I was trying to do and then save and sort the array.

How many records are we talking about searching through?

[anseljh]

I think serialize() decided to encode the result resource as an integer, so it doesn't look like that will work. :)

I will try stuffing the rows into an array. I suspect that will work fine. Right now I'm working with just a handful of records (less than a hundred), but I want to build my system to be scalable to much larger datasets.

Thanks again-

[coopster]

I tried a bunch of stuff, including:

$_SESSION['result'] = serialize($result);

I'm checking the $_SESSION array right before my 1st page quits, and what gets stored in there is

i:0;

..which appears to be the representation for the integer 0. When I unserialize() it it gives me 0.

You may be creating a multi-dimensional array by doing this. Run a print_r on $_SESSION['result'] to be sure.