mysql table problem

[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.

[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() );
 

[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.

[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:

Code: Select all

$myCityname = $name;

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.

[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.

[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.

[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....

[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?

Code: Select all

echo $name."'s city<br>";

[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.....

[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';