Link to home
Start Free TrialLog in
Avatar of Kevin Smith
Kevin SmithFlag for United States of America

asked on

How do I make an html update form (php page) to update a record on a MySQL database?

Okay, I've got a site with the following files:

authorize.php <- creates the session, selects the table and sets the resultArray
connect.php <- sets the database connection

I include those files on every page (where appropriate) and all works fine.  I can get data, see it, blah blah.  Now I need to make a page with a form that shows the data in the form, lets the user edit, and then with the click of the button, updates the database (lots of words to say "update form").  I've created an update.php file, but I don't know what to put in it...I've created my page with the form and I have the action set to the update.php page.

This doesn't work.  What do I need to add?  I've reprinted my authorization code below...what should I modify?

authorization.php code:

<?

session_start();  

if($_POST){
  $_SESSION['SSN']=$_POST["SSN"];
  $_SESSION['pass']=$_POST["pass"];  
}

$result=mysql_query("select * from mbr_main_profile  
  where SSN='" . $_SESSION['SSN'] . "' and pass='" . $_SESSION['pass'] . "'");

$num=mysql_num_rows($result);  
 if(mysql_num_rows($result)==1)
 
{
    $resultArray = mysql_fetch_array($result);
      
}
else
{
    echo "Error retrieving data";
}

if($num < 1){
  echo "You are not authenticated.  Please login.<br><br>
   
  <form method=POST action=index.php>
  username: <input type=text name=\"SSN\">
  password: <input type=password name=\"pass\">
  <input type=submit>
  </form>";
   
  exit;
}
?>
Avatar of nanharbison
nanharbison
Flag of United States of America image

Can we see the form code? What does your UPDATE query look like?
Avatar of Kevin Smith

ASKER

I don't have any code in it now...I'm guessing it should be an update/set statement.
Are you successfully loading the data into the text boxes on the page?
no, i haven't even done that yet...that'd be my starting point i suppose.  I guess I would just default them to the resultArray...?
yes, I got the data into the form fields successfully...:)
when you create the form, textboxes I assume, you fill them like this:
<input name="city" type="text" id="city" value = "'. $resultArray['city'] .'"/>
oops sorry, you first said you didn't do that yet. Then on submit, do an update query.
yes
Are you all set with this then?
not really...how do I properly syntax the update query based on my code above?
$query = "UPDATE mbr_main_profile set city = '".$_POST['city']."', state ='".$_POST['state']."', zipcode = '".$_POST['zipcode']."', phone='".$_POST['phone']."' WHERE SSN='" . $_SESSION['SSN'] . "' and pass='" . $_SESSION['pass'] . "'");
(or whatever your fields in the database are of course)
forgot that you also need to add:
$query_db = mysql_query($query, $db_connection) or die (mysql_error());

after the $query statement, if that is how you are doing your queries
I usually set up my db connection in a separate file:
$db_connection = mysql_connect ($db_info[host], $db_info[username], $db_info[password]) or die (mysql_error());;
yea, I am...I'll clean it up a bit...
Okay, here's my code (below).  It skips to the Error retrieving data message (it also drops my session after I get away from the page).  What am I doing wrong?  (Also, $query_db = mysql_query($query, $db_connection) or die (mysql_error()); returns a Can't Connect to local MySQL error):

<?
// Login & Session example by sde
// auth.php

// start session
session_start();  

// convert username and password from _POST or _SESSION
if($_POST){
  $_SESSION['SSN']=$_POST["SSN"];
  $_SESSION['pass']=$_POST["pass"];  
}

// query for a user/pass match
$result=mysql_query("UPDATE mbr_main_profile set last_name = '".$_POST['last_name']."', first_name) ='".$_POST['first_name']."', middle_inital = '".$_POST['middle_initial']."', street_address='".$_POST['street_address']."' WHERE SSN='" . $_SESSION['SSN'] . "' and pass='" . $_SESSION['pass'] . "'");

// retrieve number of rows resulted
$num=mysql_num_rows($result);  
 if(mysql_num_rows($result)==1)
 
 
{
    $resultArray = mysql_fetch_array($result);
      
}
else
{
    echo "Error retrieving data";
}

?>
session_start();   has to be the very first thing on the page right under <?PHP

