Failing to select data

Onions · April 4, 2012

Hello all!

Recently I've been playing around with PHP and MySQL. I have had plenty of experience with C++, HTML and other programming languages, so I'm picking things up quickly. I have been trying to make a simple registration form. The idea is that a form will be displayed, and the user puts their data into it. PHP then sends a query to a MySQL database to see if a user exists with that username; if there is someone, it will not register the new user. Otherwise, it will save their data to a table, along with an 'ID' - an auto-incrementing number.

Irritatingly though, the query will not work. Whenever there is a user saved in the database with a name identical to the one being inputted, a new record is created anyway. The whole code is here;

<html><head><title>
Network registration
</title></head><body bgcolor="#FAFAFA"><font face="sans-serif">
<div style="background:#FAFAFA"> <h2>Network</h2></div>
<?php
require_once 'credentials.php';
$server = mysql_connect($db_hostname, $db_username, $db_password);
if(!$server) die("Unable to connect to the database: " . mysql_error());
mysql_select_db($db_database) or die("Unable to select database: " . mysql_error());

if(isset($_POST['username']) &&
   isset($_POST['password']) &&
   isset($_POST['pplink'])){

   $username = $_POST['username'];
   $password = $_POST['password'];
   $pplink   = $_POST['pplink'];  
   $query = mysql_query("INSERT INTO users VALUES " . "('$username', '$password', '$pplink', 'NULL')");

   if(!$query){
      echo "Registration failed: " . mysql_error();
   }
   else{
     $query = mysql_query("SELECT id FROM users WHERE username=$username");
     if($query){
echo "Sorry, the username " . $username . " is already taken <br />";
        echo mysql_query("SELECT 'id' FROM 'users' WHERE 'username' = $username");
     }
     else{
       echo "Thankyou for registering, " . $username . " <br />";
       echo mysql_query("SELECT 'id' FROM 'users' WHERE 'username' = $username");
     }
   }
}

else{
echo <<<END
<form action='network.php' method='post'>
<table>
  <tr><td>Username</td><td> <input type='text' name='username' /></td></tr><br /> 
  <tr><td>Password</td><td> <input type='password' name='password' /></td></tr><br /> 
  <tr><td>Picture </td><td> <input type='text' name='pplink' /></td></tr><br /> 
  <tr><td><input type='submit' value='Sign up!' /></td></tr>
  <tr><td colspan='2'>Please enter your desired username and password.</td></tr> 
  </table><br /><br />
  'Picture' is the link to your desired profile picture. We recommend uploading the picture to imgur. Pictures must be no larger than 100 X 100.<br />
</form>
END;
}
?>
</font></body></html>

And the part that should check for an existing user with the same username:

   else{
     $query = mysql_query("SELECT id FROM users WHERE username=$username");
     if($query){
echo "Sorry, the username " . $username . " is already taken <br />";
        echo mysql_query("SELECT 'id' FROM 'users' WHERE 'username' = $username");
     }
     else{
       echo "Thankyou for registering, " . $username . " <br />";
       echo mysql_query("SELECT 'id' FROM 'users' WHERE 'username' = $username");
     }
   }

If you want to see what it does, the page it produces can be found here: http://www.harryrabbit.co.uk/electronics/misc/network.php

A screenshot of the table where the data is stored is attached.

Hopefully someone can point out what I've done wrong...

Onions.

joel24 · April 4, 2012

you need to check if the username exists before you insert it... or set the username as the primary key a nd use the insert on duplicate syntax

Onions · April 4, 2012

OK, I can see what I've done wrong, but it still doesn't work after I tried to fix it...

<html><head><title>
Network registration
</title></head><body bgcolor="#FAFAFA"><font face="sans-serif">
<div style="background:#FAFAFA"> <h2>Network</h2></div>
<?php
require_once 'credentials.php';
$server = mysql_connect($db_hostname, $db_username, $db_password);
if(!$server) die("Unable to connect to the database: " . mysql_error());
mysql_select_db($db_database) or die("Unable to select database: " . mysql_error());

if(isset($_POST['username']) &&
   isset($_POST['password']) &&
   isset($_POST['pplink'])){

   $username = $_POST['username'];
   $password = $_POST['password'];
   $pplink   = $_POST['pplink'];  

   $query = mysql_query("SELECT id FROM users WHERE username=$username");
   if($query){
echo "Sorry, the username " . $username . " is already taken <br />";
    }
   else{
     $query = mysql_query("INSERT INTO users VALUES " . "('$username', '$password', '$pplink', 'NULL')");
     if(!$query){
        echo "Registration failed: " . mysql_error();
     }
     else{
         echo "Thankyou for registering, " . $username . " <br />";
     }
   }
}

else{
echo <<<END
<form action='network.php' method='post'>
<table>
  <tr><td>Username</td><td> <input type='text' name='username' /></td></tr><br /> 
  <tr><td>Password</td><td> <input type='password' name='password' /></td></tr><br /> 
  <tr><td>Picture </td><td> <input type='text' name='pplink' /></td></tr><br /> 
  <tr><td><input type='submit' value='Sign up!' /></td></tr>
  <tr><td colspan='2'>Please enter your desired username and password.</td></tr> 
  </table><br /><br />
  'Picture' is the link to your desired profile picture. We reccomend uploading the picture to imgur. Pictures must be no larger than 100 X 100.<br />
</form>
END;
}
?>
</font></body></html>

Onions.

Onions · April 4, 2012

Got another problem :facewall:

I have got around to adding a login section for the users that have already created an account. Once again, the query to the database doesn't appear to be working:

<html><head><link rel="stylesheet" type="text/css" href="style.css" /><title>
Network registration
</title></head><body><font face="sans-serif">
<?php
require_once 'credentials.php';
$server = mysql_connect($db_hostname, $db_username, $db_password);
if(!$server) die("Unable to connect to the database: " . mysql_error());
mysql_select_db($db_database) or die("Unable to select database: " . mysql_error());

if(isset($_POST['loginUser']) &&
   isset($_POST['loginPass'])) {
  
   $user = $_POST['loginUser'];
   $pass = $_POST['loginPass'];
   
   $sql = mysql_query("SELECT password FROM users WHERE username=" . $user);
   if($sql == $pass){
     echo "Welcome, " . user . "<br />";
     $pp = mysql_query("SELECT pplink FROM users WHERE username=" . $user);
     echo "<img src='" . $pp . "'>";
     die();
   }
   else{
     echo "Incorrect login <br />";
   }
}
     

if(isset($_POST['username']) &&
   isset($_POST['password']) &&
   isset($_POST['pplink'])){

   $username = $_POST['username'];
   $password = $_POST['password'];
   $pplink   = $_POST['pplink'];  

   $query = mysql_query("SELECT id FROM users WHERE username=" . $username);
   if($query){
     echo "Sorry, the username " . $username . " is already taken <br />";
   }
   else{
     $query = mysql_query("INSERT INTO users VALUES " . "('$username', '$password', '$pplink', 'NULL')");
     if(!$query){
        echo "Registration failed: " . mysql_error();
     }
     else{
         echo "Thankyou for registering, " . $username . " <br /><br />";
         echo <<<END
           <form action='network.php' method='post'><table>
           <tr><td colspan='2'><u>Log in</u></td></tr>
           <tr><td>Username</td><td><input type='text' name='loginUser' /></td></tr>
           <tr><td>Password</td><td><input type='password' name='loginPass' /></td></tr>
           <tr><td colspan='2'><input type='submit' value='login' /></td></tr>
           </table></form>
END;
     }
   }
}

else{
echo <<<END
<form action='network.php' method='post'><table>
<tr><td colspan='2'><u>Log in</u></td></tr>
<tr><td>Username</td><td><input type='text' name='loginUser' /></td></tr>
<tr><td>Password</td><td><input type='password' name='loginPass' /></td></tr>
<tr><td colspan='2'><input type='submit' value='login' /></td></tr>
</table></form>
<form action='network.php' method='post'><table>
<tr><td colspan='2'><u>Register</u></td></tr>
<tr><td>Username</td><td> <input type='text' name='username' /></td></tr><br /> 
<tr><td>Password</td><td> <input type='password' name='password' /></td></tr><br /> 
<tr><td>Picture </td><td> <input type='text' name='pplink' /></td></tr><br /> 
<tr><td><input type='submit' value='Sign up!' /></td></tr>
</table><br /><br />
Please enter your desired username and password.
'Picture' is the link to your desired profile picture. We recommend uploading the picture to imgur. <br /> 
Pictures must be no larger than 100 X 100.
</form>
END;
}
?>
</font></body></html>

The site should search the users database for another user with the same username, and only register the user signing up if there isn't someone else with that username. It doesn't. Also, it should search the database for the password of a user logging in, then match it against the one they entered. It doesn't.

I've been spending ages trying to fix the bugs, but I'm still missing something.

Onions.

Jessica · April 4, 2012

|

V

Onions · April 8, 2012

I don't see any errors! so I used one of the links suggested to turn on reporting for every single error (error_reporting(-1). I also used print_r(); to show the variables that are being used when the problem occurs. Still no glory...

