2

I wrote this query for getting data from 3 different tables which has one similar column.

The trouble is, it throws an error:

500 internal server error

I need the output to be in the form of json, and the values from the task columns should be child arrays (in the json).

Please let me know where the error is in this query.

 $query2 = "SELECT
                jos_mobile_contacts.contact_record_id,
                jos_mobile_contacts.firstName,
                jos_mobile_contacts.lastName,
                jos_mobile_contacts.mobiePhoneNumber,
                jos_mobile_contacts.officePhoneNumber,
                jos_mobile_contacts.contactType,
                jos_mobile_contacts.email,
                jos_mobile_contacts.address,
                jos_mobile_contacts.address2,
                jos_mobile_contacts.city,
                jos_mobile_contacts.state,
                jos_mobile_contacts.zip,
                jos_mobile_contacts.notes,
                jos_mobile_contacts.customfield1,
                jos_mobile_contacts.customfield2,
                jos_mobile_contacts.customfield3,
                jos_mobile_contacts.companyName,
                jos_mobile_contacts.title,
                jos_tasks.title,
                josi_tasks.priority,
                jos_tasks.description
            FROM jos_mobile_contacts
            INNER JOIN jos_tasks
            WHERE jos_mobile_contacts.registered_mobile_id ='453'
              and jos_tasks.registered_mobile_id ='453'";

$db->setQuery($query2);
$db->query();
$numrows= $db->getNumRows();
$rowsContacts = $db->loadAssocList();

if ($numrows <= 0 ) {
    $displayAction = "display:none";
} else {
    foreach ($rowsContacts as $contactInfo) {
        $row_array['contactsId']= $contactInfo['contact_record_id'];
        $row_array['firstName']= $contactInfo['firstName'];
        $row_array['lastName']= $contactInfo['lastName'];
        $row_array['mobiePhoneNumber']= $contactInfo['mobiePhoneNumber'];
        $row_array['officePhoneNumber']= $contactInfo['officePhoneNumber'];
        $row_array['priority']= $contactInfo['priority'];
        $row_array['title']= $contactInfo['title'];
    }
    echo $row_array;
}

closed as unclear what you're asking by mickmackusa, Zollie, TryHarder, Lodder Jan 29 at 9:15

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • I would recommend to take a look into the documentary: docs.joomla.org/Special:MyLanguage/… – Dennis Heiden Aug 23 '17 at 15:01
  • @Manoj your question is Unclear because: 1. We don't have access to your database tables' structure/data 2. You did not offer a specific error -- we need to know what is causing the internal error (you need to turn on error reporting) 3. We don't know the specific json output that you require. If you can supply us with the above details, I am confident that I can provide a correct and educational answer for you. – mickmackusa Jan 28 at 4:50
  • @Manoj Furthermore John Pitchers' answer has design flaws in it that I would like to correct, but not before I get the "full story" from you. – mickmackusa Jan 28 at 4:51
  • After editing your question and adding newlines in your SELECT statement to eliminate horizontal scrolling, I've got a pretty fair guess that one of the issues with your query is that you don't have a josi_tasks table. (little typos make all the difference) Please provide the details that I requested earlier so that I can post a complete answer with supplemental advice. – mickmackusa Jan 28 at 5:14
2

A 500 error isn't specific enough to know exactly what caused the crash but I see some issues in your code.

  1. Firstly, your query only references 2 tables not 3 like you stated in your question so it may be completely wrong.
  2. Usually, the table prefix (jos_) would be replaced with #__. This way you can install your code on multiple sites with different database prefixes.
  3. $db->query() is deprecated and not necessary. $db->loadAssocList will perform the query anyway.
  4. You are using echo to output an array. Use json_encode to encode your array into a json string.

The exact cause of your 500 error could be any of the above, I suspect number 4.

I can't test the below code without access to your database but this should get you on the right track.

$mobileid = 453;
$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select('contacts.contact_record_id, contacts.firstName, 
    contacts.lastName, contacts.mobiePhoneNumber, 
    contacts.officePhoneNumber, contacts.contactType, 
    contacts.email, contacts.address, contacts.address2, 
    contacts.city, contacts.state, contacts.zip, 
    contacts.notes, contacts.customfield1, contacts.customfield2,  
    contacts.customfield3, contacts.companyName, contacts.title, 
    tasks.title, josi_tasks.priority, tasks.description')
    ->from('#__tasks AS tasks')
    ->innerJoin('#__mobile_contacts AS contacts ON tasks.registered_mobile_id = contacts.registered_mobile_id')
    ->where('tasks.registered_mobile_id = '.$mobileid);

$db->setQuery($query2);
$rowsContacts = $db->loadAssocList();

if (count($rowsContacts)){
    $displayAction = "display:none";
} else {
    foreach ($rowsContacts as $contactInfo) {
        $row_array['contactsId']        = $contactInfo['contact_record_id'];
        $row_array['firstName']         = $contactInfo['firstName'];
        $row_array['lastName']          = $contactInfo['lastName'];
        $row_array['mobiePhoneNumber']  = $contactInfo['mobiePhoneNumber'];
        $row_array['officePhoneNumber'] = $contactInfo['officePhoneNumber'];
        $row_array['priority']          = $contactInfo['priority'];
        $row_array['title']             = $contactInfo['title'];
    }
    $output = json_encode($row_array);
    echo $output;
}

I hope this helps. Regards John

  • thanks for helping me.. yes i got that but what is here $mobileid – Manoj Yadav Jan 24 '17 at 8:27
  • jos_task or jos_contact thing is working.. how do i get all task of perticular contact – Manoj Yadav Jan 24 '17 at 8:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.