meaning of replace into in mysql

meaning of replace into in mysql 

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “INSERTSyntax”.


REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL — that either inserts or updates — see Section 12.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.


Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.


Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SETcol_name = DEFAULT(col_name) + 1.


To use REPLACE, you must have both the INSERT and DELETE privileges for the table.


The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.


The affected-rows count makes it easy to determine whether REPLACE only added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).


If you are using the C API, the affected-rows count can be obtained using the mysql_affected_rows() function.


Currently, you cannot replace into a table and select from the same table in a subquery.


MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE):



  

Try to insert the new row into the table




  

While the insertion fails because a duplicate-key error occurs for a primary key or unique index:

  

Delete from the table the conflicting row that has the duplicate key value




  

Try again to insert the new row into the table








 

When i use REPLACE i can not make it work as expected. Included is an
example.

I have the following 'test' table;
 +--------+--------------+------+-----+---------+-------+
 | Field  | Type         | Null | Key | Default | Extra |
 +--------+--------------+------+-----+---------+-------+
 | name   | varchar(255) |      | PRI |         |       |
 | number | int(5)       |      |     | 0       |       |
 +--------+--------------+------+-----+---------+-------+

mysql> REPLACE INTO test VALUES ('agent', number+1);
 Query OK, 1 row affected (0.00 sec)

 mysql> select * from test;
 +-------+--------+
 | name  | number |
 +-------+--------+
 | agent |      1 |
 +-------+--------+
 1 row in set (0.00 sec)

This works fine, so i do it again:

mysql> REPLACE INTO test VALUES ('agent', number+1);
 Query OK, 2 rows affected (0.00 sec)

 mysql> select * from test;
 +-------+--------+
 | name  | number |
 +-------+--------+
 | agent |      2 |
 +-------+--------+
 1 row in set (0.00 sec)

Still works as expected

mysql> REPLACE INTO test VALUES ('agent', number+1);
 Query OK, 2 rows affected (0.00 sec)

 mysql> select * from test;
 +-------+--------+
 | name  | number |
 +-------+--------+
 | agent |      3 |
 +-------+--------+
 1 row in set (0.00 sec)

Still, but....

mysql> REPLACE INTO test VALUES ('agent2', number+1);
 Query OK, 1 row affected (0.00 sec)

 mysql> select * from test;
 +--------+--------+
 | name   | number |
 +--------+--------+
 | agent  |      3 |
 | agent2 |      4 |
 +--------+--------+
 2 rows in set (0.00 sec)

Now i had expected 'agent2' to have the value '1', but it gets the last
value from the table - and it touches 2 rows (not 1 row).

what are the encryption functions in php

what are the encryption functions in php

CRYPT()
MD5()

meaning of mime in php

meaning of mime in php

MIME is Multipurpose Internet Mail Extensions is an Internet standard for the format of e-mail. However browsers also uses MIME standard to transmit files. MIME has a header which is added to a beginning of the data. When browser sees such header it shows the data as it would be a file (for example image)

Some examples of MIME types:
audio/x-ms-wmp
image/png
aplication/x-shockwave-flash

how can get number of elements of an array

a) sizeof($array) - This function is an alias of count()
b) count($urarray) - This function returns the number of elements in an array.
Interestingly if you just pass a simple var instead of an array, count() will return 1.

main sorting function in php

main  sorting functions in PHP:
asort()
arsort()
ksort()
krsort()
uksort()
sort()
natsort()
rsort()

password protection in php with htacces

