Database records not being updated

PHP
1

Well I tried to ask this on stackoverflow but damn near had my head tore off because I am new trying to learn php/mysql. Man they are not newb friendly. (I have been learning php/mysql for about 8 days total and think I am getting it pretty good.)

Anyhow, I am trying to create a Edit-User.php page but when I edit the fields it is not updating in the database. I have looked everywhere and it seems all my code is correct but I cannot get it to work.

Any help would be greatly appreciated.

PHP

<?php
ob_start();

// contact to database

include_once('../mysql_connect.php'); 
$insert_data = mysql_real_escape_string($input_data);

$id=$_POST['EditID'];
$fname=$_POST['fname'];
$lname=$_POST['lname'];
$email=$_POST['email'];
$altemail=$_POST['altemail'];
$notes=$_POST['notes'];
$company=$_POST['company'];
$address=$_POST['address'];
$home=$_POST['home'];
$cell=$_POST['cell'];
$telephone=$_POST['telephone'];
$usertype=$_POST['usertype'];	
$assigned=$_POST['assigned'];	
$othercat=$_POST['othercat'];	
$interested=$_POST['interested'];
$redirect = "users.php?Msg=Update";



$query = "UPDATE `users` SET `fname`='$fname',`lname`='$lname',`email`='$email',`altemail`='$altemail',`notes`='$notes',`company`='$company',`address`='$address',`home`='$home',`cell`='$cell',`telephone`='$telephone',`usertype`='$usertype',`assigned`='$assigned',`othercat`='$othercat',`interested`='$interested'" .  $UF . "WHERE `id`='$id' LIMIT 1;";

echo $query;

mysql_query($query)  or die(mysql_error() . "<br />" . $query);


// Redirect
header("Location: " . $redirect);


ob_flush();
?>
2

What’s the value of your $UF variable used in the query directly between the last column title and the where clause? If it’s NULL, then you’ll have a syntax error because there isn’t a space there.

Someone will also mention that if you’re learning, you should start with either mysqli or PDO to access the database from PHP - the old-style mysql library (that is, functions starting with mysql_ ) is being deprecated, has security issues and is generally not as good. Aside from anything else, using PDO will help with the input sanitising that you really need on a query like this.

3

I believe the $UF is my images

