Inscription utilisateurs, solution de http://defeo.lu/aws/tutorials/accounts-silex.html http://defeo.lu/aws/tutorials/accounts-node.html

app.js

var express = require('express'),
    bodyP = require('body-parser'),
    twig = require('twig'),
    mysql = require('mysql');

var app = express();

app.set('views', '.');
app.use(bodyP.urlencoded({ extended : false }));

var db    = mysql.createConnection({
  host     : process.env.IP,
  user     : process.env.C9_USER.substr(0,16),
  password : '',
  database : 'c9'
});

// Cette variable devrait être une constante, mais les constantes n'arriveront
// que dans ES6
var COLORS = {
    "r" : "red",
    "y" : "yellow",
    "g" : "green",
    "b" : "blue",
    "n" : "black"
};

app.get('/userlist', function(req, res) {
    db.query("SELECT * FROM users", function (err, rows) {
        if (err) {
            console.log(err);
            res.status(500).send('SQL Error');
        } else {
            res.render('userlist.twig', { 'users' : rows,
                                          'colors': COLORS });
        }
    });
});

app.all('/signup', function(req, res) {
    if (req.method== 'POST') {
        login = req.body.login;
        pass = req.body.password;
        c1 = req.body.couleur1;
        c2 = req.body.couleur2;
        if (login && pass) {
            db.query('INSERT INTO users VALUES (?, ?, ?, ?, ?, ?, ?)',
                     [login, pass, c1, c2, 0, 0, 0], function(err, result) {
                if (err && err.errno == 1062) {
                    res.render('signup.twig', { 'colors' : COLORS,
                                                'error'  : 'Nom déjà pris.' });
                } else if (err || result.affectedRows != 1) {
                    console.log(err, result);
                    res.status(500).send('Erreur SQL');
                } else {
                    res.redirect('/userlist');
                }
            });
        } else {
            res.render('signup.twig', { 'colors' : COLORS,
                                        'error'  : 'Login ou mot de passe vide.' });
        }
    } else {
        res.render('signup.twig', { 'colors' : COLORS, 'error' : false });
    }
});

app.listen(process.env.PORT);

index.php

<?php
require_once 'vendor/autoload.php';
use Silex\Application;
use Symfony\Component\HttpFoundation\Request;

$app = new Application();

// Configuration
$app['debug'] = true;

$app->register(new Silex\Provider\TwigServiceProvider(), 
	       array('twig.path' => '.',));

$app->register(new Silex\Provider\SessionServiceProvider());

$app->register(new Silex\Provider\DoctrineServiceProvider(),
  array('db.options' => array(
        'driver'   => 'pdo_mysql',
        'host'     => getenv('IP'),
        'user'     => substr(getenv('C9_USER'), 0, 16),
        'password' => '',
        'dbname' => 'c9'
  )));

// Cette variable devrait être une constante, mais les tableaux ne peuvent pas
// être déclarés constants en PHP.
$COLORS = array(
    "r" => "red",
    "y" => "yellow",
    "g" => "green",
    "b" => "blue",
    "n" => "black"
);

// Gestionnaires

$app->get('/userlist', function(Application $app) use ($COLORS) {
    $result = $app['db']->fetchAll("SELECT * FROM users");
    return $app['twig']->render('userlist.twig', array('users' => $result,
                                                       'colors' => $COLORS));
});

$app->match('/signup', function(Application $app, Request $req) use ($COLORS) {
    $erreur = '';
    if ($req->getMethod() == 'POST') {
        $login = $req->request->get('login');
        $pass = $req->request->get('password');
        $c1 = $req->request->get('couleur1');
        $c2 = $req->request->get('couleur2');
        if ($login && $pass) {
            $q = $app['db']->prepare('INSERT INTO users VALUES (?, ?, ?, ?, ?, ?, ?)');
            try {
                if ($q->execute(array($login, $pass, $c1, $c2, 0, 0, 0)) != 1) {
                    throw new Exception('Erreur insertion.');
                }
                return $app->redirect('/userlist');
            } catch (Doctrine\DBAL\DBALException $e) {
                if ($q->errorInfo()[1] == 1062) {
                    $erreur = "Nom déjà pris.";
                } else {
                    throw $e;
                }
            }
        } else {
            $erreur = "Login ou mot de passe vide.";
        }
    }
    return $app['twig']->render('signup.twig', array('colors' => $COLORS,
                                                     'error' => $erreur));
});

$app->run();

signup.twig

<!Doctype html>
<html>
    <head>
        <meta charset="utf-8">
        <title>Créer un utilisateur</title>
        <style>
            option {
                padding: 1px 2ex 1px 0;
            }

            {% for c, col in colors %}
            [value="{{ c }}"] {
                background: linear-gradient(to left, {{ col }}, 
                                            transparent 4ex, transparent);
            }
            .error {
                color: red;
            }
            {% endfor %}
        </style>
    </head>
    <body>
        <h1>Inscrire un nouvel utilisateur</h1>
        <form method="post">
            <input type="text" name="login"
                   placeholder="choisissez un pseudo" required>
            <input type="password" name="password"
                   placeholder="choisissez un mot de passe" required>
            {% for i in 1..2 %}
            <select name="couleur{{ i }}">
                {% for c, col in colors %}
                <option value="{{ c }}" 
                        {% if i == loop.index %}selected{% endif %}>
                    {{ col }}
                </option>
                {% endfor %}
            </select>
            {% endfor %}
            <input type="submit" value="Créer">
        </form>

        {% if error %}
        <p class="error">Erreur : {{ error }}</p>
        {% endif %}
    </body>
</html>

userlist.twig

<!Doctype html>
<html>
    <head>
        <meta charset="utf-8">
        <title>Liste utilisateurs</title>
        <style>
            table {
                text-align: center;
                border-collapse: collapse;
            }
            td, th {
                padding: 1ex;
            }
            table td:not(:first-child),
            table th:not(:first-child) 
            { border-left: solid thin gray }
            .square {
                display: inline-block;
                width: 3em;
                height: 1em;
            }

            {% for c, col in colors %}
            .{{ c }}{ background-color: {{ col }}; }
            {% endfor %}
        </style>
    </head>
    <body>
        <table>
            <tr><th>Joueur</th><th>Parties</th><th>Gagnées</th><th>Couleur préférée</th></tr>
            {% for u in users %}
            <tr>
                <td>{{ u.login }}</td>
                <td>{{ u.parties }}</td>
                <td>{{ u.gagnees }}</td>
                <td><span class="{{ u.couleur1 }} square"></span></td>
            </tr>
            {% endfor %}
        </table>
    </body>
</html>

users.sql

CREATE TABLE IF NOT EXISTS `users` (
  `login` varchar(255) NOT NULL,
  `pass` varchar(255) NOT NULL,
  `couleur1` varchar(255),
  `couleur2` varchar(255),
  `parties` int(11) UNSIGNED NOT NULL,
  `gagnees` int(11) UNSIGNED NOT NULL,
  `enligne` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;