Password protection Ever wanted a specific directory in your site to be available only to people who you want it to be available to? Ever got frustrated with the seeming holes in client-side options for this that allowed virtually anyone with enough skill to mess around in your source to get in? htaccess is the answer! There are numerous methods to password protecting areas of your site, some server language based (such as ASP, PHPor PERL) and client side based, such as JavaScript. JavaScript is not as secure or foolproof as a server-side option, a server side challenge/response is always more secure than a client dependant challenge/response. htaccess is about as secure as you can or need to get in everyday life, though there are ways above and beyond even that of htaccess. If you aren't comfortable enough with htaccess, you can password protect your pages any number of ways, and JavaScript Kit has plenty of password protection scripts for your use. The first thing you will need to do is create a file called .htpasswd. I know, you might have problems with the naming convention, but it is the same idea behind naming the htaccess file itself, and you should be able to do that by this point. In the htpasswd file, you place the username and password (which is encrypted) for those whom you want to have access. For example, a username and password of wsabstract (and I do not recommend having the username being the same as the password), the htpasswd file would look like this: wsabstract:y4E7Ep8e7EYV Notice that it is UserName first, followed by the Password. There is a handy-dandy tool available for you to easily encrypt the password into the proper encoding for use in the httpasswd file. For security, you should not upload the htpasswd file to a directory that is web accessible (yoursite.com/.htpasswd), it should be placed above your www root directory. You'll be specifying the location to it later on, so be sure you know where you put it. Also, this file, as with htaccess, should be uploaded as ASCII and not BINARY. Create a new htaccess file and place the following code in it: AuthUserFile /usr/local/you/safedir/.htpasswd AuthGroupFile /dev/null AuthName EnterPassword AuthType Basic require user wsabstract The first line is the full server path to your htpasswd file. If you have installed scripts on your server, you should be familiar with this. Please note that this is not a URL, this is a server path. Also note that if you place this htaccess file in your root directory, it will password protect your entire site, which probably isn't your exact goal. The second to last line require user is where you enter the username of those who you want to have access to that portion of your site. Note that using this will allow only that specific user to be able to access that directory. This applies if you had an htpasswd file that had multiple users setup in it and you wanted each one to have access to an individual directory. If you wanted the entire list of users to have access to that directory, you would replace Require user xxx with require valid-user. The AuthName is the name of the area you want to access. It could anything, such as "EnterPassword". You can change the name of this 'realm' to whatever you want, within reason.

htaccess tips in php

Setup PHP when run as CGI / suexec

Place your php.ini file in the dir of your cgi’d php, in this case /cgi-bin/, .htaccess might look something like:

AddHandler php-cgi .php .htm
Action php-cgi /askapache.com/cgi-bin/php5.cgi

Use a custom php.ini with mod_php or php as a cgi

Article: Custom PHP.ini tips and tricks

PHP run as Apache Module (mod_php)

Add this to your root .htaccess file. Say your php.ini is in folder /askapache.com/ini/

SetEnv PHPRC /askapache.com/ini

Custom php.ini with wrapper for FastCGI and PHP

You can use FastCGI on php by creating a php binary for cgi execution, then you create a fcgi wrapper script that execs the php using FastCGI

Article: Custom php.ini/FastCGI/DreamHost

#!/bin/sh
export PHP_FCGI_CHILDREN=3
export PHPRC=/askapache.com/ini
exec /askapache.com/cgi-bin/php.cgi
# or
#exec /askapache.com/cgi-bin/php.cgi -c /askapache.com/ini/php.ini

simple class pagination in php

The PHP Class:

<?
/*
Developed by Reneesh T.K
reneeshtk@gmail.com
You can use it with out any worries...It is free for you..It will display the out put like:
First | Previous | 3 | 4 | 5 | 6 | 7| 8 | 9 | 10 | Next | Last
Page : 7 Of 10 . Total Records Found: 20
*/
class Pagination_class{
var $result;
var $anchors;
var $total;
function Pagination_class($qry,$starting,$recpage)
{
$rst = mysql_query($qry) or die(mysql_error());
$numrows = mysql_num_rows($rst);
$qry .= " limit $starting, $recpage";
$this->result = mysql_query($qry) or die(mysql_error());
$next = $starting+$recpage;
$var = ((intval($numrows/$recpage))-1)*$recpage;
$page_showing = intval($starting/$recpage)+1;
$total_page = ceil($numrows/$recpage);

if($numrows % $recpage != 0){
$last = ((intval($numrows/$recpage)))*$recpage;
}else{
$last = ((intval($numrows/$recpage))-1)*$recpage;
}
$previous = $starting-$recpage;
if($previous < 0){
$anc = "First | Previous | ";
}else{
$anc .= "<a href='javascript:pagination(0);'>First | </a>";
$anc .= "<a href='javascript:pagination($previous);'>Previous | </a>";
}

################If you dont want the numbers just comment this block###############
$norepeat = 4;//no of pages showing in the left and right side of the current page in the anchors
$j = 1;
for($i=$page_showing; $i>1; $i--){
$fpreviousPage = $i-1;
$page = ceil($fpreviousPage*$recpage)-$recpage;
$anch = "<a href='javascript:pagination($page);'>$fpreviousPage | </a>".$anch;
if($j == $norepeat) break;
$j++;
}
$anc .= $anch;
$anc .= $page_showing ."| ";
$j = 1;
for($i=$page_showing; $i<$total_page; $i++){
$fnextPage = $i+1;
$page = ceil($fnextPage*$recpage)-$recpage;
$anc .= "<a href='javascript:pagination($page);'>$fnextPage | </a>";
if($j==$norepeat) break;
$j++;
}
############################################################
if($next >= $numrows){
$anc .= "Next | Last ";
}else{
$anc .= "<a href='javascript:pagination($next);'>Next | </a>";
$anc .= "<a href='javascript:pagination($last);'>Last</a>";
}
$this->anchors = $anc;

$this->total = "<svaluestrong>Page : $page_showing <i> Of </i> $total_page . Total Records Found: $numrows</svaluestrong>";
}
}
?>


