What are Stored Procedures? how can we use them?

Stored procedure is organized SQL code. Instead of writing sql query again and again, we just write that query once and save it as stored procedure. Whenever we need this code we just call procedure and get back the result that we need (just like functions). Store Procedure is used to retrieve data, modify data, and delete data from Database table. Stored procedures are secure to use as it avoids any injection attacks and it reduces the coding mistakes. Stored procedure has some syntax and parts. It has:

Name: it can be any meaningful name

Parameter: starts from @ sign

Body: starts from “CREATE PROCEDURE” clause

Here is a simple example of student table. Let’s assuming we want to view semester details of any specific student. So we will send studentID to that function which will retrieve all its information. The code is below.

CREATE PROCEDURE spGetStudent (@StudentID int) 

AS

      SELECT FirstName, LastName, SemsterNo

      FROM Student

      WHEREStudentID=@StudentID

Here spGetStudent  is name of procedure. @StudentID int is parameter StudentID of type int; following the body of procedure.

Hope it will help you now in using stored procedures.

How to retrieve data from database through PHP with and without table?

The SELECT statement is used to select data from a database:

Syntax

SELECT column_name(s)
FROM table_name

Working code (without table) is:

<?php

$con = mysql_connect("localhost","root","") or die('Could not connect: ' . mysql_error()); 

  mysql_select_db(“againtest", $con);

$result = mysql_query("SELECT * FROM Person");

while($row = mysql_fetch_array($result))

  {

  echo $row['FirstName'] . " " . $row['LastName'] . " " . $row['Age'];

  echo "<br>";

  }

mysql_close($con);

?>

We have established connection and save it in con variable. Then we selected the database from where we want to retrieve data. In our case it is "againtest". mysql_query() is a function that takes query as a parameter and returns result in $result (my own defined variable). As there might be more than one rows fetched from table, so we applied while loop which will keep traversing this variable till reaching the last result. For fetching row one by one from the group of rows ($result ) , mysql_fetch_array() is used. This function extracts one row at a time and save it in $row variable. Information of this row is displayed through echo function. It will continue working until or unless it reaches to last result.

Output will be like this:

Now if we want to display the same data in tabular form, then we will have to embed html code for table in this. Look the following code for this:

<?php

$con = mysql_connect("localhost","root","")or die('Could not connect: ' . mysql_error()); 

mysql_select_db("test", $con);

$result = mysql_query("SELECT * FROM Persons");

echo "<table border='1'>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Age</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['FirstName'] . "</td>";

  echo "<td>" . $row['LastName'] . "</td>";

  echo "<td>" . $row['Age']. "</td>";

  echo "</tr>";

  }

echo "</table>";

mysql_close($con);

?> 

Now output will be like this:

This code is in running form. I hope you will have good understanding of displaying data through PHP and HTML now. 

What is XAMPP? How do i know that XAMPP has been successfully installed on my system?

XAMPP stands for “X which means “cross-platform”, Apache, MySQL, PHP, Perl” and is a “solution stack package” that installs each of those items.  Here is a breakdown of what each piece of item does:

Apache: This is the actually web server software.  It takes requests from clients (other computers, for example) and returns the requested content to them.
MySQL: This is the database at backend.
PHP/Perl: These are the languages used for actual web development.  Interpreters need to be installed on the web server so it knows how to understand and display them to the web users.

All these items combined make XAMPP.  It allows you to run a web server that will interpret web sites that run on PHP, Perl or HTML.

Installation:

There are four things to keep in mind for successful installation of XAMPP. These are

1. Disable windows firewall ( Control panel->Disable windows firewall )

2. Disable user activated accounts (  Control panel-> user-> account settings->Disable user activated accounts)

3. Disable port 8080  ( Through IE proxy settings (in connection tab) )

