I'm trying to edit many columns at one time. I have a lot of fields that I want users to be able to edit. I'm not sure exactly what I'm doing incorrectly. Any help would be greatly appreciated. It states that There was a problem with your mySQL query please contact technical support with the following information:
<?php
$dbserver = "";
$dblogin = "";
$dbpassword = "";
$dbname = "";
$con = mysqli_connect("$dbserver","$dblogin","$dbpassword","$dbname");
if (!$con)
{
die('Could not connect to the mySQL server please contact technical
support with the following information: ' . mysqli_connect_errno());
}
$organization = mysqli_real_escape_string($_POST['organization']);
$firstname = mysqli_real_escape_string($_POST['firstname']);
$lastname = mysqli_real_escape_string($_POST['lastname']);
$rank = mysqli_real_escape_string($_POST['rank']);
$branch= mysqli_real_escape_string($_POST['branch']);
$gender= mysqli_real_escape_string($_POST['gender']);
$emailaddress = mysqli_real_escape_string($_POST['emailaddress']);
$jobtitle = mysqli_real_escape_string($_POST['jobtitle']);
$company = mysqli_real_escape_string($_POST['company']);
$businessphone = mysqli_real_escape_string($_POST['businessphone']);
$homephone = mysqli_real_escape_string($_POST['homephone']);
$mobilephone = mysqli_real_escape_string($_POST['mobilephone']);
$faxnumber = mysqli_real_escape_string($_POST['faxnumber']);
$address = mysqli_real_escape_string($_POST['address']);
$city = mysqli_real_escape_string($_POST['city']);
$state = mysqli_real_escape_string($_POST['state']);
$zippostal = mysqli_real_escape_string($_POST['zippostal']);
$country = mysqli_real_escape_string($_POST['country']);
$notes = mysqli_real_escape_string($_POST['notes']);
$donorid = mysqli_real_escape_string($_POST['donorid']);
// make the query a variable so we can print out if it fails
$query = "UPDATE donors SET organization = '$organization', firstname =
'$firstname', lastname = '$lastname', rank = '$rank', branch = '$branch',
gender = '$gender', emailaddress = '$emailaddress', jobtitle = '$jobtitle',
company = '$company', businessphone = '$businessphone', homephone =
'$homephone', mobilephone = '$mobilephone', faxnumber = '$faxnumber', address =
'$address', city = '$city', state = '$state', zippostal = '$zippostal', country
= '$country', notes = '$notes', donorid = '$donorid' WHERE donorid =
'$donorid'";
$sql = mysqli_query($con,$query) or die('There was a problem with your mySQL
query please contact technical support with the following information: ' .
mysqli_error());
// troubleshooting for development only
if(mysqli_affected_rows($sql) < 1){
die('There was a problem with your mySQL query : ' . $query);}
mysqli_close($con);
header( 'Location: http://localhost/moddonor.php' ) ;
?>
You didnt mention whats the error but,
I think you must wrap the values using single quote ('), for example
set organization = $organization
becomes
set organization = '$organization'
You are connecting using mysql_connect(), but using mysqli_query(). You also need to enclose your values in quotes '/"
$con = mysql_connect("$dbserver","$dblogin","$dbpassword");
...
mysql_select_db("$dbname", $con);
...
mysqli_query($con,"UPDATE donors set organization = '$organization', firstname =
'$firstname', lastname = '$lastname', rank = '$rank', branch = '$branch',
gender = '$gender', emailaddress = '$emailaddress', jobtitle = '$jobtitle', company
='$company', businessphone = '$businessphone', homephone = '$homephone',
mobilephone = '$mobilephone', faxnumber = '$faxnumber', address = '$address', city =
'$city', state = '$state', zippostal = '$zippostal', country = '$country',
note = '$note' WHERE donorid= '$donorid'");
mysqli_close($con);
Change your connection to mysqli_connect() as mysql_ functions are depreciated.
$con = mysqli_connect("$dbserver", "$dblogin", "$dbpassword", "$dbname");
if (!$con)
{
die('Could not connect to the mySQL server please contact
technical support with the following information: ' . mysqli_error());
}
mysqli_query($con,"UPDATE donors set organization = '$organization', firstname =
'$firstname', lastname = '$lastname', rank = '$rank', branch = '$branch',
gender = '$gender', emailaddress = '$emailaddress', jobtitle = '$jobtitle', company
='$company', businessphone = '$businessphone', homephone = '$homephone',
mobilephone = '$mobilephone', faxnumber = '$faxnumber', address = '$address', city =
'$city', state = '$state', zippostal = '$zippostal', country = '$country',
note = '$note' WHERE donorid= '$donorid'");
Also, it would be beneficial to learn how to do prepared statements - http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php
see - http://php.net/manual/en/mysqlinfo.api.choosing.php
or http://www.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated
EDIT
Apparently you are not setting your variables before using them in your query. note: make sure to sanitize any user inputs. see mysqli_real_escape_string()
//Put this after $con = mysqli_connect(), but before mysqli_query()
$organization = mysqli_real_escape_string($_POST['organization']);
$firstname = mysqli_real_escape_string($_POST['firstname']);
$lastname = mysqli_real_escape_string($_POST['lastname']);
....
$donorid = mysqli_real_escape_string($_POST['donorid']);
// need to add the rest of your form inputs
EDIT 2
On your updated script there are some issues - organization = $_POST['$organization'], $firstname = $_POST['$firstname'], mysql_error(), etc. Try using the following code edit.
<?php
$dbserver = "";
$dblogin = "";
$dbpassword = "";
$dbname = "";
$con = mysqli_connect("$dbserver","$dblogin","$dbpassword","$dbname");
if (!$con)
{
die('Could not connect to the mySQL server please contact technical support with
the following information: ' . mysqli_connect_errno());
}
$organization = mysqli_real_escape_string($_POST['organization']);
$firstname = mysqli_real_escape_string($_POST['firstname']);
$lastname = mysqli_real_escape_string($_POST['lastname']);
$rank = mysqli_real_escape_string($_POST['rank']);
$branch= mysqli_real_escape_string($_POST['branch']);
$gender= mysqli_real_escape_string($_POST['gender']);
$emailaddress = mysqli_real_escape_string($_POST['emailaddress']);
$donorid = mysqli_real_escape_string($_POST['donorid']);
// make the query a variable so we can print out if it fails
$query = "UPDATE donors SET organization = '$organization', firstname = '$firstname', lastname = '$lastname', rank = '$rank', branch = '$branch', gender = '$gender', emailaddress = '$emailaddress' WHERE donorid = '$donorid'";
$sql = mysqli_query($con,$query) or die('There was a problem with your mySQL query please contact technical support with the following information: ' . mysqli_error());
// troubleshooting for development only
if(mysqli_affected_rows($sql) < 1){
die('There was a problem with your mySQL query : ' . $query);}
mysqli_close($con);
header( 'Location: http://localhost/moddonor.php' ) ;
Based on the conversation on @Sean answer you need to build your query dynmically, something like this should work (also it should be noted im using php5.3+ specific syntax for anon functions with array_map):
// array of field => bind type
$fields = array(
'firstname' => 's',
'lastname' => 's',
'rank' => 'i',
// other fields EXCEPT donorid
);
// template for the sql
$sqlTemplate = 'UPDATE SET %s WHERE donorid = ?';
// array to hold the fields we will actually use with the query
$params = array();
// lets check the fileds against those allowed
// and stick them in the $params array - note we exclude donorid
// because its required
foreach ($fields as $field => $type) {
if(isset($_POST[$field]) && !empty($_POST[$field])) {
$params[$field] = array(
'value' => $_POST[$field],
'type' => $type
);
}
}
// if we actually have something to update then lets prep the sql
if(!empty($params)) {
$forUpdate = array_map(function ($f) { return $field . ' = ?'; }, array_keys($params));
$sql = sprtintf($sqlTemplates, implode(',', $forUpdate));
// $sql is now the parameterized query like my example below
// compile all the parameter types into a single string like 'ssi'
$types = implode('', array_map(function($v){ return $v['type'];}, $params));
// now we need to push the $stmt and the $types onto $params
array_unshift ($params, $stmt, $types);
// params now looks like:
// Array ( 0 => Msqil_Stmt, 1 => 'ssi', 'firstname' => 'thevalue', 'lastname' => 'value', 'rank' => 1, etc..)
// now call bindparam via call_user_func_array
call_user_func_array('mysql_stmt_bind_param', $params);
// now execute the query:
mysqli_stmt_execute($stmt);
}
Youre doing muiltiple things wrong:
mysql_* and mysqli_* they are not interchangeable. Use mysqli_* because mysql_* is deprecated ans shouldnt be used anymore; All your mysql functions should be the mysqli versions.--
// with mysqli the db name is passed as an argument wen creating the connection
$con = mysqli_connect("$dbserver","$dblogin","$dbpassword", $dbname);
if (!$con) {
die('Could not connect to the mySQL server please contact
technical support with the following information: ' . mysqli_error());
}
$sql = "UPDATE donors set organization = ?, firstname =
?, lastname = ?, rank = ?, branch = ?,
gender = ?, emailaddress = ?, jobtitle = ?, company
=?, businessphone = ?, homephone = ?,
mobilephone =?, faxnumber = ?, address = ?, city =
?, state = ?, zippostal =?, country = ?,
note = ?
WHERE donorid= ?";
$stmt = mysqli_preapre($sql);
mysqli_bind_param($stmt,
'ssisss...i',
$organization,
$firstname,
$lastname,
$rank,
$branch,
$gender,
$emailaddress,
// other feilds... the must be in the same order as named in the query
// then lastly the donorid
$donorid
);
// execute the query
mysqli_stmt_excecute($stmt);
mysqli_close($con);
header( 'Location: http://localhost/moddonor.php' ) ;