Skip to main content

Posts

Showing posts with the label MySQL

How to set varchar primary key field in Mysql

How to set varchar primary key field in Mysql Table structure of Users table CREATE TABLE `users` (    `id` varchar(36) NOT NULL DEFAULT 'InitiallyEmpty',    `first_name` varchar(100) NOT NULL,    `last_name` varchar(100) NULL,    `email` varchar(100) NOT NULL,    `password` varchar(100) NOT NULL,    PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Want to fill id an automatically filled. You need to create trigger Trigger structure for automatically update DROP trigger if exists before_insert_users; delimiter $$ CREATE TRIGGER before_insert_users BEFORE INSERT ON users FOR EACH ROW BEGIN     SET new.id = uuid(); END $$ delimiter ; Now create insert query insert into users (first_name,last_name,email,password) values ('Sudhir','Pandey','psudhir20@gmail.com','123465');

How to implement Real time notification in NodeJs

Here are simple steps to create real time notification using NodeJs, Socket.io and Mysql Socket.IO enables real-time bidirectional event-based communication.It has two parts: a client-side library that runs in the browser, and a server-side library for node.js. Install Socket.IO npm install --save socket.io I hope you have install express and mysql. This are basic few code inside server file. var express = require( 'express' ); var app = express(); app.use( express.static( __dirname + '/public') ); var mysql = require('mysql'); var server = require( 'http' ).Server( app ); var io = require( 'socket.io' )( server ); server.listen( 3000, function(){   console.log( 'listening on *:3000' ); } ); app.get('/', function(req, res) {    res.sendFile(__dirname + '/index.html'); }); The require('socket.io')(http) creates a new socket.io instance attached to the http server. Now make mysql conn...

How to Implement CRUD in Node js With MySQL

How to Implement CRUD in Node.js With MySQL In this post, we are going to create a simple CRUD application in Node.js with MySQL as the database. We are using EJS as the template engine. Before get started with this tutorial: You need to have Node installed. Read my previous post for Node.js installation. Step 1: Create index.js as main file and package.json file package.json { "name": "curdnode", "version": "1.0.0", "description": "Create simple curd example in nodejs", "main": "index.js", "scripts": { "start": "node index.js", "test": "echo \"Error: no test specified\" && exit 1" }, "author": "techsudhir", "license": "ISC" } index.js console.log('Welcome You'); Now open up your command line and run : npm start Output: Welcome You Stop the c...

MySQL Examples for Beginners

MySQL Examples for Beginners This basic MySQL query which will help beginners to learn. -- Delete if it exists mysql> DROP DATABASE IF EXISTS databaseName -- Create only if it does not exists mysql> CREATE DATABASE IF NOT EXISTS databaseName -- Set the default (current) database mysql> USE databaseName -- Show the default database mysql> SELECT DATABASE() -- Show the CREATE DATABASE statement mysql> SHOW CREATE DATABASE databaseName  -- Show all the tables in the current database. mysql> SHOW TABLES; -- Create the table "products". mysql> CREATE TABLE IF NOT EXISTS products ( productID bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT, productCode CHAR(3) NOT NULL DEFAULT '', name VARCHAR(30) NOT NULL DEFAULT '', quantity INT UNSIGNED NOT NULL DEFAULT 0, price DECIMAL(7,2) NOT NULL DEFAULT 0000.00, PRIMARY KEY (productID) ); -- Describe the fields (columns) of the "products" table mysql> DESCRIBE pro...

MySql Trigger

MySql Trigger A trigger is a stored program which executed automatically to respond to a specific event. such as insert, update or delete occurred in a table. Syntax : CREATE     TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE     ON `database`.`table`     FOR EACH ROW BEGIN -- trigger body -- this code is applied to every  -- inserted/updated/deleted row     END; Notes: event_name :  All triggers must have unique names within a schema trigger_event : Indicates the kind of operation that activates the trigger. tbl_name : The trigger becomes associated with the table named tbl_name Example 1: Suppose you have user table and user_audit table and you want to track new users only. Then you have to create a trigger DELIMITER $$ CREATE TRIGGER `blog_after_insert` AFTER INSERT  ON `user`  FOR EACH ROW  BEGIN SET @changetype = 'NEW'; INSERT INTO user_audit (user_id, change...

Server Side Datatable handling using PHP and MySql

Server Side Datatable handling using PHP and MySql jQuery datatable server side processing. Using jQuery Ajax and MySql. Step 1: View Layout for display Student Record index.html  <!DOCTYPE html> <html>     <head>         <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />         <title>Server Side Datatable handling</title>         <link rel="stylesheet" id="font-awesome-style-css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" type="text/css" media="all">         <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css"/>                 <script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQue...