Welcome to WebmasterWorld Guest from 54.81.68.240

Forum Moderators: open

Message Too Old, No Replies

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

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

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 707
votes: 58


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.
8:56 pm on Mar 13, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Sept 25, 2005
posts:1787
votes: 265


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?
10:58 pm on Mar 13, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 707
votes: 58


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.
12:17 am on Mar 14, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Sept 25, 2005
posts:1787
votes: 265


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 :-)
12:29 am on Mar 14, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 707
votes: 58


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
3:15 am on Mar 14, 2017 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11279
votes: 133


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.
8:31 am on Mar 14, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Sept 25, 2005
posts:1787
votes: 265


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).