Tag Archives: database

Crashed Database Table? Never Fear, PHPMyAdmin is Here!

No matter whether you’re a veteran at using databases or a novice to the world of MySQL, there is one thing you NEVER want to encounter: a vanished database. (Especially when you’ve put a lot of work into loading that database full of content!) But if you’re facing this right now, never fear! The following article, compiled from my personal experience (and some frantic Googling) will help you attempt to restore that which seems lost.

The Situation: WP_Posts for Crooked Glasses Was GONE

Early on the morning of July 22nd, I was busily editing some WordPress pages after I had uploaded one of my weekly posts. The pages, however, would not save correctly–it seemed they “forgot” all the edits I made, no matter how many times I pressed the “Update” button.

About an hour later (I was working over dialup, so things were VERY slow), I tried again to update the page, only to be told “This page does not exist.” I tried navigating to the other pages I was working on–same message. Then I tried to view my blog…and was absolutely flabbergasted at what I saw.

NOTHING.

A big fat 650-pixel-wide space of NOTHING, where all my posts should be. I logged into my WordPress site, and both the Posts and Pages counts read 0.

You can probably imagine what happened next. Over 2 years of work (work I had only limited backups of), GONE? Just like THAT?! Furious weeping, gnashing of teeth (and, admittedly, some throwing of small items across the room) ensued. I scanned through all of WordPress’ help files hosted in my dashboard, but to no avail. I had no idea what had happened, and had no idea how to fix it.

…Well, I had no idea how to fix it, until I thought of something a little outside the box.

PHPMyAdmin: The Unexpected Savior

I remembered, in between gasping for panicked breaths, that my blog was hosted on my domain, and that the databases and tables for my blog should be housed within the PHPMyAdmin bit of my host’s control panel. After all, that’s how I’d worked with databases back in the days of fanlistings and such.

Working as quickly as dialup would allow, I opened PHPMyAdmin, clicked my WordPress blog’s database name, and pulled up the “wp_posts” table from within the huge list of tables it gave me.

Immediately, I was greeted with the message that answered my question and gave me another: “This table is marked as crashed and should be repaired.”

Okay, great, it’s crashed but it can be repaired, I thought. So how do you go about DOING that?

I Googled for help (thank God for Google!), and came across a number of articles, such as this one from SiteGround, telling me to “look for a drop-down menu below the list of tables, check the one that needs repairing, and choose ‘Repair Table.'”

oddly_blankpage
Because of my dialup connection, when I tried to look at the wp_posts table, it did not load the table list, nor did it load any options at the bottom of the screen.

cantfind_repairtable
I thought perhaps that the option to “Repair Table” lay at the bottom of the sidebar, but all I saw at the very bottom of the sidebar menu was “Create Table.”

selecting_home
In desperation, I clicked the “Home” button on the sidebar…

databases_fromhomescreen
…then clicked the “Databases” button (the very top left button in the big window).

database_list
From there, I selected my WordPress blog’s database name (all of these have been obscured for security reasons).

repair_table
Selecting the database FINALLY brought up a list of the contained tables in the larger window. And there, at the bottom of the page, lay the long-sought drop-down box. HALLELUJAH! I quickly clicked the check-mark box next to “wp_posts,” then used the drop-down menu to select “Repair Table.”

fixed_database
And, a few minutes later–presto! The table was fixed!

One Small Caveat

The “Repair Table” solution usually works for most crashed database tables…but notice I said usually. Sometimes, a table crashes and you can’t get it repaired no matter what. :C I recommend doing backups of your work as often as possible, just in case.

Puzzling through PHP, Part 2: Making a Searchable Database

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…” 😀