One of my longest-held dreams, as a fledgling Web designer and developer, was to make a fully searchable database of some sort. I used to dream about building a link database or directory, but as I became a collector of M:TG cards, I discovered I wanted to create a database of all the cards I wanted to trade instead.
But I didn’t dream about it being possible, especially not with any Web language I knew. Little did I know, PHP and MySQL working together could make my dream come true. And the best part is, you can make a database like this too! See how I managed it, with the steps below.
#1: Create the Database to Search
On your host’s control panel somewhere, you should have a way to create MySQL databases. This short tutorial shows you the generic way to create databases from your web host’s control panel, while this longer tutorial has a little bit more tech knowledge needed, but shows you how to manually set up MySQL username, password, etc.
For any database setup, you have to give the database a name, then make yourself an “admin user” account so you can access it. Lastly, you have to make a table within the database to hold the data you want to put into it. While you’re doing this, do not forget to write all this information down–you will need the database name, your admin username, and your admin password to do anything with your data afterwards.
Sample information:
Database name: mydata
Admin username: helloitsme
Admin password: epicsuperlongpassword
Database table: mydatatable
#2: Upload Your Data
To have a searchable database, you first need to have data. You could go through and manually create a MySQL database and plug data into it using a ton of MySQL commands. Buuuuut there’s a much easier way.
Microsoft Excel pages, or any other spreadsheet program pages, for that matter, can be converted over to .CSV (Comma Separated Value) format. These, when uploaded to your server through phpMyAdmin or another MySQL handling program, help populate an existing database table.
Example: For my Magic: the Gathering trades database, I had several pages’ worth of card data, which I’d spread out into individual worksheets within Excel. To translate all of those into .CSV format, I had to remove the label rows and columns (like “Name of Card,” “Condition,” etc.), and then had to save each sheet as a separate .CSV file. Then and only then could I upload it through phpMyAdmin to populate my created database with data.
#3: Write PHP Code to Search the Database
The following code is an actual example of the code I use for searching my M:TG trades database; of course, the username, password, and database name are not given here, for security reasons, but everything else remains the same. You’ll want to put this database-searching code in a separate PHP file from your HTML file where your search form is.
(By the way, I do not take the credit for making this code in any way–a dear computer programmer friend of mine used his coding skill and fixed the code so that it does work. AT LAST!! LOL)
<?php if ($searching == “yes”) {
echo “<p class=\”heading\”>Search Results</p>”; }
if ($find == ” “) {
echo “Oops! No search term entered–try again!”; exit; }
//Variables
$host = “localhost”;
$user = “helloitsme”;
$pass = “epicsuperlongpassword”;
$db = “mydata”;
$text = $_POST[‘find’];
$con = mysql_connect($host, $user, $pass) or die (“Connection error”);
$sqlDB = mysql_select_db ($db) or die(“Database selection error”);
$find = strip_tags(trim($text));
$find = strtoupper($text);
$query = mysql_query(“SELECT * FROM mydatatable”, $con);
$found = false;
//This loop will select the row and then uppercase the entire entry
while($data = mysql_fetch_array($query, MYSQL_BOTH)){
$updated = strtoupper($data[‘name’]);
if($updated == $find) {
echo $data[‘name’].” “.$data[‘rarity’].” “.$data[‘set’].” “.$data[‘condition’].” “.$data[‘amount’].” “.$data[‘color’];
$found = true;
break; }}
if(!$found)
echo “Could not find this card”; mysql_close(); ?>
What Does This Code Mean?
<?php if ($searching == “yes”) {
echo “<p class=\”heading\”>Search Results</p>”; }
if ($find == ” “) {
echo “Oops! No search term entered–try again!”; exit; }
This determines whether the search has been sent to the server for processing, and if anything’s been put into the search form.
If the search form has been passed to the server, the value of the variable $searching will be “yes”; the if statement concerning this variable cues the browser to display the heading “Search Results” in anticipation.
If the user didn’t put anything into the search form, but hit Submit anyway, the variable called $find will be empty; the second if statement returns an “Oops” message if this is the case.
$host = “localhost”;
$user = “helloitsme”;
$pass = “epicsuperlongpassword”;
$db = “mydata”;
$text = $_POST[‘find’];
$con = mysql_connect($host, $user, $pass) or die (“Connection error”);
$sqlDB = mysql_select_db ($db) or die(“Database selection error”);
These are some of the necessary variables we’ve defined for this particular script to run: the host’s name, username, and password for the database ($host, $user, $pass), the database selection ($sqlDB), the connection to said database ($con), and what the search term was ($text).
You may not need all of these, but we found that the script ran better when all of these variables were clearly defined for the browser.
$find = strip_tags(trim($text));
$find = strtoupper($text);
$query = mysql_query(“SELECT * FROM mydatatable”, $con);
$found = false;
These four variables have more to do with refining the search terms and running bits of the script.
The first $find variable strips any code from the search term, so people can’t hijack the database using malicious code. The second $find puts the search term all in uppercase letters. Both help the search script run more quickly (and protect the database from the most basic of hacks).
The $query variable executes the actual script’s purpose: searching the database for anything matching the search term. And to be honest, I don’t know what the $found variable is for at this point in the script…all I know is that it makes the script work. (Pathetic, I know…this is where my PHP knowledge is spelled F-A-I-L.)
//This loop will select the row and then uppercase the entire entry
while($data = mysql_fetch_array($query, MYSQL_BOTH)){
$updated = strtoupper($data[‘name’]);
if($updated == $find) {
echo $data[‘name’].” “.$data[‘rarity’].” “.$data[‘set’].” “.$data[‘condition’].” “.$data[‘amount’].” “.$data[‘color’];
$found = true;
break; }}
if(!$found)
echo “Could not find this card”; mysql_close(); ?>
This is the bit of the code I understand the least, but my friend’s comment in the PHP script helps a lot. The “while” code begins a looping search through the database, row by row, finding everything that matches the search term.
Once it finds a record that matches, it returns everything about that record–in this case, it finds the name of the card, its rarity, what set it came from, etc. Then the variable of $found gets set to “true” because it found something. If it can’t find anything, however, it just echoes back a “can’t find anything” statement and ends the script.
#4: Plug in the Info for Your Database
Once you’ve got your search code ready to go, all you have to do is plug in your information for the username, password, database name, and table name (for within the database). Make sure you’ve got it spelled exactly right and that the letters are uppercase or lowercase as appropriate! Can’t tell you how many times I’ve mistaken a lowercase “L” for an uppercase “I”.
#5: Test the Search
Now, you need to see if this bad boy works. Type up a quick HTML form to take in your search term, like the one below (again, taken directly from my code).
<form name=”search” method=”post” action=”search.php”>Search for:
<input type=”text” name=”find” />
in
<br>
<input type=”checkbox” name=”name” value=”Name” /> Name of Card<br>
<input type=”checkbox” name=”color” value=”Color” /> Color (White, Blue, etc.)<br>
<input type=”checkbox” name=”rarity” value=”Rarity” /> Rarity (Common, Uncommon, etc.)<br>
<input type=”checkbox” name=”type” value=”Type” /> Type (Creature, Instant, etc.)<br>
<input type=”checkbox” name=”set” value=”Set Name” /> Set Name (Zendikar, M10, etc.)<br>
<input type=”checkbox” name=”condition” value=”Condition” /> Condition (Near Mint, Good, Fair, Poor)<br>
<input type=”checkbox” name=”amount” value=”Amount” /> Amount of Copies (1, 2, 3, etc.)<br>
<input type=”hidden” name=”searching” value=”yes” />
<input type=”submit” name=”search” value=”Search” />
</form>
#6: Debug, Debug, Debug, and By the Way—-Debug
This is the most important (and infuriating) part of this database search. My friend and I spent several months (yes, I said MONTHS) debugging this very script because no matter what we did, it just wouldn’t run. The final edits he made to it, which are reflected in this post, finally made it work.
When you’re working on code like this, it’s important to make sure it’s spaced out enough so that you can read it, and that you work on getting each tiny piece of it perfect instead of trying to scan the whole document for errors. For tired eyes, a colon can sure look like a semicolon, and a lowercase “L” can sure look like an uppercase “I”. You have to watch out for the little errors!
Once you’ve caught all the little spelling and mistyping errors, then you need to check to make sure the code’s variables work like they’re supposed to, and that you’re calling functions that actually exist in PHP and MySQL. For this, it’s best to consult the latest Internet references, or forums where experts in coding gather and help all us abject newbs. LOL
If you’ve made sure everything’s spelled right, and all the functions are supposed to run…well, you do like I did and call upon a friend or trusted authority who knows more about how programming languages are supposed to be written and run. Web development involves programming languages just like the rest of computer science, so there’s no shame in asking if you’re like me and frustrated by anything that resembles math. 😛
Summary
The code I’ve demo’ed here does work, at least for searching card names. We’re not sure what was blocking it from working before…oh well, that’s web development for you, LOL. (By the way, searching by color, set name, and all the other stuff still presents an error…it’s definitely still a work in progress. But at least the whole page doesn’t vomit an error every time you hit “Submit!”)
And if you try this code and get even more success out of it, tell me about it in the comments! To quote Robert Stack, “YOU may be able to help solve a mystery…” 😀
