Forums

I wish to perform the following in the body of CC:

<?php
        $db =& JFactory::getDBO();
        $db->setQuery("SELECT txtRegno, txtRegName FROM easy_cfrmDogRegister WHERE txtRegno = '$row->dogReg'");
        $dogs = $db->loadObjectList();
        foreach ($dogs as $dog) {
          echo ($dog->txtRegName . " (" . $row->dogReg . ")");
        }
      ?>

I have picked up the $row->dogReg instead of {dogReg} and it will not display anything. I can hard code the WHERE and that part will display but it seems that $row->dogReg does not display.

Outside of the php tags, {dogReg} works fine.

Sorry, just learning this, but the SELECT statement should also return 1x record, not sure how to show this other than above.

I would prefer to use a more complex SQL statement for the opening of the CC document, but I believe that this is not possible???

Help???

Hi j.crilly,

Please try $MyRow instead of $row - I think that's the correct syntax.

$db->loadRow() should return you a single row result.

You may need better quoting for the query:

$query = "
  SELECT `txtRegno`, `txtRegName` 
    FROM `#__cfrmDogRegister` 
    WHERE `txtRegno` = '".$row->dogReg."';
  ";
$db->setQuery($query);

But, this is running in the body so it will create a query for each line. Is this what you want?

Can you give an example of the kind of 'more complex' sql you'd like to use?

Bob

Thanks for the prompt reply Bob. I tried your suggestions and with a little search and play, adjusted it as follows:

<?php
        $db =& JFactory::getDBO();
        $query="SELECT txtRegno, txtRegName 
                FROM #__cfrmDogRegister 
                WHERE txtRegno = '".$MyRow->dogReg."'";
        $db->setQuery($query);
        $dog = $db->loadRow();
        echo ($dog['1'] . " (" . $MyRow->dogReg . ")");
      ?>

First I tidied the code as suggested and certainly makes sense, something I tend to do at the end of programming, not during, but does save time and makes it easier to edit issues.

I needed to remove the ' (quotes) from your suggestion as it would not accept it in MySQL.

Changed the $row to $MyRow as suggested.

Changed to ->loadRow();

Thanks again Bob.

The complex SQL I would prefer to use to load the document is:

SELECT #__cfrmDogOwners.cf_user_id, #__cfrmDogOwnLinks.dogReg, #__cfrmDogOwnLinks.OwnReg, #__cfrmNZKCMember.txtNZKCMemName
FROM #__users
INNER JOIN (#__cfrmNZKCMember
INNER JOIN #__cfrmDogOwnLinks
ON #__cfrmNZKCMember.txtMemberNo = #__cfrmDogOwnLinks.OwnReg)
ON #__users.id = #__cfrmNZKCMember.cf_user_id

Utilising the current users ID to identify only the dogs registered to their possible many NZKC membership numbers assigned against their user ID.

It works via my MS Access db linking the MySQL tables.

This would avoid the need for calling the previous query on each body loop.

Cheers
Jamie

Hi Jamie,

No instant fix for this - but looking at the code I think it's possible that the functionality could be added. The query is built in libraries/connection.php buildQuery() and at present this uses

$query = "SELECT * FROM ".$MyConnection->connectionrow->tablenames;

I could see that whilst keeping this as a default it could be extended to

$query = "SELECT $fields FROM ".$MyConnection->connectionrow->tablenames $joins;

without too much difficulty. (Where $fields & $joins represent extra ChronoConnectivity Configuration input boxes with default values of '*' and ''.

Bob

Hi Bob, Now getting a little confusing, but exciting with learning new stuff.

Could the $join go in the SQL field (Where clause input on the main form) looking at the table, the SQL includes the WHERE anyway so changing this would not matter too much.

The issue would then be the $field being that I wish then to come from different tables.

I am happy to alter the libraries/connection.php file then add an input box to the main form. I might need some help with this part as the syntax is still a little way off from being fully comfortable.

Will look at it tomorrow and let you know.

Cheers
Jamie

P.S. will the following updates include a backup - restore facility as with CF?

Hi Jamie,

Just to let you know that I got this working by (rather crudely) hacking connection.php to accept an array result from the WHERE box - the array includes 'fields', 'from', and the original 'where' and has let me use the followign query in ChronoConnectivity

SELECT o.`datum` , o.`bericht`, o.`z_geslacht`, o.`foto`, o.`cf_id`, o.`cf_user_id`, p.`Region2` AS `plaats`, e.`event_name` AS `eventement`, u.`block` 
  FROM `#__gh_oproep` AS o 
    LEFT JOIN `#__gh_geopc_nl` AS p ON o.`plaats` = p.`RECNO` 
    LEFT JOIN `#__gh_evenementen` AS e ON o.`eventement` = e.`cf_id` 
    LEFT JOIN `#__users` AS u ON o.`cf_user_id` = u.`id` 
  WHERE u.`block` = '0' 

Bob