Search form with calc element

ontarget · Dec 19, 2019

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

Category (dropdown) {___select_category_raw}'
Attended (dropdown) '{___attended_raw}'
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

troester · Dec 19, 2019

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);

ontarget · Dec 20, 2019

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:

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?

lousyfool · Dec 20, 2019

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

Code:

$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...]

ontarget · Dec 20, 2019

Cool will give that a shot
I managed it through standard php

Code:

<!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>

cheesegrits · Dec 20, 2019

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

Search form with calc element

ontarget

Active Member

troester

Well-Known Member

ontarget

Active Member

lousyfool

Well-Known Member

ontarget

Active Member

cheesegrits

Support Gopher

Useful links

Members online