Forums

I'm attempting to query another table in the same database, and I have the code connecting to the table. However, it is still throwing my error in regards to the query. Below is the code, sanitized from some sensitive passwords/usernames. I am currently editing it in advanced wizard mode, and it is currently situated in the events before the DB Save, and after the Handle Arrays.

<?php

// Using $form->data[xxxxxx] to store data from the Form array
$name = $form->data[lastName] . ', ' . $form->data[firstName];
$county = $form->data[county];
$address1 = $form->data[streetAddress];
$address2 = $form->data[address2];
$city = $form->data[city];
$state = $form->data[state];
$zipcode = $form->data[zipcode];
$phone = $form->data[phoneNumber];
$email = $form->data[email];
$ssn = $form->data[ssn];
$changes = $form->data[changes];
$additional = $form->data[additionalInfo];

$hostname = '-----';
$user = '-----';
$password = '-----';
$database = '-----';

$link = mysqli_connect($hostname,$user,$password,$database) or die("Unable to select database");

$query = sprintf("SELECT * FROM tblEmail 
    WHERE county='%s',
    mysqli_real_escape_string($county));

$result = mysqli_query($query,$link) or die(Query will not run.);

// Statement will add the emails from the array
while ($row = mysqli_fetch_array($result))
{
$emailGroup = $row['emailGroup'];
$emailSupervisor = $row['emailSupervisor'];
}

mysqli_close($link);

$to      = '-----';

$subject = $form->data[lastName] . ', ' . $form->data[firstName];

$message = 'User information has been provided as follows:' . "\r\n" . "Name: " . $name . "\r\n";
$message .= 'County Selected: ' . $county . "\r\n";
$message .= 'Address: ' . $address1 . "\r\n";
$message .= $address2 ."\r\n";
$message .= 'City, State, Zip: ' . $city . ', ' . $state;
$message .= ' ' . $zipcode . "\r\n";
$message .= 'Phone Number: ' . $phone . "\r\n";
$message .= 'Email: ' . $email . "\r\n";
$message .= 'Social Security Number: ' . $ssn . "\r\n";
$message .= 'Changes: ' . $changes . "\r\n";
$message .= 'Additional Information: ' . $additional . "\r\n";
$message .= "\r\n" . $emailGroup . "\r\n" . $query . "\r\n";
$message .= $row . $result;

$headers = 'From: donotreply@-----.com' . "\r\n" .
    'Reply-To: donotreply@-----.com' . "\r\n" .
    'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);
?>

Could I get any help to figure out what is really going on here?

Hi jrcampbell,

Silly question, but why don't you just use a second DB Save?

You also didn't say what error you are getting.

Bob

I'm attempting to pull data from the database, not save to it. Also, it's in a different database than what the joomla install uses. The error I'm getting is the "Query will not run" error when it attempts to use the query.

Sorry if these messages are rather frank. I'm stressing over this, since we have a hard deadline on Monday. However, I do appreciate the help.

Hi jrcampbell,

If you are loading data then the Advanced tab on the DB Record Loader should do it.

I'm not auite sure what debugging your MySQL code has to do with ChronoForms and I don't use these functions if I can possibly avoid it!

But . . .

$query = sprintf("SELECT * FROM tblEmail
    WHERE county='%s',
    mysqli_real_escape_string($county));

$result = mysqli_query($query,$link) or die(Query will not run.);

You open a double quote " after sprintf but never close it so this will break the PHP.

Using the Joomla! DB code, try this:

<?php
$options = array('host' => 'xxx', 'user' => 'xxx', 'password' => 'xxx', 'database' => 'crm', 'prefix' = '', 'select' => 'tblEmail');
$db2 =& new JDatabaseMySQLi($options);
$query = "
SELECT `emailGroup`, `emailSupervisor`
  FROM `tblEmail` 
  WHERE `county` = {$db2->quote($county)} ;
";
$db2->setQuery($query);
$data = $d2b->loadObject();
$emailGroup = $data->emailGroup;
$emailSupervisor = $data->emailSupervisor;
. . .
?>

Bob

Thanks for the help. After stumbling around for a bit, I finally found the issue, which makes me feel all the more ridiculous. Although in the code I had posted, I was missing a ", that wasn't the root of the problem. It turned out that the following code:

$result = mysqli_query($query,$link) or die(Query will not run.);

should have been

$result = mysqli_query($link,$query) or die(Query will not run.);

I had the variables reversed. It happens to be that in the mysql_query(); should be mysql_query($query,$link), and mysqli_query(); should be mysqli_query($link,$query);. Talk about messing with someone's head!