Page 1 of 1
concatenate in mySQL
Posted: Thu Mar 30, 2006 10:41 pm
by vietboy505
Code: Select all
mysql_query("UPDATE $table
SET status= \"$frm_status\",
solution= concat(\"Updated: $newModifyDate\n\", \"$frm_solution\n\n\")
WHERE id= \"$frm_id\" ");
I want it to update and look like this:
Code: Select all
Updated: 2006-03-30 15:55:55
a
Updated: 2006-03-30 15:55:49
b nothing goes here.. oooo
Updated: 2006-03-30 15:55:36
bbb
I keep getting this:
Code: Select all
Updated: 2006-03-30 15:58:44
Updated: 2006-03-30 15:58:40 Updated: 2006-03-30 15:58:33 a b nothing goes here.. oooo bbb
Posted: Fri Mar 31, 2006 5:22 am
by Buddha443556
You did nl2br() if your displaying in HTML?
I assume there's a reason your not just ...
Code: Select all
mysql_query("UPDATE $table
SET status= \"$frm_status\",
solution=\"Updated: $newModifyDate\n$frm_solution\n\n\"
WHERE id= \"$frm_id\" ");
If so then here my totally wild guess. Try doubling the backslashes for the newlines.
I found this little note in
12.3.1. String Comparison Functions for LIKE. Maybe they use the same syntax in CONCAT?
Note: Because MySQL uses C escape syntax in strings (for example, ‘\n’ to represent a newline character), you must double any ‘\’ that you use in LIKE strings. For example, to search for ‘\n’, specify it as ‘\\n’. To search for ‘\’, specify it as ‘\\\\’; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.
Posted: Fri Mar 31, 2006 10:07 am
by vietboy505
the above code I have try before and it's the same output with CONCAT
I try to use \\n , it gives the same output as the two above code.
Posted: Fri Mar 31, 2006 10:14 am
by feyd
mayhaps a problem with your variables?
Posted: Fri Mar 31, 2006 10:20 am
by Buddha443556
feyd wrote:mayhaps a problem with your variables?
I was thinking they need to be escaped (for security if nothing else). Were they
mysql_real_escape_string()?
Posted: Fri Mar 31, 2006 11:12 am
by vietboy505
I try the code below, which is almost there.
I think the search & replace doesn't work.
Code: Select all
$previous_SolResult=mysql_query("
SELECT solution FROM $table WHERE id='$frm_id' ");
$previous_Solution=mysql_result($previous_SolResult,0);
$new_Solution = preg_replace('/"$previous_Solution"/', ' ', $frm_solution);
mysql_query("UPDATE $table
SET status= \"$frm_status\",
solution= \"$previous_Solution \n\nUpdated: $newModifyDate \n$new_Solution\n \"
WHERE id= \"$frm_id\" ");
This will give me:
Code: Select all
Updated: 2006-03-31 10:08:38
aa
Updated: 2006-03-31 10:08:48
Updated: 2006-03-31 10:08:38 aa bb
Updated: 2006-03-31 10:08:58
Updated: 2006-03-31 10:08:38 aa Updated: 2006-03-31 10:08:48 Updated: 2006-03-31 10:08:38 aa bb cdef
Updated: 2006-03-31 10:09:11
Updated: 2006-03-31 10:08:38 aa Updated: 2006-03-31 10:08:48 Updated: 2006-03-31 10:08:38 aa bb Updated: 2006-03-31 10:08:58 Updated: 2006-03-31 10:08:38 aa Updated: 2006-03-31 10:08:48 Updated: 2006-03-31 10:08:38 aa bb cdef xyz
But what I want is:
Code: Select all
Updated: 2006-03-31 10:08:38
aa
Updated: 2006-03-31 10:08:48
bb
Updated: 2006-03-31 10:08:58
cdef
Updated: 2006-03-31 10:09:11
xyz
Any help?
Posted: Fri Mar 31, 2006 11:39 am
by feyd
how are each of the variables you're using being created?