Creation of a Flash arcade site using WordPress - step 4

August 17, 2008 by Emanuele Feronato
Filed Under Php, WordPress •  

One of the best (or worst) things about programming is once you think you've almost done it, another problem pops up.

In part 3 I told you the hardest part was done, but I guess I was wrong.

This is an almost-working php script to include one game into WP in the same way as you would do if manually writing the entry.

The script at the moment does not handle security issues, special characters and duplicate game names.

If you want to test it or improve it, I will be happy to publish everything you will make, just make a backup of your WP database before playing with it.

I launched the script to insert Summer Couples and it worked fine, now it's time to fix the issues explained before, optimize it and make the final loop to read all games from the feed.

The script should be placed in the main WP directory.

This is a beta, so don't expect too much from it.

PLAIN TEXT
PHP:
  1. <?php
  2.  
  3. // requiring wp-config, so you don't have to remember passwords and stuff here
  4. require('./wp-config.php');
  5.  
  6. // custom function to create a slug from a string
  7. function convert($string){
  8.      $converted = ereg_replace("[^A-Za-z0-9 ]", "", $string);
  9.     $converted = str_replace(" ","-",$converted);
  10.     $converted = str_replace("--","-",$converted);
  11.      $converted = strtolower($converted);
  12.      return($converted);
  13. }
  14.  
  15. // connecting to the db
  16. $connection = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD) or die("Cannot connect to the database");
  17. mysql_select_db(DB_NAME) or die("Cannot find the database");
  18.  
  19. // associative array matching MochiAds categories (and my custom category "Leaderboard enabled")
  20. // with term_id value in term_taxonomy table
  21. $my_db[leaderboard_enabled] = 17;
  22. $my_db[Action] = 3;
  23. $my_db[Adventure] = 4;
  24. $my_db["Board Game"] = 5;
  25. $my_db[Casino] = 6;
  26. $my_db[Customize] = 10;
  27. $my_db["Dress-Up"] = 8;
  28. $my_db[Driving] = 7;
  29. $my_db[Fighting] = 9;
  30. $my_db[Other] = 13;
  31. $my_db[Puzzles] = 31;
  32. $my_db[Shooting] = 11;
  33. $my_db[Sports] = 12;
  34.  
  35. // associative array with all game data
  36. // later it will import games from the feed generating an array like this
  37. $mochi[game_tag] = "a03b7e9e53f6b24d";
  38. $mochi[name] = "Summer Couples";
  39. $mochi[author] = "Triqui";
  40. $mochi[swf_url] = "http://games.mochiads.com/c/g/summer-couples_v1/sc_secure.swf";
  41. $mochi[thumbnail_url] = "http://cdn.mochiads.com/c/g/summer-couples_v1/_thumb_100x100.jpg";
  42. $mochi[width] = 500;
  43. $mochi[height] = 550;
  44. $mochi[instructions] = "Draw a line connecting two or more tiles of the same kind\r\nYou can draw everywhere, but only over tiles of the same kind";
  45. $mochi[description] = "Draw and match as many summer couples as you can!";
  46. $mochi[created] = "2008-07-02T08:18:32.747467-08:00";
  47. $mochi[tags] = array("match", "matching", "draw", "drawing", "summer");
  48. $mochi[categories] = array("Action", "Other", "Puzzles");
  49. $mochi[leaderboard_enabled] = true;
  50. $mochi[slug] = "summer-couples_v1";
  51.  
  52. // here will be placed the main loop
  53.  
  54. // the checking if the game already exists
  55. $sql = "select * from ".$table_prefix."postmeta where meta_value = \"$mochi[game_tag]\"";
  56. $result = mysql_query($sql) or die(mysql_error());
  57. if(!mysql_num_rows($result)){
  58.      // converting mochi date to unix date
  59.     $date = substr(str_replace("T"," ",$mochi[created]),0,19);
  60.     // inserting the post
  61.      $sql = "insert into ".$table_prefix."posts (post_author,post_date,post_date_gmt,post_title,post_name,post_modified,post_modified_gmt) values (\"1\",\"$date\",\"$date\",\"$mochi[name]\",\"$mochi[slug]\",\"$date\",\"$date\")";
  62.      $result = mysql_query($sql) or die(mysql_error());
  63.      // getting this directory absolute path
  64.      $fullpath = 'http://'.$HTTP_SERVER_VARS[HTTP_HOST].$HTTP_SERVER_VARS[REQUEST_URI];
  65.      $thisfile = basename($fullpath);
  66.      $cutoffpos = strpos($fullpath,$thisfile);
  67.      $thisdir = substr($fullpath, 0, $cutoffpos);
  68.      $last_id = mysql_insert_id();
  69.      // building the content of guid column
  70.      $guid = $thisdir."?p=".$last_id;
  71.      // adjusting the post
  72.      $sql = "update ".$table_prefix."posts set guid = \"$guid\" where id = $last_id";
  73.      $result = mysql_query($sql) or die(mysql_error());
  74.      // insert all metas
  75.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"_edit_lock\",\"".time()."\")";
  76.      $result = mysql_query($sql) or die(mysql_error());
  77.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"_edit_last\",\"1\")";
  78.      $result = mysql_query($sql) or die(mysql_error());
  79.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"author\",\"$mochi[author]\")";
  80.      $result = mysql_query($sql) or die(mysql_error());
  81.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"swf_url\",\"$mochi[swf_url]\")";
  82.      $result = mysql_query($sql) or die(mysql_error());
  83.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"thumbnail_url\",\"$mochi[thumbnail_url]\")";
  84.      $result = mysql_query($sql) or die(mysql_error());
  85.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"game_tag\",\"$mochi[game_tag]\")";
  86.      $result = mysql_query($sql) or die(mysql_error());
  87.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"width\",\"$mochi[width]\")";
  88.      $result = mysql_query($sql) or die(mysql_error());
  89.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"height\",\"$mochi[height]\")";
  90.      $result = mysql_query($sql) or die(mysql_error());
  91.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"instructions\",\"$mochi[instructions]\")";
  92.      $result = mysql_query($sql) or die(mysql_error());
  93.      $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"description\",\"$mochi[description]\")";
  94.      $result = mysql_query($sql) or die(mysql_error());
  95.      // inserting or updating all tags
  96.      for($x=0;$x<count($mochi[tags]);$x++){
  97.           $sql = "select * from ".$table_prefix."terms where slug = \"".convert($mochi[tags][$x])."\"";
  98.           $result = mysql_query($sql) or die(mysql_error());
  99.           $row = mysql_fetch_array($result);
  100.           if(!mysql_num_rows($result)){
  101.                $sql = "insert into ".$table_prefix."terms (name,slug) values (\"".$mochi[tags][$x]."\",\"".convert($mochi[tags][$x])."\")";   
  102.                $result = mysql_query($sql) or die(mysql_error());
  103.                $last_term_id = mysql_insert_id();
  104.                $sql = "insert into ".$table_prefix."term_taxonomy(term_id,taxonomy,count) values (\"$last_term_id\",\"post_tag\",\"1\")";
  105.                $result = mysql_query($sql) or die(mysql_error());
  106.                // selecting the term_taxonomy id field of the inserted row
  107.                $last_taxonomy_id = mysql_insert_id();
  108.                // building term relationship
  109.                $sql = "insert into ".$table_prefix."term_relationships(object_id,term_taxonomy_id) values(\"$last_id\",\"$last_taxonomy_id\")";
  110.                $result = mysql_query($sql) or die(mysql_error()); 
  111.           }
  112.           else{
  113.                $sql = "update ".$table_prefix."term_taxonomy set count = count+1 where term_id = $row[term_id]";
  114.                $result = mysql_query($sql) or die(mysql_error());
  115.                // selecting the term_taxonomy_id field of the updated row
  116.                $sql = "select * from ".$table_prefix."term_taxonomy where term_id = $row[term_id]";
  117.                $result = mysql_query($sql) or die(mysql_error());
  118.                $row_taxonomy = mysql_fetch_array($result);
  119.                // building term relationship
  120.                $sql = "insert into ".$table_prefix."term_relationships(object_id,term_taxonomy_id) values(\"$last_id\",\"$row_taxonomy[term_taxonomy_id]\")";
  121.                $result = mysql_query($sql) or die(mysql_error());          
  122.           }
  123.      }
  124.      // manage leaderboard enabled if enabled (2nd place dumbest comment of 2008)
  125.      if($mochi[leaderboard_enabled]){
  126.           $sql = "update ".$table_prefix."term_taxonomy set count = count+1 where term_id = $my_db[leaderboard_enabled]";
  127.           $result = mysql_query($sql) or die(mysql_error());
  128.           // selecting the term_taxonomy_id of leaderboard row
  129.           $sql = "select * from ".$table_prefix."term_taxonomy where term_id = $my_db[leaderboard_enabled]";
  130.           $result = mysql_query($sql) or die(mysql_error());
  131.           $row_taxonomy = mysql_fetch_array($result);
  132.           // building term relationship
  133.           $sql = "insert into ".$table_prefix."term_relationships(object_id,term_taxonomy_id) values(\"$last_id\",\"$row_taxonomy[term_taxonomy_id]\")";
  134.           $result = mysql_query($sql) or die(mysql_error());        
  135.      }
  136.      // manage categories
  137.      for($x=0;$x<count($mochi[categories]);$x++){
  138.           $category = $mochi[categories][$x];
  139.           $sql = "update ".$table_prefix."term_taxonomy set count = count+1 where term_id = ".$my_db["$category"];
  140.           $result = mysql_query($sql) or die(mysql_error());
  141.           // selecting the term_taxonomy_id of category row
  142.           $sql = "select * from ".$table_prefix."term_taxonomy where term_id = ".$my_db["$category"];
  143.           $result = mysql_query($sql) or die(mysql_error());
  144.           $row_taxonomy = mysql_fetch_array($result);
  145.           // building term relationship
  146.           $sql = "insert into ".$table_prefix."term_relationships(object_id,term_taxonomy_id) values(\"$last_id\",\"$row_taxonomy[term_taxonomy_id]\")";
  147.           $result = mysql_query($sql) or die(mysql_error()); 
  148.      }
  149.      echo "record inserted";     
  150. }
  151. else{
  152.      echo "record not inserted";
  153. }
  154.  
  155. ?>

