|
|
alter table to add new column in mysql table
October 10, 2008 – 7:07 pmALTER TABLE customers ADD email VARCHAR(90);
This above query will add the email column with varchar type at the end of an existing table. To add the new column after a specific column, for ex: lastname, use following query:
ALTER TABLE customers ADD email VARCHAR(90) AFTER lastname;
If you want to add the new column at first place, then use below statement or query:
ALTER TABLE customers ADD email VARCHAR(90) FIRST;
delete HTML table row dynamicaly using javascript
September 30, 2008 – 5:25 pmFollowing code is used to delete the html table row(<tr>) dynamically using javascript. Just press the delete button and deletes the <tr> as well as <td>’s from the table.
<html>
<head>
<script type="text/javascript">
function delete_row(r)
{
var i=r.parentNode.parentNode.rowIndex; //calculates the index of the row to delete
document.getElementById('tasksTable').deleteRow(i); //deletes the row
}
</script>
</head>
<body>
<table id="tasksTable" border="1" width="400">
<tr>
<td>Row 1</td>
<td><input type="button" value="Delete" onclick="delete_row(this)"></td>
</tr>
<tr>
<td>Row 2</td>
<td><input type="button" value="Delete" onclick="delete_row(this)"></td>
</tr>
<tr>
<td>Row 3</td>
<td><input type="button" value="Delete" onclick="delete_row(this)"></td>
</tr>
</table>
</body>
</html>
This kind of deleting helps while creating the ajax application, when you delete some records dynamically we also want <tr> and <td> to be deleted from tables as well
Add new row dynamically to existing table
September 28, 2008 – 12:34 pmOften we want to add the rows dynamically in our applications. This can be done using passing some parameter to add one more row (<TR>). But this method reloads the page again. Using JavaScript we can add the new row(<tr>) very easily. Following example shows how to add new row to the existing table.
<html>
<head>
<script type="text/javascript">
function insert_row()
{
var newRow=document.getElementById('detailTable').insertRow(2);
var col1=newRow.insertCell(0);
var col2=newRow.insertCell(1);
col1.innerHTML="Newly added column 1";
col2.innerHTML="Newly added column 2";
}
</script>
</head>
<body>
<table id="detailTable" border="1" width="400">
<tr>
<td>Row1 cell1</td>
<td>Row1 cell2</td>
</tr>
<tr>
<td>Row2 cell1</td>
<td>Row2 cell2</td>
</tr>
<tr>
<td>Row3 cell1</td>
<td>Row3 cell2</td>
</tr>
</table>
<br />
<input type="button" onclick="insert_row()" value="Insert Row">
</body>
</html>
Above code creates the new <tr> after the 2nd <tr>. For adding the new <tr> at the end, first we need to calculate the number of rows in a table. Then by replacing 2 with length of the table, it creates new row at the end of the table.
function insert_row()
{
var trLenth = document.getElementById('detailTable').rows.length
var newRow=document.getElementById('detailTable').insertRow(trLenth);
var col1=newRow.insertCell(0);
var col2=newRow.insertCell(1);
col1.innerHTML="Newly added column 1";
col2.innerHTML="Newly added column 2";
}
Limit Your Resultset with TOP or SET ROWCOUNT Keywords
August 22, 2008 – 6:57 pmSo far you have used “SELECT * FROM ProductsTable” SQL statement, which returns all the records/rows from a table. By adding a WHERE clause to the previous SELECT SQL statement, you can limit number of result set to only match criteria. Sometime this filter condition also doesn’t limit the result set. In some case you may need only top 10 results which are important. To fulfill this, you can use either TOP or SET ROWCOUNT keywords.
A SET ROWCOUNT statement simply limits the number of records returned to the client during a single connection. As soon as the number of rows specified is found, SQL Server stops processing the query. The syntax looks like this:
SET ROWCOUNT 10
SELECT * FROM dbo.Products
WHERE ProductQty > 50
ORDER BY ProductQty
It returns the first 10 products whose quantity is more than 50.
The TOP keyword also limits the number of rows returned but TOP keyword is used as part of the SELECT SQL statement as follows:
SELECT TOP 10
PrdouctId, ProductName, ProductCategory, ProductQty
FROM dbo.Products
WHERE ProductQty > 50
ORDER BY ProductQty
The above statement also returns the same result as the SET ROWCOUNT returned. You can also mention * if you want to fetch all the feild values.
Import CSV file into mysql table using load data
August 16, 2008 – 11:00 amCreating 1 row or upto 10 rows are easy manually. But what if we have to create more than 100 rows from a file? Following is the step by step procedure to import whole CSV file into the the required mysql table.
1. Create a DB called testDb. Use the existing one if you have.
CREATE DATABASE testDb;
USE testDb;
2. Create a table inside the testDb database.
CREATE TABLE testTable (
name varchar(30),
city varchar(50),
postcode varchar(10)
);
3. Now import the CSV file (csvfile.csv) to the testDb.testTable using following code
load data local infile '/home/directoryname/csvfile.csv' into table testDb.testTable fields terminated by ',' enclosed by '"' lines terminated by '\n';
Assume csvfile.csv having the data in following format
“name1″,”city1″,”postcode1″
“name2″,”city2″,”postcode2″
“name3″,”city3″,”postcode2″
……
……
……
“nameN”,”cityN”,”postcodeN”
As this CSV file is comma seperated, in the query we mentioned “fields seperated by ‘,’” and as the fields are enclosed in double quotes, we mentioned as ‘”‘ for enclosed by clause.
If fields are not enclosed by any characters then leave the enclose by clause empty
How to generate random string and numbers
August 6, 2008 – 6:57 pmSometimes, when user gets created then system needs to create some random password for that user and send it through mail. We also require random numbers, random characters for some other cases as well.
Below scripts shows how to create or generate the random characters, random numbers and random password
1: Generate random password
<%
'Script to generate random password
Dim Long_string, result, i
Long_string = "abcdefghijklmnoprstuvwhyzABCDEFGHIJLKMNOPRSTUVWXYZ0123456789"
i=0
Do While i <= 5
result = result & Mid(Long_string, Int(Rnd * Len(Long_string)) + 1, 1)
i = i + 1
Loop
Response.write result
%>
Script 2: Generate random numbers
<%
'Script to generate random numbers
Dim Long_string, result, i
Long_string = "0123456789"
i=0
Do While i <= 5
result = result & Mid(Long_string, Int(Rnd * Len(Long_string)) + 1, 1)
i = i + 1
Loop
Response.write result
%>
Script 3: Generate random string
<%
'Script to generate random string
Dim Long_string, result, i
Long_string = "abcdefghijklmnoprstuvwhyzABCDEFGHIJLKMNOPRSTUVWXYZ"
i=0
Do While i <= 5
result = result & Mid(Long_string, Int(Rnd * Len(Long_string)) + 1, 1)
i = i + 1
Loop
Response.write result
%>
Printing all server variables in ASP
July 24, 2008 – 5:08 pmFollowing code displays all the server variables which are present in asp
<table border="0" cellpadding="0" cellspacing="0" align="center" width="700">
<tr>
<td valign="top" align="left" width="200"><font face="Verdana" size="3" color="#FF0000"><b>Variable Name</b></font></td>
<td valign="top" align="left" width="500"><font face="Verdana" size="3"><b>Value</font></b></td>
</tr>
<%
for each name in request.servervariables
%>
<tr>
<td valign=”top” align=”left” width=”200″>
<font face=”Arial” size=”2″ color=”#FF0000″>
<%= name %></font></td>
<td valign=”top” align=”left” width=”500″>
<font face=”Arial” size=”2″><%= request.servervariables(name) %></font></td>
</tr>
<%
Next
%>
</table>
The above code displays the server variable name and values. Here are the few examples
APPL_PHYSICAL_PATH, AUTH_TYPE, CONTENT_LENGTH, CONTENT_TYPE, GATEWAY_INTERFACE, QUERY_STRING, LOCAL_ADDR, PATH_TRANSLATED, PATH_INFO , REMOTE_HOST, REMOTE_ADDR, REQUEST_METHOD, SCRIPT_NAME, SERVER_NAME, SERVER_SOFTWARE, HTTP_HOST, SERVER_SOFTWARE URL, HTTP_HOST, etc
To display individual server variables values use the following script
Response.write( request.servervariables(”REMOTE_HOST”) )
Removing undeline from links
June 24, 2008 – 6:38 pmWhenever we create a href then an underline comes by default for the link. We can remove this underline from the anchored text link by adding inline style to the a tag. Example is given below
<a href="contact.php">Contact us</a> <!-- this produce the link with underline -->
<a href="contact.php" style="text-decoration:none;">Contact us</a> <!-- this produce the link without underline -->
we can also define a class for removing underlines from all the anchored occurrences
<style type="text/css">
a.nounderline {text-decoration:none;}
</style>
<a href="contact.php" class="nounderline">Contact us</a>
Different types of CSS styles
June 24, 2008 – 6:30 pmCSS gives flexibility in using different style property locally by overriding the global declared styles in external style sheet. Following are the 3 different types of styles.
- Inline or embedded style sheet
- Internal style sheet
- External style sheet
Inline or embedded style sheet
These styles can be added directly within HTML tags. This gets highest priority than Internal and external defined styles. It means it overrides the existing style and applies the inline style.
<style type="text/css">
h2 {background-color:#ffff00;} /*Internal style sheet*/
</style>
<h2 style="background-color: #cccccc;">Heading tag</h2> <!-- inline style sheet -->
The above code outputs the heading tag with background color #cccccc by overriding the color #ffff00.
Internal Style Sheets
This type of style is defined inside the body tag of the page. The style defined here get overrides the external styles. Here is an example of Internal styles.
<head>
<title>Page title</title>
<style type="text/css">
p {
font-family: verdana, arial, sans-serif;
background-color: #cccccc;
}
</style>
</head>
<body >
<p> This paragraph is having font family verdana with background color #cccccc</p>
</body>
External Style sheets
These styles are created in some another file and are linked from the required page.
<link rel="stylesheet" href="css/style.css" type="text/css">
The above tag is to be placed within the head tags of the page. here style.css will have all the styles for different tags and classes.
Script to access MySQL database using PHP
June 22, 2008 – 8:51 amPHP and MySQL are most popular combination in creating web application. PHP and MySQL gets integrated easily in any platforms. Scripts developed in PHP using Window platform works perfectly in when used in Linux environment. Below example shows how to connect between PHP and MySQL database.
<?php
// Connecting to database
$link = mysql_connect('mysqlHost', 'mysqlUser', 'mysqlPassword')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
// Selecting to database
mysql_select_db('myDatabase') or die('Could not select database');
// Performing SQL query
$query = 'SELECT * FROM myTable';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo "<table width='400'>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
echo "\t\t<td>$line['colname1']</td><td>$line['colname1']</td>\n";
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($link);
?>
The above example shows to display the contents of the MySQL table. Here mysql_connect function is used to connect to the mysql using servername, username and userpassword parameters and returns the link identifier to that MySQL server. mysql_select_db function selects the database to run the queries and finally mysql_query runs the query and stores the result set in the $result variable.
Now using mysql_fetch_array or other functions we can make the operations on the resultant datas.