18 replies to this topic

[OliverWKim]

I'm working on a PHP-based MMORPG akin to the Kingdom of Loathing and I was wondering how I should store 'buffs' and items, like energy boosts and stuff.

[Reedbeta]

How about in a database?

[OliverWKim]

Sorry, I wasn't very specific in my previous post. Should I have a separate table for every user in MySQL or a column for 'buffs' and powerups in the 'users' table? I'm not sure how you could separate all the different powerups and access each one separately when they're all in the same cell. Any thoughts?

[Reedbeta]

Neither. Have a table for users, and a table for powerups. Each should have an id number. Then create a third table with just two columns, one which is a user id, and one which is a powerup id. A user has a powerup if there is an entry connecting them in this third table.

This is a general database design strategy for many-to-many relationships that you can use all over the place.

[OliverWKim]

So, in the third table, you would have a number of the same user id connecting with multiple buffs, as it is possible to have more than one buff. Just clarifying. Thanks for the help.

[geon]

Exactly. You can also store additional data in this third collumn, like how many of this item you have.

I reckomend that you read some basic database design tutorials, since it's easy to do bad design decisions if you don't know exactly why they are bad.

[OliverWKim]

Here is a little snippet of code from my registering script that checks whether or not the entered username already exists.

else{
$cxn = mysql_connect($host,$username,$pass);
$db_selected = mysql_select_db("users", $cxn);
$sql = "SELECT user_name FROM $table_name WHERE user_name='$user'";
$result = mysql_query($sql,$cxn);
$num = mysql_num_rows($result);

I consistently get a PHP error like the following:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-8\www\lotmo\register.php on line 42

Which corresponds to the line with mysql_num_rows in the script. Can somebody please tell me what is going on? I stated all the connection variables earlier in the script.

[geon]

Your warning tells you that $result is not a valid MySQL result. That probably means that there is something wrong in the query. Put his line in your script after the call to mysql_query():

Quote

print('sql: '.$sql.' error: '.mysql_error());

Now you can see exactly what query MySQL recieves, and what is wrong with it. Check that SQL carefully. Often, there will be a slight error there.

[OliverWKim]

After I inputed the changes, the error message changed to this:

sql: SELECT user_name FROM users WHERE user_name='' error: No database selected
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-8\www\lotmo\register.php on line 43

But I already selected a database with my mysql_select_db function. Or did I do something wrong?

[stodge]

Print out $db_selected? See if mysql_select worked.

[OliverWKim]

I checked and the database has been selected properly. Does anyone know anything else that could have possibly gone wrong? I'm at my wit's end. Thanks for all the great support so far, though!

[OliverWKim]

I added extra quotes to my $sql query and the mysql error stopped showing up. In its place there are two hyphens, like so: --. I'm sorry if this is starting to sound like a wild goose chase, but my server seems to hate me!

[OliverWKim]

<?php
session_start();
include "includes/pass.inc";
$user = $_POST['username'];
$passwd = $_POST['password'];
$rpasswd = $_POST['rpassword'];
$email = $_POST['email'];
$table_name = "users";

if (strcasecmp($passwd, $rpasswd) !== 0) {
include "includes/header.inc";
echo "Passwords do not match! Please try again!";
include "pages/register.inc";
include "includes/footer.inc";
}
else{
if($user = ""){
include "includes/header.inc";
echo "The user field has been left blank! Please <a href='index.php?page=register'>try again</a>.";
include "includes/footer.inc";
}
elseif($passwd = ""){
include "includes/header.inc";
echo "The password field has been left blank! Please <a href='index.php?page=register'>try again</a>.";
include "includes/footer.inc";
}
elseif($email = ""){
include "includes/header.inc";
echo "The email field has been left blank! Please <a href='index.php?page=register'>try again</a>.";
include "includes/footer.inc";
}
elseif(strlen($user) > 30){
include "includes/header.inc";
echo "Your username is too long! Please <a href='index.php?page=register'>shorten it</a>.";
include "includes/footer.inc";
}
else{
$cxn = mysql_connect($host,$username,$pass);
$db_selected = mysql_select_db("users",$cxn);
$sql = "SELECT 'user_name' FROM $table_name WHERE 'user_name'='$user'";
$result = mysql_query($sql,$cxn);
$number = mysql_num_rows($result);

if($number > 0){
include "includes/header.inc";
echo "Username already exists! Please <a href='index.php?page=register'>try again</a>.";
include "includes/footer.inc";
}
else{
$md5pass = md5($passwd);
$q = "INSERT INTO users VALUES ('$username','$md5pass','$email')";
return mysql_query($q,$cxn);
include "includes/header.inc";
echo "Welcome to <strong>Work in Progress Game</strong>, " . $username . "!<br>";
include "includes/footer.inc";
}
}
}

?>

Here is the full text of the script for reference. I'm a beginner at PHP so I sorta overdosed on the elseifs.

[stodge]

Not sure if any of this will help, but try looking at code examples:

http://wsframework.c...1.2&view=markup

http://wsframework.c...1.3&view=markup

[stodge]

And check on the documentation at http://www.php.net - there will be some examples on using mySQL and people usually post comments on the docs.

[Reedbeta]

By the way, use [code]...[/code] tags when you post code, please.

[geon]

Adding the quotes is not helpfull here, I'm afraid.

This code...

"SELECT 'user_name' FROM $table_name WHERE 'user_name'='$user'";

...would simply return an empty result, since the WHERE clausule now compares the string "user_name" to whatever is in the variable $user.

Don't make changes like that if you don't understand what they do, and why you make them. Coding is not about randomly changing stuff and hoping they will suddenly be right. You need to figure out what is wrong, and fix it.


The SQL that you printed earlier was clearly missing a user name in it. That means the input variable to the script is not valid. Solve that problem first. (Usually, it would be fine to check that in the beginning of the script and just die() with a short message if it is not available. I guess you send the username from a form in another file, so unless the user is actually trying to break your code, it should work.)

The error message indicates that your database was'nt selected. You wrote that you have checked that, but I must say I believe the error message.

Put another

print(mysql_error());

immedeately after the call to mysql_select_db(), and you might find out was went wrong.


I should also take the opportunity to point out that you have a serious flaw in your code: SQL injection! The variable $user_name is just taken from the $_POST data without any validation, and sent into your SQL without even escaping it. If someone write this...

'; DROP DATABASE users;

...as their username when they log on, they can delete your database. Allways treat data from the outside of the script very suspiciously. The username should be matched against a regular expression to see if it's valid, or escaped before used in the SQL.

[OliverWKim]

Thanks for the help, geon. I do realize the dangers of SQL injection and thus this is not going up on the internet after some serious revisions; I still have a lot of reading to do. What SQL are you referring to that is missing the user name? I added the print mysql error snippet you suggested, but it displays no errors, which means that the database has been selected.

[Reedbeta]

He's talking about the error message you posted:

sql: SELECT user_name FROM users WHERE user_name='' error: No database selected

It shows a blank string for the username, showing your $user variable was empty.

By the way, you don't need to hold on to the $cxn variable and pass it to every function. If you call mysql_connect it will set the default connection, which will be used by the other functions.