Page 1 of 1

Variable Use Best Practices

Posted: Wed Oct 26, 2011 11:14 pm
by Lee Firth
I am a self taught php developer which probably means I don't write code according to "best practices". Recently I have been working with another PHP developer who if he wanted to write two sql queries would write the first one like so:

Code: Select all

$query1 = "SELECT COUNT(fldActComp)";
$query1 .= "FROM tblcontracts WHERE fldBEAnum = '{$this->bea_number}' ";
$query1 .= "AND fldActComp IS NULL ";
He would then execute the sql and write a second query like so:

Code: Select all

$query2 = "SELECT COUNT(fldActComp)";
$query2 .= "FROM tblcontracts WHERE fldBEAnum = '{$this->bea_number}' ";
$query2 .= "AND fldActComp IS NOT NULL ";
Then he would execute the second query.

I would write the same code this way:

Code: Select all

$query = "SELECT COUNT(fldActComp)
              FROM tblcontracts WHERE fldBEAnum = '{$this->bea_number}' 
              AND fldActComp IS NOT NULL ";
Then execute the sql and write a second query like so:

Code: Select all

$query = "SELECT COUNT(fldActComp)
               FROM tblcontracts WHERE fldBEAnum = '{$this->bea_number}'
               AND fldActComp IS NULL ";
Then execute the second query.

I have two questions:

1. Is there anything wrong with re assigning the same variable name ($query) in this manner? My logic is, even though PHP has some powerful OOP capabilities it is essentially a procedural language. When I have used the variable, it's finished with so I should be able to re assign it's value later in the code.

2. Is it really necessary to concatenate each line of the query in the manner my colleague uses? Sometimes I use Navicat to write complex SQL code that I then copy and paste between applications, it's a lot easier to copy and paste between applications if it's written the way I do it.

Re: Variable Use Best Practices

Posted: Wed Oct 26, 2011 11:36 pm
by twinedev
Well, to the question about re using the variable, yes you can. However your example is using two separate ones in the first.

I prefer the first method myself for anything but a something simple (usually if there is not a lot going on, and the whole line fits within my screen width) I usually am using more complex queries, so breaking ip up to multuple lines makes it easier to read broken up in the sections similar to what you have:

Code: Select all

$SQL  = 'SELECT pg.`Page`, pg.`Content`, pg.`Heading`, pg.`Title` ';
$SQL .= 'FROM `tblPage` AS pg ';
$SQL .= 'LEFT JOIN `tblPath` AS pt ON pg.PageID=pt.ID AND pt.Section="Pages" ';
$SQL .= 'WHERE pg.`ParentID` = '.(int)$intPageID.' AND `Status`="Linked" ';
$SQL .= 'ORDER BY pg.`ListOrder` ';
You could just do the following, but coming from watching queries as they hit the server, the extra spacing can be a pain (most people probably don't care though):

Code: Select all

$query1 = "SELECT COUNT(fldActComp)
           FROM tblcontracts WHERE fldBEAnum = '{$this->bea_number}'  
           AND fldActComp IS NULL ";
Then you can copy and paste it no problem, however, I find that if something is up and I need to copy it out to manually run, I prefer to actually echo out $query1 so that when I paste it into console to try to execute it, it contains all values.

-Greg

Re: Variable Use Best Practices

Posted: Thu Oct 27, 2011 4:58 am
by awebtech
1. If we don't need first query text somewhere below in the script - why would we use different variables for different queries ?
2. Concatenating query text line by line is rather uncomfortable for me. It requires more typing and a query text is kinda broken.

So, I would do it this way:

Code: Select all

$sql = "
      SELECT
            COUNT(fldActComp)
      FROM
            tblcontracts
      WHERE
            fldBEAnum = '{$this->bea_number}' 
            AND fldActComp IS NULL
";

...
execute_query($sql);
...

$sql = "
      SELECT
            COUNT(fldActComp)
      FROM
            tblcontracts
      WHERE
            fldBEAnum = '{$this->bea_number}' 
            AND fldActComp IS NOT NULL
";
...
execute_query($sql);
...