Error updating database

database is not updating using the insert query

[Silent Miracle]

i am using an insert query to update the database with the values get from the form. But there is an error updating the database. I dnt knw where the problem is coming. Please help me... Here is my code:
<?php
$First_Name=$_POST['First_Name'];

$Last_Name=$_POST['Last_Name'];
$City=$_POST['City'];
$Conact_num=$_POST['Conact_num'];
$User_Name=$_POST['User_Name'];
$Password=$_POST['Password'];

echo 'Welcome '.$First_Name.'! you are now registered';
$db=mysql_connect('localhost', 'root', 'root');
if(!$db)
{
echo "Error in connection";
}
$db_found=mysql_select_db('RHMS'); //choosing database
if($db_found)
{
echo "Database found";
}

$ans="INSERT INTO PAT_REG(Name_First,Name_Last,City,Contact_num,Name_User,Pswd) VALUES ('".$First_Name."','".$Last_Name."','".$City."','".$Contact_num."','".$Password."')";
$result=mysql_query($ans,$db);
var_dump($db);
//echo "the db error is " .mysql_error($ans);
mysql_close($db);
?>

[jspeed]

What is the error

[Demaestro]

Looks like you are telling SQL you are passing 6 variables to insert but you are only giving it 5

INSERT INTO PAT_REG(Name_First(1),Name_Last(2),City(3),Contact_num(4),Name_User(5),Pswd(6))

values

('".$First_Name."(1)','".$Last_Name."(2)','".$City."(3)','".$Contact_num."(4)','".$Password."(5)')

Looks like you aren't passing in the value for Name_User

[Matthew1980]

Hi there Silent Miracle,

welcome to the forums!

your code should be like this:-

<?php
$First_Name = $_POST['First_Name'];

$Last_Name=$_POST['Last_Name'];
$City=$_POST['City'];
$Conact_num=$_POST['Conact_num'];
$User_Name=$_POST['User_Name'];
$Password=$_POST['Password'];

echo 'Welcome '.$First_Name.'! you are now registered';
$db=mysql_connect('localhost', 'root', 'root');
if(!$db)
{
echo "Error in connection";
}
$db_found=mysql_select_db('RHMS'); //choosing database
if($db_found)
{
echo "Database found";
}

$ans="INSERT INTO `PAT_REG` (`Name_First`, `Name_Last`, `City`, `Contact_num`, `Name_User`, `Pswd`) VALUES ('".$First_Name."','".$Last_Name."','".$City."','".$Contact_num."', '".$User_Name."','".$Password."')";
$result=mysql_query($ans,$db);
var_dump($db);
//echo "the db error is " .mysql_error($ans);
?>

At this point I will just suggest that your code needs to be made more safe; because at the moment this code his VERY insecure, please don't use this on a live public server, as you will get hacked/ get injections very quickly...

Look into functions like mysql_real_escape_string() and strip_tags() to see what I am referring to, essentially you need to sanitise ANY incoming data that a user can generate that gets used in a mysql query.

Hope this helps,

Happy coding.

Cheers,
MRb

[Silent Miracle]

Thanks a lot for pointing out my error... bt still that didnt solve my problem. Still my database is not updated after that insert query.. :( It is not inserting any value into the database... i am using MySQL Query Browser to view my dtabase

[Matthew1980]

Hi there Silent Miracle,

Ok, amend the code so that you add error catching, then at least you can see where your code is failing:-

<?php
//turn on error reporting first
error_reporting(E_ALL);

//assign vars
$First_Name = $_POST['First_Name'];
$Last_Name=$_POST['Last_Name'];
$City=$_POST['City'];
$Conact_num=$_POST['Conact_num'];
$User_Name=$_POST['User_Name'];
$Password=$_POST['Password'];

//I assume that this needs to go at the END of the script AFTER a successful insertion! ;)
echo 'Welcome '.$First_Name.'! you are now registered';

//connect to DB
$db = mysql_connect('localhost', 'root', 'root') or die("Could not connect:".mysql_error());

//select your DB
mysql_select_db("RHMS", $db) or die(mysql_error());

//generate query - add echo at the start so you can see how it's being populated
echo $ans="INSERT INTO `PAT_REG` (`Name_First`, `Name_Last`, `City`, `Contact_num`, `Name_User`, `Pswd`) VALUES ('".$First_Name."','".$Last_Name."','".$City."','".$Contact_num."', '".$User_Name."','".$Password."')";

$result = mysql_query($ans,$db) or die("Query sending failed: ".mysql_error());

//display suitable message depending on the outcome
if ($result){
echo "New member!";
exit;
}
else{
echo "Contact admin, registration failed";
exit;
}
?>

I would suggest that using the echoed sql string (copy it) and trying it it within mysql query browser, I personally find mysql query browser to be very strict, which is a good thing, so if the query fails in that, there is something subtle going wrong.

Anyway, for now, to get your script working at least this should be ample.

error_reporting(E_ALL) essentially instructs the parser to display ALL error's notices and warnings so that you can improve your coding.

Hope that's now a little clearer.

If you still get issues, you will need to go through this sequentially so that you can execute each part of this line by line - you can achieve this by just moving and exit; instruction from line to line, and then re - running the script.

I wish as there was a step into/step over feature in a php editor somewhere, would save some time :)

Lastly, using a mysql connection handle reference only needs to be referred to once, as php 'inherits' the last known connection from the previous mysql function, so once it's used in the DB select, you can omit it from the query function.

Cheers,
MRb

[Silent Miracle]

OHHHH great :) now its inserting values into the database.. Thakuuuu so much for you help... :) There was an error in my syntax... only problem now i am getting is it is inserting only few values into the database and leaving other columns blank... now i will try to fix that problem ... Thanks again for ur help :)

[Matthew1980]

Hi there Silent Miracle,

This line:-

echo $ans="INSERT INTO `PAT_REG` (`Name_First`, `Name_Last`, `City`, `Contact_num`, `Name_User`, `Pswd`) VALUES ('".$First_Name."','".$Last_Name."','".$City."','".$Contact_num."', '".$User_Name."','".$Password."')";

Is the one that should get displayed in the browser, ensure that all relevant values are populating all the values as you would expect them, ie: where the variables are concatenated into the sql statement.

My personal preference would be to insert this:

print_r($_POST);

here:-

<?php
//turn on error reporting first
error_reporting(E_ALL);

//check incoming data
print_r($_POST);
.
.


But as I stated in my original post, you really need to add suitable error handling. Something like this should suffice:-

if(isset($_POST['submit']) && !empty($_POST['submit'])){
// Sanitise, assign vars, error checking and carry on processing the data
}
else{
//refer user back to the first page as something has gone wrong..
}

Something as simple as this can and will save you heartache in the future.

Hope that makes sense.

Cheers,
MRb

[Silent Miracle]

okkk :) i did that error checking thing.. n now my problem is solved...all values are inserted into the database.. Thanks for your help :)

[Silent Miracle]

ok one last problem.. actually m new to php and mysql .. before that i was working on C# :(.. kindly help me with the SELECT command for login purpose. After the user has entered the user name and password i want to match that data with the registered members and then if it matches direct them to a new page.. here is what i have tried.. i dnt knw what to do further :(
<?php
error_reporting(E_ALL);
$db=mysql_connect('localhost','root','root');
$User_Name=$_POST['User_Name'];
$Pswd=$_POST['Pswd'];
$db_found=mysql_select_db('RHMS');
$ans= "SELECT * FROM PAT_REG WHERE Name_User='".$User_Name."' AND Pswd='".$Pswd."'";
$result=mysql_query($ans) or die ("Error handling query".mysql_error());
?>