I have been adding to my code to try and figure out what is wrong, but I really don't know:

<html><head><link rel="stylesheet" type="text/css" href="style.css" /><title>
Network registration
</title>
<script type="text/javascript">

  var _gaq = _gaq || [];
  _gaq.push(['_setAccount', 'UA-23079515-1']);
  _gaq.push(['_trackPageview']);

  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();

</script>
</head><body><font face="sans-serif">
<?php
error_reporting(-1); ///////////////////////////////////////////////////////////////////////////////////////////////////
require_once 'credentials.php';
$server = mysql_connect($db_hostname, $db_username, $db_password);
if(!$server) die("Unable to connect to the database: " . mysql_error());
mysql_select_db($db_database) or die("Unable to select database: " . mysql_error());

if(isset($_POST['loginUser']) &&
   isset($_POST['loginPass'])) {
  
   $user = $_POST['loginUser'];
   $pass = $_POST['loginPass'];
   
   $sql = mysql_query("SELECT password FROM users WHERE username=\"" . $user . "\"");
   if($sql == $pass){
print_r($sql);/////////////////////////////////////////////////////////////////////////////////////////////////////////
print_r($pass);////////////////////////////////////////////////////////////////////////////////////////////////////////
     echo "Welcome, " . user . "<br />";
     $pp = mysql_query("SELECT pplink FROM users WHERE username=\"" . $user . "\"");
     echo "<img src='" . $pp . "'>";
     die();
   }
   else{
print_r($sql);//////////////////////////////////////////////////////////////////////////////////////////////////////////
     echo "<h4><u><font color='red'> Incorrect login </font></u></h4><br />";
   }
}
     

if(isset($_POST['username']) &&
   isset($_POST['password']) &&
   isset($_POST['pplink'])){

   $username = $_POST['username'];
   $password = $_POST['password'];
   $pplink   = $_POST['pplink'];  

   $query = mysql_query("SELECT id FROM users WHERE username=" . $username);
   if($query){
     echo "Sorry, the username " . $username . " is already taken <br />";
   }
   else{
     $query = mysql_query("INSERT INTO users VALUES " . "('$username', '$password', '$pplink', 'NULL')");
     if(!$query){
        echo "Registration failed: " . mysql_error();
     }
     else{
         echo "Thankyou for registering, " . $username . " <br /><br />";
         echo <<<END
           <form action='network.php' method='post'><table>
           <tr><td colspan='2'><u>Log in</u></td></tr>
           <tr><td>Username</td><td><input type='text' name='loginUser' /></td></tr>
           <tr><td>Password</td><td><input type='password' name='loginPass' /></td></tr>
           <tr><td colspan='2'><input type='submit' value='login' /></td></tr>
           </table></form>
END;
     }
   }
}

else{
echo <<<END
<form action='network.php' method='post'><table>
<tr><td colspan='2'><u>Log in</u></td></tr>
<tr><td>Username</td><td><input type='text' name='loginUser' /></td></tr>
<tr><td>Password</td><td><input type='password' name='loginPass' /></td></tr>
<tr><td colspan='2'><input type='submit' value='login' /></td></tr>
</table></form>
<form action='network.php' method='post'><table>
<tr><td colspan='2'><u>Register</u></td></tr>
<tr><td>Username</td><td> <input type='text' name='username' /></td></tr><br /> 
<tr><td>Password</td><td> <input type='password' name='password' /></td></tr><br /> 
<tr><td>Picture </td><td> <input type='text' name='pplink' /></td></tr><br /> 
<tr><td><input type='submit' value='Sign up!' /></td></tr>
</table><br /><br />
Please enter your desired username and password.
'Picture' is the link to your desired profile picture. We recommend uploading the picture to 
<a href="http://imgur.com/" style="text-decoration:none">imgur</a>. <br /> 
Pictures must be no larger than 100 X 100.
</form>
END;
}
?>
</font></body></html>

I don't want someone to do my project for me, because that sorta takes away the point of it If anyone can see the erroneous line(s) and point them out, then I'll know what needs fixing. (hint)

Onions.

Jessica · April 8, 2012

So, the most important part is you're not telling us what does happen.

Reading your code, You're not using mysql_query correctly. I'd recommend starting there. http://php.net/mysql_query Look at ALL the examples.

fenway · April 9, 2012

Please, please stop posting all of your code.

Sign In

Failing to select data

Recommended Posts

Onions

Link to comment

Share on other sites

joel24

Link to comment

Share on other sites

Onions

Link to comment

Share on other sites

Onions

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

Onions

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information