var express = require('express');
var app = express();

app.set('view engine', 'jade');

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: ',fhf,fy86',
    database: 'hello-world-express'
});
connection.connect();


app.get('/', function (req, res) {
    connection.query('SELECT * FROM post ORDER BY id DESC LIMIT 10', function (err, rows) {
        res.render('index', {posts: rows});
    });
});

app.get('/category/:id', function (req, res) {
    var id = req.params.id;

    connection.query('SELECT * FROM category WHERE id = ?', [id], function (err, rows) {
        var category = rows[0];

        connection.query('SELECT * FROM post WHERE category_id = ?', [id], function (err, rows) {
            res.render('category', {category: category, posts: rows});
        });
    });
});

app.get('/post/:id', function (req, res) {
    connection.query('SELECT * FROM post WHERE id = ?', [req.params.id], function (err, rows) {
        res.render('post', {post: rows[0]});
    });
});

app.listen(3000);


//connection.end();