Additional Database Query inside ChronoConnectivity Item

Hi,

i'm trying to figure out if your component is a solution for a certain function:
regarding content that has been generated out of database fields, everything is working fine, but i have to place more queries to get the output fitting.

e.g.:
i use the jos_content tables using a dynamic catid.
but the name of the catid is stored in a different table, in this case inside jos_categories.
in my case, the name of the category is a must-have, but we all know that the basic idea behind ChronoConnectivity is a single database query.

is it possible to place another query somewhere inside the header/body/footer fields?
if not, is there any other possibility to get the catid name, if i initialized a ChronoConnectivity query in the jos_content table?
and if so, how do i adress the gathered data of this additional query (meaning how is the $row variable called in this case)?

Thanks for help,
Gekko

Hi Gekko,

you can add some PHP code with SQL queries in the head/body/footer boxes yes! sorry but I don't know understand what exactly you need to do!

Regards
Max

could you give me an example of a query inside the PHP tags?
is Joomla code allowed?

Hi Gekko,

yes, joomla code, <?php joomla code; ?> look over the forums here for many J1.5 code with SQL queries!

Regards
Max

sorry,.. following on from this. I've got a similar problem and I just don't know enough about php and chronoConnectivity to work it out. i've done a bunch of searches which have kinda helped but i haven't found a topic where someone's trying to do the same thing.

i have two tables - one that holds details of job vacancies and another that has agency contact details. i'm trying to get a list of open jobs and use the 'contact' field in the jobs table to extract the 'phone' and 'email' fields from the agencyContact table.

the body code is:

<?php
$contact = JRequest::getString('{contact}', '', 'get'); // i should mention here, i tried without the { } around 'contact'
echo "contact: $contact <br />";
$db = "jos_chronoforms_agencyContact"; 
$query = "SELECT contact, phone, email FROM `$db` WHERE `contact` = $contact"; //i tried this without the backticks too.
echo $query;
$db->setQuery( $query );
$db->$query();
$email = $db->email; 
$phone = $db->phone; 
?>

<table style="text-align: left; width: 100%;" border=1 rules="cols" frame="box" cellpadding="2" cellspacing="3">
 <tr>
  <td style="width: 15%";>{title}</td>
  <td style="width: 15%";>{contact}</td>
  <td style="width: 15%";><?php echo $phone; ?></td>
  <td style="width: 15%";><?php echo $email; ?></td>
 </tr>
</table>

the result is:

contact:
SELECT contact, phone, email FROM `jos_chronoforms_agencyContact` WHERE `contact` =
Fatal error: Call to a member function setQuery() on a non-object in /share/MD0_DATA/Qweb/Joomla/components/com_chronoconnectivity/chronoconnectivity.html.php(176) : eval()'d code on line 7

it's probably something simple but i have no idea what.

i have a book on php programming that i borrowed from a mate and it says to use mysql_db_query() to send the query but i haven't seen a single example of that in this forum.

any help would be greatly appreciated.

thanks
Jason

Hi jason16c,

Joomla packages up the PHP/MySQL code into a friendlier and more secure set of commands. Try this

<?php
$db =& JFactory::getDBO();
$contact = JRequest::getString('contact', '', 'post');
echo "contact: $contact <br />";
$query = "
    SELECT phone, email 
        FROM #__chronoforms_agencyContact 
        WHERE `contact` = ".$db->quote($contact).";"; 
if ($debug) echo $query;
$db->setQuery( $query );
$result =& $db->loadObject();
$email = $result->email;
$phone = $result->phone;
?>

<table style="text-align: left; width: 100%;" border=1 rules="cols" frame="box" cellpadding="2" cellspacing="3">
    <tr>
      <td style="width: 15%";>{title}</td>
      <td style="width: 15%";>{contact}</td>
      <td style="width: 15%";><?php echo $phone; ?></td>
      <td style="width: 15%";><?php echo $email; ?></td>
    </tr>
</table>

Bob

Later: corrected a few gloopers - see later posts

thanks Bob.

