Using PHP to update a database

Page 1 of 1

8 Replies - 2368 Views - Last Post: 01 September 2011 - 04:35 PM Rate Topic: -----

#1 D_MeNtEd  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 32
  • Joined: 27-August 10

Using PHP to update a database

Posted 01 September 2011 - 08:03 AM

Okay, so I'm trying to use php and mysql to update a database.

It's a simple idea of a form, when the submit button is clicked, the username and id update to a table on a localhost server, database named users, table named pwd.

However, I'm new to this and am stuck.

I know that I need to have a con.php file // connects to the server and database.

Then I need to have a file that updates the table, so when the submit button is clicked, it updates the files with the input information... This is where I'm stuck though.

I have a root user set up on the server, still using the default no password at least until I get this situated.

Any help or suggestions would be awesome! I've been looking all over and just getting confused on what to do for the update file.

Here is the usercreate file - to update the server.

<?php
//usercreate.php

require_once("database.php");


$strOne = $_POST['Username'];
$strTwo = $_POST['pwd'];





$sql = 'INSERT INTO `users`.`pwd` (`Username`, `pwd`) VALUES (\'$strOne\', \'$strTwo\');'; 




?>


and the newuser form 


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
			"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<? php


include_once("usercreate.php");

?>
 <head>
	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title> Welcome New Users </title>
<link type="text/css" rel="stylesheet" href="DIYFYI.css" />
</head>
<body>
<form action="<usercrea.php); ?>" method="post"> 
User Name : <input type ="text" name ="Username" /><br />
Password: <input type ="password" name ="pwd" /><br />

<input type ="submit" value="Submit" /> <br />



</form>



Return to the <a href="index.php"> Homepage </a>

</body>
</html>


thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Using PHP to update a database

#2 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 2936
  • View blog
  • Posts: 7,690
  • Joined: 08-June 10

Re: Using PHP to update a database

Posted 01 September 2011 - 08:31 AM

View PostD_MeNtEd, on 01 September 2011 - 05:03 PM, said:

I know that I need to have a con.php file // connects to the server and database.

there is no need to have such a file, though it usually is feasible to include the connection data (DB login & password) this way.

the thing you have to "include" is database security, keyword: SQL-Injection

there is an (more or less) easy way to do that with PHP
<?php
// start error handling
try
{
    // connect to your database
    // assuming PHP 5
    $options    = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
    $connection = new PDO('mysql:host=localhost;dbname=users', $login, $password, $options);

    // establish secure query
    // the call might look somewhat strange, but it works like a  standard function call
    $query = $connection->prepare('INSERT INTO pwd (Username, pwd) VALUES (?, ?)');

    // pass username
    $query->bindValue(1, $_POST['Username']);
    // pass password hash – never store plain passwords!
    $query->bindValue(2, hash('sha265', $_POST['pwd']));

    // run query
    $query->execute();
}
// process errors (if any)
catch (Exception $e)
{
    // if an error occured, echo it out
    // on your live server, you would mail the message to yourself
    // (another kind of attack prevention)
    echo $e->getMessage();
}

Was This Post Helpful? 2
  • +
  • -

#3 Ember  Icon User is offline

  • D.I.C Head

Reputation: 70
  • View blog
  • Posts: 160
  • Joined: 24-April 10

Re: Using PHP to update a database

Posted 01 September 2011 - 08:38 AM

View PostD_MeNtEd, on 01 September 2011 - 08:03 AM, said:

the username and id update to a table on a localhost server, database named users, table named pwd.


Okay, let's start here first. You seem to have a misconception of how MySQL is structured. A database is a collection of tables, for example you would usually have one database for an entire website, with tables like: Users. In that table, there would be 'columns', for example username and password.

What I think you are trying to do, is have a database, let's call it 'Website', and in that database, a table like the one mentioned above, with a username and a password. To update such a table, in your connect.php file, you would connect to the specific database, so your connect.php file would be structured like:

$con = mysql_connect("localhost","mysql_user","mysql_pwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error()); //If the mysql couldn't be created, die.
  }


$db_selected = mysql_select_db("website", $con); //Select your database we named website

if (!$db_selected)
  {
  die ("Can\'t use website: " . mysql_error()); //If it can't find the database or an error occurs, die.
  }

There are probably better ways to connect (mysqli, or what have you), but we will worry about just the bare-bone basics for now. So now you have a variable called $con (short for connection), that is connected to your database. When you require / include this, you now have direct access to your mysql server.

The next step is cleansing, parsing and inserting your data into your table.

You always need to 'cleanse' your input because you can never trust the end user. The person might be a 70 year old person who will do the right thing, OR it might be a devious hacker trying to bring down your website. If you have basic things like, removing slashes and making sure that scripts can't be inserted, you can avert a decent amount of security problems. You already are using POST variables, so you are on your way.

So, strip_slash and remove html from your input, like so:

$user = strip_tags(stripslashes($_POST['Username']));
$password = strip_tags(stripslashes($_POST['pwd']));


Lastly, we come to the insertion into the database. Insertion follows a certain structure, in which you name the table (we called ours users), and follow the table to a T, so if your table is just username and password, it will look like this:

