Forums

OK 'nother stupid question by a newbie!

I am trying to sort by my column company_name, so entered
'company_name' DESC
in the WHERE SQL box.

I am getting:
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 ''company_name' DESC' at line 1 SQL=SELECT count(*) FROM jos_chronoforms_data_Distributors 'company_name' DESCYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''company_name' DESC' at line 1 SQL=SELECT count(*) FROM jos_chronoforms_data_Distributors 'company_name' DESCYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''company_name' DESC' at line 1 SQL=SELECT count(*) FROM jos_chronoforms_data_Distributors 'company_name' DESC

I am finding your tutorials very helpful. It's my own ignorance that is the stumbling block!

Really appreciate your help and will soon owe you a week's worth of coffees at least ...on which I will make good ;-)

Hi nancyh,

There's a separate Order box you can use to set an order. You also need to use back-ticks `` for the column-name and not straight quotes e.g. `company_name` DESC

Bob

Thank you Bob. That worked. At least I had a table...then I started working my way through the "dynamic filters" tutorial and boy, have I messed everything up again!

In the tutorial, you are using "title" and "post". I am trying to order by "company_name" and filter by "County."

I worked through the tutorial, adding code (mostly blindly I admit). Then I went through the complete code at the end and cleaned it up. I never could get County to filter and now my tables are gone.

PLEASE PLEASE PLEASE help. I can see light at the end of the tunnel..just not sure if it's a train or not.

Here's what I have:

WHERE SQL:
<?php
$County = JRequest::getString('County', '', 'post');
if ( $County ) {
echo " WHERE `County` LIKE '%$County%' ";
}
?>

ORDER:
`company_name` ASC

HEADER:
<?php
// get the number of records returned
$db =& JFactory::getDBO();
global $count;
$count = $db->loadResult();

// add style & script snippets
$style = $script = "";
$style .= "

table.cf_listing {
margin-bottom: 12px;
}
";

$script .= "
$('clear').addEvent('click', function() {
$('title').value = '';
});
";
$doc =& JFactory::getDocument();
if ( $script ) {
$script = "window.addEvent('domready', function() { $script });";
$doc->addScriptDeclaration($script);
}
if ( $style) {
$doc->addStyleDeclaration($style);
}

<!-- I AM NOT USING TITLE and POST...but want to order by company_name and be able to filter by County -->

// get the previous filter string
$title = JRequest::getString('title', '', 'post');

?>
<!-- display the filter box and buttons -->
<input type='text' name='County' id='County' value='<?php echo $title; ?>' /> <input type='submit' name='filter' id='filter' value='Filter' />
 <input type='button' name='clear' id='clear' value='Clear' />

<!-- start the listing table -->
<table class='cf_listing'>
<?php

// check if there are any records returned
if ( !$count ) {
// no records - show the message
echo "<div>Sorry, no results were found.</div>";
} else {
// some records, show the header & footer rows
?>
<thead>
<tr>
<th style='width:250px;'>Company</th>
<th style='width:5px;'>Website</th>
<th style='width:20px; margin-left:12px;'>Phone</th>
<th style='width:100px;'>Address</th>
<th style='width:100px;'>City</th>
<th style='width:10px;'>State</th>
<th style='width:20px;'>Zip</th>
<th style='width:25px;'>County</th>
</tr>
</thead>
<tfoot>
<tr>
<td colspan='8' style='background:silver; height:4px;'></td>
</tr>
</tfoot>
<?php
}
?>
<tbody>

BODY:
<tr>
<td>{company_name}</td>
<td>{URL}</td>
<td>{Phone}</td>
<td>{Street_Address_and_Suite}</td>
<td>{City},</td>
<td>{State}</td>
<td>{Zip_Code}</td>
<td>{County}</td>
</tr>

FOOTER:
</body>
</table>
<?php
// get the row count and show the pagination if needed
global $count;
if ( $count ) {
?>
{pagination}
<?php
}
?>
</body>
</table>

Hi nancyh and Bob!

Well... just a suggestion, to sort by one column, have you tried the "Order fields" in the connectivity settings? There you can type the fields name separated with commas.

Also there's one field to filter too.

And the SQL would countain just a normal query.


I think that should work, best regards. ^__^