Forums

This is probably more php than chrono but thanks to some previous posts and great answers I have a search form (collecting 'searchword') and a query that returns relevant entries.

But the display (the 'foreach' part) prints only one record.
Do I need to create a new array/key or should this be working as is?

There is so much help on how to get stuff INTO databases but so much less on how to get it OUT.
And that's the whole point - I'm sure everyone already knows except me!

<?php
$db =& JFactory::getDBO();
$searchword = JRequest::getString('searchword', '', 'post');
if ( $searchword ) {
  $query="
    SELECT *
      FROM `#__chronoforms_venues`
      WHERE `venue_name` LIKE '%$searchword%'
      OR `town` LIKE '%$searchword%'
      ORDER BY `venue_name` ASC";

$db->setQuery($query);
//echo '<div>$query: '.print_r($query, true).'</div>';
$rows = $db->loadRowList();
//echo '<div>$rows: '.print_r($rows, true).'</div>';

//so far so good, I think.
foreach ($rows as $result ); {
	echo "<div>".$result [5].", ".$result [6].", ".$result [8]."</div>";
}

}

?>

Hi,
Have you verified the generated query using a different SQL client (phpMyAdmin, etc), and confirmed that you recieve multiple records?

That said, your current query is vulnerable to SQL injection exploits. I strongly suggest you alter it to something like below:

<?php
$db =& JFactory::getDBO();
$searchword = JRequest::getString('searchword', '', 'post');
if ($searchword)
{
  $pst = 'SELECT * FROM %1$s WHERE %2$s LIKE %4$s OR %3$s LIKE %4$s ORDER BY %2$s ASC';
  //%1$s: Table name: #__chronoforms_venues
  //%2$s: Column name: venue_name
  //%3$s: Column name: town
  //%4$s: search value, from JRequest::getString()
  $query = sprintf(
    $pst,
    $db->nameQuote('#__chronoforms_venues'),
    $db->nameQuote('venue_name'),
    $db->nameQuote('town'),
    $db->quote('%$searchword%')
  );
} else
{
  $query = '';
}
$db->setQuery($query);
//and so on...

The big change, is the use of the namequote and quote methods of the JDatabase instance, which properly escapes any characters that might break the query. In addition, sprintf is used to keep commands out of the query definition, and keep things tidy (as close as you'll get to a PreparedStatement in php).

Also, I prefer using named indices rather than index offsets when fetching results from a "SELECT *" query, which would require the use of loadAssocList() or loadObjectList() instead of loadRowList().
/Fredrik

Got it. Not a direct answer but probably more valuable - I will reconstruct as you suggest.
Thanks for your help Fredrik, sincerely appreciated.
Nick

Hi,
Re-Reading your previous post revealed what's most likely the issue; you've got a semi-colon in your foreach-loop, effectively causing it to do nothing:

//this line is incorrect:
foreach ($rows as $result); {

//It should look like this:
foreach ($rows as $result) {

/Fredrik

Doh! Thank you - even obvious to me when it's pointed out.