else{

$UF = "";	
		if (strlen($_FILES['image']['name']) > 1)
		{
		$FName = md5($_FILES['image']['name'] . time()) . "." . end(explode('.', $_FILES['image']['name']));
		$NewFile = "../UImages/" . $FName;
			if (!move_uploaded_file($_FILES['image']['tmp_name'], $NewFile))
			{
			die("Failed to move file " .$_FILES['image']['tmp_name']." to " . $FName);	
			}

I will look into the mysqli for this. Will I have to redo all my code to implement this?

Just so I am clear and understand.

I should use THIS

$db = new mysqli('localhost', 'user', 'pass', 'demo');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

instead of mysql_connect?

4

Redoing the code isn’t bad, actually it’s a nice learning opportunity :wink:

When you switch from mysql_ to mysqli_ you’ll have to change all mysql_ functions.

5

Ok, I changed the mysql to mysqli but I still cannot get it to update the data.

Here is what I am using now.

<?php
ob_start();

// contact to database

$db = new mysqli('localhost', 'user', 'pass', 'demo');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}


$id=$_POST['EditID'];
$fname=$_POST['fname'];
$lname=$_POST['lname'];
$email=$_POST['email'];
$altemail=$_POST['altemail'];
$notes=$_POST['notes'];
$company=$_POST['company'];
$address=$_POST['address'];
$home=$_POST['home'];
$cell=$_POST['cell'];
$telephone=$_POST['telephone'];
$usertype=$_POST['usertype'];	
$assigned=$_POST['assigned'];	
$othercat=$_POST['othercat'];	
$interested=$_POST['interested'];
$redirect = "users.php?Msg=Update";



$updateSQL = "UPDATE `users` SET `fname`='$fname',`lname`='$lname',`email`='$email',`altemail`='$altemail',`notes`='$notes',`company`='$company',`address`='$address',`home`='$home',`cell`='$cell',`telephone`='$telephone',`usertype`='$usertype',`assigned`='$assigned',`othercat`='$othercat',`interested`='$interested' WHERE `id`='$id' LIMIT 1;";

//echo $query;


//echo $query;

//mysql_query($query)  or die(mysql_error() . "<br />" . $query);


// Redirect
header("Location: " . $redirect);


ob_flush();
?>

I got the code from
http://phplens.com/adodb/tutorial.generating.update.and.insert.sql.html

6

I’m noob at PHP/SQL but you have you have the mysql_query commented out. You have the SQL statement in quotes but nothing is being run on the DB.

7

I think this is what needs to be used when dealing with mysqli

$stmt = $mysqli->prepare("UPDATE movies SET filmName = ?, 
   filmDescription = ?, 
   filmImage = ?,  
   filmPrice = ?,  
   filmReview = ?  
   WHERE filmID = ?");
$stmt->bind_param('sssdii',
   $_POST['filmName'],
   $_POST['filmDescription'],
   $_POST['filmImage'],
   $_POST['filmPrice'], 
   $_POST['filmReview'],
   $_POST['filmID']);
$stmt->execute(); 
$stmt->close();

of course with the proper database info.

Am I getting close?

8

I commented that out because I am using mysqli and not mysql_query. Unless I misunderstood you dont want to use mysql anymore

9

Yes :slight_smile:

That’s correct. But of course the database won’t be updated until you replace that part with it’s mysqli equivalent.

10

Can you give a hint to what it is or where I can find it?

I have been reading here but it is a little confusing to me.

11

This where I am at right now, but I think I am overwriting what needs to be done. Its like you keep looking for answers and you keep overwriting the correct one.

$db = new mysqli('localhost', 'admin', 'pass', 'database');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

if($stmt = $mysqli->prepare('UPDATE `users` SET `fname`=?, `lname`=?, `email`=?, `altemail`=?, `notes`=?, `company`=?, `address`=?, `home`=?, `cell`=?, `telephone`=?, `usertype`=?, `assigned`=?, `othercat`=?, `interested`=?, WHERE `id`=?'))


   
$stmt->bind_param('sssdii',
   $_POST['fname'],
   $_POST['lname'],
   $_POST['email'],
   $_POST['altemail'], 
   $_POST['notes'],
   $_POST['company'],
   $_POST['address'],
   $_POST['telephone'],
   $_POST['home'],
   $_POST['cell'],
   $_POST['usertype'],
   $_POST['assigned'],
   $_POST['othercat'],
   $_POST['interested'],
   $_POST['id']);
$stmt->execute(); 
$stmt->close();

When I try that, I am getting the same error

NULL
Fatal error: Call to a member function prepare() on a non-object in /home3/squiggle/public_html/Terry/Admin/EditUser.php on line 16

Which is

if($stmt = $mysqli->prepare('UPDATE `users` SET `fname`=?, `lname`=?, `email`=?, `altemail`=?, `notes`=?, `company`=?, `address`=?, `home`=?, `cell`=?, `telephone`=?, `usertype`=?, `assigned`=?, `othercat`=?, `interested`=?, WHERE `id`=?'))
12

This is what I would do, I would break this even down even more, so that you have a $query variable that way if any modifications need to be done it will be easier to spot and fix. I would also lose the back ticks for you are using prepared statements anyways:

<?php

$db = new mysqli('localhost', 'admin', 'pass', 'database');

if($db->connect_errno > 0){
  die('Unable to connect to database [' . $db->connect_error . ']');
}

$query = 'UPDATE users 
  SET fname=?, 
    lname=?, 
    email=?, 
    altemail=?, 
    notes=?, 
    company=?, 
    address=?, 
    home=?, 
    cell=?, 
    telephone=?, 
    usertype=?, 
    assigned=?, 
    othercat=?, 
    interested=? 
  WHERE 
    id=?';

$stmt = $mysqli->prepare($query);
/* Took a guess on the paramaters */
$stmt->bind_param('sssssssiiissssi',
 $_POST['fname'],
 $_POST['lname'],
 $_POST['email'],
 $_POST['altemail'], 
 $_POST['notes'],
 $_POST['company'],
 $_POST['address'],
 $_POST['telephone'],
 $_POST['home'],
 $_POST['cell'],
 $_POST['usertype'],
 $_POST['assigned'],
 $_POST['othercat'],
 $_POST['interested'],
 $_POST['id']);
$stmt->execute(); 
$stmt->close();
13

You called your object $db and not $mysqli :wink: