Writing to SQL from PHP

blighter

blighter

blighter

Soldato
Joined
31 Mar 2006
Posts
3,272
Location
Gravesend, Kent
Hey all,

I've been reading through various tutorials on this but am now completely confused.

I'm trying to do a form for the OcUK Members Map so people can add their Location.

I just need a form that adds the following to a MySQL table:
Name
Town
Latitude
Longitude
a 'Type' box (just normal text)

I also wanted to make a register bit so people can log on and change theirs if need be.

Any help will be mentioned on the site :)
 
I can't remember the exact code because the last PHP I did was PHP4 in 2002. I'm currently reading up on PHP5, but I can give you a pointer in what you need to do.

Create a HTML form with the fields you want to be filled in. Make sure each form field has a 'name' attribute as this is what gets submitted to the PHP script, for example:

<input type="text" name="location" />

The method for your form should be POST or GET, either should work but use POST. The action should be the PHP script. For example:

<form name="userdata" method="POST" action="process.php">
...
</form>

Don't forget the submit button or your data is going nowhere. Additionally a reset button can be added to clear the data, for example:

<input type="submit" name="submit_form" value="Send" />
<input type="reset" name="reset_form" value="Clear Form" />

As for the PHP (keep in mind this is my PHP4 knowledge) you need to use the POST variables to get the form data, for example:

$name = $_POST['name'];
$town = $_POST['town'];
$location = $_POST['location'];

For the SQL part you need to establish a MySQL database conenction which requires the following information:

  • MySQL Username
  • MySQL Password
  • Database Name
  • Database Server Address
You then use this information to establish a MySQL database connection, for example:

$address = localhost;
$username = "mysqluser";
$password = "mysqlpassword";
$database = "mysql_db";

