ArcEmu: [Tutorial #3]: Managing MySQL - ArcEmu

Jump to content

Toggle shoutbox Lastest Announcements

dfighter  : (07 December 2014 - 12:06 PM) Arcemu is in hibernation mode, please read http://arcemu.org/fo...showtopic=26903
dfighter  : (01 January 2013 - 05:56 PM) Arcemu wishes you all a happy new year!
Hasbro  : (12 September 2012 - 10:01 AM) Please excuse our outage from the web! Our web host had a major malfunction!
dfighter  : (01 September 2012 - 04:05 PM) Since the spam bots just don't want to stop, I've enabled admin verification when registering.
dfighter  : (23 January 2012 - 09:56 PM) Please note that from now on you will need to confirm your email on the wiki in order to edit it!
Hasbro  : (31 December 2011 - 12:50 PM) Happy New Years all!
Navid  : (26 December 2011 - 04:09 AM) Merry Christmas !!!!!! Happy holidays all :)
WAmadeus  : (24 December 2011 - 03:54 PM) Merry Christmas to all!
dfighter  : (24 December 2011 - 11:05 AM) The Arcemu team wishes y'all a Merry Christmukkah!
Hasbro  : (05 October 2011 - 12:53 PM) Looking for web designers for upcoming web related project. If you're interested in designing user interfaces contact me
dfighter  : (02 September 2011 - 03:47 PM) So who here wants vehicles in Arcemu? :P http://arcemu.org/fo...showtopic=25440
Hasbro  : (14 August 2011 - 03:25 PM) Join us on irc, grab an irc client and connect to irc.freenode.net join channel #arcemu /server irc.freenode.net:6667 /join #arcemu
jackpoz  : (03 August 2011 - 05:33 AM) to all Lua Engine (old one) users: please check http://arcemu.org/fo...showtopic=25274
Hasbro  : (20 May 2011 - 05:27 PM) Looking for people experienced with CMake configuration and setup! Contact me asap
Hasbro  : (15 May 2011 - 05:03 PM) ArcEmu is recruiting C++ programmers, contact Hasbro if interested.
paroxysm  : (03 May 2011 - 06:26 PM) Updated luabridge gossip example to describe the whole gossip creation process rather than just how to create menu. Gossip tutorial
paroxysm  : (23 April 2011 - 11:35 AM) Lua writers can refer to the Luabridge Tutorials section in the Wiki to learn how to write gossip code correctly.
Hasbro  : (20 April 2011 - 05:22 PM) Thank you for your continuous contribution of bug reports, we are working on them.
Hasbro  : (17 April 2011 - 03:20 AM) Please consider donating to support our bills. Donations can be sent using PayPal to donations@arcemu.org - Thank you for your support.
paroxysm  : (10 April 2011 - 12:43 AM) Refer to the Luabridge Tutorials section in the Wiki to learn the new syntax of luabridge.
Resize Shouts Area

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

