Here I will outline the code used to create the server side code required by the Quote Of The Month WordPress plugin. The plugin calls this PHP file from this server in order to retrieve a random quote from our database.
First we require a database that holds the quotes and authors. You can use the code below within phpMyAdmin and run it as a SQL query in order to set up your own database as follows:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE IF NOT EXISTS `qotm` ( `id` int(11) NOT NULL AUTO_INCREMENT, `quote` text NOT NULL, `author` varchar(50) NOT NULL, `rating` int(11) NOT NULL, `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) |
As can be seen above, we create a table called ‘qotm’ with the necessary fields for each quote along with its author. Two additional fields were created. The ‘added’ field simply timestamps when each new entry is added, and the ‘rating’ field is currently not used but perhaps in the future will contain a number out of 5 depending on how a user may rate the quality of any given quote.
Here’s a snapshot of a section of the database currently in use:
So we have our database, but how do we populate it? There are a number of options, and here I will describe one which involves a few PHP files and a little jQuery magic in order to add them by hand.
As always we need our config.php file which is included in our main PHP file in order to connect to our database:
|
1 2 3 4 5 6 7 8 9 10 11 |
<?php $mysql_host = 'localhost'; $mysql_user = 'username'; $mysql_password = 'password'; $mysql_database = 'database_name'; $db = mysqli_connect($mysql_host,$mysql_user,$mysql_password,$mysql_database); if (!$db) { die('Could not connect to MySQL: ' . mysqli_error()); } ?> |
Next we’ll have our basic form allowing us to input our quote and its author:
|
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 |
<head> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script> </head> <script> $(function() { $(".update_button").click(function() { var quote = $("#quote").val(); var author = $("#author").val(); var password = $("#password").val(); var dataString = 'quote=' + quote + '&author=' + author + '&password=' + password; if(quote=='' || author =='' || password =='') { alert("Please Enter Some Text"); } else { $.ajax({ type: "POST", url: "addsql.php", data: dataString, cache: false, success: function(html){ $("#display_result").after(html); document.getElementById('author').value=''; document.getElementById('quote').value=''; document.getElementById('quote').focus(); } }); } return false; }); }); </script> <div> <form method="post" name="form" action=""> <h3>Password</h3> <input type = "password" name="password" id="password" maxlength="145" > <h3>Quote</h3> <textarea cols="30" rows="2" name="quote" id="quote" maxlength="400" > </textarea> <h3>Author</h3> <textarea cols="30" rows="2" name="author" id="author" maxlength="145" > </textarea><br /><br /> <input type="submit" value="Update" name="submit" class="update_button"/> </form> </div> <div id="display_result"></div> |
Whats with all that javascript? Well, it allows us to use AJAX in order to update our database using the addsql.php code without refreshing our page. This means that we haven’t got to fill in our ultra top secret password each time we want to add another quote.
Here’s the final piece of the puzzle, the actual code that adds the data to our database and returns the little bit of text that displays at the bottom of the above code in our div id ‘display_result':
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?php // connect to db require_once( dirname( __FILE__ ) . '/config.php' ); if(isset($_POST['password']) && !empty($_POST['quote']) && !empty($_POST['author']) && $_POST['password'] == 'awesomepassword'){ $quote = trim($_POST['quote']); $author = trim($_POST['author']); mysqli_query($db,"INSERT INTO qotm (quote, author)VALUES('$quote', '$author')"); echo "Added successfully: ".$quote." - <b>".$author."</b><BR />"; } else { echo "Yo, that didn't work<BR />"; } ?> |
You will need to change ‘awesomepassword’ for something actually awesome yourself.
So we have our table, our quote and author entry page with our javascript allowing us to update our database on the fly.
We now need a little PHP code that randomly picks out a quote and author and spits out our html ready to be displayed by our plugin.
|
1 2 3 4 5 6 7 8 9 10 11 |
<?php header('Access-Control-Allow-Origin: *'); // connect to db require_once( dirname( __FILE__ ) . '/config.php' ); $result = mysqli_query($db,"SELECT quote,author FROM qotm ORDER BY RAND() LIMIT 1"); $row = mysqli_fetch_array($result); $quote = $row[0]; $author = $row[1]; echo "<div id=\"qotm_quote\">".$quote."</div><div id=\"qotm_author\"> - ".$author."</div>"; ?> |
Couldn’t be simpler.
But wait, why have we altered the header?
|
1 |
header('Access-Control-Allow-Origin: *'); |
By default cross domain AJAX is not allowed by this server. What does this mean? See this post for a full explanation. Put simply, this allows anyone running our plugin from any domain to access this server using the AJAX method which is employed in our plugin.