I usually create a query as a separate statement:
$query = "UPDATE mbr_main_profile set last_name = '".$_POST['last_name']."', first_name) ='".$_POST['first_name']."', middle_inital = '".$_POST['middle_initial']."', street_address='".$_POST['street_address']."' WHERE SSN='" . $_SESSION['SSN'] . "' and pass='" . $_SESSION['pass'] . "'");

then I say
$result=mysql_query($query);
because if the query is not working, under the $query statement, I can say:
echo $query;
so I can see if the query grabbed everything the way it is supposed to.
You can also echo other things like $num.

BUT! because you are using an update, I don't think you are returnng a result, you have to use SELECT to retrieve a row. DId you look at the database table to see if the row was updated?
it did not update.
try echoing the query to see if it is getting the POST values.
and print out all your post variables to see what is going on there:

foreach ( $_POST as $key => $value ) {
 print $key . " " . "=" . " " . $value;
 print "<BR/>";
}
Okay, I guess the problem comes down to my update.php.  Here's what's in it:

<?
session_start();  

$query=mysql_query("UPDATE mbr_main_profile set last_name = '".$_POST['last_name']."', first_name) ='".$_POST['first_name']."', middle_inital = '".$_POST['middle_initial']."', street_address='".$_POST['street_address']."' WHERE SSN='" . $_SESSION['SSN'] . "' and pass='" . $_SESSION['pass'] . "'");

$result=mysql_query($query);

header("location:../primary_info.php");

?>

My editprofilepage shows the data in the forms, and the action is set to the above update.php.  I click the button and it goes back to primary info page, but nothing changes...?
use this for your result statement so you get the error message returned;

$result = mysql_query($query) or die('Query failed: ' . mysql_error());
Query failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
so the problem is that you are not properly connected to your database. Have you included a file on all pages that need to connect to the db? I assume you have this set up properly because you have other pages that are connecting, right?
yes...by the way, I made a couple of changes and got to the following error:

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  ') ='Kevinadf', middle_inital = 'L', street_address='545 Buxx

I'll follow the connection issue, but am I closer or further away with the above error?
researched the error...apparently it's just another error related to not connecting...so, back to connection issue..:)
I always set up my connection string like this:

$db_info[username] = "my_username";
$db_info[password] = "my_password";
$db_info[host] = "localhost";
$db_info[dbname] = "name_of_database";
$db_connection = mysql_connect ($db_info[host], $db_info[username], $db_info[password]) or die (mysql_error());;
mysql_select_db ($db_info[dbname], $db_connection) or die (mysql_error());

and then my working queries look like this:

$query = "SELECT * FROM some_table where id = 'some_id'";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$row=mysql_num_rows($result);

can you try setting it up like this?
Here's my current connection string...you want me to modify it to look like yours and see what happens?  I'm currently connecting fine, so I'm not sure if there's a problem with my conn string, but I'm willing to give anything a shot:

$hostname="xxx";
$mysql_login="xxx";
$mysql_password="xxx";
$database="xxx";

if (!($db = mysql_connect($hostname, $mysql_login , $mysql_password))){
  die("Can't connect to mysql.");    
}else{
  if (!(mysql_select_db("$database",$db)))  {
    die("Can't connect to db.");
  }
}
never mind, that looks fine!
I copy and pasted your query into a word doc so I could make it really large. I think you do have an error in the query -  you have a closing parenthesis after first_name
 first_name) ='".$_POST['first_name']."',
noted, and fixed.  I'm still not getting a connection however :(
Did you refreshed the page? Are you using IE? What is the error now?
Nothing happens...I've got an ELSE going to a page, and it pops back to that page when i run it.  Here's my final update.php (I added the actual connection instead of the includes).

<?php

$host="xxx"; // Host name
$username="xxx"; // Mysql username
$password="xxx"; // Mysql password
$db_name="xxx"; // Database name
$tbl_name="mbr_main_profile"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from signup form
$SSN=$_POST['SSN'];
$pass=$_POST['pass'];

$sql=("UPDATE mbr_main_profile set last_name = '".$_POST['last_name']."', first_name ='".$_POST['first_name']."', middle_inital = '".$_POST['middle_initial']."', street_address='".$_POST['street_address']."' WHERE SSN='" . $_SESSION['SSN'] . "' and pass='" . $_SESSION['pass'] . "'");

$result=mysql_query($sql);


// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){

