buzzy: Rigby from Regular Show sitting in front of a computer (Computer Rigby)
Ever since PHP stopped working with the official builds of Apache, I had to use an unofficial build. Since then, I've had problems, though I can't figure out whether they're the fault of the unofficial build or PHP. The big problem is that Internet Explorer seems to cause Apache to stop responding to requests after a while. It'll work without problems if requests come from any other browser. I know that lighttpd works just fine on my Linux machine with PHP (FastCGI), so I tried looking at that for my Windows development machine. Unfortunately, the only way I could get it to work properly was with CGI, which is kind of slow compared to the always-running Apache module. That pretty much wipes out the benefit of switching to a light web server like lighttpd. While looking at the PHP documentation (some of which is pretty dated), I found that Apache can run PHP using FastCGI. I figured I'd give it a try. Using the comments and my knowledge of the changes to the Apache configuration file format for Apache 2.4, I set Apache to use FastCGI instead of the PHP Apache 2.4 module by using mod_fcgid. The appropriate changes look something like this:
<Directory "C:/PROGRA~2/PHP/">
    AllowOverride None
    Options None
    Require all granted

FcgidInitialEnv PHPRC "C:/PROGRA~2/PHP" # Tell PHP where php.ini is
AddHandler fcgid-script .php
FcgidWrapper "C:/PROGRA~2/PHP/php-cgi.exe" .php # Can't have spaces in the path
Notice the "PROGRA~2" path. The FastCGI module doesn't like spaces in paths. Also, the PHP path needs to have some permissive access or it won't bother loading PHP. The document root needs to have "ExecCGI" in the "Options" directive or PHP scripts will return a 403 error. Once I got that squared away, I loaded a page with phpinfo();. "CGI/FastCGI" showed up in the Server API and php-cgi.exe showed up in my running processes. I ran some other more complex scripts and those ran just fine.
buzzy: Rigby from Regular Show sitting in front of a computer (Computer Rigby)

A good chunk of yesterday was trying to come up with some MySQL/MariaDB code that could generate a random row for me. Doing the simplest thing (something like SELECT ID FROM tablename ORDER BY RAND() LIMIT 1) is slow. This huge thing worked, but you can't run a sequence of commands the same way you run prepared [single] statements:

SET @r := (RAND()*(SELECT MAX(ID) FROM tablename));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
Every attempt to condense that into one single query with a subquery always gave my an ID number below would only return ID numbers lower than about 310. It's like the statement "(RAND()*(SELECT MAX(ID) FROM tablename))" gives different results when it's run separately versus running it in another statement, like SELECT ID FROM tablename WHERE ID>=(RAND()*(SELECT MAX(ID) FROM tablename)) ORDER BY ID ASC LIMIT 1. Enter mysqli_multi_query. Apologies for any bad formatting, this was whipped up quickly just to see if it worked:
$query = 'SET @r := (' . (mt_rand(0, mt_getrandmax()) / mt_getrandmax()) . '*(SELECT MAX(ID) FROM tablename));' . PHP_EOL . 'SET @sql := CONCAT(\'SELECT ID,\',@r, \' FROM tablename WHERE ID>=\',@r,\' ORDER BY ID ASC LIMIT 1\');' . PHP_EOL . 'PREPARE stmt1 FROM @sql;' . PHP_EOL . 'EXECUTE stmt1;';
if ($mysqli->multi_query($query)) {
while ($mysqli->more_results() && $mysqli->next_result()); // jump to last one
    if ($result = $mysqli->store_result()) {
        while ($row = $result->fetch_row()) $theID = $row[0];
(The code assumes you opened a link to the database as $mysqli.) Note that I used mt_rand instead of the MySQL/MariaDB RAND() because the latter produces too many repeats. I made a test to benchmark and test the randomness the code I posted above and I think the results are satisfactorily random (this is 1000 runs on a database with 5522 rows with IDs ranging from 2 to 6155):
1 results repeated 5 times
1 results repeated 19 times
2 results repeated 4 times
4 results repeated 3 times
76 results repeated 2 times
Highest ID: 6148
Lowest ID: 22
Max Time to Execute (returning only an ID): 0.0022029876708984
Min Time to Execute (returning only an ID): 0.0006871223449707
Average Time to Execute (returning only an ID): 0.00086945366859436
Max Time to Execute (returning all 45 columns): 0.0029420852661133
Min Time to Execute (returning all 45 columns): 0.00085997581481934
Average Time to Execute (returning all 45 columns): 0.001065233707428
First Repeat at Iteration #61
I'm assuming that one that comes up a lot appears because there's a giant hole between IDs 5624 and 5808, and this code just seems to like picking a number between those. If any holes in your table's IDS are fairly evenly distributed, they won't repeat that many times. If you have no holes at all and you can plan on it, something simpler may be in order. Even with the repeating numbers, I don't expect someone to click the link for a random result 61 times either. As for the time to execute, the ORDER BY RAND() LIMIT 1 code took about 0.25 seconds to run, so I'd say the figures here are an improvement. (Don't laugh at my 45 column table; I'm just working with a database someone else designed that I can't change.) Hopefully this is useful to someone.


buzzy: Steven Universe from the show of the same name with a big smile (Default)

January 2016

101112131415 16


RSS Atom

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Sep. 26th, 2017 12:54 pm
Powered by Dreamwidth Studios