CODEWITHSUNDEEP
phphtmlmysqlsqlyii
mortiped
mortiped

Reputation: 867

Dynamically display the result of a MySQL query into an HTML page

I use the YII Framework and I would like to put the results of a MySQL query in a table in index.php.

The MySQL query is already good:

SELECT categories.name,
    systemes.name,
    systemes.etat_de_base,
    maintenances.name,
    maintenances.date,
    maintenances.duree 
FROM systemes_maintenances 
LEFT JOIN systemes 
ON systemes_maintenances.id_systemes = systemes.id_systemes 
LEFT JOIN categories 
ON systemes.id_categories = categories.id_categories 
LEFT JOIN maintenances 
ON systemes_maintenances.id_maintenances = maintenances.id_maintenances;

And my PHP page looks like this at the moment:

<?php
/* @var $this SiteController */

$this->pageTitle=Yii::app()->name;
?>

<!--<h1>Welcome to <i><?php echo CHtml::encode(Yii::app()->name); ?></i></h1>

<p>Congratulations! You have successfully created your Yii application.</p>

<p>You may change the content of this page by modifying the following two files:</p>
<ul>
    <li>View file: <code><?php echo __FILE__; ?></code></li>
    <li>Layout file: <code><?php echo $this->getLayoutFile('main'); ?></code></li>
</ul>

<p>For more details on how to further develop this application, please read
the <a href="http://www.yiiframework.com/doc/">documentation</a>.
Feel free to ask in the <a href="http://www.yiiframework.com/forum/">forum</a>,
should you have any questions.</p>-->

<table>
    <caption>&Eacute;tat des systèmes</caption>
    <tr>
    <th>Cat&eacute;gorie</th>
    <th>Nom</th>
    <th>&Eacute;tat actuel</th>
    <th>Maintenance prévue</th>
    <th>Début de l'incident</th>
    </tr>
    <tr>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    </tr>



</table>

I want to display the results in the empty <td> </ td>.

Does anyone know how to do it without jQuery?

Upvotes: 4

Views: 1489

Answers (5)

mortiped
mortiped

Reputation: 867

To connect to the database and the connection was defined in the config yii. Must be added to the top of page two line of code.

$sql = 'querySQL';
$connection=Yii::app()->db;
$dataReader=$connection->createCommand($sql)->query();

Upvotes: 2

Chakradhar Reddy Veeramreddy
Chakradhar Reddy Veeramreddy

Reputation: 167

I am not so sure of the YII framework but this should definitely help you out.

Let the result of the mySql query be in a variable $result

now , start with while/for loop like this :

<?php

while ($row=mysqli_fetch_array($result)){
    //Do something with the $row variable data
?>
    <td>Some Data</td>
    <td>echo $row["SomeColoumn1"];</td>
    <td>echo $row["SomeColoumn2"];</td>
    <td>echo $row["SomeColoumn3"];</td>
    <td>echo $row["SomeColoumn4"];</td>
<?php    
}

?>

This should print all the rows of the table as required and add the table and th parts before and after accordingly.

Upvotes: -2

Asped
Asped

Reputation: 3103

Try sth like this:

   $query = "SELECT categories.name,
        systemes.name,
        systemes.etat_de_base,
        maintenances.name,
        maintenances.date,
        maintenances.duree 
        FROM systemes_maintenances 
        LEFT JOIN systemes 
        ON systemes_maintenances.id_systemes = systemes.id_systemes 
        LEFT JOIN categories 
        ON systemes.id_categories = categories.id_categories 
        LEFT JOIN maintenances 
        ON systemes_maintenances.id_maintenances = maintenances.id_maintenances";

    $count= Yii::app()->db->createCommand($query)->queryScalar();

    $dataProvider = new CSqlDataProvider($query, array(
           'totalItemCount'=>(int) $count,
           'keyField' => 'SOME_UNIQUE_ID_FROM_THE_SQL',
           'pagination'=>array( 'pageSize'=>30, ),
    ));

    $this->widget('zii.widgets.grid.CGridView', array(
      'id'=>'yourGrid',
      'dataProvider'=> $dataProvider,
    ));

You will have to customize the grid yourself, so that it displays only what you need, but you can find this in the Yii documentation of CGridView

Upvotes: 0

chameera
chameera

Reputation: 389

Since you are using Yii framework you can use CGridView component. This give nice set of features such as sorting, pagination and filtering. Check following link for example usage. http://www.yiiplayground.com/index.php?r=UiModule/dataview/gridView

Upvotes: 2

Mandeep Singh
Mandeep Singh

Reputation: 37

their is a tutorial How to display data in php from Mysql.

Link: http://hightechnology.in/how-to-display-data-in-php-from-mysql/

may it will help you.

Upvotes: 0

Related Questions