session_register("SSN");
session_register("pass");
header("location:../primary_info.php");
}
else {
header("location:../wrong_unpw.php");
}
?>
try taking out the extra spaces in the WHERE clause. You have the single quotes grabbing a space in front and after the SSN and PASS, so it might be looking for " ssn " not "ssn", which of course isn't there.

WHERE SSN='".$_SESSION['SSN']."' and pass='".$_SESSION['pass']."'");
that's not causing an issue anywhere else?  Would it be causing it here?

Trying it now...
no change.
I am clearly grasping at straws for lack of other suggestions!
Did you echo the query, the result and the $count to see what is getting through? And are you SURE this username and password actually are in the table? You should also check in the table fields for username and password and make sure there are no leading or trailing spaces in either field. That actually has happened to me.
I would generally agree with the passwords and usernames issue, but the rest of the site is filling in the data just fine.  Only this update query is giving problems.
did you try commenting out:
header("location:../wrong_unpw.php");
so it stays on the same page and you can see what the error on the page is?
same thing.
just says Query failed: Query was empty
so it is not the connection. There is a typo somewhere. I usually get this error when I have copied and pasted queries, like my query is $query2 but in my $result = mysql_query($query..... I forget to put $query2 there and have $query.

Have you made any other changes to the code you posted at 1:46?
I found one error...my result was $query, so I changed it to $sql...no longer getting error, but nothing is changed in the database...here's the revised code (bottom part is commented out):

// username and password sent from signup form
$SSN=$_POST['SSN'];
$pass=$_POST['pass'];
$last_name=$_POST['last_name'];
$first_name=$_POST['first_name'];
$middle_initial=$_POST['middle_initial'];
$street_address=$_POST['street_address'];

$sql=("UPDATE mbr_main_profile SET last_name = '".$_POST['last_name']."', first_name ='".$_POST['first_name']."', middle_initial = '".$_POST['middle_initial']."', street_address='".$_POST['street_address']."' WHERE SSN='".$_SESSION['SSN']. "' and pass='".$_SESSION['pass']. "'");

$result = mysql_query($sql) or die('Query failed: ' . mysql_error());


// Mysql_num_row is counting table row
//$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

//if($count==1){

//session_register("SSN");
//session_register("pass");
header("location:../primary_info.php");
//}
//else {
//header("location:../wrong_unpw.php");
//}
?>
try taking out the parentheses around the query:
$sql="UPDATE mbr_main_profile set last_name = '".$_POST['last_name']."', first_name ='".$_POST['first_name']."', middle_inital = '".$_POST['middle_initial']."', street_address='".$_POST['street_address']."' WHERE SSN='" . $_SESSION['SSN'] . "' and pass='" . $_SESSION['pass'] . "'";
is there still a session running on the page? Your where clause is where the values are session values:

WHERE SSN='".$_SESSION['SSN']."' and pass='".$_SESSION['pass']."'");
how about

WHERE SSN= '$SSN' and pass='$pass' ");
SWEET!

That's getting it...removing the session :)  It's not updating the street address, so can you check the syntax below and see why not?  I can't find it...thanks for all the help!

$sql=("UPDATE mbr_main_profile SET last_name = '".$_POST['last_name']."', first_name ='".$_POST['first_name']."', middle_initial = '".$_POST['middle_initial']."', street_address='".$_POST['street_address']." WHERE SSN='$SSN and pass='$pass'");
you are missing the right single quote on the street address post value

you have: '".$_POST['street_address']."
it should be '".$_POST['street_address']."'
This syntax mistake should have thrown an error. Is error reporting turned off?
i'm missing something somewhere else...adding the single quote throws everything off and returns and error.  I think I'm missing a single quote elsewhere in the query.

Also, updating one record updates the other...ugh.  I'll get to that on another question :)
you are also missing the single quote on the right side of $SSN
found that too.  Here's my final line:

$sql=("UPDATE mbr_main_profile SET last_name = '".$_POST['last_name']."', first_name ='".$_POST['first_name']."', middle_initial = '".$_POST['middle_initial']."', street_address='".$_POST['street_address']."' WHERE SSN='$SSN and pass='$pass'");

The single quote after SSN actually screws it up for some reason.

The street address line doesn't change.
check the spelling of street_address in text box the POST value comes from and the field name in the mbr_main_profile table.
ASKER CERTIFIED SOLUTION
Avatar of nanharbison
nanharbison
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You worked hard on this'n!  Thanks!