Showing posts with label mySql. Show all posts
Showing posts with label mySql. Show all posts

Limiting results by ID and order by rating

0 Replys

It is simple in MySql to query some from a table by its ID and order it using another field say "rating"
here is the php code for that


$data = mysql_query(SELECT * FROM (SELECT * FROM table_name ORDER BY ID DESC LIMIT 50) AS ttbl ORDER BY rating ASC;


ttbl is a virtual table or view (I don't know what exactly it is)

Category : , | Read More...... edit post

MySql Back Up

To take backup:


All database of a user : mysqldump -Q -u[User] -A -p > foo.sql
particular Database : mysqldump -Q -u[dbUser] -B[dbName] -p > bar.sql


Restore

mysql -u[UserName] -p
mysql > source foo.sql;

mysql -u[UserName] -p
mysql > create database user_bar; # for restoring single database
mysql > source bar.sql;


To dump all database from a remote server to local server The best option is
follows
------------------------------------------------------------------------------------------------------------------------------------------------
#mysqldump --all-databases --add-drop-table --host=10.10.10.1 -p > foo.dump
--------------------------------------------------------------------------------------------------------------------------------------------------

To Restore the dump
#mysql < foo.dump

Category : | Read More...... edit post

ERROR 1045 - Access Denied - MySql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Causes of Access Denied:
1. Make sure that the server is running. If it is not, clients cannot connect to it.
2. It might be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To correct this when you invoke a client program, specify a --port option to indicate the proper port number, or a --socket
option to indicate the proper named pipe or Unix socket file. To find out where the socket file is, you can use this command: 


shell> netstat -ln | grep mysql

There is many more reasons, which are enlisted on the MySql site

Solution: You can easily override this problem by using the following commands on the terminal.



# /etc/init.d/mysql stop
# mysqld --skip-grant-tables --user=root&
# mysql
mysql> use mysql
mysql> update user set Password='' where User='root';
mysql> exit
# killall -9 mysqld
# /etc/init.d/mysql start
# mysql -p
Enter password:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
mysql> FLUSH PRIVILEGES;
mysql> exit

This will flush out all the previlage and set a new root password.

Category : , | Read More...... edit post

sql Questions

I got busted with some easy mySql questions; anyway I expect this heck again and again. So I buried my self in google to find out the answers:

and here we go!

1. What is the difference between primary key and unique key?

Primary Key:

This is an index that cannot be NULL, Primary Keys are used in building relationships between tables in a database. (an index is automatically created on the primary key). The difference between primary and ordinary keys is that there can be multiple keys, but only one primary key.

Unique Key:

Unique and Index are same, the difference is, in Unique, duplicate are not allowed in any circumstances and that is enforced by database server.

2. How can you get rows in between of two keys ?

It can be done by using sql between.


Syntax:
********** SELECT <fields> FROM <table> WHERE <key_field> BETWEEN <key1> AND <key2>


Example : select f_name, l_name, age from employee_data where age BETWEEN 32 AND 40;

Category : , | Read More...... edit post

get top 'n' queries - php mysql

0 Replys

Many CMS sites having a facility to show the latest topics on site. I have created a general function for get the top 'n' posts. It is in PHP with MySql.



code:



< ?php
function select_top($table, $fields, $key, $nos)
{
//done by : thecoderin.gmail.com
//date: 28 feb 2009

$count_field = count($fields);
$query = "SELECT ";
for($i=0; $i<$count_field -1; $i++) +++$query = $query."`".$fields[$i]."`, ";

$query = $query."`".$fields[$count_field -1]."` ";

$query .= "FROM $table ORDER BY $key DESC LIMIT $nos";
// echo $query;
$result = mysql_query($query);
return $result;
}
?>

Category : , | Read More...... edit post

nth largest from a table - mySql

0 Replys

In many technical interviews, many of you face the same question as I did. After a long search and try I find out the exact query

It is just like this:



SELECT * FROM <tablename> ORDER BY <fieldname> desc limit <n-1>, 1;



Just check it with your table and comment your findings.

Category : | Read More...... edit post

Category

Followers

FeedCount

Latest Posts

thecoderinc

Subscribe Now

 Subscribe in a reader