arinze arinze - 7 months ago 18
SQL Question

Join table and display

I am using the Yii PHP framework, trying to display user_comment and user_reply from two tables who both have a column named comment_id.

This is the comment table

CREATE TABLE `comment` (
`comment_id` int(11) NOT NULL,
`byy` varchar(30) NOT NULL,
`user_comment` varchar(900) NOT NULL,
`topic_id` varchar(30) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`up` bigint(11) NOT NULL,
`down` bigint(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


This is the reply table

CREATE TABLE `reply` (
`reply_id` int(11) NOT NULL,
`comment_id` int(19) NOT NULL,
`byy` varchar(29) NOT NULL,
`user_reply` varchar(29) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`up` varchar(29) NOT NULL,
`down` varchar(29) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


I want to display user_comment and user_reply in a list view format, sorted according to the comment_id. I am having problems with that.

<?php
$querys = new Query;
$querys->select([
'comment.user_comment',
'reply.user_reply']
)
->from('comment')
->join('LEFT OUTER JOIN', 'reply',
'reply.comment_id =comment.comment_id') ;
$command = $querys->createCommand();
$data = $command->queryAll();

$queryd = new Query;
$queryd->select([
'comment.user_comment',
'reply.user_reply']
)
->from('comment')
->join('LEFT OUTER JOIN', 'reply',
'reply.comment_id =comment.comment_id')
->LIMIT(5);
$command = $queryd->createCommand();
$data = $command->queryAll();


foreach ($data as $detail) {
$dataArr[] = array(
'user_reply' => $detail['user_reply']
);
}
$dataProvider = new ArrayDataProvider([
'allModels' => $dataArr,
'pagination' => [
'pageSize' => 10,
],
'sort' => [
'attributes' => ['comment_created_at'],
],
]);
echo ListView::widget([
'dataProvider' => $dataProvider,
'itemOptions' => ['class' => 'comment-item'],
'itemView' => 'adapter',

]);
?>


Adapter class

<?php
use yii\helpers\Html;
use yii\helpers\HtmlPurifier;

//var_dump($model);exit;
?>

<div class="col-lg-12 col-sm-12">
<section>

<?php echo $model['user_reply']; ?>


</section>
</div>


I am getting this error:


Getting unknown property: Undefined index: user_reply


What am I doing wrong?

Answer

Try

foreach ($data as $detail) {
        $dataArr[] = array(
            'user_reply' => $detail['reply.user_reply']
        );
      }

or use alias

$queryd = new Query;
$queryd->select('comment.user_comment as user_comment, 
                reply.user_reply as user_reply')  
->from('comment')
->join('LEFT OUTER JOIN', 'reply',
    'reply.comment_id =comment.comment_id')    
 ->LIMIT(5); 
 $command = $queryd->createCommand();
 $data = $command->queryAll();  

 foreach ($data as $detail) {
        $dataArr[] = array(
            'user_reply' => $detail['user_reply']
        );
      }

and for dataProvider you must remeber there is a models collection then

<div class="col-lg-12 col-sm-12">
    <section>

      <?php  
          foreach($models as $model){
               //echo $model['user_reply']; 
                echo $model->user_reply; 
          }
        ?>

     </section>
 </div>
Comments