好的伙计们,基本上我想问的是:我的网站上有一个个人资料页面。现在,用户输入名字和姓氏。例如,稍后他们想要更新他们的名字。那么,单个查询中的 UPDATE 语法会更新该 id 的整个字段,对吗?
我正在尝试做的是保留姓氏原始值,即使表已更新为名字。我在想这样的事情会奏效。
$database = new Database;
if( isset($_POST['submit']) ) {
$id = isset($_POST['user_id'])?$_POST['user_id']:null;
$fname = isset($_POST['fname'])?$_POST['fname']:null;
$lname = isset($_POST['lname'])?$_POST['lname']:null;
$profession = isset($_POST['profession'])?$_POST['profession']:null;
$phone = isset($_POST['phone'])?$_POST['phone']:null;
$fax = isset($_POST['fax'])?$_POST['fax']:null;
$filtered_email = isset($_POST['email'])?$_POST['email']:null;
$workbio = isset($_POST['workbio'])?$_POST['workbio']:null;
$employers = isset($_POST['employers'])?$_POST['employers']:null;
$years = isset($_POST['years_in_industry'])? $_POST['years_in_industry']:null;
$database->query('UPDATE users
SET firstname = COALESCE(:fname,firstname),
lastname = COALESCE(:lname,lastname),
profession = COALESCE(:profession,profession),
phone = COALESCE(:phone,phone),
fax = COALESCE(:fax,fax),
email = COALESCE(:email,email),
projects = COALESCE(:workbio,projects),
companies = COALESCE(:employers,companies),
exp_years = COALESCE(:years_in_industry,exp_years)
WHERE user_id = :id');
$database->bind(':id', $id);
$database->bind(':fname', $fname);
$database->bind(':lname', $lname);
$database->bind(':profession', $profession);
$database->bind(':phone', $phone);
$database->bind(':fax', $fax);
$database->bind(':email', $filtered_email);
$database->bind(':workbio', $workbio);
$database->bind(':employers', $employers);
$database->bind(':years_in_industry', $years);
$database->execute();
}
这些是我的数据库字段名称:user_id、名字、姓氏、职业、电话、传真、电子邮件、项目、公司、exp_years、acct_date
所以这是我的 html 表单:
<div class="content">
<!-- You only need this form and the form-basic.css -->
<form class="form-basic" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
<div class="form-title-row">
<h1>Profile:</h1>
</div>
<div class="form-row">
<label>
<span>id:</span>
<input type="hidden" name="id">
</label>
</div>
<div class="form-row">
<label>
<span>Profession:</span>
<input type="text" name="profession">
</label>
</div>
<div class="form-row">
<label>
<span>First name</span>
<input type="text" name="fname">
</label>
</div>
<div class="form-row">
<label>
<span>Last name</span>
<input type="text" name="lname">
</label>
</div>
<div class="form-row">
<label>
<span>Email:</span>
<input type="email" name="email">
</label>
</div>
<div class="form-row">
<label>
<span>Phone:</span>
<input type="tel" name="tel">
</label>
</div>
<div class="form-row">
<label>
<span>Fax:</span>
<input type="tel" name="fax">
</label>
</div>
<div class="form-row">
<label>
<span>Companies I have worked with:</span>
<textarea name="employers"></textarea>
</label>
</div>
<div class="form-row">
<label>
<span>Projects I've worked on:</span>
<textarea name="workbio"></textarea>
</label>
</div>
<div class="form-row">
<label>
<span>Years I've been in my industry:</span>
<select name="years_in_industry">
<option>1 year</option>
<option>2+ years</option>
<option>5+ years</option>
<option>10+ years</option>
</select>
</label>
</div>
<div class="form-row">
<label><span>Radio</span></label>
<div class="form-radio-buttons">
<div>
<label>
<input type="radio" name="radio">
<span>Radio option 1</span>
</label>
</div>
<div>
<label>
<input type="radio" name="radio">
<span>Radio option 2</span>
</label>
</div>
<div>
<label>
<input type="radio" name="radio">
<span>Radio option 3</span>
</label>
</div>
</div>
</div>
<div class="form-row">
<button type="submit">Submit Form</button>
</div>
</form>
谢谢。
如果我理解正确,您可以只使用 UPDATE 命令来执行此操作。
if( isset($_POST['submit']) ) {
$firstname = $_POST['fname'];
$lastname = $_POST['lname'];
$db->query('UPDATE users
SET fname = COALESCE(:firstname,fname),
lname = COALESCE(:lastname,lname)
WHERE id = :id');
$db->bind(':fname', $firstname);
$db->bind(':lname', $lastname);
$db->bind(':id', $_POST['id']); //it seems you forget the ID
$db->execute();
}
使用 COALESCE
函数,仅当字段不为 null 时,您才会更改字段的值,如果是,它将使用与数据库相同的值。
UPDATE
将仅更新您在SET
子句上指定的字段,所有其他字段将保持相同的值。
您的代码实际上只缺少 ID 值的绑定。它实际上会按照您想要的方式工作。我建议的代码是对仅运行一个更新命令而不是两个的改进。
编辑
我实际上犯了一个小错误。如果 _POST
变量上没有这样的索引,则其计算结果将变为错误,假设您尝试了$_POST['someVar']
但表单中不存在someVar
,它将返回错误Undefined index
因此,为了解决它,您可以将当前代码编辑为:
if( isset($_POST['submit']) ) {
$id = isset($_POST['user_id'])?$_POST['user_id']:null;
$fname = isset($_POST['fname'])?$_POST['fname']:null;
$lname = isset($_POST['lname'])?$_POST['lname']:null;
$profession = isset($_POST['profession'])?$_POST['profession']:null;
$phone = isset($_POST['phone'])?$_POST['phone']:null;
$fax = isset($_POST['fax'])?$_POST['fax']:null;
$filtered_email = isset($_POST['email'])?$_POST['email']:null;
$workbio = isset($_POST['workbio'])?$_POST['workbio']:null;
$employers = isset($_POST['employers'])?$_POST['employers']:null;
$years = isset($_POST['years_in_industry'])?$_POST['years_in_industry']:null;
这是一个三元运算符,我正在检查该变量是否来自您的表单,如果不是,我将 null 分配给它。三元条件是condition?value if true:value if false;
使用此代码,更新命令应与coalesce
命令一起使用。