真的在CONCAT_WS上苦苦挣扎...请帮助新手:) [英] Really struggling with CONCAT_WS ... please help a newbie :)
问题描述
这里的PHP和MYSQL新手.....现在真的可以使用您的帮助.我在脚本中实现了一个子模式，最后将其提交给数据库中的用户注释".不幸的是，对于每个新的提交，提交的文本都会覆盖以前的提交.有人告诉我使用CONCAT_WS来防止这种情况的发生...我已经在Dreamweaver上尝试了几个小时才能完成该任务，但是它不起作用.这是我在那部分的代码...
PHP and MYSQL newbie here.....can really use your help right now. I implemented a Submodal into my script, and finally got it to submit "User Notes" into the database. Unfortunately with each new submission, the text that is submitted overwrites the previous submission. I was told to use CONCAT_WS to prevent that from happening...I have been trying for several hours on Dreamweaver to accomplish that but it's not working. Here is my code for that section...
出现此错误的新代码:
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 'great'')
WHERE Id=35' at line 1
代码...
<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL);
?>
<?php require_once('Connections/cms.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) :
mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "lead_note_form")) {
$updateSQL = sprintf ("UPDATE Leads SET Notes = CONCAT_WS('\n', notes, '%s') WHERE Id=%d",
GetSQLValueString($_POST['note'], "text"),
GetSQLValueString($_POST['Id'], "int"));
mysql_select_db($database_cms, $cms);
$Result1 = mysql_query($updateSQL, $cms) or die(mysql_error());
$updateGoTo = "test6.php";
if (isset($_SERVER['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
$updateGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $updateGoTo));
}
$colname_Recordset1 = "-1";
(isset($_GET['Id'])) {
$colname_Recordset1 = $_GET['Id'];
}
mysql_select_db($database_cms, $cms);
$query_Recordset1 = sprintf("SELECT Id, First_Name, Last_Name, Notes FROM Leads WHERE Id = %s",
GetSQLValueString($colname_Recordset1, "int"));
$Recordset1 = mysql_query($query_Recordset1, $cms) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script language="JavaScript" src="jquery.js" type="text/javascript"></script>
<script>
function clickSubmit() {
$("form").submit();
}
</script>
</head>
<body>
<table><tr><td>
<div id="lead_box">
<form action="<?php echo $editFormAction; ?>" method="POST" name="lead_note_form" id="lead_note_form"
target="_top">
<input type="hidden" name="Id" id="Id" value="<?php echo $row_Recordset1['Id']; ?>" />
<table width="100%" border="0" cellpadding="0" cellspacing="5">
<tr>
<td colspan="2" bgcolor="#eeeeee"><p align="center" class="name"></p></td>
</tr>
<tr>
<td><textarea rows="15" style="width: 99%" readonly><?php echo $row_Recordset1['Notes']; ?>
</textarea></td>
</tr>
<tr>
<td valign="top"><p class="title">Add Note:</p></td>
<tr>
<td>
<textarea name="note" id="note" style="width: 99%" rows="5"></textarea>
</td>
</tr>
</table>
<input type="hidden" name="MM_update" value="lead_note_form" />
</form>
</div><!-- and of lead box -->
</td></tr>
<tr><td>
<div align="right">
<input type="submit" name="save" id="save" value="Save" onClick="clickSubmit()"/>
<input type="button" name="cancel" id="cancel" value="Cancel" onClick="window.parent.hidePopWin()"/>
</div>
</td></tr></table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
推荐答案
我首先看到的是您的sprintf()具有两个替换参数，但只有一个占位符.我在想你的意思是
First thing I can see is your sprintf() has two substitution parameters but only one placeholder. I'm thinking you actually meant
$updateSQL = sprintf ("UPDATE Leads SET Notes = CONCAT_WS('\n', Notes, %s) WHERE Id=%d",
GetSQLValueString($_POST['note'], "text"),
GetSQLValueString($_POST['Id'], "int"));
此外，您的GetSQLValueString()函数会自动将文本"参数括在引号中，因此您需要删除占位符周围的引号.
Also, your GetSQLValueString() function automatically wraps "text" parameters with quotes, so you need to removes the quotes around the placeholders.
我也将ID占位符更改为%d，因为我假设您要输入数字.
I also changed the ID placeholder to %d as I assume you're expecting a number.
您可以从启用错误报告进行开发中受益.该错误(以及可能与未定义的$Notes变量有关的另一个错误)将使调试更加容易.
You may benefit from enabling error reporting for development. This error (and potentially another regarding the undefined $Notes variable) would have made debugging easier.
将其放在脚本顶部(仅用于开发)
Place this at the top of your script (for development only)
ini_set('display_errors', 'On');
error_reporting(E_ALL);
个人远征
删除MySQL库并将代码移至PDO，您将不会回头.
Personal Crusade
Drop the MySQL library and move your code to PDO, you won't look back.
$stmt = $db->prepare('UPDATE Leads SET Notes = CONCAT_WS(:sep, Notes, :note) WHERE Id = :id');
$stmt->execute(array(
'sep' => PHP_EOL,
'note' => $_POST['note'],
'id' => $_POST['Id']
));
更好的方法
请考虑将每个注释条目存储在一个单独的表中，该表具有与父级"Lead"和创建时间戳记的外键关系.这样，您只需按创建顺序检索所有子注释条目，然后只需插入新条目即可.
A Better Approach
Consider storing each note entry in a separate table with a foreign key relationship to the parent "Lead" and creation timestamp. That way, you simply retrieve all the child note entries in creation order and new entries are simply inserted.
CREATE TABLE LeadNotes (
id INT NOT NULL AUTO_INCREMENT,
lead_id INT,
note TEXT,
created_dt TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (lead_id) REFERENCES Leads (Id)
ON DELETE CASCADE
) ENGINE=INNODB;
这篇关于真的在CONCAT_WS上苦苦挣扎...请帮助新手:)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