[Tutorial #3]: Managing MySQL Comes with an item searcher! :D

#1 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 16 September 2009 - 06:31 PM

Introduction

Hello once more for my third tutorial. This will prolly be my last tutorial for this month- I'm going to a camp next week and it's for 3 long weeks :unsure: This tutorial will focus once again on forms, except this time I'm going to focus more on MySQL then last time. So what are we making now? An item searcher that will create the same item tooltip as Wowhead's AND correctly color the items based on their quality!

I would sincerely recommend you read my previous tutorials (ESPECIALLY THE SECOND ONE) since this directly picks up from where I last left off. Here is the links to them:

[Tutorial #1]: Website Basics
[Tutorial #2]: Fancy Forms

For this tutorial you will need to know everything from my previous tutorial, plus some basic HTML. If you're new with HTML you can find great guides at w3schools!

Brain stormin'

Since this is the most complicated script I've written for my tuts so far, I think that I should also show you my thinking process as I make it. I always stop and think on how I'm going to make my script for about 5 seconds. It's not much, but it helps.

Here's what I know:

-This PHP will require a form of some sort
-I know that Wowhead's tooltip (to show up and work correctly) must have "<script src="http://static.wowhead.com/widgets/power.js"></script>" in the header and the tooltip must look like: "<a href="http://www.wowhead.com/?item=ITEMIDHERE">[ITEMNAME]</a>"
-Therefore for my script to work I will need to get the following item rows: The Entry ID, the Name, and the Quality.
-This form will follow the exact same guidelines as the other one, except that I will need to convert MySQL data into usable variables

And that is all I need to do to brainstorm. With that I can start coding.

The Form

The form for this one will stay EXTREMELY basic. Like the tutorial before this form will consist of only one file. I'm not going to explain this part of the form, since I completely covered it in the second tutorial.

<?php
echo "<html><body>";
echo "<center><table>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<form name=myform method=post action=form.php>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<tr><td width=200><b>Item Name</b></td></tr><td><input type=text name=itemname value=''></td>";
echo "<br \><br \>";
echo "<tr><td colspan=2 align=center><br><input type=submit name=submit value=Search></td></tr>";
echo "</form>";
?>


This uses the file 'form.php'.

The Script

Now here comes the fun part! First I want to turn off error reporting. If my user searches for an invalid item I don't want the PHP blaring out mistakes all over my page :D

error_reporting(0);


Next I want to make an If Else statement that will show the results (but hide the form) IF the user submitted a search and vice versa. So I'll add:

error_reporting(0);

if(isset($_POST['submit']))
{ }


Inside the {} will go the result PHP and after the {} will be the form. The first thing I want to do is turn the posted name in the search box into a variable AND make my result create a connection to my database. If you read my previous tutorials you'd know how to do this:

error_reporting(0);

if(isset($_POST['submit']))
 {
 $itemname = $_POST['itemname'];

 $con = mysql_connect("127.0.0.1":"3306","root","root") or die(mysql_error());
 mysql_select_db("world") or die(mysql_error());

 $itemname = mysql_real_escape_string(html_entity_decode(htmlentities($itemname)));

 mysql_close();
 }


The only new thing up there is the second $itemname. What it does is refine the previously defined variable 'itemname'. It does NOT make a new variable.

Now that my result field can connect to my connection, and enter in the world database I just need to make it go to the correct table. The thing about PHP though is that the simplest way to display the information is through a set of new variables. Let's write them out so that I can explain them to you.

$query = "SELECT name1, quality, entry FROM items WHERE name1 LIKE '%".$itemname."%'";

$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);
$row = mysql_fetch_array($result);


The query says is the core to these 4 variables. It says which table I want (FROM items), and inside of it it says that I want to get the following rows: name1, quality, and entry. Then it says that I want to restrict this search to where in column 'name1' the data fits what the user submitted in the text field. Notice that I used LIKE, and that just before the 'itemname' variable there are %. These are the wild cards saying that there may be more in the name of the item before OR after the submitted info. This gives some leeway to lazy people who can't type in an item's full name :P

Next the result variable just says: "Execute the query variable, and if it doesn't work popup an error". This variable will just be used for the 'numrows' and 'row' variables.

The 'numrows' variable is just there to see if the item that the player searched for exists. It says "see if this data exists, and if so in how many rows?".

The 'row' says get the data in row # X. NOTE: A common mistake here is that it counts off using the tables row. IT DOES NOT! This actually counts off from our 'query' variable, starting from 0. So name1 would be row[0], quality row[1], and entry row[2] (or instead of a number you could just use the rows name. I'll do both so that you can see how it works).

Enough said, let's move on. We want to make sure that a correct error comes up if there is no search results found. So let's add another IF statement using our 'numrows' variable.


$query = "SELECT name1, quality, entry FROM items WHERE name1 LIKE '%".$itemname."%'";

$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);
$row = mysql_fetch_array($result);

if($numrows == 0)
 {
 die("<font color='#FF0000'><b>Invalid item!</b></font>");
 }


And that's all we need. Next we need the item's name to show up. This is where we will use our 'row' variable.


$query = "SELECT name1, quality, entry FROM items WHERE name1 LIKE '%".$itemname."%'";

$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);
$row = mysql_fetch_array($result);

if($numrows == 0)
 {
 die("<font color='#FF0000'><b>Invalid item!</b></font>");
 }

echo "Name:[".$row[0]."]";


Great! So now it displays the name. But we still need our tooltip to work. So let's add everything we've made together first, so that we can see what we should do.

<?php
error_reporting(0);

if(isset($_POST['submit']))
 {
 $itemname = $_POST['itemname'];

 $con = mysql_connect("127.0.0.1":"3306","root","root") or die(mysql_error());
 mysql_select_db("world") or die(mysql_error());

 $itemname = mysql_real_escape_string(html_entity_decode(htmlentities($itemname)));

$query = "SELECT name1, quality, entry FROM items WHERE name1 LIKE '%".$itemname."%'";

$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);
$row = mysql_fetch_array($result);

