MySQL: build a resource that can be read with mysql fetch row - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

MySQL: build a resource that can be read with mysql fetch row

csdude55

9:08 am on Mar 13, 2017 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

This might seem unorthodox, but...

In my PHP script, I check for certain variables, and if they exist then I INSERT INTO a MySQL table (to ensure up-to-date content). Then, later in the script, I SELECT that same data back out of the table.

To get around that, I'm INSERTing, and then immediately building an array with the same data. Then later, I check to see if that array exists, and if not then I SELECT, then build the array from that data. So either way, I'm manipulating the same data several times.

So what I'm wondering is, can I manually build a resource in the INSERT section that would be identical to the result from mysql_query($sth), so I could then use mysql_fetch_row() to list it?

Something like:

if ($condition == 1) {
 // get updated content
 list($var1, $var2) = get_data();

 $query = "REPLACE INTO table (col1, col2) VALUES ('$var1', '$var2')";
 mysql_query($query);

 // Build $sth manually using the same data
 $sth = build_resource('$var1', '$var2');
}

if (!$sth) {
 $read_query = "SELECT * FROM table";
 $sth = mysql_query($read_query);
}

while(list($var1, $var2) = mysql_fetch_row($sth)) {
 // do stuff
}

That's obviously just written up to give you a visual of what I'm asking... I'm just hoping to find "build_resource()" to manually build $sth using data that I already have, without a second MySQL query.

robzilla

8:56 pm on Mar 13, 2017 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

Perhaps I'm misunderstanding, but can't you simply build an array of the data after executing the REPLACE INTO query and use mysql_fetch_assoc() to do the same in the if(!$sth) block, and then finally foreach() over the array?

csdude55

10:58 pm on Mar 13, 2017 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

I can, and that's what I'm doing now. But I realized that it would take less coding (and, presumably, faster processing) if I could skip that second array altogether and just build the $sth resource.

As it is, I have an array and a resource, where I'm thinking it would be faster to just have a resource and skip the array.

robzilla

12:17 am on Mar 14, 2017 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

I'm all for avoiding unnecessary queries, but I don't think there's a way of creating a mysql_result object other than by using mysql_query(). Even if it were possible, I doubt it would require less coding than creating an array of the same data, because the principle is the same.

I don't know what kind of data you're handling, but beware of premature optimization :-)

csdude55

12:29 am on Mar 14, 2017 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

I don't know what kind of data you're handling, but beware of premature optimization

Haha, yeah, I've been told that before :) At this point I'm just rebuilding my site from the ground up, and I'm trying to focus on load time. Every second I can save results in more pageviews, so load time is directly proportionate to revenue :) This is just one of those things where, I figure that if I can save 1/10th of a second then it might result in a few extra dollars at the end of the month.

But cool, thanks for the input! I really didn't think it was possible, either, but figured I'd let larger brains than mine tell me for sure :D

phranque

3:15 am on Mar 14, 2017 (gmt 0)

WebmasterWorld Administrator

10+ Year Member

Top Contributors Of The Month

i would try simply creating an array of string keyed arrays (hash tables) and try passing that array as $sth.
there's a lot more than that in the mysql results object but it may be enough to fool mysql_fetch_row.

robzilla

8:31 am on Mar 14, 2017 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

If the SELECT query is slowing you down, use EXPLAIN to see if it can be helped by an additional INDEX. For complex queries, my experience is that it's sometimes better to chop them up into multiple separate queries than to try to put all the logic into one. If it's a simple SELECT query, it is unlikely to be slowing you down unless you're on a bad server; for me, a database connection plus a simple SELECT query usually takes less than half a millisecond, so that's not something to optimize unless you're seeing a ton of traffic.

If it suits your data, a user cache like APC may be a better option. You would execute the SELECT after the REPLACE query and then cache the results. That way, you only need the SELECT when the data is updated. This can get a little trickier, of course, when you change the data in the database in multiple places or separately from this script, but it may be worth investigating. If you don't have a user cache, storing data in text files will also likely be faster than MySQL.

there's a lot more than that in the mysql results object but it may be enough to fool mysql_fetch_row.

The mysql result is an object, for one, so mysql_fetch_row won't be expecting an array, and is not built to handle one. That would be a bit like passing an actual mysql result object to a function like array_push(). You need a conversion between array and mysql_result object, but that's a one-way street only (mysql_result > array).