concatenate in mySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
vietboy505
Forum Commoner
Posts: 53
Joined: Wed Feb 22, 2006 9:30 am

concatenate in mySQL

Post 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
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post 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.
vietboy505
Forum Commoner
Posts: 53
Joined: Wed Feb 22, 2006 9:30 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

mayhaps a problem with your variables?
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post 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()?
vietboy505
Forum Commoner
Posts: 53
Joined: Wed Feb 22, 2006 9:30 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

how are each of the variables you're using being created?
Post Reply