INSERT INTO users(username, password) VALUES($user, $password)

I will let you translate that into a valid string. Now you have to send that code as a query to the MySQL database, so we have a special command in php, using the mysql connector $con to do that:

mysql_query($querystring);

query string is the string you constructed above (INSERT INTO blah blah blah).

YOU ARE DONE! You have now inserted the safe data into your database.

(Here is a link if you want more detailed information about SQL. It's a place to start, check around the site as well: http://www.w3schools...sql/default.asp )

This post has been edited by Ember: 01 September 2011 - 08:39 AM

Was This Post Helpful? 1
  • +
  • -

#4 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: Using PHP to update a database

Posted 01 September 2011 - 09:38 AM

Hi,

In the event that I a misreading your post and you do in fact just have a table named users that has Username and pwd fields then you would just need to change your query:
$sql = "INSERT INTO `users`(Username`, `pwd`) VALUES ('$strOne', '$strTwo')";


That being said you I suggest, especially since you are just starting, to avoid using the mysql* functions and instead use PDO as was used in Dormilich's example. mysql* functions are deprecated.

There are a number of tutorials here at D.I.C that show how to use PDO:
Introduction to PDO
Database Recycling
Be Prepared for Your Database

Again, I really think you should look into these and start using them now. You will see allot of tutorials and such that use the mysql* functions but they are most likely old and out of date.


Good luck, hope this helps :).
Was This Post Helpful? 1
  • +
  • -

#5 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5723
  • View blog
  • Posts: 22,635
  • Joined: 23-August 08

Re: Using PHP to update a database

Posted 01 September 2011 - 10:45 AM

Is the action here a typo?

<form action="<usercrea.php); ?>" method="post">

Was This Post Helpful? 0
  • +
  • -

#6 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 2936
  • View blog
  • Posts: 7,690
  • Joined: 08-June 10

Re: Using PHP to update a database

Posted 01 September 2011 - 12:12 PM

definitely
Was This Post Helpful? 0
  • +
  • -

#7 D_MeNtEd  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 32
  • Joined: 27-August 10

Re: Using PHP to update a database

Posted 01 September 2011 - 12:35 PM

lol yeah the ); ?> was one part of the issue... looking at too many sites and trying to change too many things.

Okay. so I've got it now to where I have an echo saying the server and database connection are established. It's submitting with no errors, but it's still not updating the database.

It's on the server localhost
database name is users
table that I'm trying to update is pwd

I have the following php file for establishing the connection to the database;


<?php

//database.php
 
$dbcon = @mysql_connect("localhost","root");
if ($dbcon)
echo "<p> connected to server </p>";
else
 {
  die("Could not Connect: " . mysql_error());
  echo " <p>Unable to connect to the database at this time. </p>";
 }
$db_selected= @mysql_select_db("users" , $dbcon);
if ($db_selected)
echo "<p> Connected to db </p>";
else
 {
 die ("Could not find database:" . mysql_error());
 }
 
 exit ();
 
 ?>


Then I have this php file for creating the users;


<?php
//usercreate.php

include_once "database.php";

$user = strip_tags(stripslashes($_POST['Username']));
$password= strip_tags(stripslashes($_POST['pwd']));

$sql=("INSERT INTO users(User, pwd) VALUES ('$user', '$pwd')");

if
($sql)
{
	echo  "<p> Updated </P>";
}
else {
	die('Error: ' . mysql_error());
}

mysql_close($dbcon)
?>


and this is the webpage that uses the forum for a new user to sign up;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
			"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<? php


include_once "database.php";

?>
 <head>
	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title> Welcome New Users </title>
<link type="text/css" rel="stylesheet" href="DIYFYI.css" />
</head>
<body>
<form action="usercreate.php" method="post"> 
User Name : <input type ="text" name ="Username" /><br />
Password: <input type ="password" name ="pwd" /><br />

<input type ="submit" value="Submit" /> <br />



</form>



Return to the <a href="index.php"> Homepage </a>

</body>
</html>


It's still not updating the table on the server though... is the INSERT INTO code correct since it's pointing to the database 'users' and not the table 'pwd'? I tried changing it to pwd but it's not working still.


Thank you all very much for helping! Usually once I get it figured out I'm good to go, and I still have other stuff to do with this site, but until I can manipulate the tables on the database I'm stuck.
Was This Post Helpful? 0
  • +
  • -

#8 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 9154
  • View blog
  • Posts: 33,962
  • Joined: 27-December 08

Re: Using PHP to update a database

Posted 01 September 2011 - 02:40 PM

You're not executing your INSERT query.
mysql_query($sql) or die(mysql_error());

Was This Post Helpful? 0
  • +
  • -

#9 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5723
  • View blog
  • Posts: 22,635
  • Joined: 23-August 08

Re: Using PHP to update a database

Posted 01 September 2011 - 04:35 PM

Also, if you're not going to use PDO, then go with mysql_real_escape_string() over the previous strip_tags/stripslashes solution.
Was This Post Helpful? 2
  • +
  • -
Page 1 of 1