Using Variable In Select

[lauralee]

I have set up a database for contact information that I am attempting to display using a variable in the SELECT statement in order to list the Businesses alphabetically with alphabet letter dividers. The business information will display following the letters if I substitute the letters (A,B,C, etc.) in the SELECT statement, but will not display anything if I use "$letter[$i]". The echo statement preceeding the $result line shows up as alphabet dividers as intended using $letter[$i], but nothing is displayed after the divider letter unless I use LEFT(BusinessName, 1)"A", or "B", or "C", etc. Evidently there is a different format or syntax needed for the SELECT statement, although I don't receive an error for the page. The page simply opens with a list of letters, with nothing following them. Suggestions?<?php$i=0;while ($letter = array(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z)){echo '<a name=' . $letter[$i] . '></a><h4>-' . $letter[$i] . '-</h4>';$result= @mysql_query ('SELECT * FROM contacts WHERE busmember="Yes" AND LEFT(BusinessName, 1)="$letter[$i]" [/color]ORDER BY BusinessName'); if (!result) { $error = 'Error fetching Business Name from join'; exit(); } while ($row = @mysql_fetch_array($result)) { echo '<p><div class="memberbox"><h3>' . $row['BusinessName'] . '</h3>' . $row['StreetAddress'] . '<br />'; echo $row['City'] . ' ' . $row['State'] . ' ' . $row ['Zip'] . '<br />'; echo $row['work_phone'] . '<br />'; echo $row['cellphone'] . '<br />'; echo $row['tollfree'] . '<br />';echo $row['fax'] . '<br />'; echo '</div></p>'; } if ($i==25){ exit(); } $i++; } ?>

[jeffman]

Normally I see queries built with the double quotes on the outside and the single quotes on the inside. I don't remember what happens when you try to interpolate variable when quotes are used in the other sequence (the sequence you are using).In any case, in ALL circumstances where you wish to interpolate array elements inside double quotes, they must be wrapped in curly braces:"{$arr['index']}"When you do this, the single quote marks inside the [brackets] are not understood as the closing quotation marks of the whole string.Many developers avoid the whole by assigning the array element's value to a scalar variable somewhere before the query string is built.FWIW, it is easier to debug SQL techniques when you build the query statement outside the query itself, and assign it to a variable. That way, if you ever need to, you can echo the query statement and see if it is forming correctly.

[lauralee]

Again, a simple fix. I've change the code as follows:$sql = "SELECT * FROM contacts WHERE busmember='Yes' AND LEFT(BusinessName, 1)='$letter[$i]' ORDER BY BusinessName";$result= @mysql_query ($sql);with perfect results! Thanks again!

[Synook]

By the way, you should also have quotes around the strings in the array.

[lauralee]

Double quotes or single? Please give me an example of where to place them in my code. Everything is working properly, so don't know where I should add the quotations.

[Synook]

Either - the only difference is whether variables inside the string are interpolated.

array('A','B','C','D' /* etc */);

If you don't do that, PHP will actually first look for a constant named A, and then when it doesn't find one assume you actually meant 'A'. If you change the error reporting level to include E_NOTICEs, you will see these sort of errors.

[lauralee]

Thanks for the tip. I'll make those changes, and change the setting so I'll see those error messages.