Creation of a Flash arcade site using WordPress – step 4

August 17, 2008 • Php, WordPress by Emanuele Feronato • 16 Comments

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155

<?php
 
// requiring wp-config, so you don't have to remember passwords and stuff here
require('./wp-config.php');
 
// custom function to create a slug from a string
function convert($string){
     $converted = ereg_replace("[^A-Za-z0-9 ]", "", $string);
	$converted = str_replace(" ","-",$converted);
	$converted = str_replace("--","-",$converted);
     $converted = strtolower($converted);
     return($converted);
}
 
// connecting to the db
$connection = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD) or die("Cannot connect to the database");
mysql_select_db(DB_NAME) or die("Cannot find the database");
 
// associative array matching MochiAds categories (and my custom category "Leaderboard enabled")
// with term_id value in term_taxonomy table
$my_db[leaderboard_enabled] = 17;
$my_db[Action] = 3;
$my_db[Adventure] = 4;
$my_db["Board Game"] = 5;
$my_db[Casino] = 6;
$my_db[Customize] = 10;
$my_db["Dress-Up"] = 8;
$my_db[Driving] = 7;
$my_db[Fighting] = 9;
$my_db[Other] = 13;
$my_db[Puzzles] = 31;
$my_db[Shooting] = 11;
$my_db[Sports] = 12;
 
// associative array with all game data
// later it will import games from the feed generating an array like this
$mochi[game_tag] = "a03b7e9e53f6b24d";
$mochi[name] = "Summer Couples";
$mochi[author] = "Triqui";
$mochi[swf_url] = "http://games.mochiads.com/c/g/summer-couples_v1/sc_secure.swf";
$mochi[thumbnail_url] = "http://cdn.mochiads.com/c/g/summer-couples_v1/_thumb_100x100.jpg";
$mochi[width] = 500;
$mochi[height] = 550;
$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";
$mochi[description] = "Draw and match as many summer couples as you can!";
$mochi[created] = "2008-07-02T08:18:32.747467-08:00";
$mochi[tags] = array("match", "matching", "draw", "drawing", "summer");
$mochi[categories] = array("Action", "Other", "Puzzles");
$mochi[leaderboard_enabled] = true;
$mochi[slug] = "summer-couples_v1";
 
// here will be placed the main loop
 
