Forums

Dynamic query

Permalink 1 user found helpful
I'm trying to dynamically pull a selection to a dropdown menu directly from the tables. I've spent numerous hours googling and sifting through the forums here. If someone could point me in the right direction it would very much be appreciated. This is the code I'm stuck on:

<?
$db = Loader::db();
$list = $db->mysql_query("SELECT answer FROM btFormAnswers WHERE msqID = 25");
   while($row=mysql_fetch_assoc($list)){
?>
<option value="<? echo $row['answer']; ?>" ><? echo $row['answer']; ?></option>
<? } ?>


I've tried many variations including 
"$row->$answer";
unsuccessfully. Thanks in advance for any guidance.
 
olliephillips replied on at Permalink Reply
olliephillips
That's not going to work I don't think Loader::db() will create the connection you need for the rest of your code. Database access is ADODB in concrete5. You could use something like the below.

$db = Loader::db();
$query = "select myField from myTable";
$rs = $db->Execute($query);
while (!$rs->EOF) {
   ## Iterate through fields 
   echo $rs->fields['myField'];
$rs->MoveNext();   
}


Hope that helps
jordanlev replied on at Permalink Reply
jordanlev
There's also another syntax (in addition to the one olliephillips posted) that I prefer to use:
<?php
$db = Loader::db();
$rs = $db->query('SELECT answer FROM btFormAnswers WHERE msqID = 25');
while($row = $rs->fetchRow()) {
?>
    <option value="<?php echo $row['answer']; ?>"><?php echo $row['answer']; ?></option>
<? } ?>

...or better yet, you should get in the habit of using parameterized queries to avoid SQL injection attacks:
<?php
$db = Loader::db();
$rs = $db->query('SELECT answer FROM btFormAnswers WHERE msqID = ?', array(25));
while($row = $rs->fetchRow()) {
?>
    <option value="<?php echo $row['answer']; ?>"><?php echo $row['answer']; ?></option>
<? } ?>
erniek replied on at Permalink Reply
Just got back from a vacay. Thanks olliphillips and jordanlev. These codes work well. Unfortunately being new to C5 I find that the php portion of the scripts work in the php block and the html part of the form works in the html block, but haven't figured out how to make the whole form script with the php call work together in one block. Any advice? Thanks in advance!
Mnkras replied on at Permalink Reply
Mnkras
you can't split it up,

in the php block, paste this:

$db = Loader::db();
$rs = $db->query('SELECT answer FROM btFormAnswers WHERE msqID = ?', array(25));
while($row = $rs->fetchRow()) {
?>
    <option value="<?php echo $row['answer']; ?>"><?php echo $row['answer']; ?></option>
<?php }


i removed the beginning and ending tags, cause the php block adds them,
fastcrash replied on at Permalink Reply
fastcrash
hii.. this habit bother me all this time.

whats different using :
$db->execute($mrSql) and $db->query($mrSql)

what's better to use join or not
SELECT ps.cID, COUNT( * ) Visits
        FROM PageStatistics ps
        JOIN Pages p ON p.cID = ps.cID
        WHERE cParentID =$cParentID 
or
SELECT ps.cID, COUNT( * ) Visits
        FROM PageStatistics ps, Pages p 
        WHERE p.cID = ps.cID AND cParentID =$cParentID


it's better to not use alias ?
exmp : Pages.cID not p.cID

i have often see this too : $row[user] vs $row['user']

thanks all.
jordanlev replied on at Permalink Reply
jordanlev
There is basically no difference between $db->execute and $db->query. Use either one (flip a coin if you can't decide :)

There is no difference between the two queries you showed (one with the join and one without) -- the JOIN is just a nicer syntax which makes the query easier for other people to read (but the database engine winds up doing the same thing in either case).

Aliases are also more of a nicer syntax that makes queries easier for people to read and write -- but there is no difference as far as the database engine is concerned.

You should always use $row['user'] (or $row["user"]), not $row[user]. See this page:
http://php.net/manual/en/language.types.array.php...
(scroll down to the section called "Why is $foo[bar] wrong?")

Hope that helps.

-Jordan