mysql_connect($address,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

Once you have the database connection you can then issue queries to insert, update, delete and retrieve data from your database table. Here is an example of a query (the tabe has already been setup in the database):

$query = "INSERT INTO ocuk_users VALUES ('$name','$town','$lat,'$long','$type')";
mysql_query($query);

mysql_close();

This is just a brief idea of what you need to do to get this working. I haven't done PHP in a while and I was no expert so there may be better ways to do this but this is what I remember.

Have you considered using Frappr?
 
Wow, thanks for that.
I've created two pages as follows:

page1:
Code: 
<form name="userdata" method="POST" action="page2.php"><br>
Username: <input type="text" name="username" /><br>
Town: <input type="text" name="town" /><br>
Latitude: <input type="text" name="lat" /><br>
Longitude: <input type="text" name="lng" /><br>
Type: <input type="text" name="type" /><br>
Password: <input type="text" name="password" /><br>
<input type="submit" name="submit_form" value="Send" />
<input type="reset" name="reset_form" value="Clear Form" />
</form>

page2:
Code: 
<?php
$name = $_POST['username'];
$town = $_POST['town'];
$lat = $_POST['lat'];
$lng = $_POST['lng'];
$type = $_POST['type'];
$password = $_POST['password'];

$address = localhost;
$username = "*****";
$password = "*****";
$database = "ocuk";

mysql_connect($address,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO markers VALUES ('','$username','$town','$lat,'$lng','$type','$password')";
mysql_query($query);

mysql_close();
?>

but it just displays a blank page and no data when i press submit :(
 
add "or die" to the each mysql bit as that may indicate where the problem is. also it might worth checking php error reporting is turned on. this will help pinpoint errors like this....

$address = localhost;

missing quotes... :p
 
You have used the variable $password twice, for the db and the POST data. I would be a good idea to use this naming convention for the database varables:

$db_address = localhost;
$db_username = "********";
$db_password = "********";
$db_database = "********";

That's probably not the problem though, can you post the structure for your MySQL table? If you are using phpMyAdmin then you can check your queries by printing them out from your script by insering a print statement:

$query = "INSERT INTO markers VALUES ('','$username','$town','$lat,'$lng','$type','$password')";
print($query);
mysql_query($query);

NB: missing quotes from localhost won't raise an error as it's a legal value.
 
Last edited:
OK, have changed the username and password bits to user and pass

test1.html
Code: 
<form name="userdata" method="POST" action="test2.php"><br>
Username: <input type="text" name="user" /><br>
Town: <input type="text" name="town" /><br>
Latitude: <input type="text" name="lat" /><br>
Longitude: <input type="text" name="lng" /><br>
Type: <input type="text" name="type" /><br>
Password: <input type="text" name="pass" /><br>
<input type="submit" name="submit_form" value="Send" />
<input type="reset" name="reset_form" value="Clear Form" />
</form>

test2.php
Code: 
<?php
$name = $_POST['user'];
$town = $_POST['town'];
$lat = $_POST['lat'];
$lng = $_POST['lng'];
$type = $_POST['type'];
$pass = $_POST['pass'];

$address = localhost;
$username = "******";
$password = "******";
$database = "ocuk";

mysql_connect($address,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO maps VALUES ('$id','$name','$town','$lat,'$lng','$type','$pass')";
print($query);
mysql_query($query);

mysql_close();
?>

edit:
INSERT INTO maps VALUES ('','usernamee','townn','latt,'longg','typee','passs')
 
Last edited:
table.JPG


edit:
just changed the "username" and "password" rows to "user" and "pass"

phpmyadmin says:
Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unclosed quote @ 76
STR: '
SQL: INSERT INTO maps VALUES ('','usernamee','townn','latt,'longg','typee','passs')


SQL query:

INSERT INTO maps VALUES ('','usernamee','townn','latt,'longg','typee','passs')

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'longg','typee','passs')' at line 1
Click to expand...
 
Last edited:
blighter said:
WOO! Fixed it.
There was a missing ' after the Latitude :)
Click to expand...

now you've fixed it you want to....

have 2 passwords boxes - one to choose- one to confirm
set the input type to password so it's not plain text on screen
encrypt the password before adding it to the database
validate user input
protect yourself against sql injection
 
marc2003 said:
now you've fixed it you want to....

have 2 passwords boxes - one to choose- one to confirm
set the input type to password so it's not plain text on screen
encrypt the password before adding it to the database
validate user input
protect yourself against sql injection
Click to expand...

OK, just added the password input type, but have no idea how to do the others :(

I also need to find a way of converting (51.123456,0.123456) into two values. One being 51.123456 and the other being 0.123456 :eek::confused::(
 
For the conversion you can use:

$str = "51.123456,0.123456";
$splitstr = preg_split(",", $value);

$splitstr[0] // First value
$splitstr[1] // Second value

You can see the contents of an array easily by using:

print_r($splitstr);

For the password, you can use the md5() function to hash a password. For example:

$pass = $_POST['pass'];
$encpass = md5($pass);
// Store the encrypted version in the database

When you need to validate a user you can hash the supplied password and compare it agains the one in the database. You don't need to deal with the cleartext password at all.

SQL injection I can't remember that much about but it involves stripping any garbage from the text fields. I remember using stripslashes() before but I don't think that is foolproof. I have been reading about the "data firewall" in PHP5 but I don't know enough yet to provide any help with that. All I know is that it filters everything by default and you have to poke holes in it to allow your form data through.
 
Last edited:
RobH said:
For the conversion you can use:

$str = "51.123456,0.123456";
$splitstr = preg_split(",", $value);

$splitstr[0] // First value
$splitstr[1] // Second value

You can see the contents of an array easily by using:

print_r($splitstr);
Click to expand...

Thanks :) Will that also remove the brackets?
 
blighter said:
Thanks :) Will that also remove the brackets?
Click to expand...

(updated my post above ^)

Ah, didn't see the brackets were part of the string, it won't remove them, they will be part of the split strings. I'm not too good with regular expressions, in Java I would use a regular expression to extract data matching a pattern, I'm not familiar with the process in PHP but I think you would need to use one of the PHP preg_ functions.
 
just use str_replace as it's only a simple replace....

Code: 
$splitstr = explode(',', $_POST['lat_long']);
$lat = str_replace('(', '', $splitstr[0]);
$long = str_replace(')', '', $splitstr[1]);

i'm not familiar with preg_split - looks the same as explode. :p
 
marc2003 said:
just use str_replace as it's only a simple replace....

Code: 
$splitstr = explode(',', $_POST['lat_long']);
$lat = str_replace('(', '', $splitstr[0]);
$long = str_replace(')', '', $splitstr[1]);

i'm not familiar with preg_split - looks the same as explode. :p
Click to expand...

That is working lovely! Thanks!

Once all the code is public-safe, I'll finish off the other bits (encrypting passwords etc) then launch it and put you all on the new credits page (http://ocuk.blighter.net/credits.php) :)
 
here's a quick example to show input validation and protection against sql injection - one thing that is not taken into account is duplicate usernames. are you going to allow them? i'm guessing not - set the username field to unique in phpmyadmin

because it's a simple form, i've included everything in one page, the form submits to it's self and the php is only processed if the submit button is clicked. :)

Code: 
<?php
mysql_connect('localhost', 'marc2003', '****');
mysql_select_db('db');
if($_POST['submit']) {
	function clean($value) {
		if(get_magic_quotes_gpc()) $value = stripslashes($value);
		return trim(mysql_real_escape_string($value));
	}
	$username = clean($_POST['username']);
	$town = clean($_POST['town']);
	$splitstr = explode(',', $_POST['lat_long']);
	$lat = str_replace('(', '', $splitstr[0]);
	$long = str_replace(')', '', $splitstr[1]);
	$type = clean($_POST['type']);
	switch(true) {
		//you can add/remove as you like
		case(strlen($username) < 4):
			$error = 'Your username must be at least 4 characters.';
			break;
		case(empty($town)):
			$error = 'You did not enter your town';
			break;
		case(strlen($_POST['pass']) < 4):
			$error = 'Your password must be at least 4 characters.';
			break;
		case($_POST['pass'] != $_POST['confirm']):
			$error = 'Your passwords do not match.';
			break;
		default:
			//everything ok
			$pass = md5($_POST['pass']);
                        //db connect, mysql query
	}
}
?>
<form action="" method="post" name="form">
<p>username - <input type="text" name="username"></p>
<p>town - <input type="text" name="town"></p>
<p>password - <input type="password" name="pass"></p>
<p>confirm password - <input type="password" name="confirm"></p>
<p>latitude / longitude - <input type="text" name"lat_long"></p>
<p>type - <input type="text" name="type"></p>
<p><input type="submit" name="submit"></p>
</form>
<?php
if($error) echo '<p>'.$error.'</p>';
?>
 
Last edited:
Here is the code with with proper XHTML 1.0 Strict formatting. You may not want it set to strict but best to set it strict and switch to transitional if you find you need to later.

Code: 
<?xml version="1.0" encoding="utf-8"?>
<!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" xml:lang="en-GB" lang="en-GB">
<head>
<title>Test Page</title>
</head>
<body>
<?php
mysql_connect('localhost', 'marc2003', '****');
mysql_select_db('db');
if($_POST['submit']) {
	function clean($value) {
		if(get_magic_quotes_gpc()) $value = stripslashes($value);
		return trim(mysql_real_escape_string($value));
	}
	$username = clean($_POST['username']);
	$town = clean($_POST['town']);
	$splitstr = explode(',', $_POST['lat_long']);
	$lat = str_replace('(', '', $splitstr[0]);
	$long = str_replace(')', '', $splitstr[1]);
	$type = clean($_POST['type']);
	switch(true) {
		//you can add/remove as you like
		case(strlen($username) < 4):
			$error = 'Your username must be at least 4 characters.';
			break;
		case(empty($town)):
			$error = 'You did not enter your town';
			break;
		case(strlen($_POST['pass']) < 4):
			$error = 'Your password must be at least 4 characters.';
			break;
		case($_POST['pass'] != $_POST['confirm']):
			$error = 'Your passwords do not match.';
			break;
		default:
			//everything ok
			$pass = md5($_POST['pass']);
                        //db connect, mysql query
	}
}
?>
<form action="" method="post">
<p>username - <input type="text" name="username" /></p>
<p>town - <input type="text" name="town" /></p>
<p>password - <input type="password" name="pass" /></p>
<p>confirm password - <input type="password" name="confirm" /></p>
<p>latitude / longitude - <input type="text" name="lat_long" /></p>
<p>type - <input type="text" name="type" /></p>
<p><input type="submit" name="submit" /></p>
</form>
<?php
if($error) echo '<p>'.$error.'</p>';
?>
</body>
</html>
 
Hi guys, and sorry to hijack

I'm doing something very similar at the moment but with an enquiry form.

I have the php code pulling fields from the HTML form and emailing them to me and inserting the data into the database.

Whilst the form-process.php is not viewable directly through the web (it moans at you), I have had to physically type my username and password in as I do not know any other way

Code: 
$name = $_POST['name'];
$telephone = $_POST['telephone'];
$email = $_POST['email'];

$address = localhost;
$username = "my actual username in text";
$password = "my actual password in text";
$database = "mark1e_bourne";

mysql_connect($address,$username,$password);
@mysql_select_db($database) or die( "Unable to select 

database");

$query = "INSERT INTO contacts VALUES 

('$id','$name','$telephone','$email')";
print($query);
mysql_query($query);

mysql_close();

How do I get around this?
 
You must log in or register to reply here.
Back
Top Bottom