Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am coding a reoccurring sql select with a varying clause. In the clause i have a PHP variable (supposed to be replaced later within a WHILE.

My code:

$custaccount_id_clause = "(SELECT DISTINCT(custaccount_id) AS custaccount_id FROM accountcomms_def_tbl a WHERE subagent_def_id = " . '\'$interest_id\'' . " AND agentorder > (SELECT agentorder FROM accountcomms_def_tbl WHERE subagent_def_id = '$subagent_def_id' and custaccount_id = a.custaccount_id))";

$subagent_def_id works becuase its already defined.

$interest_id does NOT work because its not defined. I am trying to substitute it with $interest_id later on during a while loop in which I am calling $custaccount_id_clause as part of a while loop:

    $allresidual_sql = mysql_query("SELECT SUM(txn_value) as txn_sum, COUNT(DISTINCT(custaccount_id)) AS accounts FROM account_stmnt_tbl WHERE txn_type IN ('Fixed Residual','Variable Residual','One Time Adjustment') AND custaccount_id IN ". $custaccount_id_clause)or die(mysql_error());

  1. Within the $custaccout_id clause, I have the text '$interest_id' which I want PHP to replace the variable within. It is not replacing the variable.

  2. This is a quick example app I am writing for a demo, please dont lecture me about which API I am using, as I wont be coding anything that goes into production! :)

share|improve this question
Are you trying to re-create the functionality of prepared statements? – Carsten Apr 8 at 8:37
Cartsen, It seems though that forgetting MYSQL, you should be able to put a variable in a string, and then refer to that in another variable: $variable2 = "String " . $variable . " String"; $variable = "Middle"; echo $variable2; – user1955162 Apr 8 at 8:43
1  
If you really want to do it that way, sprintf() is you friend. – Carsten Apr 8 at 8:48
If I understand this correctly I see 2 possibilities: 1) don't create the query string until you know all the variables or 2) use a custom macro string and do a string replacement of your custom macro once you know the correct value. str_replace( '%%%interest_id%%%', $value, $query). I'd opt for the solution 1 as the other is just a dirty hack and your future dev partners will hate you for it. – smassey Apr 8 at 8:52
sprintf IS MY FRIEND! Thanks Cartsen. smassey, This is throw away to illustrate the commission structure of a deal-higherarcy for which the data will put into a power-point! Basically the audience is meant to suggest numbers we plug in, and it spits out a fancy graph. Nothing should exist after a quick demo! Thanks for your time! – user1955162 Apr 8 at 8:58

1 Answer

up vote 0 down vote

A simple way to use the first variable you wanted to use ($interest_id) would be to test it and place a null inside instead of the variable in a direct fashion like this:

WHERE subagent_def_id = '" . ($interest_id ? $interest_id : null) . "' AND agentorder

This should protect you from getting errors about the variable/property not being defined.

The substitution part is what intrigues me... do you mean, at a given point in your structure, replacing $interest_id with, let's say, $my_other_variable? You could accomplish that if you model the whole SQL query as a string and, depending on a given condition, you re-write it.

Something like this could work (it's just a simple test, adapt it for your needs if you see it fit):

$query = ($a ? "SELECT * FROM `products` WHERE `price` > '{$a}';" : "SELECT * FROM `products` WHERE `date_added` < '{$b}' AND price > 20;");

That's if I have understood you correctly. Otherwise, I suspect you want to use the eval() function for parsing your encoded string and making PHP process your variable as you wish.

Hope that helps

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.