I copied your code above but it gave me a bunch of "unexpected T_STRING" errors. It looked like there was one too many double-quotation marks in the query line so I took one out.

it now looks like this:

    $query = "
        SELECT phone, email
            FROM #__chronoforms_agencyContact
            WHERE `contact` = ".$db-quote($contact).";";

but the output I get is:

contact:

Fatal error: Call to undefined function quote() in /share/MD0_DATA/Qweb/Joomla/components/com_chronoconnectivity/chronoconnectivity.html.php(176) : eval()'d code on line 9

Hi jason 16c,

Oops another typo. Sorry. Should be

WHERE `contact` = ".$db->quote($contact).";";

Bob

ah ok. got it. took me a minute to find the difference but got there in the end.

I still get an error though:

Fatal error: Call to a member function quote() on a non-object in /share/MD0_DATA/Qweb/Joomla/components/com_chronoconnectivity/chronoconnectivity.html.php(176) : eval()'d code on line 9

Hi jason16c,

Please remove this line $db = ""; - it's unsetting the database object. I'll go back and correct my earlier post (or try to).

Bob

yeah.. tried that and I was still getting the same error.

Fatal error: Call to a member function quote() on a non-object in /share/MD0_DATA/Qweb/Joomla/components/com_chronoconnectivity/chronoconnectivity.html.php(176) : eval()'d code on line 9

I tried a google search to get an exact definition of the error but, in true google fashion, I got about 500 million hits. One of those, though, didn't use the backticks so I took them out, which seems to have done the trick.

The problem now is that it's not retrieving the contact information from chronocon ($contact is empty.) I thought it might be because it's a 'get' not a 'post' form so I changed that JRequest line from 'post' to 'get' but there was no difference.

Hi jason16c,

Please turn the Form DeBug on in the General Tab and see what value is being returned for $contact. You may need to replace getString with getVar.

Bob

hey Bob,

