Chinese Idiom Database Updates

×

This post was originally published in 2008
It may contain stale & outdated information. Or it may have grown more awesome with age, like the author.

I hit a brick wall with my plugin development, so I spent some time updating the Chinese Idiom Database.

A long time ago a kind person emailed me with some suggestions for the database. I thought all of his ideas were wonderful, and have just finished implementing them all, plus some minor improvements to the code, which will make further updates less painful. As this database was my first attempt at PHP programming, the code was … messy.

I also implemented a few things I wanted to do a long time ago, but couldn’t because of the state the code was in.

These are the changes:

There is now an “All Idioms” page that displays all the idioms’ English meaning, which will make it possible for people just starting to learn Chinese to browse idioms.

Both the English and the Chinese “All Idioms” pages’ links open in a Greybox above the page, which avoids a full page load.

One may move forward or backwards through the idioms within the Greybox, using the “Previous, Next” links.

Idioms may be given thumbs-up/down ratings.

Each time an idiom is returned as a search result or viewed by following a link from one of the “All Idioms” pages, a count is incremented. This will allow us to see which idioms have been viewed the most, which will give me some idea of how many people actually use the database (if any at all).

The hardest part was making the Greybox pages show the “Next/Previous” links, but that was only hard because I am a still pretty new to MySQL. During the page generation, I needed to be able to pull the idiom/English translation from the next and previous entries in the list of all idioms. After much irritation, I managed to do it like this:

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
$result = mysql_query("SELECT * FROM idioms WHERE zh_idiom!=''") or die(mysql_error());
	$result_copy = mysql_query("SELECT * FROM idioms WHERE zh_idiom!=''") or die(mysql_error());
 
	$prev =  mysql_result($result_copy, mysql_num_rows($result_copy)-1 ,'zh_idiom'	);	
 	$next =  mysql_result($result_copy, 1 ,'zh_idiom');
 
 
 	$count = 0;
 
 	while($row = mysql_fetch_array($result)) {  //make one page for each idiom
 
	$file_name = $row['zh_idiom'] . ".php";	
 
    $file = fopen($file_name,"w");
 
	$update_count = "<?php ".'$'."dbhost = 'localhost';
				 ".'$'."dbuser = 'pagesofi_student';
				 ".'$'."dbpass = '".'$'."a|Qt;jXg+Nf';
				 ".'$'."dbname = 'pagesofi_idioms';
 
				 ".'$'."conn = mysql_connect(".'$'."dbhost, ".'$'."dbuser, ".'$'."dbpass) or die('Error connecting to mysql');
 
				mysql_select_db('pagesofi_idioms');
				mysql_query(\"UPDATE idioms SET times_accessed=times_accessed+1 WHERE zh_idiom='".$row['zh_idiom']."'\") or die(mysql_error());
 
				".'$'."result = mysql_query(\"SELECT * FROM idioms WHERE zh_idiom='".$row['zh_idiom']."'\");
 
				".'$'."count = mysql_fetch_array(".'$'."result);
				?>";
 
	$top =  "<META http-equiv=Content-Type content='text/html; charset=UTF-8'><head><link rel='stylesheet' type='text/css' media='screen' href='idioms_individual.css' /></head>
        ".$update_count ."
 
        <title>".$row['zh_idiom']."</title>
        <html><body><p></p>
 
<p>".$row['zh_idiom']."</p><br>".$row['pinyin']."<br><br>". "<p1>".stripslashes($row['en_translation'])."</p1><br><br><p5>" . $row['example'] . "</p5><br/><br/><p2>Keywords: " .$row['key_word']."<br/>Viewed <?php echo " .'$'. "count['times_accessed'] ?> times. <br/>Entered by ".$row['creator']." on ".$row['date_created']."</p2><br/><div class='js-kit-rating' view='score' title='".$row['zh_idiom']."' path='".$row['zh_idiom']."' permalink='http://pagesofinterest.net/idiom/search_results.php?zhongwen=".$row['zh_idiom']."'></div>
<div id='nextPrev'><a id='prev' href='".$prev.".php'>Previous</a><a id='next' href='".$next.".php'>Next</a></div>
<script src='http://js-kit.com/ratings.js'></script>
</body>
</html>";
 
fwrite($file, $top);

I’m sure that my way is not the best way, but it works. If the database ever becomes massive, or I can’t find anything better to do, I’ll look at changing it.

As this script is only run when an idiom is added, so a little inefficiency is OK, right?

Shhh… I’ll make it better later, promise!

No comments | Trackback