Archive for the 'MySQL' Category

OSCON 2007 Tutorial: PHP and MySQL Best Practices

Wednesday, July 25th, 2007

Here are the slides for our talk today.

best_practices.pdf

If this site is slow, you can try http://www.laurathomson.com

Fun with Alexadex

Monday, February 27th, 2006

In case you are not aware, Alexadex is a virtual stock market game, where the values of stocks depend on their Alexa reach ratings.

Because I have too much time on my hands, I wanted to track my portfolio value in the sidebar of my blog. Look over there somewhere —–> and you will probably see it.

In case it holds amusement value to somebody, here is the code. It relies on PHP and MySQL and just does some simple screen scraping.

The fact that this URL works:
http://alexadex.com/ad/api?&method=getQuote&url=lukewelling.com
hints that there might be an API to do this at some point, but for now, I am screen scraping. (url pulled from Cal Evans’ blog)

The database table looks like this:

CREATE TABLE alexadex (
  timestamp timestamp(14) NOT NULL,
  value int(11) NOT NULL default '0',
  PRIMARY KEY  (timestamp)
)

From a cron job I am running:

<?php
require('functions.php');

connectToDb();

$username = 'tangledweb';
$url = "http://alexadex.com/ad/user/$username";
$marker = 'total:</b></td><td align=right>$';

$current =  scrape( $url, $marker );
if($current!==false)
{
   echo "stored: ";
   storeCurrent($current);
}

echo $current; 

?>


In case it is not obvious, my Alexadex username is tangledweb.

In my blog sidebar I have:

<?php
require('functions.php');
echo '<li><a href = "http://alexadex.com/ad/user/tangledweb"
      >My current portfolio is $';
$temp = getMostRecentFromDb();
echo number_format($temp['value']).'</a>';
?>

The functions these rely on are:

function storeCurrent($value)
{
 $value = intval($value);
 $sql = "INSERT
         INTO alexadex
         VALUES (NOW(), $value)";
  $result = mysql_query($sql);
}

function getMostRecentFromDb()
{
  $sql = "SELECT *
          FROM alexadex
          WHERE 1
          ORDER BY `timestamp` DESC
          LIMIT 1";

  $result = mysql_query($sql);

  return mysql_fetch_array($result);
}

function scrape($url, $marker, $maxLength = 50)
{
  $page = file_get_contents($url);
  if($page === false)
  {
    return false;
  }
  $pos = strpos($page, $marker);
  if($pos === false)
  {
    return false;
  }
  $value= substr($page, $pos + strlen($marker), $maxLength);
  $value= str_replace(',', '', $value);
  $value= intval($value);
  return $value;
}

function connectToDb()
{
  $connection = mysql_connect("host",
                              "user",
                              "pass");
  mysql_select_db("dbname", $connection);
}

This code comes with no warranty of any kind. You can have it as public domain, but I would appreciate a link to this blog if you use it. I hope it still works. WordPress seems to really, really want to mess with it when it saves it.