1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

Search form with calc element

Discussion in 'Community' started by ontarget, Dec 19, 2019.

  1. ontarget

    ontarget Active Member

    Level: Community
    Hi I have created a form (only) with no list - the purpose of the form is to query a DB table
    There are 3 fields

    1. Category (dropdown) {___select_category_raw}'
    2. Attended (dropdown) '{___attended_raw}'
    3. Output (calc)

    The form doesn't actually need to be submitted as i was planning to use Ajax in the "Output" calc element to show the result.

    The calc element query works with hard values e.g see the WHERE statement

    PHP:

    $category = '{___select_category_raw}';
    $attended = '{___attended_raw}';
    $year = '{___enter_year}';

    $db =& JFactory::getDBO();
    $db->setQuery('select count(*)
    from aed_reg_list2 c
    inner join nipd_eb_event_categories o
    on c.event_id = o.event_id
    WHERE o.category_id = 1 AND c.attended = 2 '
    );
    return $db->loadResult();
     
    however when I substitute the values in the WHERE clause with the fabrik elements i get a 1054 error
    "Unknown column '$category' in 'where clause' "

    PHP:

    WHERE o.category_id = $category AND c.attended = $attended');
    Do you know where I am going wrong?
    Thanks
     
  2. troester

    troester Well-Known Member Staff Member

    Level: Community
    If you want to use php variables inside a string you must use doublequotes
    ...("....WHERE o.category_id = $category AND c.attended = $attended");

    Or concatenate
    ...('....WHERE o.category_id = '. $category . 'AND c.attended = ' . $attended);
     
  3. ontarget

    ontarget Active Member

    Level: Community
    Thanks for the support Troester,

    I tried both concatenate and using double quotes e.g :
    PHP:
    $db =& JFactory::getDBO();
    $db->setQuery("select count(*)
    from aed_reg_list2 c
    inner join nipd_eb_event_categories o
    on c.event_id = o.event_id
    WHERE o.category_id = $category AND c.attended = $attended"
    );
    return $db->loadResult();
    In both instances i get the error:
    Code (Text):
    1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND c.attended =' at line 5
    I echoed out the values of $category and $attended to check the values make sense which they do - they both come back as integers.
    I can't figure out how the syntax is wrong?
     
  4. lousyfool

    lousyfool Active Member

    Level: Community
    Why not stick with Joomla:
    https://docs.joomla.org/Selecting_data_using_JDatabase

    Code (Text):

    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('c.*')
        ->from($mydb->quoteName('aed_reg_list2'))
        ->join('INNER', $mydb->quoteName('nipd_eb_event_categories', 'o').' ON '.$mydb->quoteName('c.event_id').' = '.$mydb->quoteName('o.event_id'))
        ->where($mydb->quoteName('o.category_id').' = '.$mydb->quote($category).' AND '.$mydb->quoteName('c.attended').' = '.$mydb->quote($attended));
    $mydb->setQuery($query);
    $mydb->execute();
    return $mydb->getNumRows();
     
    [EDIT: $mydb everywhere...]
     
    Last edited: Dec 20, 2019
  5. ontarget

    ontarget Active Member

    Level: Community
    Cool will give that a shot
    I managed it through standard php:)

    Code (Text):
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">

    <title>Course Summary</title>
    </head>

    <body>

    <h1>Course Summary Lookup</h1>
    <p>This form will query your database of attendance records for major course categories</p>
    <form action="" method="post" enctype="multipart/form-data" name="summarydata">
    <dl>
    <dt>Select Course Category:
    <dd>
    <select name="category">
    <option value="1">Primary 1</option>
    <option value="2">Post Primary 2</option>
    <option value="18">Wellness 18</option>
    </select>
    <dt>Select Attendance Status:
    <dd>
    <select name="attended">
    <option value="1">Pending 1</option>
    <option value="2">Attended 2</option>
    <option value="3">Not Attended 3</option>
    </select>
    <dt>Enter Year in this format 2019:
    <dd>
    <input name="year" type="text" />
    <dt>Submit the enquiry:
    <dd>
    <input name="Submit" type="submit" />
    </dl>


    </form>

        <?php
    $category = $_POST['category'];
    $attended = $_POST['attended'];
    $year = $_POST['year'];

    echo "<h2>Data for $year Category Value = $category, Attended Value = $attended</h2>";


    $db=mysqli_connect("localhost","","","");

    $q = "select count(*) from aed_reg_list2 c inner join nipd_eb_event_categories o on c.event_id = o.event_id WHERE o.category_id = '$category' AND c.attended = '$attended' AND year(c.course_date) = '$year'";
    $result = mysqli_query ($db,$q);
    if (!$result) {
        printf("Error: %s\n", mysqli_error($db));
        exit();
    }

    while ($row = $result->fetch_assoc()) {
        echo  "<h3>Result: " . $row['count(*)'] . "</h3>";
    }

    ?>


    </body>
    </html>
     
     
  6. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Seriously, don't use the native MySQL calls. Use the J! library. You really don't want to be hard coding all your database connections details in there.

    -- hugh
     

Share This Page