updating MySQL database table

Doug · April 15, 2012

For the last few days I have been trying to work out why my code does not update the MySQL database table. Having tried several variation I have the below but cannot see anything wrong. The rest of the program produces the correct results (displaying what is currently on the table) showing it is connecting to the correct table but altering the table is not working. Any help greatly appreciated.

The few lines in question are:


// Update the profile data in the database
    
if (!$error) {
      if (!empty($name)&& !empty($phone) && !empty($address1) && !empty($address2)) {
        // Only set the picture column if there is a new picture
        if (!empty($new_picture)) {

//if (!empty($postcode)){
          $query = "UPDATE antique SET name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " .
            " email = '$email', webadd = '$webadd', picture = '$new_picture', username = '" . $_SESSION['username'] . "'  WHERE name = '" . 		$row['name'] ."'";
        
}}
       
        else {
          
$query = "UPDATE antique SET name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " .
            " email = '$email', webadd = '$webadd', username = '" . $_SESSION['username'] . "'  WHERE name = '" . 		$row['name'] ."'";
        }
        mysqli_query($dbc, $query) or die("<br>Query $query<br>Failed with error: " . mysqli_error($dbc) . '<br>On line: ' . __LINE__);

The whole program is below


<?php
  
error_reporting(E_ALL);
session_start();

  
  ?>

<?php
  
require_once('appvars.php');
  require_once('connectvars1.php');

  



// Connect to the database
  
$dbc = mysqli_connect(DB_Host, DB_User, DB_Password, DB_Name);

  
if (!isset($_GET['user_id'])) {
    
$query = "SELECT * FROM antique WHERE user_id = '" . $_SESSION['user_id'] . "'";
  
  
}
  else 
{
    $query = "SELECT * FROM antique WHERE user_id = '" . $_GET['user_id'] . "'";
  
}
  $data = mysqli_query($dbc, $query);

  
if (mysqli_num_rows($data) == 1) {
    
// The user row was found so display the user data
    $row = mysqli_fetch_array($data);
    
echo '<table>';
    

if (!empty($row['name'])) {
      echo '<tr><td class="label">Name:</td><td>' . $row['name'] . '</td></tr>';
    

   
}



   if (!empty($row['phone'])) {
      echo '<tr><td class="label">Phone:</td><td>'  . $row['phone'] .
        ' </td></tr>';
    
}
    
   if (!empty($row['address1'])) {
      echo '<tr><td class="label">Address1:</td><td>'  . $row['address1'] .
        ' </td></tr>';
    
}
   if (!empty($row['address2'])) {
      echo '<tr><td class="label">Address2:</td><td>'  . $row['address2'] .
        ' </td></tr>';
    
}
   if (!empty($row['postcode'])) {
      echo '<tr><td class="label">Postcode:</td><td>'  . $row['postcode'] .
        ' </td></tr>';
    
}
   if (!empty($row['webadd'])) {
        echo '<tr><td class="label">Web address:</td><td>'  . $row['webadd'] .
        ' </td></tr>';
    
} 
   if (!empty($row['email'])) {
        echo '<tr><td class="label">Email:</td><td>'  . $row['email'] .
        ' </td></tr>';
  
}
  if (!empty($row['username'])) {
      echo '<tr><td class="label">Username:</td><td>'  . $row['username'] .
        ' </td></tr>';
    
}
if (!empty($row['user_id'])) {
      echo '<tr><td class="label">User ID:</td><td>'  . $row['user_id'] .
        ' </td></tr>';
    
}
echo '</table>';
  

//echo '<class = "label">USER ID: ' . $_SESSION['user_id'] . '';

if (!isset($_GET['postcode']) || ($_SESSION['postcode'] == $_GET['postcode'])) {
      echo '<p>Would you like to <a href="index5.php">Go to Homepage</a>?</p>';
    } } 

// End of check for a single row of user results
  
else {
    echo '<p class="error">There was a bit of a problem accessing your profile.</p>';
  }



?>

<hr>






<?php

require_once('appvars.php');
  require_once('connectvars1.php');

  
// Make sure the user is logged in before going any further.
  
if (!isset($_SESSION['user_id'])) {
    echo '<p class="login">Please <a href="login1.php">log in</a> to access this page.</p>';
    
exit();
  
}
  


  // Connect to the database
  
$dbc = mysqli_connect(DB_Host, DB_User, DB_Password, DB_Name);


if (isset($_POST['submit'])) {
    

// Grab the profile data from the POST
    
$name = mysqli_real_escape_string($dbc, trim($_POST['name']));
    
$phone = mysqli_real_escape_string($dbc, trim($_POST['phone']));

$address1 = mysqli_real_escape_string($dbc, trim($_POST['address1']));
    
$address2 = mysqli_real_escape_string($dbc, trim($_POST['address2']));
    
$postcode = mysqli_real_escape_string($dbc, trim($_POST['postcode']));

$webadd = mysqli_real_escape_string($dbc, trim($_POST['webadd']));

$email = mysqli_real_escape_string($dbc, trim($_POST['email']));
    
$old_picture = mysqli_real_escape_string($dbc, trim($_POST['old_picture']));
    
$new_picture = mysqli_real_escape_string($dbc, trim($_FILES['new_picture']['name']));
    
$new_picture_type = $_FILES['new_picture']['type'];
    $new_picture_size = $_FILES['new_picture']['size']; 


$username = mysqli_real_escape_string($dbc, trim($_POST['username']));

$user_id = mysqli_real_escape_string($dbc, trim($_POST['user_id']));

    
if (!empty($_FILES['new_picture']['tmp_name'])) {list($new_picture_width, $new_picture_height) = getimagesize($_FILES['new_picture']['tmp_name']);

}




//list($new_picture_width, $new_picture_height) = getimagesize($_FILES['new_picture']['tmp_name']);
    
$error = false;

    

// Validate and move the uploaded picture file, if necessary
    
if (!empty($new_picture)) {
      if ((($new_picture_type == 'image/gif') || ($new_picture_type == 'image/jpeg') || ($new_picture_type == 'image/pjpeg') ||
        ($new_picture_type == 'image/png')) && ($new_picture_size > 0) && ($new_picture_size <= MM_MAXFILESIZE) &&
        ($new_picture_width <= MM_MAXIMGWIDTH) && ($new_picture_height <= MM_MAXIMGHEIGHT)) {
        if ($_FILES['new_picture']['error'] == 0) {
          

// Move the file to the target upload folder
          
$target = MM_UPLOADPATH . basename($new_picture);
          
if (move_uploaded_file($_FILES['new_picture']['tmp_name'], $target)) {
            

// The new picture file move was successful, now make sure any old picture is deleted
            
if (!empty($old_picture) && ($old_picture != $new_picture)) {
              
            }
          }
          else {
            

// The new picture file move failed, so delete the temporary file and set the error flag
            
@unlink($_FILES['new_picture']['tmp_name']);
            
$error = true;
            echo '<p class="error">Sorry, there was a problem uploading your picture.</p>';

          }
        }
      }
      
else {
        
// The new picture file is not valid, so delete the temporary file and set the error flag
        
@unlink($_FILES['new_picture']['tmp_name']);

        $error = true;
        
echo '<p class="error">Your picture must be a GIF, JPEG, or PNG image file no greater than ' . (MM_MAXFILESIZE / 1024) .
          ' KB and ' . MM_MAXIMGWIDTH . 'x' . MM_MAXIMGHEIGHT . ' pixels in size.</p>';
      }
    }



$error = false;


// Update the profile data in the database
    
if (!$error) {
      if (!empty($name)&& !empty($phone) && !empty($address1) && !empty($address2)) {
        // Only set the picture column if there is a new picture
        if (!empty($new_picture)) {

//if (!empty($postcode)){
          $query = "UPDATE antique SET name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " .
            " email = '$email', webadd = '$webadd', picture = '$new_picture', username = '" . $_SESSION['username'] . "'  WHERE name = '" . 		$row['name'] ."'";
        
}}
       
        else {
          
$query = "UPDATE antique SET name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " .
            " email = '$email', webadd = '$webadd', username = '" . $_SESSION['username'] . "'  WHERE name = '" . 		$row['name'] ."'";
        }
        mysqli_query($dbc, $query) or die("<br>Query $query<br>Failed with error: " . mysqli_error($dbc) . '<br>On line: ' . __LINE__); 

        

// Confirm success with the user
        
echo '<p>Your profile has been successfully updated. Would you like to <a href="viewprofile4.php">view your profile</a>?</p>';


        
mysqli_close($dbc);
        exit();
    
  }
      
else {
        echo '<p class="error">You must enter all of the profile data (the picture is optional).</p>';
   
   
    }
  } 

// End of check for form submission
  else {
    

// Grab the profile data from the database
    
$query="SELECT * FROM antique WHERE user_id= '" . $row['user_id'] . "'";

$data = mysqli_query($dbc, $query);
    
$row = mysqli_fetch_array($data);

    
if ($row != NULL) {
      $name = $row['name'];
      
$phone = $row['phone'];

$address1 = $row['address1'];

$address2 = $row['address2'];

$postcode = $row['postcode'];
      
$email = $row['email'];

$webadd = $row['webadd'];
      
$old_picture = $row['picture'];

$username = $_SESSION['username'];

$user_id = $row['user_id'];
    
}
    else {
      echo '<p class="error">There was a problem accessing your profile.</p>';

    }
  }


  mysqli_close($dbc);

?>

  
<form enctype="multipart/form-data" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

    <input type="hidden" name="MAX_FILE_SIZE" value="<?php echo MM_MAXFILESIZE; ?>" />
    
<fieldset>
      <legend>Personal Information</legend>

      <label for="name">Name:</label>
      <input type="text" id="name" name="name" value="<?php if (!empty($name)) echo $name; ?>" /><br />

      <label for="phone">Phone:</label>
      <input type="text" id="phone" name="phone" value="<?php if (!empty($phone)) echo $phone; ?>" /><br />

<label for="address1">Address1:</label>
      <input type="text" id="address1" name="address1" value="<?php if (!empty($address1)) echo $address1; ?>" /><br />


<label for="address2">Address2:</label>
      <input type="text" id="address2" name="address2" value="<?php if (!empty($address2)) echo $address2; ?>" /><br />

      <label for="postcode">Postcode:</label>
      <input type="text" id="postcode" name="postcode" value="<?php if (!empty($postcode)) echo $postcode; ?>" /><br />

      <label for="email">Email:</label>
      <input type="text" id="email" name="email" value="<?php if (!empty($email)) { echo $email; } else { echo 'No email entered';} ?>" /><br />

      <label for="webadd">Web address:</label>
      <input type="text" id="webadd" name="webadd" value="<?php if (!empty($webadd)) { echo $webadd; } else { echo 'No web address entered';}  ?>" /><br />


<input type="hidden" name="old_picture" value="<?php if (!empty($old_picture)) echo $old_picture; ?>" />
      <label for="new_picture">Picture:</label>
      <input type="file" id="new_picture" name="new_picture" />
      <?php if (!empty($old_picture)) {
        echo '<img class="profile" src="' . MM_UPLOADPATH . $old_picture . '" alt="Profile Picture" style: height=100px;" />';
      } 

    ?> <br />

<label for="username">Username:</label>
      <input type="text" id="username" name="username" value="<?php if (!empty($username))  echo $username;  ?>" /><br />

<label for="user_id">User ID:</label>
      <input type="text" id="user_id" name="user_id" value="<?php echo '' . $row['user_id'] . '' ; ?>" /><br />
   
</fieldset>
    
<input type="submit" value="Save Profile" name="submit" />
  
</form>
<?php echo('<p class="login">You are logged in as ' . $_SESSION['username'] . '. <a href="logout3.php">Log out</a>.</p>');
echo '<class = "label">USER ID: ' . $row['user_id'] . '';

?>


<p><a href="index.php">Return to homepage</a></p>


<?php require_once('footer.php');
?>
</body> 

</html>

NLT · April 15, 2012

error_reporting(-1);

Add that to your page, error_reporting may help us find the problem.

Doug · April 15, 2012

That is the problem...I don't get any error report!

I get the "There was a bit of a problem accessing your profile." that I have in my code right before I get the message "Your profile has been successfully updated. Would you like to view your profile?" which is, obviously, not true.

I perhaps should mention that I arrive on this page from another page that selects the profile. Could the problem lie there?


<?php
  
error_reporting(E_ALL);
session_start();

  
  ?>



<!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" lang="en">
<head>
<title>OneSevenoaks - Claim Antique in Sevenoaks</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<link rel="stylesheet" href="onesevenoaks2.css" type="text/css"
media="screen" />
</head>
<body>



<?php


  
require_once('appvars.php');
  
require_once('connectvars1.php');

  

// Connect to the database 
  
$dbc = mysqli_connect(DB_Host, DB_User, DB_Password, DB_Name); 





// Retrieve the user data from MySQL
  
$query = "SELECT * FROM antique";
  
$data = mysqli_query($dbc, $query);

  


// Loop through the array of user data, formatting it as HTML
  



echo '<h4>Antique shops:</h4>';
  
                
       
echo '<table>';

  while ($row = mysqli_fetch_array($data)) {
    

echo 'Name: ' . $row['name']; ?> <br />
<?php
echo 'Phone: ' . $row['phone']; ?> <br />
<?php
echo 'Address: ' . $row['address1']; ?> <br />
<?php
echo ' ' . $row['address2']; ?> <br />
<?php
echo ' ' . $row['postcode']; ?> <br />
<?php

echo '<a href="editbusprofile6.php?user_id=' . $row["user_id"] .'">Is this business yours? Claim it and add more details </a>';
?> <br />
<?php



    }




echo '</table>';





mysqli_close($dbc);

?>


<hr>
<p><a href="addcontent.php">Add your own buisnes</a><br />
<a href="retail.php">Go back to Shopping page</a><br />
<a href="index5.php">Go to the Homepage</a></p>


</body> 

</html>

NLT · April 15, 2012

Try to echo the queries and variables to see if you are getting what you should be.

Doug · April 15, 2012

I already do.

Yes the correct data appears (first half of top code)

NLT · April 16, 2012

Are you getting the problem with the if or the else?

Doug · April 16, 2012

I'm not sure. The program reports no error. It's just that the database is not changed when the user alters the form.

Obviously the code references the wrong table as it cannot find the table and therefor remains unaltered.

I do not know why.

thank you for your continued help.

NLT · April 16, 2012

Where you've got: $query = "";

Make that line a query, e.g.

$query = mysql_query("UPDATE users SET something='1'");

Aravinthan · April 16, 2012

Where you've got: $query = "";

Make that line a query, e.g.
$query = mysql_query("UPDATE users SET something='1'");

No because he splits the query line and the actual query.

Doing:

$sql = "SOME SQL STATEMENT";
mysql_query($sql);

Is the same as

mysql_query("SOME SQL STATEMEMENT");

Sign In

updating MySQL database table

Recommended Posts

Doug

Link to comment

Share on other sites

NLT

Link to comment

Share on other sites

Doug

Link to comment

Share on other sites

NLT

Link to comment

Share on other sites

Doug

Link to comment

Share on other sites

NLT

Link to comment

Share on other sites

Doug

Link to comment

Share on other sites

NLT

Link to comment

Share on other sites

Aravinthan

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information