Hi, I am new to Yii framework and I am in the process of doing the Yii Music Store App. I have run into a problem and need some expert advice please. I am trying to browse the store and get this database error.
…localhost/MusicStore/index.php/store/browse?gid=1
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 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 '. ‘Name’ . ‘t1’ . ‘ArtistId’ FROM tbl_artist t1 LEFT JOIN ‘tbl_album’ ON ‘tb’ at line 1. The SQL statement executed was: SELECT DISTINCT ‘t1’ . ‘Name’ . ‘t1’ . ‘ArtistId’ FROM tbl_artist t1 LEFT JOIN ‘tbl_album’ ON ‘tbl_album’ . ‘ArtistId’ = ‘t1’ . ‘ArtistId’ WHERE ‘tbl_album’ . ‘GenreId’ = 1 ORDER BY ‘t1’ . ‘ArtistId’ ASC
This is the error im getting in the error handler.
3:47:29.763791 error system.db.CDbCommand
CDbCommand::fetchAll() failed: SQLSTATE[42000]: Syntax error or access
violation: 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
'. ‘Name’ . ‘t1’ . ‘ArtistId’ FROM tbl_artist t1 LEFT JOIN ‘tbl_album’
ON ‘tb’ at line 1. The SQL statement executed was: SELECT DISTINCT ‘t1’ .
‘Name’ . ‘t1’ . ‘ArtistId’ FROM tbl_artist t1 LEFT JOIN ‘tbl_album’ ON
‘tbl_album’ . ‘ArtistId’ = ‘t1’ . ‘ArtistId’ WHERE ‘tbl_album’ . ‘GenreId’
= 1 ORDER BY ‘t1’ . ‘ArtistId’ ASC.
in C:\wamp\www\MusicStore\protected\controllers\StoreController.php (40)
in C:\wamp\www\MusicStore\index.php (13)
23:47:29.829086 error exception.CDbException
exception ‘CDbException’ with message 'CDbCommand failed to execute the SQL
statement: SQLSTATE[42000]: Syntax error or access violation: 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 '. ‘Name’ . ‘t1’ .
‘ArtistId’ FROM tbl_artist t1 LEFT JOIN ‘tbl_album’ ON ‘tb’ at line 1.
The SQL statement executed was: SELECT DISTINCT ‘t1’ . ‘Name’ . ‘t1’ .
‘ArtistId’ FROM tbl_artist t1 LEFT JOIN ‘tbl_album’ ON ‘tbl_album’ .
‘ArtistId’ = ‘t1’ . ‘ArtistId’ WHERE ‘tbl_album’ . ‘GenreId’ = 1 ORDER BY
‘t1’ . ‘ArtistId’ ASC’ in C:\wamp\www\framework\db\CDbCommand.php:528
Stack trace:
#0 C:\wamp\www\framework\db\CDbCommand.php(390):
CDbCommand->queryInternal(‘fetchAll’, Array, Array)
#1 C:\wamp\www\framework\db\ar\CActiveRecord.php(1291):
CDbCommand->queryAll()
#2 C:\wamp\www\framework\db\ar\CActiveRecord.php(1410):
CActiveRecord->query(Object(CDbCriteria), true)
#3 C:\wamp\www\MusicStore\protected\controllers\StoreController.php(40):
CActiveRecord->findAll(Object(CDbCriteria))
#4 C:\wamp\www\framework\web\actions\CInlineAction.php(50):
StoreController->actionBrowse()
#5 C:\wamp\www\framework\web\CController.php(309):
CInlineAction->runWithParams(Array)
#6 C:\wamp\www\framework\web\CController.php(287):
CController->runAction(Object(CInlineAction))
#7 C:\wamp\www\framework\web\CController.php(266):
CController->runActionWithFilters(Object(CInlineAction), Array)
#8 C:\wamp\www\framework\web\CWebApplication.php(283):
CController->run(‘browse’)
#9 C:\wamp\www\framework\web\CWebApplication.php(142):
CWebApplication->runController(‘store/browse’)
#10 C:\wamp\www\framework\base\CApplication.php(162):
CWebApplication->processRequest()
#11 C:\wamp\www\MusicStore\index.php(13): CApplication->run()
#12 {main}
REQUEST_URI=/MusicStore/index.php/store/browse?gid=1
my store index.php
<?php
$this->breadcrumbs=array(
'Store',
);
?>
<img
src="<?php echo Yii::app()->request->baseUrl; ?>/images/logo.png" />
<h1><em><?php echo CHtml::encode(Yii::app()->name); ?></em></h1>
<?php if(isset($_GET['gid'])
foreach ($Genres as $Genre){
echo '<h1>' . $Genre->Name . "</h1><br />";
$desc = $Genre->Description;
}
?>
<div id="gmenu">
<?php echo $desc; ?></div>
<table>
<tr>
<?php
$cntRow = 0;
foreach ($Albums as $Album)
{
$aid = $Album->ArtistId;
$cntRow++;
if($cntRow % 2) echo "</tr><tr>";
foreach ($Artists as $Artist){
if($Artist->ArtistId === $aid){
$aname = $Artist->Name . "<br />";
}
}
echo "<td><center><strong>" . CHtml::link($aname, array('/Store/ArtistDetails/', 'artistid'=>$aid)) . "</strong>";
echo CHtml::link('<img src="' . Yii::app()->request->baseUrl . $Album->AlbumArtUrl . '" /><br />', array('store/details/', 'albid' => $Album->AlbumId, 'aid'=>$aid));
echo $Album->Title . "<br />" . $Album->Price . "</center></td>";
}
?>
</tr>
</table>
<?php }
elseif($_GET["albumid"] && $_GET["aid"]){
foreach($Artists as $Artist){
$aname = $Artist->Name;
$abio = $Artist->bio;
}
foreach($Albums as $Album){
$title = $Album->Title;
$tracks = $Album->tracks;
$price = $Album->Price;
$albid = $Album->AlbumId;
$aid = $Album->ArtistId;
$lnotes = $Album->LinerNotes;
$AlbumArtLargeUrl = $Album->AlbumArtLargeUrl;
} ?>
<h1><?php echo CHtml::link($aname, array('/Store/ArtistDetails/', 'artistid'=>$aid)); ?></h1>
<table>
<tr>
<td valign="top"><?php echo '<img src="' . Yii::app()->request->baseUrl . $AlbumArtLargeUrl . '" /><br /></td>'; ?>
<td></td>
<td valign="top"><?php echo "<h4>TRACKS</h4>" . $tracks; ?></td>
</tr>
<tr>
<td valign="top"><?php echo $title . "<br />";
echo $price . "<br />";
echo CHtml::link('Add to Cart', array('/ShoppingCart/', 'albid'=>$albid, 'aid'=>$aid)) . "<br />"; ?>
</td>
</tr>
</table>
<?php echo '<h3>Liner Notes</h3>' . $lnotes; ?>
<?php
}
elseif($_GET['artistid']){
foreach($Artists as $Artist){
$aname = $Artist->Name;
$abio = $Artist->bio;
$artistArtlUrl = $Artist->ArtistGraphicUrl;
} ?>
<h1><?php echo $aname; ?></h1>
<?php echo '<img style="float:left; margin: 0 5px 5px 0;" src="' . Yii::app()->request->baseUrl . $artistArtlUrl . '" /><br /></td>'; ?>
<?php echo "<h4>Bio</h4>" . $abio; ?>
<?php } else {
echo "<h1>" . $this->id . '/' . $this->action->id . "</h1>";
echo "<h3>" . $content . "</h3>";
}
?>
my store controller.php
<?php
class StoreController extends Controller
{
public $message;
public function actionIndex()
{
$this->message = "Hello from Store.index()";
$this->render('index' , array('content'=>$this->message));
}
public function actionBrowse()
{
if (isset($_GET["gid"]))
$genreCriteria = new CDbCriteria();
$genreCriteria->select = "'GenreId', 'Name', 'Description'";
$genreCriteria->condition = "GenreId = " .(isset($_GET["gid"]));
$artistCriteria = new CDbCriteria();
$artistCriteria->alias = "t1";
$artistCriteria->select = "DISTINCT 't1' . 'Name' . 't1' . 'ArtistId'";
$artistCriteria->join = "LEFT JOIN 'tbl_album' ON 'tbl_album' . 'ArtistId' = 't1' . 'ArtistId'";
$artistCriteria->condition = "'tbl_album' . 'GenreId' = " .(isset($_GET["gid"]));
$artistCriteria->order = "'t1' . 'ArtistId' ASC";
$albumCriteria = new CDbCriteria();
$albumCriteria->alias = "t2";
$albumCriteria->select = "'AlbumId' , 'GenreId', 'ArtistId', 'Title', 'Price', 'AlbumArtUrl'";
$albumCriteria->condition = "'GenreId' = " . (isset($_GET["gid"]));
$albumCriteria->order = " 'ArtistId' ASC";
$this->render('index', array('Albums' => Album::model()->findAll($albumCriteria),
'Artists'=> Artist::model()->findAll($artistCriteria),
'Genres'=> Genre::model()->findAll($genreCriteria)));
$this->message = 'Hello from Store.Browse()';
$this->render('index', array('content'=>$this->message));
}
public function actionDetails()
{
$this->message = "Hello from Store.Details()";
$this->render('index', array('content'=>$this->message));
}
// Uncomment the following methods and override them if needed
/*
public function filters()
{
// return the filter configuration for this controller, e.g.:
return array(
'inlineFilterName',
array(
'class'=>'path.to.FilterClass',
'propertyName'=>'propertyValue',
),
);
}
public function actions()
{
// return external action classes, e.g.:
return array(
'action1'=>'path.to.ActionClass',
'action2'=>array(
'class'=>'path.to.AnotherActionClass',
'propertyName'=>'propertyValue',
),
);
}
*/
}
Please let me know what is wrong…Thanks
