Forums

External Database to QUERY

uburoibob 26 Sep, 2011
I run six web sites on a single server, each on Joomla 1.5, with ChronoForms and ChronoConnectivity. They're doing everything I want except for one thing -- I have a common dealer database for all six sites. Because so far I can only get ChronoConnectivity to access Joomla's own database, I have to have six identical copies of that database out there and manually keep them up to date.

What I really want is one database table (preferably not within any of the six sites' Joomla databases) that all six can access. This would entail somehow telling ChronoConnectivity that I don't want to query the Joomla database; I want to query this other one, for which I'll gladly supply hostname, database name, username and password.

I have seen you describe a way to make ChronoForms go write to a second database, but even those instructions seem a little vague and not quite what I want.

There are plenty of other plugins out there that will read an arbitrary MySQL database, but they don't offer me the flexibility in my queries that your plugin does. So, I'd really rather try to make ChronoConnectivity work, if I can.

Scott
GreyHead 27 Sep, 2011
Hi Scott,

My first reaction is that you might be better looking at the MySQL replication commands here - though I could well be wrong about that :-(

You'd need to hack the ChronoConnectivity Code code to get it to work with a remote database. This would be tedious rather than difficult. I have a How-to document here that tells you how to use the Joomla! DB methods with a remote database.

Bob
uburoibob 27 Sep, 2011
I had read that document, but unless I missed something critical, that information deals with ChronoForms -- which, in this application, I'm not using at all! Instead, I simply have ChronoConnectivity querying a database table.

It's set up like this: on the General tab I have it named DealerQuery and the tablename set to jos_dealers.

In the query-related settings the WHERE SQL looks like this:

<?php
$search_array = array('territory');
$where = array();
foreach ( $search_array as $search ) {
  $value = JRequest::getVar($search, '' , 'post');
  if ( $value ) {
    $where[] = " $search LIKE '%$value%'";
  }
}
if ( !empty($where) ) {
  echo " WHERE ".implode(' AND ', $where);
} else { echo " WHERE 'territory' LIKE '%zzz%'"; }
?>


Under connection view settings, In my header I have a simple HTML SELECT:

<div margin-bottom: 40px;>
<p><small>Our dealer list is organized by country. To view the dealers that service your area, please select your country in the pull-down box on the right. A list of dealers will be displayed for you in the area below, with all available contact information.</small></p>

<?php $i = 0;?>
<div style="float: right; margin-bottom: 20px;">
<b>Select country:</b>
<select size="1" name="territory" onchange="this.form.submit();">
<option selected value="">Select</option>
<option value="United States">United States</option>
<option value="Argentina">Argentina</option>
<option value="Armenia">Armenia </option>
<option value="Australia">Australia</option>
<option value="Azerbaijan">Azerbaijan</option>
<option value="Bangladesh">Bangladesh</option>
<option value="Belize">Belize</option>
<option value="Bolivia">Bolivia</option>
<option value="Botswana">Botswana</option>
<option value="Brazil">Brazil</option>
<option value="Brunei">Brunei</option>
<option value="Cambodia">Cambodia</option>
<option value="Canada">Canada</option>
<option value="Chile">Chile</option>
<option value="China">China</option>
<option value="Colombia">Colombia</option>
<option value="Costa Rica">Costa Rica</option>
<option value="Czech Republic">Czech Republic</option>
<option value="Denmark">Denmark</option>
<option value="El Salvador">El Salvador</option>
<option value="Ecuador">Ecuador</option>
<option value="Faroue Islands">Faroue Islands</option>
<option value="Finland">Finland</option>
<option value="Fiji">Fiji</option>
<option value="France">France</option>
<option value="Georgia">Georgia</option>
<option value="Germany">Germany</option>
<option value="Greece">Greece</option>
<option value="Greek Cyprus">Greek Cyprus</option>
<option value="Greek Macedonia">Greek Macedonia</option>
<option value="Greenland">Greenland</option>
<option value="Guatemala">Guatemala</option>
<option value="Guyana">Guyana</option>
<option value="Honduras">Honduras</option>
<option value="Hong Kong">Hong Kong</option>
<option value="India">India</option>
<option value="Indonesia">Indonesia</option>
<option value="Iraq">Iraq</option>
<option value="Ireland">Ireland</option>
<option value="Israel">Israel</option>
<option value="Italy">Italy</option>
<option value="Japan">Japan</option>
<option value="Korea">Korea</option>
<option value="Laos">Laos</option>
<option value="Macao">Macao</option>
<option value="Madagascar">Madagascar</option>
<option value="Malaysia">Malaysia</option>
<option value="Mexico">Mexico</option>
<option value="Morocco">Morocco</option>
<option value="Mozambique">Mozambique</option>
<option value="Namibia">Namibia</option>
<option value="Nepal">Nepal</option>
<option value="Netherlands">Netherlands</option>
<option value="New Zealand">New Zealand</option>
<option value="Nicaragua">Nicaragua</option>
<option value="Nigeria">Nigeria</option>
<option value="Norway">Norway</option>
<option value="Pakistan">Pakistan</option>
<option value="Panama">Panama</option>
<option value="Paraguay">Paraguay</option>
<option value="Peru">Peru</option>
<option value="Philippines">Philippines</option>
<option value="Poland">Poland</option>
<option value="Port Of Spain">Port of Spain</option>
<option value="Romania">Romania</option>
<option value="Russia">Russia</option>
<option value="Singapore">Singapore</option>
<option value="Solomon islands">Solomon Islands</option>
<option value="South Africa">South Africa</option>
<option value="EspaƱa">Spain</option>
<option value="Sri Lanka">Sri Lanka</option>
<option value="Suriname">Suriname</option>
<option value="Sweden">Sweden</option>
<option value="Taiwan">Taiwan</option>
<option value="Thailand">Thailand</option>
<option value="Trinidad">Trinidad</option>
<option value="Turkey">Turkey</option>
<option value="United Kingdom">United Kingdom</option>
<option value="Uruguay">Uruguay</option>
<option value="Vanuatu">Vanuatu</option>
<option value="Venezuela">Venezuela</option>
<option value="Vietnam">Vietnam</option>
</select>
</div>
<?php
$MyData =& CFChronoConnectionData::getInstance($MyConnection->connectionrow->id);
if ( !count($MyData->getDataRows() ) ) {
  echo "<H1>Please select a country. =====></H1>";
} else {echo "<h1>Dealers for ", $_POST["territory"], "</h1>"; }
?>
</div>


Finally in the body I display the data in an easily readable form:

<div style="margin-bottom: 20px; <?php if ($i % 2) echo ' background-color: #DDD;';?>">
<p><strong>{dealername}</strong>
<?php if ( !empty($MyRow->contactname)) echo "<br /><strong>{contactname}</strong>"; ?>
</p>
<?php if ( !empty($MyRow->address)) echo "<p>{address}</p>"; ?>
<?php if ( !empty($MyRow->phone)) echo "{phone} Voice<br />"; ?>
<?php if ( !empty($MyRow->fax)) echo "{fax} Fax<br />"; ?>
<?php if ( !empty($MyRow->email)) echo "<a href=mailto:{email}>{email}</a><br />"; ?>
<?php if ( !empty($MyRow->website)) echo "<a href={website} target=_blank>{website}</a> on the Web<br />"; ?>
<?php if ( !empty($MyRow->products)) echo "<strong>Products Carried: </strong>"; ?>
<?php if ( strpos($MyRow->products,"R") !== false) echo "<strong>(Wheatstone Radio)  </strong>"; ?>
<?php if ( strpos($MyRow->products,"T") !== false) echo "<strong>(Wheatstone TV) </strong>"; ?>
<?php if ( strpos($MyRow->products,"A") !== false) echo "<strong>(Audioarts) </strong>"; ?>
<?php if ( strpos($MyRow->products,"C") !== false) echo "<strong>(Commercial) </strong>"; ?>
<?php if ( strpos($MyRow->products,"V") !== false) echo "<strong>(Vorsis) </strong>"; ?>

</div>
<?php $i++ ?>


As i say, this works like a charm ... it's just that I have to copy that one database table, jos_dealers, over to the other five sites every time, because I can only access it from within that particular Joomla installation's database. I sure would like to centralize it; if you have any ideas I'd love to hear 'em. Thanks!
GreyHead 29 Sep, 2011
Hi kd4dcy,

Well, yes the example was for use in ChronoForms. The suggestion was that you could use the same idea it to hack ChronoConnectivity to work with a remote table. It will work equally well anywhere in Joomla!.

The MySQL Replication functionality is described here.

Bob
This topic is locked and no more replies can be posted.