Page 1 of 1
mysql table problem
Posted: Fri Jan 14, 2011 3:55 pm
by Slav2
ok i have everything so far working properly, just the only thing wrong is that it doesnt add the info i needed it to the 'resources' table. ill show you my code:
Code: Select all
<?php
include("confff.php");//check
//set variable
$passkey=$_GET['passkey'];
$sql1="SELECT * FROM temp WHERE code='$passkey'";
$result1=mysql_query($sql1);
//if sucessfully queried
if($result1)
{
//how many rows have key
$count=mysql_num_rows($result1);
//if passkey in database, retrieve data
if($count==1)
{
$rows=mysql_fetch_array($result1);
$namex=$rows['username'];
$emailx=$rows['email'];
$passwordx=$rows['password'];
//takeoutspace
$name=str_replace(' ','',$namex);
$email=str_replace(' ','',$emailx);
$password=str_replace(' ','',$passwordx);
//insert into users table
$sql2="INSERT INTO users SET username='$name', points='5', mycities='1', email='$email', password='$password'";
$result2=mysql_query($sql2);
if ($result2)
{
//starting values
$myCityname = $name . '\'s city';
$sql3="INSERT INTO resources SET username='$name', cityname='$myCityname', wood='200', stone='200', iron='200', people='10', gold='100', happiness='100', loyalty='100'";
$result3=mysql_query($sql3);
//remove from temp
$sql5="DELETE FROM temp WHERE code='$passkey'";
$result=mysql_query($sql5);
header("location:confirmation2.html");
}
else
{
echo "wrong confirmation code";
}
}
}
?>
it inserts corectly into the users table, only not into the 'resources' table. ive tried a lot of things, but its still not working for some reason.
Re: mysql table problem
Posted: Fri Jan 14, 2011 4:50 pm
by Chris
Try see if MySQL is having any errors with the
mysql_error() function:
Code: Select all
$result3=mysql_query($sql3) or die( mysql_error() );
Re: mysql table problem
Posted: Sat Jan 15, 2011 4:58 am
by Slav2
see it does this corectly
Code: Select all
//insert into users table
$sql2="INSERT INTO users SET username='$name', points='5', mycities='1', email='$email', password='$password'";
$result2=mysql_query($sql2);
and not this:
Code: Select all
//starting values
$myCityname = $name . '\'s city';
$sql3="INSERT INTO resources SET username='$name', cityname='$myCityname', wood='200', stone='200', iron='200', people='10', gold='100', happiness='100', loyalty='100'";
$result3=mysql_query($sql3);
they both are setup exactly the same way.
Re: mysql table problem
Posted: Sat Jan 15, 2011 9:04 am
by alexander19
I think the problem could be here:
Code: Select all
$myCityname = $name . '\'s city';
I remember having a problem once with the single quotes too,it didn't allowed me to insert them into database and if I did so manually using phpmyadmin it didn't allowed me to call a text that was using them from the database.
So try to change it just for testing purposes to:
And if it works this way,then you know where the problem is,if not,try using the mysql_error() function that chris mentioned,and see whats going wrong.
Re: mysql table problem
Posted: Sat Jan 15, 2011 7:19 pm
by Slav2
ok ill try it in a couple hours and tell you if it worked guys
thanks for the help tho.
question:
but i need the city name to be the players Name 's City. so idk how to get that right.
Re: mysql table problem
Posted: Sat Jan 15, 2011 7:29 pm
by PaxBritannia
Instead of
Code: Select all
$myCityname = $name . '\'s city';
try
Code: Select all
$myCityname = $name . "\'s city";
See if that fixes it, good luck!
pax.
Re: mysql table problem
Posted: Sat Jan 15, 2011 11:25 pm
by Slav2
AWESOME!!! thanks it just worked. it put the info into both tables...
i have one more problem. umm testing if the username doesnt contain %$%@#% and checking if that username already exsist and same with email already being used.
i tried registering with the same email, and it allows it, and same with username thats already tooken, but it still adds it to the table. and it doesnt stop $%!@#$! from being used....if someone could tell me what im going wrong pls ??
Code: Select all
<?php
include('confff.php');
//test to see if username is alfanumeric
$testname=$_POST['username'];
if (!eregi("([^A-Za-z0-9])",$testname))
{
//test for duplicate names
$query="SELECT * FROM users WHERE username ='$_POST(username)'";
$result=mysql_query($query);
$num=mysql_num_rows($result);
if ($num == 0)
{
//test for duplicate email
$query2="SELECT * FROM users WHERE email = '$_POST(email)'";
$result2=mysql_query($query2);
$num2 = mysql_num_rows($result2);
if($num2==0)
{
//if emails and passwords match up
if(($_POST['pass']==$_POST['pass2']) && ($_POST['email']==$_POST['email2']))
{
//generate random confirmation code
$confirmation_code=md5(uniqid(rand()));
//get rid of all html from hackers
$name=strip_tags($_POST['username']);
$email=strip_tags($_POST['email']);
$pass=strip_tags($_POST['pass']);
$sql="INSERT INTO temp (code, username, email, password) VALUES ('$confirmation_code', '$testname', '$email', '$pass')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
if($result)
{
$message="Your Confirmation link \r\n
Click on this link to activate your account \r\n
http://www.royalempires.com/confirmation.php?passkey=$confirmation_code";
$sentmail=mail("$email",'Registration Confirmation',"$message" ,'From: admin@royalempires.com');
header("Location:thankyou.html");
}
else
{
echo "Not found your email in our database";
}
//if your email succesfully sent
if($sentmail)
{
echo "Your Confirmation link has been sent to your e-mail account";
}
else
{
echo "cannot send confirmation link to your e-mail adress";
}
}
else
{
header("location:badmatch.html");
}
}
else
{
header("Location:emailinuse.html");
}
}
else
{
header("Location:nameinuse.html");
}
}
?>
there is something wrong there with checking and not allowing #@^@#, same usernames if one is already tooken, and same with email, but i cant see what it is....
Re: mysql table problem
Posted: Sat Jan 15, 2011 11:36 pm
by Callan S.
Why do you need to record the city name like that anyway? You already have the $name? Surely you can do it in the presentation and not bother with the database entry?
Re: mysql table problem
Posted: Sat Jan 15, 2011 11:44 pm
by Slav2
well because cities can be tooken over by players...and also players can change their own city name and make new ones...so it has to be stored somewere.....
Re: mysql table problem
Posted: Sat Jan 15, 2011 11:47 pm
by PaxBritannia
First off, your problem with not detecting duplicate emails and users:
Code: Select all
$query="SELECT * FROM users WHERE username ='$_POST(username)'";
the problem here is that your "'$_POST(username)" variable is in the incorrect format. The correct format is $_POST['username'], but you can't insert that into the query without breaking it, so the solution is to define another variable and use that in the query.
Code: Select all
//test for duplicate names
$username = $_POST['username']
$query="SELECT * FROM users WHERE username ='$username'";
The same problem happens when checking their email.
As for not stopping "$%!@#$!", you have never actually tested for it.
Code: Select all
//get rid of all html from hackers
$name=strip_tags($_POST['username']);
$email=strip_tags($_POST['email']);
$pass=strip_tags($_POST['pass']);
Unfortunately, strip_tags doesn't remove "$%!@#$!, you'll have to use preg_match to detect for it or write your own function. Also, you want to alert the user before modifying any of his input such as password or the user might not be able to login. Emails can also contain several of those characters.
I hope that clears things up.
pax.
@Callan S.: At first sight, I though that storing city's name would be redundant too. But with enough experience playing PBBG's I remembered that often, they provide you with a default city name "your-name's city" and then let you change it after logging in. In Slav2's code, before defining it, it says starting values. Experience does pay off!
Code: Select all
//starting values
$myCityname = $name . '\'s city';