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?