// the checking if the game already exists
$sql = "select * from ".$table_prefix."postmeta where meta_value = \"$mochi[game_tag]\"";
$result = mysql_query($sql) or die(mysql_error());
if(!mysql_num_rows($result)){
     // converting mochi date to unix date
	$date = substr(str_replace("T"," ",$mochi[created]),0,19);
	// inserting the post
     $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\")";
     $result = mysql_query($sql) or die(mysql_error());
     // getting this directory absolute path
     $fullpath = 'http://'.$HTTP_SERVER_VARS[HTTP_HOST].$HTTP_SERVER_VARS[REQUEST_URI]; 
     $thisfile = basename($fullpath); 
     $cutoffpos = strpos($fullpath,$thisfile); 
     $thisdir = substr($fullpath, 0, $cutoffpos);
     $last_id = mysql_insert_id();
     // building the content of guid column
     $guid = $thisdir."?p=".$last_id;
     // adjusting the post
     $sql = "update ".$table_prefix."posts set guid = \"$guid\" where id = $last_id";
     $result = mysql_query($sql) or die(mysql_error());
     // insert all metas
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"_edit_lock\",\"".time()."\")";
     $result = mysql_query($sql) or die(mysql_error());
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"_edit_last\",\"1\")";
     $result = mysql_query($sql) or die(mysql_error());
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"author\",\"$mochi[author]\")";
     $result = mysql_query($sql) or die(mysql_error());
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"swf_url\",\"$mochi[swf_url]\")";
     $result = mysql_query($sql) or die(mysql_error());
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"thumbnail_url\",\"$mochi[thumbnail_url]\")";
     $result = mysql_query($sql) or die(mysql_error());
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"game_tag\",\"$mochi[game_tag]\")";
     $result = mysql_query($sql) or die(mysql_error());
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"width\",\"$mochi[width]\")";
     $result = mysql_query($sql) or die(mysql_error());
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"height\",\"$mochi[height]\")";
     $result = mysql_query($sql) or die(mysql_error());
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"instructions\",\"$mochi[instructions]\")";
     $result = mysql_query($sql) or die(mysql_error());
     $sql = "insert into ".$table_prefix."postmeta (post_id,meta_key,meta_value) values(\"$last_id\",\"description\",\"$mochi[description]\")";
     $result = mysql_query($sql) or die(mysql_error());
     // inserting or updating all tags
     for($x=0;$x<count($mochi[tags]);$x++){
          $sql = "select * from ".$table_prefix."terms where slug = \"".convert($mochi[tags][$x])."\"";
          $result = mysql_query($sql) or die(mysql_error());
          $row = mysql_fetch_array($result);
          if(!mysql_num_rows($result)){
               $sql = "insert into ".$table_prefix."terms (name,slug) values (\"".$mochi[tags][$x]."\",\"".convert($mochi[tags][$x])."\")";   
               $result = mysql_query($sql) or die(mysql_error());
               $last_term_id = mysql_insert_id();
               $sql = "insert into ".$table_prefix."term_taxonomy(term_id,taxonomy,count) values (\"$last_term_id\",\"post_tag\",\"1\")";
               $result = mysql_query($sql) or die(mysql_error());
               // selecting the term_taxonomy id field of the inserted row
               $last_taxonomy_id = mysql_insert_id();
               // building term relationship
               $sql = "insert into ".$table_prefix."term_relationships(object_id,term_taxonomy_id) values(\"$last_id\",\"$last_taxonomy_id\")";
               $result = mysql_query($sql) or die(mysql_error());  
          }
          else{
               $sql = "update ".$table_prefix."term_taxonomy set count = count+1 where term_id = $row[term_id]";
               $result = mysql_query($sql) or die(mysql_error());
               // selecting the term_taxonomy_id field of the updated row
               $sql = "select * from ".$table_prefix."term_taxonomy where term_id = $row[term_id]";
               $result = mysql_query($sql) or die(mysql_error());
               $row_taxonomy = mysql_fetch_array($result); 
               // building term relationship
               $sql = "insert into ".$table_prefix."term_relationships(object_id,term_taxonomy_id) values(\"$last_id\",\"$row_taxonomy[term_taxonomy_id]\")";
               $result = mysql_query($sql) or die(mysql_error());       	
          }
     }
     // manage leaderboard enabled if enabled (2nd place dumbest comment of 2008)
     if($mochi[leaderboard_enabled]){
          $sql = "update ".$table_prefix."term_taxonomy set count = count+1 where term_id = $my_db[leaderboard_enabled]";
          $result = mysql_query($sql) or die(mysql_error());
          // selecting the term_taxonomy_id of leaderboard row
          $sql = "select * from ".$table_prefix."term_taxonomy where term_id = $my_db[leaderboard_enabled]";
          $result = mysql_query($sql) or die(mysql_error());
          $row_taxonomy = mysql_fetch_array($result);
          // building term relationship
          $sql = "insert into ".$table_prefix."term_relationships(object_id,term_taxonomy_id) values(\"$last_id\",\"$row_taxonomy[term_taxonomy_id]\")";
          $result = mysql_query($sql) or die(mysql_error());       	
     }
     // manage categories
     for($x=0;$x<count($mochi[categories]);$x++){
          $category = $mochi[categories][$x];
          $sql = "update ".$table_prefix."term_taxonomy set count = count+1 where term_id = ".$my_db["$category"];
          $result = mysql_query($sql) or die(mysql_error());
          // selecting the term_taxonomy_id of category row
          $sql = "select * from ".$table_prefix."term_taxonomy where term_id = ".$my_db["$category"];
          $result = mysql_query($sql) or die(mysql_error());
          $row_taxonomy = mysql_fetch_array($result);
          // building term relationship
          $sql = "insert into ".$table_prefix."term_relationships(object_id,term_taxonomy_id) values(\"$last_id\",\"$row_taxonomy[term_taxonomy_id]\")";
          $result = mysql_query($sql) or die(mysql_error());  
     }
     echo "record inserted";      
}
else{
     echo "record not inserted";
}
 
?>

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

Rate this post: 1 Star2 Stars3 Stars4 Stars5 Stars (14 votes, average: 2.64 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.
Be my fan on Facebook and follow me on Twitter! Exclusive content for my Facebook fans and Twitter followers

This post has 16 comments

  1. Kesh

    on August 17, 2008 at 8:46 pm

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

  2. Natetronn

    on August 17, 2008 at 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 17, 2008 at 11:42 pm

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

  4. Natetronn

    on August 17, 2008 at 11:43 pm

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

  5. Houen

    on August 18, 2008 at 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 18, 2008 at 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 18, 2008 at 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 18, 2008 at 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 18, 2008 at 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 19, 2008 at 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 20, 2008 at 5:07 pm

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

  12. Bob

    on August 23, 2008 at 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. Creation of a Flash arcade site using WordPress - step 5 : Emanuele Feronato

    on August 24, 2008 at 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 [...]

  14. Art

    on August 27, 2008 at 5:15 am

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

  15. Free online games

    on October 26, 2008 at 5:39 pm

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

  16. Cristiano Leoni

    on November 23, 2008 at 3:07 pm

    If you want to add multiplayer / mmorpg games to your wordpress site, here is a free multiplayer plugin based on the DimensioneX engine:

    http://www.dimensionex.net/en/wordpress_multiplayer_plugin.htm

< >