Demo Page:


<?php

include('pagination_class.php');
mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('std_db');


/*
-- Table structure for table `students`

CREATE TABLE `students` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`)
);

INSERT INTO `students` VALUES (1, 'Reneesh');
INSERT INTO `students` VALUES (2, 'Aniesh');
INSERT INTO `students` VALUES (3, 'Babu');
INSERT INTO `students` VALUES (4, 'Antony');
INSERT INTO `students` VALUES (5, 'Praveesh');
INSERT INTO `students` VALUES (6, 'Dixon');
INSERT INTO `students` VALUES (7, 'Sanju');
INSERT INTO `students` VALUES (8, 'Neeraj');
INSERT INTO `students` VALUES (9, 'Siju');
INSERT INTO `students` VALUES (10, 'Noble');
INSERT INTO `students` VALUES (11, 'Bibin');
INSERT INTO `students` VALUES (12, 'Febin');
INSERT INTO `students` VALUES (13, 'Binu');
INSERT INTO `students` VALUES (14, 'Charles');
INSERT INTO `students` VALUES (15, 'jaggu');
INSERT INTO `students` VALUES (16, 'mani');
INSERT INTO `students` VALUES (17, 'milu');
INSERT INTO `students` VALUES (18, 'aravind');
INSERT INTO `students` VALUES (19, 'jay');
INSERT INTO `students` VALUES (20, 'hari');
*/
?>

<script language="JavaScript">
function pagination(page)
{
window.location = "testpage.php?search_text="+document.form1.search_text.value+"&starting="+page;
}
</script>
<?
$qry = "SELECT * FROM students";

if($_REQUEST['search_text']!=""){
$searchText = $_REQUEST['search_text'];
$qry .=" where name like '$searchText%'";
}

//for pagination
if(isset($_GET['starting'])&& !isset($_REQUEST['submit'])){
$starting=$_GET['starting'];
}else{
$starting=0;
}
$recpage = 2;//number of records per page

$obj = new pagination_class($qry,$starting,$recpage);
$result = $obj->result;


?><form name="form1" action="testpage.php" method="POST">

<table border="1" width="40%">
<tr>
<TD colspan="2">
Search <input type="text" name="search_text" value="<? echo $searchText;?>">
<input type="submit" value="Search">
</TD>
</tr>
<tr><TD>Sl no</TD><TD>Name</TD></tr>
<?if(mysql_num_rows($result)!=0){
$counter = $starting + 1;
while($data = mysql_fetch_array($result)) {?>
<tr>
<TD><? echo $counter; ?></TD>
<TD><? echo $data['name']; ?></TD>
</tr><?
$counter ++;
} ?>

<tr><TD align="center" colspan="2"><? echo $obj->anchors; ?></TD></tr>
<tr><TD align="center" colspan="2"><? echo $obj->total; ?></TD></tr>
<?}else{
echo "No Data Found";
}?>
</TD></tr></table></form>

how can combine two variables together in php

it will achieve on  via concatenating operator(.)

$st1 = 'Welcome';

$st2 = 'php';

$st3 = $st1.$st2;

What’s __sleep and __wakeup in php array

 __sleep returns the array of all the variables than need to be saved, while __wakeup retrieves them.

define constant in php

define constant in php 
 

define() directive, like define ("MYCONSTANT", 100);

how can optimize mysql query

it can achive lot of things, Make sure table has primary key. - If there is any field which is used more frequently for searching then index it!. - The order of condition does matters. - In select statement where condition should have indexed column. - Fetch required amount of records from db (use limit clause). e.g. if you want total number of records then use count() function rather than select *.

difference in mysql4 and mysql5

