SQL CONCAT Function Issue

FranktheTank

Hello,

I am kinda new to the PHP world. I have writen the following SQL which works perfectly by itself in phpadmin, but errors out in PHP code.

INSERT INTO logs SELECT NULL AS log_id,
UNIX_TIMESTAMP(NOW()) AS log_date,'{L_ACTION_INDIVADJ_ADDED}' AS log_type,
CONCAT("$log_action = array('header' => '{L_ACTION_INDIVADJ_ADDED}','{L_ADJUSTMENT}' => '",
((member_earned - member_spent + member_adjustment) * -.1),
"','{L_REASON}' => 'Weekly 10% DKP Tax','{L_MEMBERS}' => '",
member_name,
"','{L_ADDED_BY}' => 'Lynx');") AS log_action,
'192.168.1.1' AS log_ipaddress,
'111' AS log_sid,
'{L_SUCCESS}' AS log_result,
1 AS admin_id 
FROM members 
WHERE (member_earned - member_spent + member_adjustment) >0;

I have broken apart the code, and have narrowed it down to the CONCAT line in the select:

CONCAT("$log_action = array('header' => '{L_ACTION_INDIVADJ_ADDED}','{L_ADJUSTMENT}' => '",
((member_earned - member_spent + member_adjustment) * -.1),
"','{L_REASON}' => 'Weekly 10% DKP Tax','{L_MEMBERS}' => '",
member_name,
"','{L_ADDED_BY}' => 'Lynx');") AS log_action,

How do I perform this function in PHP SQL code?

Thanks!

HalfaBee

You have php functions in the middle of the SQL, this will not work.

FranktheTank

HalfaBee;10891494 wrote:
You have php functions in the middle of the SQL, this will not work.

Could you specify what I have wrong in there?

I need to concatinate the following lines in the SQL code:

LINE 1: $log_action = array('header' => '{L_ACTION_INDIVADJ_ADDED}','{L_ADJUSTMENT}' => '
LINE 2: ((member_earned - member_spent + member_adjustment) * -.1)
LINE 3: ','{L_REASON}' => 'Weekly 10% DKP Tax','{L_MEMBERS}' => '
LINE 4: member_name,
LINE 5: ','{L_ADDED_BY}' => 'Lynx');

bpat1434

Why do you need to store php code in the database at all? There are alternatives to storing php code directly in the database. You could encode the array using JSON and store that string, or serialize the array/object and store it that way. Of course you could always set up the log database so that log_action is actually split up into individual columns.

Just to clean things up a bit.... here's your SQL query rewritten...

[color=blue]INSERT INTO[/color] logs 
	[color=blue]SELECT NULL AS[/color] log_id,
		[color=blue]UNIX_TIMESTAMP(NOW()) AS[/color] log_date,
		[color=magenta]'{L_ACTION_INDIVADJ_ADDED}'[/color] [color=blue]AS[/color] log_type,
		[color=blue]CONCAT[/color]([color=magenta]"$log_action = array('header' => '{L_ACTION_INDIVADJ_ADDED}','{L_ADJUSTMENT}' => '"[/color],
			((member_earned - member_spent + member_adjustment) * -.1),
			[color=magenta]"','{L_REASON}' => 'Weekly 10% DKP Tax','{L_MEMBERS}' => '"[/color],
			member_name,
			[color=magenta]"','{L_ADDED_BY}' => 'Lynx');"[/color]) [color=blue]AS[/color] log_action,
		[color=magenta]'192.168.1.1'[/color] [color=blue]AS[/color] log_ipaddress,
		[color=magenta]'111'[/color] [color=blue]AS[/color] log_sid,
		[color=magenta]'{L_SUCCESS}'[/color] [color=blue]AS[/color] log_result,
		1 [color=blue]AS[/color] admin_id 
	[color=blue]FROM[/color] members 
	[color=blue]WHERE[/color] (member_earned - member_spent + member_adjustment) >0;

Right now, I don't see anything wrong with it. Have you tried dumping it out from within PHP before you execute it to see what the query looks like? Also, what error is mySQL giving you when you try to run this?

Need to find out what the error code is? Use this type of code:

$query = ""; // The contents of the above string...

$result = mysql_query($query) or die('MySQL Error ('.mysql_errno().'): '.mysql_error());