if($numrows == 0)
 {
 die("<font color='#FF0000'><b>Invalid item!</b></font>");
 }

echo "Name:[".$row[0]."]";

 mysql_close();
 }

echo "<html><body>";
echo "<center><table>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<form name=myform method=post action=form.php>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<tr><td width=200><b>Item Name</b></td></tr><td><input type=text name=itemname value=''></td>";
echo "<br \><br \>";
echo "<tr><td colspan=2 align=center><br><input type=submit name=submit value=Search></td></tr>";
echo "</form>";
?>


Mkay. So let's go back up to our things that we know...

Quote

-I know that Wowhead's tooltip (to show up and work correctly) must have "<script src="http://static.wowhead.com/widgets/power.js"></script>" in the header and the tooltip must look like: "<a href="http://www.wowhead.com/?item=ITEMIDHERE">[ITEMNAME]</a>"


Hmmm. So now I need to add a head AND add an anchor tag around the item name. Let's start by making a nice and fancy <head>

<html>
     <head>
     <title>Item Searcher</title>
     <script src="http://static.wowhead.com/widgets/power.js"></script>
     </head>
<body>
<?php
error_reporting(0);

if(isset($_POST['submit']))
 {
 $itemname = $_POST['itemname'];

 $con = mysql_connect("127.0.0.1":"3306","root","root") or die(mysql_error());
 mysql_select_db("world") or die(mysql_error());

 $itemname = mysql_real_escape_string(html_entity_decode(htmlentities($itemname)));

$query = "SELECT name1, quality, entry FROM items WHERE name1 LIKE '%".$itemname."%'";

$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);
$row = mysql_fetch_array($result);

if($numrows == 0)
 {
 die("<font color='#FF0000'><b>Invalid item!</b></font>");
 }

echo "Name:[".$row[0]."]";

 mysql_close();
 }

echo "<html><body>";
echo "<center><table>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<form name=myform method=post action=form.php>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<tr><td width=200><b>Item Name</b></td></tr><td><input type=text name=itemname value=''></td>";
echo "<br \><br \>";
echo "<tr><td colspan=2 align=center><br><input type=submit name=submit value=Search></td></tr>";
echo "</form>";
?>
</body>
</html>


If you don't know what <html> or <head> or <body> tags are then go to w3schools. These are the most basic HTML tags in the world! But it's fine if you don't know them. :P

Anyways, now we need to add that anchor tag for the tool tip. It actually is extremely simple to make, and if you've been paying attention to this tutorial you should be able to have made it your self. Anyways, here is the perfect anchor tag for our script!

<a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a>


Now let's plug that in to our previous script.


<html>
     <head>
     <title>Item Searcher</title>
     <script src="http://static.wowhead.com/widgets/power.js"></script>
     </head>
<body>
<?php
error_reporting(0);

if(isset($_POST['submit']))
 {
 $itemname = $_POST['itemname'];

 $con = mysql_connect("127.0.0.1":"3306","root","root") or die(mysql_error());
 mysql_select_db("world") or die(mysql_error());

 $itemname = mysql_real_escape_string(html_entity_decode(htmlentities($itemname)));

$query = "SELECT name1, quality, entry FROM items WHERE name1 LIKE '%".$itemname."%'";

$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);
$row = mysql_fetch_array($result);

if($numrows == 0)
 {
 die("<font color='#FF0000'><b>Invalid item!</b></font>");
 }

echo "Name: <a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a>";

 mysql_close();
 }

echo "<html><body>";
echo "<center><table>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<form name=myform method=post action=form.php>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<tr><td width=200><b>Item Name</b></td></tr><td><input type=text name=itemname value=''></td>";
echo "<br \><br \>";
echo "<tr><td colspan=2 align=center><br><input type=submit name=submit value=Search></td></tr>";
echo "</form>";
?>
</body>
</html>


So now we have everything ready and set up, EXCEPT for the color quality! To make this work I'm just going to set up a massive if statement. Here it is:

