User to Log in and Fill form but cant get data to co-operate in mysql

K3rb3r0s · August 28, 2013, 4:40pm

Hello there, I’m wondering if anyone can help me? I am adding an enrolment and diary logging system to a company website and as i am a novice with PHP this problem is giving me some hassle.

What i want is to provide the learners with a pre-made username and password. Then once they log in it loads up the Enrolment.php and once they fill in the form on that page and click submit it puts their information on the users table on my mysql DB. Instead i get something that looks like this (ID is auto increment and username as passwors are made by me:)

It treats it as a new entry. This was my code that did the above.
[php]<?php
error_reporting(0);
session_start();

if (!isset($_POST[‘submit’]))
{
$male= $_POST [‘m_14’];
$female= $_POST [‘f_15’];
$fname= $_POST [‘fn_21’];
$lname= $_POST [‘ln_22’];
$dob= $_POST [‘dob_25’];
$housename= $_POST [‘hn_23’];
$housestreet= $_POST [‘hs_24’];
$pcode= $_POST [‘pc_31’];
$nino= $_POST [‘ni_26’];
$homephone= $_POST [‘ht_28’];
$mobilephone= $_POST [‘mt_30’];
$email= $_POST [‘em_32’];
$emergencyname= $_POST [‘ecn_33’];
$emergencynum= $_POST [‘ecn_34’];

$connect = mysql_connect("127.0.0.1","root","") or die ("Could not Connect to DB");
mysql_select_db("users") or die ("Could not find Database");

	$query = mysql_query("INSERT INTO user VALUES ('','','','$male','$female','$fname','$lname','$dob','$housename','$housestreet','$pcode','$nino','$homephone','$mobilephone','$email','$emergencyname','$emergencynum')") or
	die ("Thankyou");

}
?>[/php]

Then someone suggested this. Now no data will go in the data base but apparently the WHERE statement should work.

New code (as described above)
[php]<?php
error_reporting(0);
session_start();
if (!isset($_POST[‘submit’]))
{

	$male= $_POST ['m_14'];
$female= $_POST ['f_15'];
$fname= $_POST ['fn_21'];
$lname= $_POST ['ln_22'];
$dob= $_POST ['dob_25'];
$housename= $_POST ['hn_23'];
$housestreet= $_POST ['hs_24'];
$pcode= $_POST ['pc_31'];
$nino= $_POST ['ni_26'];
$homephone= $_POST ['ht_28'];
$mobilephone= $_POST ['mt_30'];
$email= $_POST ['em_32'];
$emergencyname= $_POST ['ecn_33'];
$emergencynum= $_POST ['ecn_34'];

$connect = mysql_connect("127.0.0.1","root","") or die ("Could not Connect to DB");
mysql_select_db("users") or die ("Could not find Database");

	$query = mysql_query("UPDATE `user` SET '$male', '$female', `fn_21`='$fname', `ln_22`='$ln', `dob_25`='$dob', `hn_23`='$housename', `hs_24`='$housestreet', `pc_31`='$pcode', `ni_26`='$nino', `ht_28`='$homephone', `mt_30`='$mobilephone, em_32`='$email', `ecn_33`='$emergencyname', `ecn_34`='$emergencynum' WHERE `ID`='1'");

}
?>[/php]

Can anyone help/suggest or even fix this? I would be Hugely apprechaiteive. I am more then happy to Zip up my site and send it if it helps. Also please find a ScreenShot of my Database here Never mind it wont let me add an image…I can e-mail it if it helps…

tanzeelniazi · August 28, 2013, 7:37pm

Are you providing pre-made username and passwords to all the users? or is this some sort of demo account which lets them decide whether to enroll for something or not? and all the fields necessary?

K3rb3r0s · August 28, 2013, 7:41pm

Each user will get a pre-made Username and Password with the course details logged with them, and then as they log on they need to fill every box in, i have not put all the fields in yet, I’m just trying to get it to work before i put all 132 questions in the form.

Thanks for your reply

tanzeelniazi · August 28, 2013, 8:21pm

Well first thing storing password as plain text is not a good and idea and If you are learning PHP then I would suggest to change your source of information because you are learning the stuff that will soon be depreciated. Anyways here is your code, hope it does the work you want it to do

[php]<?php

/* I think creating a session here is unnecessary, unless you want to use session later, therefore I’ve commented it for the time being */