Let me know how would you improve it and your name will be featured in the theme credits.

Improve the blog rating this post
Tell me what do you think about this post. I'll write better and better entries.
1 Star2 Stars3 Stars4 Stars5 Stars (12 votes, average: 2.58 out of 5)
Loading ... Loading ...

» WordPress themes are designs for WordPress - one of the most popular blogging software nowadays.
You will be pleasantly surprised by WordPress Themes provided by Template Monster. All of them are of professional design and high quality.

15 Responses to “Creation of a Flash arcade site using WordPress - step 4”

  1. Kesh on August 17th, 2008 8:46 pm

    FLASH FLASH FLASH FLASH……………………………. WTF is WORDPRESS AND WHY SHOULD WE CARE

  2. Natetronn on August 17th, 2008 9:15 pm

    That was kind of random. Anyway, I am going to try this whole thing out from start to finish on a wamp install and I will let you know how it goes.

  3. Natetronn on August 17th, 2008 11:42 pm

    “The script should be placed in the main WP directory.” What should we call the file?

  4. Natetronn on August 17th, 2008 11:43 pm

    or does it matter at this point? Because more steps are on the way?

  5. Houen on August 18th, 2008 5:16 am

    Hi Emanuele - Nice work putting this together!
    I have three suggestions:

    First off, i would solve the duplicate game names problem by putting a UNIQUE constraint on the ‘meta_value’ column in the SQL database - that way, the database will enforce you to not have two identical posts on your site, without any code.

    Second - instead of writing the whole thing in one big function, i would use template functions to improve the readability and later ease of rewriting, like this:

    connect_to_db();
    $my_db_arr = create_category_integer_shorts();
    $mochi_arr = get_mochi_game_info
    if(!game_exists_in_blog()) { /*(CAN BE AVOIDED IF UNIQUE CONSTRAINT ON DB) */
    $date = convert_mochi_to_unix_date($mochi_arr[created]);
    insert_post($date,$mochi_arr);
    $last_id = mysql_insert_id();
    $guid = get_guid(); /*Doesn’t need arguments, since fullpath, thisfile, etc. can be calculated in the function */
    adjust_post($guid,$last_id);
    insert_metas($mochi_arr,$last_id);
    (etc. etc.)
    }

    Third, i would recommend using a private static varible $ERROR_OCCURRED and checking the value of each of the INSERT INTO and UPDATE SQL calls in the script as if one of them fails (and others have succeded - f.ex. the meta inserts fail but the post is inserted) you will have an inconsistent WP database that in the worst case will break your page, since WP or later code from you will look for missing information (i dont know how WP internally will handle this, or if it will do this kind of damage, but it will be bad nonetheless). If any of the SQL calls fail i would set $ERROR_OCCURED to true and in the bottom of the script check this and mail me if this occurred. If you have the option from your DB hosting, i would put as much SQL INSERTS/Updates in a SQL transaction as possible, since these either succeed completely, or roll back changes.

    Hope you could use this, for more suggestions or elaborations, just contact me, but its getting late in Denmark now :D

  6. Houen on August 18th, 2008 5:23 am

    Btw, could you elaborate on what you mean by security issues, and special characters?
    Could the special characters problem benefit from the php utf8_encode() function?

  7. voidSkipper on August 18th, 2008 1:36 pm

    Houen - he is referring to the risk of SQL injection - when someone, whether by accident or design, places commands for the database into an input field. The risk is that those instructions create an administrator account, change passwords, or just plain delete the database. An example of unintentional SQL injection would be writing a sentence in quotation marks, causing a syntax error (ie, your string is $string = “My favourite movie is ‘Aliens’.”; The php code would be, for example, mysql_query(’UPDATE user SET favmovie = ‘.$string.’ WHERE id = 1′); - this would end the quote early, causing an error and crashing the code.

  8. Houen on August 18th, 2008 2:42 pm

    thx voidSkipper -
    I don’t think thats really an issue here: Since this is an admin-only function, it could very easily be protected using a .htaccess file to allow only one person access to the corresponding php file holding either the textfields for posting, or the startup script for mochiads feed retrieval.

  9. Ivan on August 18th, 2008 11:26 pm

    Why use a blog platform for a Flash games site when you can use something like drupal or joomla for that matter?

  10. Sunil Changrani on August 19th, 2008 5:01 pm

    Hi,
    I guess the point here on using wordpress is that its very easy to use, free and versatile enough to be used for a flash games site. I’ve seen many examples of it. Emanuele is just trying to start from scratch and tick away the problems we face so everybody learns in the process.

    Anyways, nice post. Just spent some bucks on my hosting… am working on my own blog now. Many thanks to this tutorial series.

    Sunil

  11. Sante on August 20th, 2008 5:07 pm

    This script will make a lot of people happy
    Word press arcade are great

  12. Bob on August 23rd, 2008 1:51 am

    Is it going to have step5 ?
    I think it’s incomplete,
    Please write next step and describe more for us.
    thanks.

  13. Art on August 27th, 2008 5:15 am

    what do we save the file as? And what do you do once you have the file uploaded?

  14. Free online games on October 26th, 2008 5:39 pm

    Damn this is a very nice post for free online games site - arcade site creation keep up man!

Leave a Reply




Trackbacks

  1. Creation of a Flash arcade site using WordPress - step 5 : Emanuele Feronato on August 24th, 2008 8:17 pm

    [...] Creation of a Flash arcade site using WordPress - step 4, we saw how to post a game into a wp database, now we’ll see how to retrieve game [...]