Checking if Entry is in the Database

[Akenatehm]

Can you please help me with altering this script so that when it is inserting or deleting from the database, it actually checks if the user is there already or even exists and if it doesn't, it returns an error or sends you to a page.

 

Here are the scripts:

 

Insert Script

 

<?php

include "connect.php";

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

{

   $username=$_POST['username'];

   $password=$_POST['password'];
   
   $email=$_POST['email'];

   if(strlen($username)<1)

   {

      print "You did not enter a username.";

   }

   else if(strlen($password)<1)

   {

      print "You did not enter a password.";

   }

   else

   {

    $insert = "INSERT INTO `users` (username,password,email)",
mysql_real_escape_string($username),
mysql_real_escape_string($password),
mysql_real_escape_string($email));

      mysql_query($insert) or die("Could not insert comment" . mysql_error());
  
  echo "User Added. <A href''<a href=\"home.html\">Click here</a> To Go Home.";
   }

  }

?>

 

Delete Script

 

<?php

include "connect.php";

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

{
   $username=$_POST['username'];

   $email=$_POST['email'];

   if(strlen($username)<1)

   {

      print "You did not enter a username.";

   }

   else

   {

      $delete="DELETE FROM `users` WHERE 'username' = ".mysql_real_escape_string($username)." OR 'email' = ".	mysql_real_escape_string($email)."";

      mysql_query($delete) or die("Could not delete user" . mysql_error());
  
  echo "User Deleted. <A href''<a href=\"home.html\">Click here</a> To Go Home.";
   }

  }

?>

<?php
$delete="DELETE FROM `users` WHERE 'username' = ".mysql_real_escape_string($username)." OR 'email' = ".mysql_real_escape_string($email)."";


?>

[shutat]

To check if a user exists, you could try something like

 

...
$res = mysql_query("select ID from users where username='$username' limit 1;");
if(mysql_num_rows($res) == 1) {
   // username already exists
} else {
   // insert form data
   // delete form data
}
...

 

There's probably better methods, but that's what I usually do.

 

HTH

[Akenatehm]

Could you please put in comments or explain how this works. I'm very new to PHP.

[The Little Guy]

// Checks the database for a user with a particular user name
$res = mysql_query("select ID from users where username='$username' limit 1;");
// get a row count of the number of rows found
if(mysql_num_rows($res) == 1) {  // if it is equal to 1 (found 1 row) there is a user with this name
   // username already exists
} else {  // else there is no user with this name
   // insert form data
   // delete form data
}

[Akenatehm]

Thank you very much The Little Guy, you've been really helpful.

[Akenatehm]

How would I have that as well as sql injection prevention.

[The Little Guy]

I just answered another post on this a few moments ago, you can find it here:

http://www.phpfreaks.com/forums/index.php/topic,227398.0.html

 

If you have any question just ask.

[Akenatehm]

Yeah I saw it, but I am unsure of how they fit together without creating syntax errors or having unnecessary functions. Could you please fit them together in this window with comments to explain. Thanks.

[The Little Guy]

You can also find it in the php documentation, just scroll down to the examples:

 

http://us2.php.net/mysql_real_escape_string

 

Good Luck, Have Fun, Ask lots!

[Akenatehm]

Yeah I saw it, but I am unsure of how they fit together without creating syntax errors or having unnecessary functions. Could you please fit them together in this window with comments to explain. Thanks.

 

[The Little Guy]

This is best I can explain it...

 

// Connect to your database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());

// make a query
// use %s or other
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),  // Matches the first %s
            mysql_real_escape_string($password)  // Matches the second %s
            );
// for each %s there is a parameter that goes along with it.

 

Sorry if it isn't clear enough

[Akenatehm]

Ok. I have the database connecting variables in an external php file which i link using the include function. It was well explained. So how do I add it so that it has if and else functions and if the $query variable fails it shows a message and if it succeeds I need a space to put in the delete from/insert into functions.

[The Little Guy]

This may help you out:

 

// Connect to your database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());

// make a query
// use %s or other
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),  // Matches the first %s
            mysql_real_escape_string($password)  // Matches the second %s
            );
// for each %s there is a parameter that goes along with it.
// Acutally do the query
$sql = mysql_query($query);
// Check if there is a user with the provided name and password
if(mysql_num_rows($sql) > 0){ // Yes there was
// Get the data from the results an put them into an array
$row = mysql_fetch_array($sql);
// Log the user in using sessions
// Start the session
session_start();
// Assign session variables
$_SESSION['id'] = $row['id'];
$_SESSION['firstName'] = $row['firstname'];
// Set their current status equal to TRUE (Means their logged in)
$_SESSION['logged'] = TRUE;
// Send the user to the logged in users home page
header("Location: usersPage.php");
// end script execution
exit;
}else{ // No name and password found
echo 'Invalid username and/or password'; // Don't tell them which one is worng
}

[Akenatehm]

Ok sweet. So what does the Login part of the script do. Store a cookie or what? I dont know what the _SESSION thing does, im not very familiar with it.

[The Little Guy]

It is a way to track the user around the site.

 

it stores information, that can be used similar to _POST, the only difference is that you can use this at any time, and you don't need a form.

 

Here is more info:

http://us2.php.net/manual/en/function.session-start.php

[Akenatehm]

Thanks

[Akenatehm]

Hmmmm I read that page and it has made me more confused. Could you please maybe put it in lame terms?

[The Little Guy]

Did you copy and paste the examples into their own files, and run them?

 

page1.php creates the session and places a value for each one.

 

page2.php just echo's out what was saved into that session.

 

This allows for a user to move across multiple pages, with the same session information. Basically it is like using a post, only the values can be used on more than one page, whereas post values only get saved to be used on the next page and that is it.

[Akenatehm]

I am creating an html site so all I wanted was little snippets of PHP code that I could put on each page, if thats possible?