Definition And Explaination:-
ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create indexes, change the type of existing columns, or rename columns or the table itself.You can use ALTER TABLE to modify the structure of a table that has not been added to a database.Alter table command allows
* To add the field;
* To delete the field out;
* To change the DEFAULT value for any of the field;
* To add or delete the table PRIMARY KEY;
* To add or delete the table FOREIGN KEY;
* To add or delete UNIQUE constraint;
* To add or delete CHECK constraint;
* To rename fields or table itself;
* To change field type.
| ALTER [ COLUMN ] column_name < SET DEFAULT default_option | DROP DEFAULT>
| DROP [ COLUMN ] column_name [ drop_behavior ]
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP CONSTRAINT IDENT
This is a very basic example of php/mysql.We used to make use of mysql database and a php file for getting this done.To run this code you can first create database as provided in sql here and after creating database run this code.Make modifications as you want to do in it.
<html>
<head>
<title>Phone Book Example</title>
</head>
<body bgcolor=”#f6f6f6″ style=”font-family:arial”>
<marquee behavior=”alternate”>
<font size=’7′>Welcome to Phone Book Demo</font>
</marquee>
<br/><hr/><br/>
<table border=”1″ align=”center” width=”100%”>
<tr>
<td>
<h2 align=”center”>Enter Details</h2>
<table border=”1″ align=”center”>
<form action=”phonebookDemo.php” method=”post”>
<tr><td>First Name:</td><td><input type=”text” name=”FirstName” /></td></tr>
<tr><td>Last Name:</td><td><input type=”text” name=”LastName” /></td></tr>
<tr><td>Phone Number:</td><td><input type=”text” name=”PhoneNumber” /></td></tr>
<tr><td colspan=”2″ align=”center”><input type=”submit” value=”Add Contact” /></td></tr>
</form>
</table>
</td>
<td>
<?php
//Connectivity with MySql
$con=mysql_connect(”localhost”,”root”,”");
if(!$con)
{
echo “You are unable to connect with server</br>”;
die(”Error : “.mysql_error());
}
//Connectivity with database in MySql.
mysql_select_db(”phonebook”,$con);
$firstname = $_REQUEST['FirstName'];
$lastname = $_REQUEST['LastName'];
$phone = $_REQUEST['PhoneNumber'];
if($firstname != “” )
{
@mysql_query(”INSERT INTO contacts (FirstName,LastName,PhoneNumber) VALUES(’”.mysql_escape_string($firstname).”‘, ‘”.mysql_escape_string($lastname).”‘, ‘”.mysql_escape_string($phone).”‘)”);
$msg=”Record Added in phonebook”;
}
if($_REQUEST['action']==”del”)
{
@mysql_query(”DELETE FROM contacts WHERE id=”.intval($_REQUEST['id']).”;”);
}
$result = mysql_query(”SELECT * FROM contacts”);
echo “<table border=’1′ align=’center’ cellpadding=’5′>”;
echo “<tr><th>First Name</th>
<th>Last Name</th>
<th>Phone Number</th>
<th>Action</th>”;
if(!mysql_num_rows($result))echo “<tr><td colspan=4>No entry in phonebook</td></tr>”;
if($result)
{
while($row = mysql_fetch_array($result))
{
echo “<tr>”;
echo “<td>”.$row['FirstName'].”</td>”;
echo “<td>”.$row['LastName'].”</td>”;
echo “<td>”.$row['PhoneNumber'].”</td>”;
echo “<td><a onclick=\”return confirm(’Are u sure to delete this entry?’);\” href=’phonebookDemo.php?action=del&id=”.$row['id'].”‘>Delete</a></td>”;
echo “</tr>”;
}
}
echo “</table>”;
//mysql_query(”DELETE FROM contacts WHERE FirstName=’Kuldeep’”);
mysql_query($sql,$con);
mysql_close($con);
?>
</td>
</tr>
</table>
</body>
</html>
SQl for This exampple:-
CREATE TABLE `contacts` (
`id` int(10) NOT NULL auto_increment,
`FirstName` varchar(200) NOT NULL,
`LastName` varchar(200) NOT NULL,
`PhoneNumber` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
–
– Dumping data for table `contacts`
–
INSERT INTO `contacts` (`id`, `FirstName`, `LastName`, `PhoneNumber`) VALUES (3, ‘Inder’, ‘Singh’, ‘98726-82279′),
(5, ‘Inderweb’, ‘India’, ‘98726-82279′);
A Mysql injection is attack tried by site visitors/users to get/damage data in databases by taking benefit from poor programming of websites.An injection attack occurs when a visitor to your site types something into a form input with the purpose of changing the outcome of your MySQL query. For example, at a login screen someone may try this type of attack to gain access to a secure area of the website.
If your query to check the username and password entered by the user was this:
“SELECT * FROM users WHERE username = ‘”.$_POST['username'].”‘ AND password = ‘”.$_POST['password'].”‘”
Someone could login by using any username and for the password they would type ‘ OR ”=” which would be placed into your MySQL query changing it to be:
“SELECT * FROM users WHERE username = ‘anyuser’ AND password = ” OR ”=””
As you can see, MySQL injection attacks can be pretty serious depending on the information the person has access to once they are logged in. It is very important for you to secure your site against injection attacks. Luckily, PHP can aid you in preventing injection attacks.
MySQL will then return all the rows in the table and then, depending on your script’s logic, you will probably log them in because there was a match. Now, in most cases, people have magic_quotes_gpc turned on (it’s the PHP default) which will add backslashes to escape all ‘ (single-quote), ” (double quote), (backslash) and NULL characters. This is not foolproof though because there are other characters that should be escaped to be safe.
Preventing Mysql/PHP injections:-
There are php mysql functions to prevent such type of things:
into your queries. One of The function is mysql_real_escape_string().
use Like :- $value = “‘” . mysql_real_escape_string($value) . “‘”;
For integer values dont forgot to use intval() function
mysql> INSERT INTO employee VALUES (’Fred Flinstone’,'Quarry Worker’,'Rock Digger’);
mysql> INSERT INTO employee VALUES (’Wilma Flinstone’,'Finance’,'Analyst’);
mysql> INSERT into employee values (’Barney Rubble’,'Sales’,'Neighbor’);
mysql> INSERT INTO employee VALUES (’Betty Rubble’,'IT’,'Neighbor’);
Note: Data type used was CHAR. Other data types include:
* CHAR(M) : Fixed length string. Always stores M characters whether it is holding 2 or 20 characters. Where M can range 1 to 255 characters.
* VARCHAR(M) : Variable length. Stores only the string. If M is defined to be 200 but the string is 20 characters long, only 20 characters are stored. Slower than CHAR.
* INT : Ranging from -2147483648 to 2147483647 or unsigned 0 to 4294967295
* FLOAT(M,N) : FLOAT(4,2) - Four digits total of which 2 are after the decimal. i.e. 12.34 Values are rounded to fit format if they are too large.
* DATE, TEXT, BLOB, SET, ENUM
4. Add a user. Use the MySQL SQL console to enter SQL commands. The command mysql with the correct login/password will connect you to the database. The admin tables are stored in the database “mysql”.
[prompt]$ mysql -h localhost -u root -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.41
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> USE mysql;
mysql> SHOW TABLES;
+—————–+
| Tables_in_mysql |
+—————–+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+—————–+
mysql> INSERT INTO user (Host, User, Password, Select_priv) VALUES (”, ‘Dude1′, password(’supersecret’), ‘Y’);
mysql> FLUSH PRIVILEGES; - Required each time one makes a change to the GRANT table
mysql> GRANT ALL PRIVILEGES ON bedrock.* TO Dude1;
mysql> FLUSH PRIVILEGES; - Required each time one makes a change to the GRANT table
mysql> quit
Note:
* There is NO space between the -p and the password! You can omit the password and you will be prompted for it.
* The SQL flush command is equivalent to issuing the command:
[prompt]$ mysqladmin reload
5. Test the database:
mysql> SELECT * from employee;
+—————–+—————+————-+
| Name | Dept | jobTitle |
+—————–+—————+————-+
| Fred Flinstone | Quarry Worker | Rock Digger |
| Wilma Flinstone | Finance | Analyst |
| Barney Rubble | Sales | Neighbor |
| Betty Rubble | IT | Neighbor |
+—————–+—————+————-+
1 row in set (0.00 sec)
mysql> SELECT name FROM employee WHERE dept=’Sales’;
+—————+
| name |
+—————+
| Barney Rubble |
+—————+
1 row in set (0.00 sec)
6. Quit from the SQL shell:
[prompt]$ quit
7. Shutting down the database:
[prompt]$ mysqladmin -u root -ppassword shutdown - PREFERRED
OR
[prompt]$ /etc/rc.d/init.d/mysqld stop
OR
[prompt]$ service mysqld stop
First of all you should know how you can add users in mysql databases:-
Using CREATE USER and/or GRANT commands
Inserting a new record into the mysql.user table
First let’s see how to use the CREATE USER command:-
CREATE USER user [IDENTIFIED BY [PASSWORD] ‘password‘]
e.g
mysql>CREATE USER ‘myuser’@‘localhost’ IDENTIFIED BY‘mypassword’;
Once user added in databases you need to provide privileges to that user.You can use like:-
mysql>GRANTSELECT,INSERT,UPDATE,DELETE ON *.* TO ‘myuser‘@‘localhost’;
or mysql>GRANT ALL ON *.* TO ‘myuser’@‘localhost’;
Another way for creating users in mysql is just enter directoly in user table of mysql and make few entries:-
mysql> INSERT INTO userVALUES('localhost','monty',PASSWORD('some_pass'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
or
mysql> INSERT INTO user (Host,User,Password)VALUES('localhost','usename','pass');
mysql> FLUSH PRIVILEGES;
Many times it is needed to get the structure of table in PHP file or drupal module.There may be conditions that you dont have got admin user name and passwords for cpanel/phpmyadmin.In such cases you must have idea how to know the structure using a small PHP code.you can get connection info for mysql from code files or if you are using ftp it is sure that u can run a SQl query from code file.So below is the simple script for getting table info
Simple PHP Mysql
$table_name="node";
$sql=sprintf("SHOW COLUMNS FROM %s",$table_name);
$$rs=mysql_query($sql);
while($col=mysql_fetch_object($rs))
{
print_r($col);
}
Drupal And Mysql
$table_name="node";
$sql=sprintf("SHOW COLUMNS FROM %s",$table_name);
$$rs=db_query($sql);
while($col=db_fetch_object($rs))
{
print_r($col);
}
Once you have checked structure you must be able now to add alter structure of table using same code method:-
mysql_query("ALTER TABLE user_info ADD user_coupon varchar2(50)");
Requirements before getting started with development or web programming
Any one who want to develop a web application must have the following systems:
1. A web server.
2. An editor to develop the web pages.
3. A browser to view the web page you develop.
4. A database program like MS Access, SQL Server etc, if your web site need to save data into a database.
In the real world situation, a web server will be hosted on a secure server, located in a safe place and will be always connected to high speed internet. However, to develop a web application, you don’t need to worry about security and internet connectivity. You can use your own development computer as the ‘Web Server’.
So, your development computer must have all the 4 program’s mentioned above running well.
Since this tutorial is guiding you to develop web sites using ASP.NET, all the tools we are going to talk here will be the ASP.NET specific tools.
Web Server
There are several types of web servers. But if you like to develop ASP.NET web applications, you need a specific web server called ‘Internet Information Server’ (IIS). And if you like to develop your web site using PHP then i will prefer you to use apache web server.
IIS comes as part of Windows. But it is not installed by default, when you install Windows. Please see the chapter ‘Installing IIS‘ to find more about installing IIS. IIS can be configured to work with PHP.But for PHP technologies Apache with LINUX is prefered.
Editor to develop web pages
Ideally, you do not need any special editor to develop a web application. If you are an expert, you can simply use ntoepad to type HTML and the code for the web pages.
You don’t need to work too on basics so Microsoft gives a tool called ‘Visual Studio .NET’ to edit web pages and write code for ASP.NET.
In case of other technologies then you can get EditPlus,Nusphere PHPed, Dreamweawear etc.You can just search on google for free programming editor and you will get a list
Visual Studio .NET (VS.NET)
Visual Studio .NET allows to easily create web pages. Some of the benefits in using Visual Studio .NEt are:
You can simply drag and drop html controls to the web page and VS.NET will automatically write the HTML tags for you.
Start typing an HTML tag and VS.NET will complete it! When you start typing a tag, VS.NET will show you the HTML tags starting with the characters you typed. So, you don’t need to even remember all the tags.
If you type any HTML tags wrong, VS.NET will highlight the errors and tell you how to correct it.
So, even if you are not an expert, VS.NET can help you develop great web pages.
Browser
You need a browser to view the web pages you create. If you have any windows operating system in your computer, you will already have a free browser (called ‘Internet Explorer’).You can also download and install other web browsers like Mozilla Firefox and Netscape navigator or Opera.Just search for any of keyword like Opera,Firefox and you will get a long listing.I will prefer you to have at least three browsers but having Mozilla’s and IE6 is better combination for testing and development purpose.
Database program
A database program like MS Access or SQL Server or SQL Express is required only if you need to save data into database. It is not mandatory that all web sites need a database program.But if you want to have a nice dynamic site then i will prefer to make use of databases.It greatly can reduce your file system load by keeping most of data in database and so will result interactive and dynamic web sites.Most of web sites these days are database driven.
For PHP users the suggested database is MySQL. You can get this free from mysql.com web site.
Downloading Required Softwares:-(PHP users)
In case you want to use PHP for web development then you can easily download You required softwares from internet.You can choose any of pref red one fromhere:-
WAMP (Window Apache MySQL PHP) combination of all of Window Apache MySQL PHP.Freely can be downloaded from internet.Search for WAMP and download this.
XAMP Same like WAMP integrates all tools like MYsql Apache into a common space.
Download Apache from apache.net,Mysql from mysql.com and install them seperatly.