4. Always keep all pages of extension .html and .php in htdocs folder. (  C//xampp:htdocs )

Follow the following steps after installation has been completed.

Step 1: search xampp icon in right bottom corner of your desktop and double click on it. You will see the xampp control panel as below. Select first three options Apache, MySql and FileZilla one by one  and click on Run button. you will see the following screen, showing Running status of all three options. (Run button turns to Stop once you enter it).

Step 2: Now Open Internet Explorer browser and type the url http://localhost/xampp/

Step 3: You will see the following screen. now click on first option (Engish)

Step 4: on this screen click on  phpMyAdmin that falls in Tools category.

If you reach on this screen , that means PHP has been installed successfully.

How to insert data in database through HTML form and PHP?

Client views html form for data entry. After entering data, when he clicks on submit button, data transfers to PHP form (which works as mediator)  and then through this PHP form data finally submits in database. So, first we will write one html file and one php file for this. Open your notepad for writing html code and save it with .html extension. We have following sample code for this html page. This page will have three text boxes for user's firstname, lastname and age and there will be one submit button.

HTML form

<html>

<body>

<form action="insert.php" method="post">

Firstname: <input type="text" name="firstname“ >

Lastname: <input type="text" name="lastname“ >

Age: <input type="text" name="age”>

<input type="submit”>

</form>

</body>

</html>

Note: Make sure to give exact file name of your php file in action clause of <form> tag such as in this example i will be using insert.php file so i am supposed to give same file name here in html file.

PHP file to insert data in database:

<?php

$con = mysql_connect("localhost","root","") or die('Could not connect: ' . mysql_error());

 

mysql_select_db(“againtest", $con); //you have to select database first then write insert command.

$sql="INSERT INTO Person (FirstName, LastName, Age)

VALUES

('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

if (!mysql_query($sql,$con))

  {

  die('Error: ' . mysql_error());

  }

echo "1 record added";  // echo is builtin function used to display message in browser

mysql_close($con)

?>

Note: To get PHP to execute the statement, use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

What is SQL? What are its basic commands?

SQL (Structured Query Language) is a database computer language used for the retrieving and managing data in relational database management systems (RDBMS).

SQL contains DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language) and TCL (Transaction Control) commands. DDL statements are used to define the database structure or schema. There are different commands lies under this category.

DDL Commands:  Create, Alter, Drop, Rename, Truncate

            CREATE –      command is used to create objects in the database. Such as Create Table.

            ALTER –        command is used to alter the structure of the database. For example we can ALTER                                     Table for modifying existing column or adding new column in it.

            DROP -          delete objects from the database and it can’t be rolled back.

           TRUNCATE - remove all records from a table, including all spaces allotted for the records. It can’t                                      be rolled back. And it does not have where clause in command.

            RENAME –   command is used to rename an object

DML statements are used for managing data. DML Commands include Insert, Update, Delete and Select commands.

            INSERT -   insert data into a table

            UPDATE – it updates present data within a table

            DELETE - deletes all records from a table, but the space for the records remain means structure of                                 table exists. And it can be roll back and have where clause in command.

           SELECT -  retrieve data from the a database

Here are few examples emphasizing on syntax and usage of these commands. If we want to create Table Person, we will have to use the following syntax.

DDL Commands:

            CREATE TABLE Person

             (          PID numeric(10,0),

                        PName varchar(100) );

Now if you want to add some other column or modifying its type. Use Alter command.

// Adding Column

             ALTER TABLE Person

             ADD PAddress varchar(100);          

// Deleting Column

             ALTER TABLE Person

             DROP COLUMN PAddress;

// Alter Data Type of a column

             ALTER TABLE Person

             ALTER COLUMN PAddress varchar(MAX)

For deleting table, use DROP command. 

DROP TABLE Person;

To rename table,
use this code.

sp_RENAME "Person","Persons";

To delete all the rows from table, the query would be like,

TRUNCATE TABLE Person;

DML Commands:

Select clause can use with variations like this.

select* FROM Person //select all rows from table

SELECT Name FROM Person // it will select only Name column from Person (Selecting column wise)

SELECT * FROM Persons                

            WHERE Age = 31 // it will display record where age will be 31.

SELECT DISTINCT Name FROM Persons

For inserting data in table, use following code.

INSERT INTO course

VALUES (12,'OOP') // this query will insert 12 and OOP in respective columns of course table.

UPDATE  course

SET Course_Name = 'C++'               

WHERE Course_ID = 12;

Above command will update course table.

These were few examples concentrating on SQL commands. I hope you will find this post easy and helping.