I can't see a debug option under General (I'm using ChronoConnectivity.) Is there a php thing for it? (i notice you've written an 'if($debug)' in the code.)

Jason

edit:
The other thing I wanted to ask about is - does the 'get' or 'post' parameter in the getString or getVar make much difference? Does it have to match the GET/POST variables later down on the page? (title and contact are the only ones I'm using from the jobs table and they're both listed under 'GET Variables'.)

Hi Jason,

Apologies - I had lost track of which forum this is in - the DeBug option is in ChronoForms only :-(

Bob

Hi Jason,

I have read your description of the the objective, and you are not using ChronoConnectivity the way you could really benefit from it, so, did you read the tutorial from the home page ? if you did then can you start by creating a list of records from the vacancies table then from this point I can show you how to get the extra data from the 2nd table quickly, this way you will do what you need and will get familiar with CC!

regards
Max

hey max,

i read the tutorial ages back (trying to figure this out for myself before asking for help) so i read it again just now but i didn't really understand it.

i'd love to know the best way to achieve what i need. i would actually prefer to work it out myself but i've been at it for days and i'm about five minutes from... well... from testing the aerodynamics of my laptop... from the top of a ten-storey building.

as i mentioned earlier, i have two tables; one with a list of jobs and one with a list of contacts. i would like to be able to pull the email and phone details for a particular contact from a table, matching against a contact's name for a particular job.

if you can give me a better way of doing it then i'd love to know.

Jason

edit:
i re-read your post - I've got it to retrieve a list of open vacancies using a few things i learnt from surfing these forums, one of which was from bob using $i=1-$i to get alternate colouring for each row. the site currently works, it's just that sorting this problem out would make it work better.

Hi Jason,

Glad you got something running now, let me see your body code and if possible a screenshot of your vacancies table structure so I make sure all is going well!

Regards
Max

I still have the problem with the db query in this second form I'm trying to create.

This is the current code.

    <?php
    $db =& JFactory::getDBO();
    $contact = JRequest::getVar('contact', '', 'get');
    echo "contact: $contact <br />";
    $query = "
        SELECT phone, email
            FROM #__chronoforms_agencyContact
            WHERE contact = ".$db->quote($contact).";";
    if ($debug) echo $query;
    $db->setQuery( $query );
    $result =& $db->loadObject();
    $email = $result->email;
    $phone = $result->phone;
    ?>

    <table style="text-align: left; width: 100%;" border=1 rules="cols" frame="box" cellpadding="2" cellspacing="3">
        <tr>
          <td style="width: 15%";>{title}</td>
          <td style="width: 15%";>{contact}</td>
          <td style="width: 15%";><?php echo $phone; ?></td>
          <td style="width: 15%";><?php echo $email; ?></td>
        </tr>
    </table>

-I'm using the jobs table in "Tablename" field.
-The job title and contact field are given in the "GET Variables" (these are in the jobs table.)
-There are no "POST variables" listed.

It's still not getting a value for $contact and so it never gets $phone or $email.

Thanks for this Max. Here's the form code and table structure. It's been years since I did SQL and DB admin so I'm very (very, very) rusty. If you can spot something I can do differently, that'd be great.

I've purposely used a field for each checkbox but only because I wanted to filter on the checkbox results. (Incidentally, I'm having a problem with this bit - clearing the checkbox doesn't empty the value of the field. I figure I've done something wrong but haven't investigated this fully yet so I didn't want to create a new topic.)

Head code:

<?php $i = 0;?>
<div style="text-align: right;"> {new_record} </div>
<br>
<br>
<br>
<table style="text-align: left; width: 100%;" border=1 rules="cols" frame="box" cellpadding="2" cellspacing="3">
  <tbody>
    <tr>
      <td style="width: 20%;">Title</td>
      <td style="width: 7%;">Rate</td>
      <td style="width: 14%;">Contact</td>
      <td colspan=6 style="width: 50%;">Status</td>
      <td style="width: 10%;">Action</td>
    </tr>
  </tbody>
</table>

Body code:

<table style="text-align: left; width: 100%;" border=1 rules="cols" frame="box" cellpadding="2" cellspacing="3">
  <tbody>
    <tr <?php echo "class='row_$i'"; ?>>
      <td style="width: 20%;">{title}</td>
      <td style="width: 7%;">{rate}</td>
      <td style="width: 14%;">{contact}</td>
      <td style="width: 7%;">{CallAgencyLeftVM}</td>
      <td style="width: 7%;">{CallAgencyNoGo}</td>
      <td style="width: 8%;">{CalledAgencySentCV}</td>
      <td style="width: 9%;">{HoldAgencyFwdCV}</td>
      <td style="width: 10%;">{HoldIntFbk}</td>
      <td style="width: 9%;">{HoldWait2ndInt}</td>
      <td style="width: 10%;">{edit_record} {delete_record}</td>
    </tr>
  </tbody>
</table>
<?php $i=1-$i ?>

Table structure:

Hi Jason,

Can I see how do you create the link ? try to replace :

$contact = JRequest::getVar('contact', '', 'get');

with

$contact = JRequest::getVar('contact');

Regards
Max

hey max,

ok, tried that but it's still not populating $contact. I also tried getString (from Bob's original post) but that hasn't worked either.

I'm guessing that the column names aren't case-sensitive. I've matched the cases anyway and it hasn't made a difference.

Is there anything else I can give you? How do I echo the $db to see what it's picking up from getDBO? I tried "echo $db" but it didn't like it ("could not be converted to string" error.)

thanks
Jason

edit: so i used a foreach loop to echo the values in $db. this is the code:

    <?php
    $db =& JFactory::getDBO();

foreach ( $db as $value ) {
 echo "$value <br />";
}
...

the results were:

mysql
0000-00-00 00:00:00
`
SHOW FIELDS FROM jos_chronoforms_jobs
0

jos_
Resource id #11
Resource id #43
0
0
0
0
Array
1
Array

Array

Hi Jason,

A few answers for you.

The $db that JRequest::DBO() returns is a PHP object that stores information about the database 'connection' It doesn't itself contain any database 'data'. The output you've got is what I would expect to see so that looks OK.

The variations on JRequest::getVar() that we are giving you depend on what variable you need to use. gatVar takes four main parameters getVar('field_name', 'default_value', 'source', 'value_type') where the source can be 'post' for form variables, 'get' for url or 'get form' variables, 'cookie' or '' to try them all. The 'value_type parameter does some simple checking to try to eliminate hazardous junk from your input code. And getString(. . .) is equivalent to getVar(. . .'string'). In general for development use the most general form of getVar('fiend_name') but for production tighten this down as far as possible.

In a form checkbox fields (and radio buttons) *always* have values regardless of whether they are checked or not. What the $_POST array returns is a string that either has the field value (for a checked field), is empty (for an unchecked field), or is an array of such values (for an group of checkboxes using a shared array name). Arrays should be converted into comma separated string before being saved in the database. So your database fields should either be empty or contain a string.

I'm not clear what you mean my 'listed under POST or GET variables' I think this may be a major red herring but I'd need to build a test Connection to be clear.

Bob

Hi Bob,

Thanks for the clarification. I have a quick question about the checkboxes though: if you clear/untick a checkbox and save, shouldn't the resulting array/field value also be cleared? The problem I'm having is that if I clear/untick a checkbox, save the form then re-edit that record, the box is ticked again. I thought it would clear the value from the table but, as I understand your post, it shouldn't clear it at all, so how does it record the checkbox state?

My understanding of GET and POST is that the first is used to retrieve values and the second is used to save them. Should I be using POST instead of GET so that it actually saves the updated field data when the record is edited?

Going back to the original problem I've been having with the query, how does getVar interact with the chronocon body code? As you know, the body code is looking for variables that look like this: {varName}. How does getVar know to pull ('contact') from {contact}? Does it need to? or does it just pull it straight from the database?

To test the connectivity, I hard-coded a value for $contact (to be a test one I put in the database) to check that the rest of the code worked. It did. it successfully populates the $phone and $email variables, so the problem seems to be with getVar.

Also, where I've said 'listed under POST and GET variables', I meant the bit at the bottom of the page on the General Tab. The only two field names I've written there are 'title,contact' because they are the only two retrieved from the jobs table.

thanks,
Jason

Hi Jason,

Here's a partial answer to the 'saved checkboxes' question. Yes they should be saved - but I'm not certain that they are saved and restored correctly. I don't know ChronoConnectivity well enough to be sure. You need a code loop to check the saved result against the checkbox values and set the matches to 'checked'.

Your understanding of GET and POST is pretty much like mine - you can set a form method to 'GET' though so it's not quite black and white.

I'm not sure now that getVar is appropriate here, it will only get variables from the Request arrays and that doesn't include stuff in the database. I'll tale a closer look at ChronoConnectivity when I have a moment.

Bob

hey Bob,

'Contact' is one of the fields requested from the jobs table in the 'GET variables' field at the bottom of the page. Is that what you mean by 'request array?'

Re the checkboxes: I've seen some code for that lying around in this forum. I'll dig it out and give it a go.

Jason

Hi Jason,

No the $_REQUEST array is the PHP array that holds user info from forms, urls cookies. etc.

Bob

I tried the following code to test whether the $_REQUEST array was being used.

   import_request_variables('g');        //I also changed this to 'p'
   $pst = $_POST['contact'];
   $getvar = $_GET['contact'];
   $reqvar = $_REQUEST['contact'];
   echo "post: $pst <br />";
   echo "get: $getvar <br />";
   echo "req: $reqvar <br />";

but it only returned blank lines.

Any suggestions?

--Jason

Hi Jason,

Can you show me an example link which contains the contact element data ?

Max

sorry, Max, but it's not externally accessible. It's a NAS box with a built-in web server and it's just in my home network (and my site security isn't strong enough for me to redirect port 80 to the box.)

I can give a screen shot of browsing it in myPHPAdmin, if that helps.

Hi Jason, no problems, I only want to see the link to make sure the syntax is ok!

Regards
Max

Apologies, I thought you were asking for a link to the box.

I'm still not 100% sure what you're after. The link I use to access the page I'm trying to create is the one on the chronocon admin page (index.php?option=com_chronoconnectivity&connectionname=jobscontacts.) I then just refresh the page after applying any changes I make to the code.

I guess the thing I'm asking here is: where I would normally use {contact} to tell chronocon which field I want to use, how do I bring that into the php script? It'd be great if it could be $contact={contact} but I'm guessing that won't work in php.

The getVar and getString functions don't appear to work this way in the chronocon body code.

Thanks,
Jason

Hi Jason,

it depends on the way you designed the connections to work, how many connections you have setup now ? do you want to display a single list with all details of every record or a list page then a detail view page ?

Max

It's for this query we've been chatting about over the last few posts.

The query needs to pull the job title and contact from one table and the phone and email details for that particular contact from another table. It then needs to present them in a list. There will be no editing or deleting of records (although an edit/update function would be great, I think it's a little above my current ability!🙂 )

So what I need to do is match the contact from the job table query to the contact from the agencyContact table query. Everything else works, I just can't get chronocon to recognise the contact in each loop of the body code. I've also tried including a hidden input field but, i guess because nothing is submitted, it never gets added to #_POST.

I don't want to edit the chronocon php code because the other connections I have set up work beautifully. I just haven't been able to find any info on how to get chronocon to use the current variable values in php. I'm sure there's going to be a way, I just haven't been able to find it.

Jason

edit: I've setup three connections now - one each for the list of jobs and agency contacts and this one to mix the two.

Hi Jason,

That's fine, to do what you need we need to use only 1 connection, the GET method will not work, because you will have the contact info of one record but the page has to list many records, so at the connection body you will need to use your query and instead of JRequest::getVar('contact') use $row->contact to get the current row record, if you need help then show me your body code and I will show you how to do it!

Cheers
Max

You're a star, thanks Max (and Bob, for all the early help.)

I haven't yet worked out how I'm going to edit the values (or update the two different tables.) Do you know of any threads which might help me out? (I haven't searched for anything yet, the priority was to get this working first.)

For other people trying to do the same thing, I've copied the code below. I've included comments to explain what each bit is doing.

Header code:

<?php
// Initialise row counter
    $i=0;
?>

<table style="text-align: left; width: 100%;" border=1 rules="cols" frame="box" cellpadding="2" cellspacing="3">
 <tr>
  <td style="width: 33%";>Title</td>
  <td style="width: 15%";>Contact</td>
  <td style="width: 17%";>Phone</td>
  <td style="width: 27%";>Email</td>
  <td style="width: 8%";>Action</td>
 </tr>
</table>

Body code:

<?php
    $db =& JFactory::getDBO();

// Define query in the jobs table...
    $jobQuery = "
        SELECT title, contact
        FROM #__chronoforms_jobs;";
    $db->setQuery( $jobQuery );

// ... and execute the query
    $jobContactList =& $database->loadObject();

// Pull out the two values needed from the jobs table.
    $contact = $row->contact;
    $title = $row->title;

// Define the query for the agencyContacts table...
    $query = "
        SELECT phone, email
        FROM jos_chronoforms_agencyContact
        WHERE name = ".$db->quote($contact).";";
    $db->setQuery( $query );

// ..and execute it.
    $result =& $db->loadObject();

// Pull out the values needed from the result
    $email = $result->email;
    $phone = $result->phone;
?>

    <table style="text-align: left; width: 100%;" border=1 rules="cols" frame="box" cellpadding="2" cellspacing="3">
        <tr class="<?php echo "row_$i";?>">
          <td style="width: 33%";>{title}</td>
          <td style="width: 15%";>{contact}</td>
          <td style="width: 17%";><?php echo $phone; ?></td>
          <td style="width: 27%";><?php echo "<a href='mailto:$email?subject=$title'>$email</a>"; ?></td>
          <td style="width: 8%";>{edit_record} {delete_record}</td>
        </tr>
    </table>
<?php
// Modify row counter
    $i=1-$i;
?>

There's nothing in the footer code.

Thanks Jason, regarding editing, there are some users who posted their final code, you can find it some where in the forums here🙂

Regards
Max