/* This function will strip out any bad data entered by the user */
function safe_output($conn, $string){

$string = strip_tags($string);
$string = trim($string);
$string = htmlspecialchars($string);
$string = mysqli_real_escape_string($conn, $string);

return $string;

}

if (	$_SERVER['REQUEST_METHOD'] == 'POST' &&
		isset($_POST['submit'])
	){
	
	/* Creating a connection to the DB */
	
	$host = 'localhost';
	$user = 'root';
	$pass = '';
	$db = 'users';

	$conn = mysqli_connect($host, $user, $pass, $db);

	if (mysqli_connect_errno()){

		die (mysqli_connect_error());
	}

	/* You also need to bring in user's username for verification from the database, although not necessary but you can use it to make sure the account exists in the DB. */

	$query = "SELECT * from users 
							WHERE username = '{$username}'
			 ";
	
	$results = mysqli_query($conn, $query);

	if (!$results || mysqli_affected_rows($conn) != 1){

		die ("Sorry Your account doesn't exsist");

	}

	/* Use some validation methods before saving this post data to the database, for now I've left it as is */

		$male= safe_output($conn, $_POST ['m_14']);
		$female= safe_output($conn, $_POST ['f_15']);
		$fname= safe_output($conn, $_POST ['fn_21']);
		$lname= safe_output($conn, $_POST ['ln_22']);
		$dob= safe_output($conn, $_POST ['dob_25']);
		$housename= safe_output($conn, $_POST ['hn_23']);
		$housestreet= safe_output($conn, $_POST ['hs_24']);
		$pcode= safe_output($conn, $_POST ['pc_31']);
		$nino= safe_output($conn, $_POST ['ni_26']);
		$homephone= safe_output($conn, $_POST ['ht_28']);
		$mobilephone= safe_output($conn, $_POST ['mt_30']);
		$email= safe_output($conn, $_POST ['em_32']);
		$emergencyname= safe_output($conn, $_POST ['ecn_33']);
		$emergencynum= safe_output($conn, $_POST ['ecn_34']);

		/*Update this query accroding to your database columns */
		$query = "UPDATE users 

						SET
							first_name = '{$fname}',
							last_name = '{$lname}',
							dob = '{$dob}',
							....
							....
							...
							...
							/* All the columns and thier VALUES */
						
						WHERE
							username = '{username}'

					";

	$results = mysqli_query($conn, $query);

	if ($results && mysqli_affected_rows($conn) == 1){

		echo "Your Information has been updated";

	} else { echo "There was a problem in updating your info"; }

?>[/php]

K3rb3r0s · August 28, 2013, 8:25pm

I will try it now. Thankyou so much! also i will. I am not very good at self teaching so im struggling a bit. But i promised this charity im working for i would have a go at it. Also for password protection would i be better using MD5?

tanzeelniazi · August 28, 2013, 8:29pm

MD5 is also history, use SHA-1 or SHA-2 or BlowFish

K3rb3r0s · August 28, 2013, 8:54pm

Thankyou i will have a look into them…also im getting the error SCREAM: Error suppression ignored for
( ! ) Parse error: syntax error, unexpected end of file in C:\wamp\www\HOME\rarpa.php on line 204

This is the code you have sent me in replace of the old code

[php]

<?php error_reporting(0); /* I think creating a session here is unnecessary, unless you want to use session later, therefore I've commented it for the time being */ /* This function will strip out any bad data entered by the user */ function safe_output($conn, $string){ $string = strip_tags($string); $string = trim($string); $string = htmlspecialchars($string); $string = mysqli_real_escape_string($conn, $string); return $string; } if ( $_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['submit']) ){ /* Creating a connection to the DB */ $host = 'localhost'; $user = 'root'; $pass = ''; $db = 'users'; $conn = mysqli_connect($host, $user, $pass, $db); if (mysqli_connect_errno()){ die (mysqli_connect_error()); } /* You also need to bring in user's username for verification from the database, although not necessary but you can use it to make sure the account exists in the DB. */ $query = "SELECT * from users WHERE username = '{$username}' "; $results = mysqli_query($conn, $query); if (!$results || mysqli_affected_rows($conn) != 1){ die ("Sorry Your account doesn't exsist"); } /* Use some validation methods before saving this post data to the database, for now I've left it as is */ $male= safe_output($conn, $_POST ['m_14']); $female= safe_output($conn, $_POST ['f_15']); $fname= safe_output($conn, $_POST ['fn_21']); $lname= safe_output($conn, $_POST ['ln_22']); $dob= safe_output($conn, $_POST ['dob_25']); $housename= safe_output($conn, $_POST ['hn_23']); $housestreet= safe_output($conn, $_POST ['hs_24']); $pcode= safe_output($conn, $_POST ['pc_31']); $nino= safe_output($conn, $_POST ['ni_26']); $homephone= safe_output($conn, $_POST ['ht_28']); $mobilephone= safe_output($conn, $_POST ['mt_30']); $email= safe_output($conn, $_POST ['em_32']); $emergencyname= safe_output($conn, $_POST ['ecn_33']); $emergencynum= safe_output($conn, $_POST ['ecn_34']); /*Update this query accroding to your database columns */ $query = "UPDATE users SET m_14 = '{$male}', f_15 = '{$female}', fn_21 = '{$fname}', ln_22 = ' {$lname}', dob_25 = ' {$dob} ', hn_23 = ' {$housename} ', hs_24 = ' {$housestreet} ', pc_31 = ' {$pcode} ', ni_26 = ' {$nino} ', ht_28 = ' {$homephone} ', mt_30 = ' {$mobilephone} ', em_32 = ' {$email} ', ecn_33 = ' {$emergencyname} ', ecn_34 = ' {$emergencynum} ', /* All the columns and thier VALUES */ WHERE username = '{username}' "; $results = mysqli_query($conn, $query); if ($results && mysqli_affected_rows($conn) == 1){ echo "Your Information has been updated"; } else { echo "There was a problem in updating your info"; } ?> RARPA body{margin:0;padding:0;} .Artistic-Body-C { font-family:"Verdana", sans-serif; font-size:107.0px; line-height:1.21em; } .Body-C { font-family:"Verdana", sans-serif; font-size:16.0px; line-height:1.13em; }

RARPA

<?php if ($_SESSION['username']) { echo "You are Logged in as: ".$_SESSION['username']; } ?>

Male

Female

First Name

Last Name

Date Of Birth

House Number

Street Name

Postcode

National Insurance Number

House Telephone

Mobile Telephone

E-mail Address

Emergency Contact Name

Emergency Contact Number

[/php]

tanzeelniazi · August 28, 2013, 9:08pm

I have fixed the problem but you will still get an error because you haven’t used the correct column name according to to you database. Fix the both the queries where

username = '{$username}'

and make sure you are getting value from the $_SESSION[‘username’] otherwise the code will not run.

Here is your code.

[php]<?php
error_reporting(0);

session_start();

/* This function will strip out any bad data entered by the user */
function safe_output($conn, $string){

$string = strip_tags($string);
$string = trim($string);
$string = htmlspecialchars($string);
$string = mysqli_real_escape_string($conn, $string);
return $string;

}

if ( $_SERVER[‘REQUEST_METHOD’] == ‘POST’ &&
isset($_POST[‘submit’])
){

	/* Creating a connection to the DB */

	$host = 'localhost';
	$user = 'root';
	$pass = '';
	$db = 'users';
	

$conn = mysqli_connect($host, $user, $pass, $db);

if (mysqli_connect_errno()){
	die (mysqli_connect_error());
}

/* You also need to bring in user’s username for verification from the database, although not necessary but you can use it to make sure the account exists in the DB. */

$username = $_SESSION['username'];

$query = "SELECT * from users
			WHERE username = '{$username}'
	";

$results = mysqli_query($conn, $query);
if (!$results || mysqli_affected_rows($conn) != 1){
	die ("Sorry Your account doesn't exsist");
}

/* Use some validation methods before saving this post data to the database, for now I’ve left it as is */

$male= safe_output($conn, $_POST [‘m_14’]);
$female= safe_output($conn, $_POST [‘f_15’]);
$fname= safe_output($conn, $_POST [‘fn_21’]);
$lname= safe_output($conn, $_POST [‘ln_22’]);
$dob= safe_output($conn, $_POST [‘dob_25’]);
$housename= safe_output($conn, $_POST [‘hn_23’]);
$housestreet= safe_output($conn, $_POST [‘hs_24’]);
$pcode= safe_output($conn, $_POST [‘pc_31’]);
$nino= safe_output($conn, $_POST [‘ni_26’]);
$homephone= safe_output($conn, $_POST [‘ht_28’]);
$mobilephone= safe_output($conn, $_POST [‘mt_30’]);
$email= safe_output($conn, $_POST [‘em_32’]);
$emergencyname= safe_output($conn, $_POST [‘ecn_33’]);
$emergencynum= safe_output($conn, $_POST [‘ecn_34’]);

/*Update this query accroding to your database columns */

$query = "UPDATE users 
				SET
				m_14 = '{$male}',
				f_15 = '{$female}',
				fn_21 = '{$fname}',
				ln_22 = ' {$lname}',
				dob_25 = ' {$dob} ',
				hn_23 = ' {$housename} ',
				hs_24 = ' {$housestreet} ',
				pc_31 = ' {$pcode} ',
				ni_26 = ' {$nino} ',
				ht_28 = ' {$homephone} ',
				mt_30 = ' {$mobilephone} ',
				em_32 = ' {$email} ',
				ecn_33 = ' {$emergencyname} ',
				ecn_34 = ' {$emergencynum} '
			WHERE

			username = '{$username}'
		";

$results = mysqli_query($conn, $query);
if ($results && mysqli_affected_rows($conn) == 1){
echo “Your Information has been updated”;
} else { echo “There was a problem in updating your info”; }

}

?>

RARPA body{margin:0;padding:0;} .Artistic-Body-C { font-family:"Verdana", sans-serif; font-size:107.0px; line-height:1.21em; } .Body-C { font-family:"Verdana", sans-serif; font-size:16.0px; line-height:1.13em; }

RARPA

<?php if ($_SESSION['username']) { echo "You are Logged in as: ". $_SESSION['username'];} ?>

Male

Female

First Name

Last Name

Date Of Birth

House Number

Street Name

Postcode

National Insurance Number

House Telephone

Mobile Telephone

E-mail Address

Emergency Contact Name

Emergency Contact Number

[/php]

K3rb3r0s · August 28, 2013, 9:20pm

Thanks, it runs now without the errors popping up but i do not understand what i need to change. username? in my db the column is names username. Is this right? I tried running it without changing anything because i am unsure what to change and it does not put anything in the Database.

I’m sorry i know it is me getting it wrong but im struggling to understand.

tanzeelniazi · August 28, 2013, 9:33pm

What’s the name of the column that stores your user’s username?

Is it just username or are you using a name like ni_26, ht_28 etc???

K3rb3r0s · August 28, 2013, 9:37pm

I hope this helps. Like i say i apologize. I’m making it harder than it should be.
Please copy link address below in to your browser for a DB view.

http://tinypic.com/r/2eykimg/5

tanzeelniazi · August 28, 2013, 9:44pm

No problem at all and you don’t need to apologize for anything.

ok, I saw the sanpshot, all your code is fine but needs a minor modification

change this line

$query = "SELECT * from users

to

$query = "SELECT * from user

and similarly, change this line

$query = "UPDATE users

to

$query = "UPDATE user

You script will work fine ;D

K3rb3r0s · August 28, 2013, 9:50pm

I have made the updates. Which looking at them makes sense because were allocating which table we want it to look in. But when i checked my db there were no changed made

tanzeelniazi · August 28, 2013, 9:53pm

remove or comment out this part from the code and then give it a try

[php]
$query = "SELECT * from users
WHERE username = ‘{$username}’
";

$results = mysqli_query($conn, $query);
if (!$results || mysqli_affected_rows($conn) != 1){
	die ("Sorry Your account doesn't exsist");
}

[/php]

K3rb3r0s · August 28, 2013, 9:59pm

I have commented it out and still no luck. I am not getting an echo from the last If statment of “your information was updated”

esle “There was a problem updating your information”

I am not receiving either of these upon clicking submit.

tanzeelniazi · August 28, 2013, 10:04pm

Are you getting both the echos at the same time ??

K3rb3r0s · August 28, 2013, 10:27pm

lm getting none at all.

tanzeelniazi · August 29, 2013, 4:59am

In development environment, it’s a bad idea to turn all errors off. first thing change error reporting to

error_reporting(E_ALL);

Now add the following piece

[code]echo mysqli_affected_rows($conn);

if (!$results){
die(mysqli_error($conn));
} [/code]

before this line:

if ($results && mysqli_affected_rows($conn) == 1){ echo "Your Information has been updated";