In the world of databases that work with PHP, MYSQL 4 has been the norm for quite some time now. It functions well, has decent performance and generally does what it is supposed to do. But as with all things, its time is passing and it is time to upgrade to its successor, MYSQL 5. There are many reasons to upgrade to MYSQL 5, the main reason being stored procedures, triggers, and views.

Lets first take a look at stored procedures. Stored procedures have many benefits in terms of performance and security. A stored procedure is a procedure that is that is stored in a database. This is sort of like a function or sub-routine in a regular computer language. Like functions in other languages, stored procedures come in two flavors, those that you call to perform a task or those that you call to return a value that you use in another part of the query. The main reason why you should use stored procedures is because of speed. Stored procedures are stored on the server so if you need to do a certain task over and over again, stored procedures will give great boosts in speed and also put less of a burden on the server because it will not need to send messages back and forth to the client machine since its all on the server side. Another advantage is that stored procedures are common throughout all MYSQL deployments. You never have to install an extension class or add-on feature to any MYSQL setup to get stored procedures to work. Not only will they work in all deployments of MYSQL, but since MYSQL uses standard SQL syntax and stored procedures are largely the same throughout any SQL database, you should be able to port your stored procedure code easily into either MSSQL, Oracle 10g or any other SQL based system.
The second advantage we are going to look at in MYSQL 5 are triggers. A trigger is a piece of procedural code that is automatically executed in response to certain events that happen on a table or in a database. Triggers give programmers more control over what happens when a specific alteration is made to a table. It also can restrict access to specific data, perform logging, or audit data modifications.
The final advantage that we will examine here are views. Views are a handy programming tool that MYSQL 5 developers can use to show actual code directly from the screen of the client program. This is useful for debugging and troubleshooting. Views are also often used to help write queries. There have been some performance problems with using the view function, but those will soon be resolved.
With these three advantages, MYSQL 5 is definitely a step beyond MYSQL 4 and worth an upgrade now.

simple join in mysql

SELECT ArticleTitle, Copyright, AuthID
FROM Articles AS b, AuthorArticle AS ab
WHERE b.ArticleID=ab.ArticleID
ORDER BY ArticleTitle;

difference in PHP4 and PHP5?

PHP4 cannot support oops concepts and Zend engine 1 is used.

PHP5 supports oops concepts and Zend engine 2 is used.
Error supporting is increased in PHP5.
XML and SQLLite will is increased in PHP5.

what are the tables in mysql

 what are the  tables in mysql
Total 5 types of tables we can create

1. MyISAM
2. Heap
3. Merge
4. INNO DB
5. ISAM

MyISAM is the default storage engine as of MySQL 3.23. When you fire the above create query MySQL will create a MyISAM table.

how can open file in php

The fopen() is used to open files in PHP.Frist parameter is name of file,second is mode of file
<?php
$file=fopen("welcome.txt","r");
?>

ip address in php

ip address in php
 <?php
echo $_SERVER['REMOTE_ADDR'];
?>

How can file download in php

<?php 
$dir="/path/to/file/";
if (isset($_REQUEST["file"])) {
    $file=$dir.$_REQUEST["file"];
    header("Content-type: application/force-download");
    header("Content-Transfer-Encoding: Binary");
    header("Content-length: ".filesize($file));
    header("Content-disposition: attachment; filename="".basename($file).""");
    readfile("$file");
} else {
    echo "No file selected";
}
 ?>

sql Injection in php

sql Injection in php

SQL injection means to act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.
SQL Injection Example
<?php
// a good user's name
$name = "test";
$query = "SELECT * FROM customers WHERE username = '$name'";
echo "Normal: " . $query . "<br />";
// user input that uses SQL Injection
$name_dul = "' OR 1'";
// our MySQL query builder, however, not a very safe one
$query_dul = "SELECT * FROM customers WHERE username = '$name_dul'";
// display what the new query will look like, with injection echo "Injection: " . $query_dul; 

?> 
it will display query

Normal: SELECT * FROM customers WHERE username = 'test'
Injection: SELECT * FROM customers WHERE username = '' OR 1''

How can Prevent SQL Injection

Lucky for you, this problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string. 

<?php
 //NOTE: you must be connected to the database to use this function! // connect to MySQL

$name_bad = "' OR 1'";

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";

echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";

$name_evil = "';

DELETE FROM customers WHERE 1 or username = '";
$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";

echo "Escaped Evil Injection: <br />" . $query_evil;

?>

it will display query

Escaped Bad Injection:
SELECT * FROM customers WHERE username = '\' OR 1\''
Escaped Evil Injection:
SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''