-
-
Save greghelton/1749455 to your computer and use it in GitHub Desktop.
| var http = require('http') | |
| , mysql = require('mysql'); | |
| var client = mysql.createClient({ | |
| user: 'root', | |
| password: '' | |
| }); | |
| client.useDatabase('cookbook'); | |
| http.createServer(function (req, res) { | |
| if (req.url == '/') { | |
| client.query("SELECT r.name AS 'Recipe', ri.amount AS 'Amount',mu.name AS 'Unit',i.name" | |
| + " AS 'Ingredient' FROM Recipe r JOIN RecipeIngredient ri on r.id = ri.recipe_id" | |
| + " JOIN Ingredient i on i.id = ri.ingredient_id LEFT OUTER JOIN Measure mu on mu.id = measure_id", | |
| function(err, results, fields) { | |
| if (err) throw err; | |
| var output = '<html><head></head><body><h1>Latest Posts</h1><ul><table border=1><tr>'; | |
| for (var index in fields) { | |
| output += '<td>' + fields[index].name + '</td>'; | |
| } | |
| output += '</tr>'; | |
| for (var index in results) { | |
| output += '<tr><td>' + results[index].Recipe + '</td>'; | |
| output += '<td>' + results[index].Amount + '</td>'; | |
| output += '<td>' + results[index].Unit + '</td>'; | |
| output += '<td>' + results[index].Ingredient + '</td></tr>'; | |
| } | |
| output += '</ul></body></html>'; | |
| res.writeHead(200, {'Content-Type': 'text/html'}); | |
| res.end(output); | |
| } | |
| ); | |
| } | |
| }).listen(8000, "127.0.0.1"); |
And I figured it out!
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'user',
password : 'pass',
database : 'db'
});
connection.connect();
connection.query("SELECT r.name AS 'Recipe', ri.amount AS 'Amount',mu.name AS 'Unit',i.name"
+ " AS 'Ingredient' FROM Recipe r JOIN RecipeIngredient ri on r.id = ri.recipe_id"
+ " JOIN Ingredient i on i.id = ri.ingredient_id LEFT OUTER JOIN Measure mu on mu.id = measure_id",
function(err, rows, fields) {
if (err) throw err;
i = 0;
while (i < rows.length){
temp=''; // an empty string for generic output
c=1; // a counter to tell when we are at the end of a record
for (f in fields){
temp += rows[i][fields[f].name]; // here is where the magic happens
if (c == fields.length){temp+='\n'} // input a new line after the last column
else {temp+=' '} // input a space after all other columns
c++; // increment our counter
}
console.log(temp); // spit out the generic output string
i++; // go to the next row
}
});
connection.end();
Thanks. Until today I did not know I had a question and a solution to my post.
This is great, but is there a way to do it without hard-coding in the column names? (.Recipe, .Amount, .Unit, .Ingredient)
I need a generic way to reference the columns.