Hello all.
I am trying to execute a snippet that has two queries in it. I am still learning php and mysql so there may be a better way to accomplish this.
here is my snippet code
$output = '';//create variable for holding the output
$sql = $modx->db->query( 'SELECT cat_bespoke.co_name,main_info.co_name,main_info.city,main_info.country,main_info.logo_large,main_info.recommend,main_info.type FROM main_info,cat_bespoke WHERE cat_bespoke.co_name=main_info.co_name ORDER BY main_info.co_name ASC');
$resultArray = $modx->db->makeArray( $sql );//put it into an array
foreach($resultArray as $item)//go through each record in the array
{//start looping through the data array
$co = $item['co_name'];//to be used in second query for a random image form the company designated by $item['co_name']
$cFolder= $item['co_name'];
$cFolder= str_replace('&', 'and', $ciFolder);
$cFolder= str_replace(' ', '', $ciFolder);
$cFolder= str_replace('\'', '', $ciFolder);
$params['cPath']= $cFolder;
$params['iPath'] = 'assets/images/';
$params['company']=$item['co_name'];//set the placeholders, the $params sets the name of the placeholder, the $item is the column name from the DB table
$params['city']=$item['city'];
$params['country']=$item['country'];
$params['logo']=$item['logo_large'];//set the placeholders, the $params sets the name of the placeholder, the $item is the column name from the DB table
$params['rec']=$item['recommend'];
$params['btype']=$item['type'];
$q = $modx->db->query('SELECT images.small_img,images.alt WHERE images.co_name='.$co.' ORDER BY Rand()');
$resultArray = $modx->db->makeArray($q);
foreach($resultArray as $image)
{
$params['img']=$image['small_img'];
$params['alt']=$image['alt'];
}
$output.=$modx->parseChunk('companyListTpl', $params, '[+', '+]');//define the chunk name and process the content, here it's ProjectTpl which has the placeholders for the data
}//finish looping through the data array
return $output;//now return the output from the processed chunk
I get an error that states the following: " Execution of a query to the database failed - 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 ’WHERE images.co_name=Axfords ORDER BY Rand()’ at line 1 "
Axfords is the first company in this particular category - designated by the $co variable. The site is a collection of information on various lingerie companies/designers.
I am not sure why but anytime I use a variable in a modx query in a WHERE clause I get an error. I have searched the documentation and the forums but to no avail. Not sure what I am missing but I am sure it is a newbie mistake.
A point in the right direction would be greatly appreciated
MODX Staff
The quotes need to be escaped.