if($row[1] == 0) //Super shitty item
 {    
 echo "Name: <font color='#C9C299'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 1) //Gay white item, aka michael jackson
 {    
 echo "Name: <font color='#FFFFFF'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 2) //Uncommon
 {    
 echo "Name: <font color='#66FF33'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 3) //Rare
 {    
 echo "Name: <font color='#0022DD'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 4) //Epic
 {    
 echo "Name: <font color='#6633DD'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 5) //Artifact
 {    
 echo "Name: <font color='#FFA500'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 6) //Made in Heaven
 {    
 echo "Name: <font color='#D2B48C'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }


Please forgive my sarcasm. I get pretty cranky late at night :o . Anyways, now let's plug this baby into our script.


<html>
     <head>
     <title>Item Searcher</title>
     <script src="http://static.wowhead.com/widgets/power.js"></script>
     </head>
<body>
<?php
error_reporting(0);

if(isset($_POST['submit']))
 {
 $itemname = $_POST['itemname'];

 $con = mysql_connect("127.0.0.1":"3306","root","root") or die(mysql_error());
 mysql_select_db("world") or die(mysql_error());

 $itemname = mysql_real_escape_string(html_entity_decode(htmlentities($itemname)));

$query = "SELECT name1, quality, entry FROM items WHERE name1 LIKE '%".$itemname."%'";

$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);
$row = mysql_fetch_array($result);

if($numrows == 0)
 {
 die("<font color='#FF0000'><b>Invalid item!</b></font>");
 }

if($row[1] == 0) //Super shitty item
 {    
 echo "Name: <font color='#C9C299'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 1) //Gay white item, aka michael jackson
 {    
 echo "Name: <font color='#FFFFFF'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 2) //Uncommon
 {    
 echo "Name: <font color='#66FF33'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 3) //Rare
 {    
 echo "Name: <font color='#0022DD'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 4) //Epic
 {    
 echo "Name: <font color='#6633DD'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 5) //Artifact
 {    
 echo "Name: <font color='#FFA500'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }
if($row[1] == 6) //Made in Heaven
 {    
 echo "Name: <font color='#D2B48C'><b><a href='http://www.wowhead.com/?item=".$row['entry']."'>[".$row[0]."]</a></b></font><br />";
 }

mysql_close();
}

echo "<html><body>";
echo "<center><table>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<form name=myform method=post action=form.php>";
echo "<tr><td colspan=2 align=center></td></tr>";
echo "<tr><td width=200><b>Item Name</b></td></tr><td><input type=text name=itemname value=''></td>";
echo "<br \><br \>";
echo "<tr><td colspan=2 align=center><br><input type=submit name=submit value=Search></td></tr>";
echo "</form>";
?>
</body>
</html>


And that's it! It's done! This will display ANY item that Wowhead has in it's database.

I sincerely hope that you've learned a bunch from my few tutorials. If you guys like these I may keep writing more. Show your support and post any comments, suggestions, or requests.

Bob - Concept designer at Serenade of Sorrow. If you want to talk to me, just ask for Maverfax

PS: None of the PHP here has been tested yet. It should, however, work fine.
1

#2 User is offline   Ashberry 

  • Member
  • Pip
  • Group: Members
  • Posts: 99
  • Joined: 10-August 09
  • Gender:Male
  • Interests:rofni's girlfriend (and his coding talents)

Posted 16 September 2009 - 06:35 PM

Quote

if($row[1] == 1) //Gay white item, aka michael jackson


Voted it up, would have just for the above comment, but it was very useful. I don't have need for it now, but I'm sure one day I will! Also it wasn't in broken English mixed with teenspeak, so an old fart like me could read it. :unsure:
0

#3 User is offline   this_is_junk 

  • Retired Retard
  • Group: Contributor
  • Posts: 2,432
  • Joined: 08-October 08
  • Gender:Male
  • Location:Washington State

Posted 16 September 2009 - 06:36 PM

Very little on actually "managing" MySQL here. :unsure:
GAH!!! >_< ...can't...just...stop...working...
0

#4 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 16 September 2009 - 06:49 PM

Thanks Ash! :unsure:

And what do you mean by that junkie? Editing MySQL and stuff like that?
0

#5 User is offline   Navid 

  • Persian Prance of ArcEmu
  • Group: Super Moderator
  • Posts: 718
  • Joined: 30-June 09
  • IRC:Lurker-
  • Gender:Male
  • Location:Iran
  • Interests:PHP,C++,ArcEmu and exactly our lovely community and specially money xD
  • Server OS:Windows

Posted 16 September 2009 - 06:51 PM

Good try,I must spend 10 min just for read :unsure:
I'm here to check,lurk and kill you so behave or you will be lurked

Posted Image
0

#6 User is offline   Bob Herman 

  • Interested
  • PipPipPipPip
  • Group: Members
  • Posts: 452
  • Joined: 11-October 08
  • Gender:Male

Posted 16 September 2009 - 07:05 PM

Lol, what do you mean?
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users