Nodejs + mysql 1, nodejsmysql

Nodejs + mysql 1, nodejsmysql
1. Set to create a database package. json

{"name":"shopping-cart-example","version":"0.0.1","dependencies":{"express":"4.10.6","express-session":"1.9.3","jade":"1.8.2","body-parser":"1.10.0","mysql":"2.5.4"}}

Database Configuration:

{"host":"localhost","user":"root","database":"cart_example"}

Running the database startup code will complete the initial creation of the database, because mysql and mongodb are different

Var mysql = require ('mysql'), config = require ('. /config'); delete config. database; var db = mysql. createConnection (config); // connect to the database. on ('error', function () {}); // console. log (db); db. query ('create database if not exist' cart _ example ''); // CREATE a DATABASE. query ('use 'cart _ example ''); // USE the database. query ('drop table if exits item'); // deletes a TABLE database. query ('create TABLE item ('+ // create table 'id INT (11) AUTO_INCREMENT,' + 'title VARCHAR (255), '+ 'description TEXT, '+ 'created DATETIME,' + 'Primary KEY (id) '); db. query ('drop table if exits review'); // deletes a TABLE database. query ('create TABLE review ('+ // create table 'id INT (11) AUTO_INCREMENT,' + 'item _ id INT (11), '+ 'text text, '+ 'Stars INT (1),' + 'created DATETIME, '+ 'Primary KEY (id)'); db. end (function () {process. exit ();});

2. server code

<Pre name = "code" class = "javascript"> var express = require ('express '); var session = require ('express-session '); // previously included in express, now independent var bodyParser = require ('body-parser '); // previously included in express, var mysql = require ('mysql'); // mysql driver var config = require ('. /config'); var app = express (); // app. use (session ({secret: 'My secret', resave: false, saveUninitialized: true}); app. use (bodyParser. urlencoded ({ex Tended: true}); // only true can the Member app in the POST information be correctly parsed. use (bodyParser. json (); // process parameters sent from the client and store them in req. body. app in user. use (express. static ('views'); // prevents the folder app of client resources. set ('view engine ', 'jade'); // set the HTML parsing engine to jadeapp. set ('view options', {layout: false}); // var db = mysql. createConnection (config); // connect to the mysql database // app. get ('/', function (req, res, next) {// getconsole on the homepage. log ('---------------/----------------'); db. query ('select Id, title, description FROM item', function (err, results) {// search for all items on the console. log ('------------------------ get "/"'); console. log (results); res. render ('index', {items: results}); // send the item to the home page as a parameter}); app. post ('/create', function (req, res, next) {// create an item postconsole. log ('---------------/create ----------------'); db. query ('insert INTO item SET title = ?, Description =? ', // Insert a newly created item [req. body. title, req. body. description], function (err, info) {// Replace the parameter in '? 'To prevent SQL injection attacks. if (err) return next (err); console. log ('-item created with id % s', info. insertId); res. redirect ('/'); // locate the home page, so that the item is immediately displayed}); app. get ('/item/: id', function (req, res, next) {// select the get path console of the item. log ('---------------/item/: id ------------------'); function getItem (fn) {// locate the item db from the database. query ('select id, title, description FROM item WHERE id =? LIMIT 1', // find the item of the corresponding id [req. params. id], function (err, results) {if (err) {return next (err);} if (! Results [0]) {return res. send (404) ;}fn (results [0]) ;});} function getReviews (item_id, fn) {// obtain the evaluation information db of an item. query ('select text, stars FROM review WHERE item_id =? ', // Read the database [item_id], function (err, results) {if (err) {return next (err);} fn (results ); // return all evaluation information});} getItem (function (item) {// get the item getReviews (item. id, function (reviews) {console. log ('------------ getReviews -------------'); console. log ('item = ') console. log (item); console. log ('Reviews = '); console. log (reviews); res. render ('item', {item: item, reviews: reviews}); // locate the page and pass the item, and comment}) ;}); app. post ('/item/: id/Review', functio N (req, res, next) {// comment item routing console. log ('---------------/item/: id/review ----------------'); db. query ('insert INTO review SET item_id = ?, Stars = ?, Text =? ', // Insert evaluation information [req. params. id, req. body. stars, req. body. text], function (err, info) {console. log ('-review created with id % s', info. insertId); res. redirect ('/item/' + req. params. id); // locate the item Page to display the rating information}); app. listen (3000, function () {console. log ('-listining on http: // *: 100 ');});

3. Client File.

doctype htmlhtmlheadtitle My shopping cartbodyh1 My shopping cart#cartblock body

B. index

extends ./layoutblock bodyh2 All itemsif (items.length)uleach item in itemslih3: a(href = "/item/#{item.id}")= item.title= item.descriptionelsep No items to showh2 Create new itemform(action="/create",method="POST")plabel Titleinput(type="text",name="title")plabel Descriptiontextarea(name="description")pbutton Submit

C. item

extends ./layoutblock bodya(href="/") Go backh2= item.titlep= item.descriptionh3 User reviewsif(reviews.length)each review in reviews.reviewb #{review.stars} starsp= review.texthrelsep No reviews to show.Write one!form(action="/item/#{item.id}/review",method="POST")fieldsetlegend Create reviewplabel Starsselect(name="stars")option 1option 2option 3option 4option 5plabel Reviewtextarea(name="text")pbutton(type="submit") Send

4. Running result

A. Database status

B. Console

C.index.html display

D.item.html display

The source of this example is: The Amazing nodejs