Mysql - phponwebsites.com

Showing posts with label Mysql. Show all posts

26 Mar 2014

mysql_fetch_row in php and mysql

Mysql_fetch_row() return row as numeric array. We can't use associative indices to retrieve data from mysql table using php.

Consider following example:

Retrieve data from table using mysql_fetch_row() in php:

You can retrieve data from mysql table using mysql_fetch_assoc() in php. The following php script is used for retrieve data.

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1') or die(mysql_error());
while($res=mysql_fetch_row($query))
{
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res[2].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

Where,
the result of array should be like this:
Array(
[0] => 1
[1] => Adventures
[2] => Achilles
)

Suppose you'll use following php script,
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use associative index instead numerical, then you will get undefined index error like as follows as:

Where,
'game_Title' is the field name of column. You can't use $res['game_title'] here. Because you retrieved data from table using mysql_fetch_row().

Related Post:

Retrieve data from mysql database using mysql_fetch_assoc in php
Retrieve data from mysql database using mysql_fetch_array() in php

24 Mar 2014

mysql_fetch_assoc in php and mysql

Mysql_fetch_assoc() return row as an associative array. Here, we can use numeric indices to get values from mysql data using php.

Consider following example:

Retrieve data from table using mysql_fetch_assoc() in php:

You can retrieve data from mysql table using mysql_fetch_assoc() in php. The following php script is used for retrieve data.

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?
php mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1') or die(mysql_error());
while($res=mysql_fetch_assoc($query))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

Where,
the result of array should be like this:
Array(
[game_ID] => 1
[category_Name] => Adventures
[game_Title] => Achilles
)

Suppose you'll use

echo'<tr><td>'.$res[0].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use numeric index instead associative, then you will get undefined offset error like as follows as:

Where,
$res[0] represents the first column of mysql table. But you can't use this to retrieve data from table. Because you used mysql_fetch_assoc() mysql function to retrieve data.

Related Post:

Retrieve data from mysql database using mysql_fetch_row in php
Retrieve data from mysql database using mysql_fetch_array() in php

19 Mar 2014

Avoid duplicate values storing in table using php and mysql

We can avoid duplicate values storing in table when we insert data to table. Because the duplicate entry is one of the main problem, while insert data into mysql table . We removed duplicate values using 'DISTINCT' key in mysql query, if it is present in table. We can check whether the value is present in table or not using PHP and Mysql before add data to table.

We can also avoid duplicate values storing in mysql table while inserting by primary key.

Consider following example. You've a database named with 'new' and table named with 'user'. You try to add names in user table. Now you've to check the values is present or not while you inserting. The table look like this:

Avoid duplicat values sotring in table using PHP at phponwebsites

Now you try to add name 'guru' again. You can check whether the name 'guru' is present in table or not using below PHP script.

<?php
if(isset($_POST['submit']))
{
mysql_connect('localhost','root','');
mysql_select_db('new');
$name=$_POST['name'];
$query=mysql_query("select * from user where name='".$name."' ") or die(mysql_error());
$duplicate=mysql_num_rows($query);
if($duplicate==0)
{
$query1=mysql_query("insert into user values('".$name."')") or die(mysql_error());
}
else
{
echo'The name '.$name.' is already present in the user table';
}
}
?>
<html>
<body>
<form method='post' action='#'>
name: <input type='text' name='name'>
<input type='submit' name='submit' value='Submit'>
</form>
</body>
</html>

Where,
mysql_query() - execute the query and select if any similar values are present in table. mysql_num_rows() - returns numeric value of selected rows. If its value is 0, then there is no similar values present in table. Otherwise the value is already present in table.
Where,
you can also use mysql_fetch_row() instead of mysql_num_rows().
If the value is present in table, it could not be stored in table. Now you can avoid duplicate entries in table using PHP and Mysql.

Related Post:

How to store values into mysql table using php

13 Mar 2014

Insert data into mysql table using php

You can insert values to mysql table using php. The following mysql query is used for insert data into table.
INSERT INTO table_name VALUES(values datatype(length))

Store values into mysql table using php:

The following php script is used to store values into mysql table.

<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query("insert into table1 values(1,'sample1', 'sample1.jpg')") or die(mysql_error());
if($query)
{
echo 'Vaules are inserted into table';
}
else
{
echo'Vaules are not inserted';
}
?>

Note:
If you declared field name as varchar, then you must insert values between single quotation marks ( 'sample1').

Now the values are inserted into table. You can see it in your mysql table at phpmyadmin. Your mysql table look like this:

You inserted values to your table using php.

Get values from user and insert into mysql table using php:

Suppose you want to get values from user. First you have to know how to get values from text boxes.

How to get values from textbox using php:

$name=$_POST['name']; if(method is post)
or
$name=$_GET['name']; if(method is get)
or
$name=S_REQUEST['name']; if(method is either post or get)

where,
S_POST[] is used to get values from textbox, If you use POST method.
S_GET[] is used to get values from textbox, If you use GET method.
S_REQUEST[] is used to get values from textbox, If you use either GET or POST method.

Then the following php code is used to insert values into table.

Where,
the values will get from users using post method like as below:
$_POST['textbox_name']

Now you run this file. Your display look like this:

Then you can get values, if user type anything in textbox. Now i added values as like this:

Get values from user and stored into mysql table using php

Now you'll get output like this:

Your mysql table have user inserted values.

Related Post:

How to avoid duplicate values storing in mysql table using php and mysql

6 Mar 2014

Create mysql table using php

You can create mysql table using php. The following mysql query is used to create mysql table.
CREATE TABLE table_name(field_name datatype(length))

The php script as follows as:

<?php
mysql_connect('localhost','root','');
mysql_select_db('new');
$query=mysql_query("create table table1(id int(5),name varchar(40),img varchar(30))") or die(mysql_error());
if($query)
{
echo 'Table is created';
}
else
{
echo'Table is not created';
}
?>

Where,
- mysql_connect() is used to connect the database.
- 'localhost' is server name.
- 'root' is user name of database.
- '' represent the password of database.
- mysql_select_db() is used to select the database for use.
- 'new' is the database new.
- mysql_query() is used for execute the mysql query.
- mysql_error() gives error message when the mysql query is not running successfully.

Now the mysql table 'table1' is created. You can see it in your mysql database at phpmyadmin.
Your mysql database should be like this:

Create mysql table using CREATE TABLE mysql command in php

Now you created your mysql table.

Related Post:

How to connect mysql database using php
How to create mysql database using php

24 Feb 2014

Connect mysql database using php

You must connect mysql database to insert and retrieve add from it. In php, you can connect mysql server easily. It needs only two steps.

1. Open connection to mysql server.
2. Select database to add and retrieve data from mysql.

Open connection to mysql:

You must open connection to mysql server to connect database.

mysql_connect():

The mysql_connect() function is used to open connection on mysql. The syntax for open connection in mysql as follows as:

<?php

mysql_connect('localhost','root','');

where,

'localhost' is the server name.

root is the user name of mysql server. The 'root' is default username in mysql.

'' is the password of mysql server. The empty is default password in mysql.

Select database in mysql using php:

After opened connection to mysql database, you've to select database to add and retrieve data from it.

mysql_select_db():

It is used to select the database in mysql server. The syntax for select database in mysql as follows:

<?php

mysql_select_db('new');

where,

'new' is the database name in mysql.

Connect mysql database in php:

Now combined the above two codes to one for connect mysql database using php. The php code as follows as:

<?php

mysql_connect('localhost','root','') or die('Cannot connect mysql server');

mysql_select_db('new') or die('cannot connect database');

Now you can check whether the connection is opened or not to mysql. You got error message if either mysql server is not connect or database is not selected.

Related Post:

How to create mysql database using php
How to create mysql table using php

21 Feb 2014

Create mysql database using php

All of you know, create mysql database in phpmyadmin. Now we are going to know how to create database using php.

The mysql query is:
CREATE DATABASE db_name

Then you use the particular database. The mysql query is:
USE db_name

The php coding as follows as:

<?php
mysql_connect('localhost','root','');
$query=mysql_query('create database new') or (mysql_error());
$qu=mysql_query('use new') or (mysql_error());
if($query)
{
echo'Database is created';
}
else
{
echo'Database is not created';
}
?>

Where,
- mysql_connect() is used to connect the database.
- 'localhost' is server name.
- 'root' is user name of database.
- '' represent the password of database.
- mysql_query() is used for execute the mysql query.
- 'new' is the database new.
- mysql_error() give error message while the mysql query is execute.
- die() terminate the excution.

Now you'll get output as " Database is created ". Then you can see the new database in your phpmyadmin.

create database in mysql using php at phponwebsites

The database 'new' is created and ready for use.

Related Post:

How to connect mysql database using php
How to create mysql table using php

17 Feb 2014

Reset auto increment initial value in mysql

Auto_ increment is used to increase by 1 while insert a new data to mysql table. The auto increment column must be defined as key. By default auto_increment is start with 1. Suppose you want to auto_increment starts with 'N' values. Then you can change the auto_increment initial value in mysql. IT is possible. First you create table with auto_increment. Then use following mysql query :

Alter table table_name AUTO_INCREMENT=N

Where, N can be any numbers like 100,1000,10000...

Create table with auto_increment in mysql:

You can add auto_increment while creating new table in mysql. The mysql query is:

CREATE TABLE table1

(No int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,

Game_name varchar(15), Image varchar(15))

Now the table is created with auto increment. If you add any values, then auto_increment starts with 1 in mysql. Consider following example. You add 3 values to mysql table. ie,

insert into table1 values('','sample1','sample1.jpg')
insert into table1 values('','sample3','sample2.jpg')
insert into table1 values('','sample3','sample3.jpg')

Now your table look like this:

reset auto increment initial value in mysql

Change auto_increment initial value in mysql:

You change auto_increment initial value to 100. The mysql query is:

Alter table table1 AUTO_INCREMENT=100

Now you insert data to your mysql table. Then it look like this:

change auto increment initial value in mysql

Now you can see the row starts with 100. You can changed starting value of auto_increment in mysql

Related Post:

distinct in mysql
Order by in mysql
Group by multiple columns in mysql
How to use order by together with group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql
Primary key in mysql

12 Feb 2014

Primary key in mysql

The primary key in mysql is used to identify the unique values in table. It don't allow duplicate entries in mysql table. The primary key column should be NOT NULL. It don't allow null values in mysql table. The mysql table can have only one primary key.

How to create primary key in mysql:

The mysql query for create primary key is:

Create table table_name (column_name datatype(length), primary key(column_name))

For example,
create table table1(name varchar(30), address varchar(100), primary key(name))

Where the column 'name' is primary key.

How to add primary key to existing table in mysql:

You created table without primary like this:

create table table1(name varchar(30),address varchar(100))

But you need to add primary key in order to remove duplicate entries in mysql. At the time you can add primary key to existing table in mysql. Following mysq query is used for add primary key.

Alter table table_name add primary key(column_name)

For example,

Alter table table1 add primary key(num)

How to remove primary key in mysql:

You created table with primary. But you need not primary key for particular table. Then how can remove primary key from table in mysql. The following mysql query is used to drop the primary key from table in mysql.

alter table table_name drop primary key

For example,

alter table table1 drop primary key

where,

The primary key is removed from table 'table1'. Now you can add duplicate values to your mysql table.

Related Post:

6 Feb 2014

Remove auto_increment from column in mysql

You created a table in which the column with specific definitions in mysql. You created table column with auto increment. To add auto increment to existing column, visit add-auto-increment-to-existing-column in mysql. Then how can you remove it from column in mysql. The following mysql query is used to remove auto increment from column in mysql.

Mysql Query:

Alter table table_name DROP PRIMARY KEY,
change column_name column_name datatype(length) definition

Consider the example:
The structure of table1 in database new look like this:

remove auto increment from column in mysql using alter, drop and change mysql command

Now we are going to remove auto increment from column 'No' in mysql table 'table1'. The mysql query is:

Alter table table1 DROP PRIMARY KEY,
change No No int(5) NOT NULL

Now your structure look like this:

remove auto increment from column in mysql

Now you can see the column name 'No' is normal. There is no auto increment.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to reset auto increment initial value in mysql
How to use order by before group by in mysql
Select distinct values in mysql

3 Feb 2014

Select distinct values in mysql

If your column in mysql table contains duplicate values, then you need to display distinct values by mysql command 'DISTINCT' and 'GROUP BY'.

Select unique values in Mysql by distinct:

The mysql command 'DISTINCT' is used to select distinct values. The mysql query for select distinct values as follows as:

SELECT DISTINCT column_name FROM table_name

Consider the following table. table1 is name of this table.

Now we are going to get unique values in column 'id' by 'DISTINCT' mysql command like this:

SELECT DISTINCT id FROM table1

Now you'll get output like this:

select unique values using distinct in mysql

You can see the table 'table1' which have unique values.

Select unique values in Mysql by 'Group by':

You can also display unique values using 'GROUP BY' mysql command. If you want to select distinct values from one column and also other values from table, then you can use mysql command 'GROUP BY'. The mysql query is:

SELECT * FROM table_name GROUP BY column_name

Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

SELECT * FROM table1 GROUP BY id

Now you'll get output like this:

display unique values using group by in mysql

You can see the column 'id' values in mysql table 'table1' are unique.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

How to remove auto increment from column in mysql
How to reset auto increment initial value in mysql

31 Jan 2014

Use order by before group by in mysql

Normally you can use order by before group by in mysql. Now the question is rise, can you use order by before group b in mysql? Yes you can use order by before group by in mysql. It is possible. You've to use subquery for this. ie,

SELECT * FROM (
SELECT * FROM `table` ORDER BY column_name DESC
) t
GROUP BY column_name
Where,
t is alias because you use subquery. If you don't add it, then you'll get error message "Every derived table must have its own alias".

Group by in Mysql:

Consider the following table. table1 is name of this table.

Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

SELECT * FROM table1 GROUP BY id

Now you'll get output like this:

You can see the column 'id' values in mysql table 'table1' are unique. The id value 1 are present 1st, 2nd and 5th row. But it takes only the first row.

Use order by before group by:

On above example, you want to select highest count of each id and id should be unique. Then you have to use subquery to select highest count values. The mysql query as follows as:

SELECT * FROM (
SELECT * FROM `table1` ORDER BY count DESC
) t
GROUP BY id

where,
SELECT * FROM `table1` ORDER BY count DESC run first. It ordered the data in descending order by count.
SELECT * FROM ( ... ) t GROUP BY id it takes unique values.
Now you'll get output like this:

Now you'll get id with highest count values.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

26 Jan 2014

Add auto_increment to existing column in mysql

Auto_ increment is used to increase by 1 while insert a new data to mysql table. The auto increment column must be defined as key.

Create table with auto_increment in mysql:

You can add auto_increment while creating new table. The mysql query is:

CREATE TABLE table1

(No int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,

Game_name varchar(15), Image varchar(15))

Now the table is created with auto increment.

Add auto_increment to existing column in mysql:

You created a table in which the column with specific definitions in mysql. You created table column without auto increment. Then how can you add it to existing column in mysql. The following mysql query is used to add auto increment to existing column in mysql.

Mysql Query:

Alter table table_name modify column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

Consider the example:
The structure of table1 in database new look like this:

Add auto increment to existing column in mysql

Now we are going to add auto increment to column 'No' in mysql table 'table1'. The mysql query is:

Alter table table1 modify No int(5) AUTO_INCREMENT PRIMARY KEY

Now your structure look like this:

Now you can see the column name 'No' is auto increment. Suppose your mysql query like below:

Alter table table1 modify No int(5) AUTO_INCREMENT

Then you'll get error like this:

mysql error while adding auto increment in mysql

So you should be add 'AUTO_INCREMENT' with 'PRIMARY KEY' in mysql table.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to remove auto increment from column in mysql
How to reset auto increment initial value in mysql

how to use order by before group by in mysql

23 Jan 2014

Add primary key to existing column in mysql

Primary key is used to avoid duplicate entries in mysql table. A mysql table can have only one primary key.

Create table with primary key in mysql:

The mysql query for create table with primary key is:

CREATE TABLE table_name(column_name datatype(length),
PRIMARY KEY(column_name))

For example,
CREATE TABLE table1(No int(5) datatype(length), ,Game_name varchar(15), Image varchar(15), PRIMARY KEY(No))

Now the table column 'No' is primary key in this table.

How to add primary key to existing column in mysql:

You can add primary while creating new table. But can you add primary key to existing column in mysql? Yes, you can add primary key to existing column in mysql. The mysql query is:

Alter table table_name add PRIMARY KEY(column_name)

Consider the example:
table1 is name of this table.

changing column size in mysql at phponwebsites

When you click the structure of table in your database, it is look like this:

There is no index defined. Suppose you've to add primary key to column 'Game_name'. On that time you can add the primary key. The mysql query is:

Alter table table1 add PRIMARY KEY(Game_name)

Now you click the structure of table in mysql database, the index is defined as below:

add primary key existing column in mysql

Now your Game_name column is changed from NULL into NOT NULL with primary key. Because the primary key should not be null. Only one primary key in a mysql table. A mysql table haven't more than one primary key.

Related Post:
Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

19 Jan 2014

Change column with NOT NULL to NULL in mysql

You created a table in which the column with 'NOT NULL'. Suppose you want to change mysql column definition from 'NOT NULL' to 'NULL'. Then how can you change it in mysql. The following mysql query is used to change column from 'NOT NULL' to 'NULL' in mysql.

Mysql Query:

Alter table table_name
change column_name column_name datatype(length) definition

Change column NOT NULL to NULL in mysql:

Consider the example:
The structure of table1 in database new look like this:

Where, the column name 'No' is NOT NULL. You can see the Null column value is No in above mysql table at first row. Now we are going to change column 'NOT NULL' to 'NULL' in mysql table 'table1'. The mysql query is:

Alter table table1 change No No int(5) NULL

Now your structure look like this:

Now you can see the column name 'No' is NULL.

Change column NULL to NOT NULL in mysql:

Similarly you can change the column definition from NULL to NOT NULL. The mysql query as follows as:

Alter table table1 change No No int(5) NOT NULL

Now the column 'No' changed NULL to NOT NULL.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
How to remove auto increment from column in mysql
how to add auto increment to existing column in mysql

16 Jan 2014

Rename table in mysql

You can change the column name in mysql. Can you change table name in mysql. Yes you can rename mysql table. The mysql query for rename table is:

RENAME TABLE old_table_name TO new_name

Consider the following example: The table list in mysql database 'new' is below.

Now we are going to change the table name 'table1' to table4. The mysql query as follows as:

RENAME TABLE table1 TO table4

Now your table list should be like this:

Now the table name 'table1' is changed into 'table4' in mysql

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to remove auto increment from column in mysql

10 Jan 2014

Difference between the delete, truncate and drop in mysql

DELETE in MySql:

1. The mysql command 'DELETE' is used to delete space allocated by mysql server and the structure of table remains same.

2. It is removed rows.

3. It can be used by either 'WHERE' clause or without it in mysql.

4. The data can be roll backed, if it is removed by 'DELETE' mysql command. If the transaction is used which is yet committed before delete the row, then you can roll backed again.

The mysql query for delete paricular row:

DELETE FROM table_name WHERE column_name = condition

Consider the following example:

table1 is name of this table. We are going to delete particular row in mysql using 'DELETE' mysql command. The mysql query as follows as:

DELETE FROM table1 WHERE id = 3

Now you'll get output like this:

delete particular row in mysql using delete command

You can delete whole rows in table. But the structure of table is present in mysql database.
The mysql query for delete table is:

DELETE FROM table1

Now the mysql table values are deleted from mysql database. Suppose you click table1 in mysql database, then you'll get output in database like this:

TRUNCATE in MySql:

1. Remove rows from mysql table but the structure of table remains same.

2. The data cannot be roll backed if it is deleted by 'TRUNCATE' mysql command.

3. It can be used by only without 'WHERE' clause in mysql.

The mysql query for 'TRUNCATE' as follows as:

TRUNCATE TABLE table_name

DROP in MySql:

1.Remove whole table from mysql database.

2. The data cannot be roll backed if it is dropped by 'DROP' mysql command.

The mysql query for 'DROP' as follows as:

DROP TABLE table_name

Consider following example. The tables in databases like this:

Now drop a table 'table1' using 'DROP' mysql command.

DROP TABLE table1

Now you'll get output like this:

Now the table 'table1' removed from mysql table list
Related Post:

distinct multiple columns in mysql
Order by multiple columns in mysql
Group by multiple columns in mysql
How to use order by together with group by in mysql
how to use order by before group by in mysql
Primary key in mysql

Pages

26 Mar 2014

Retrieve data from table using mysql_fetch_row() in php:

24 Mar 2014

Retrieve data from table using mysql_fetch_assoc() in php:

19 Mar 2014

13 Mar 2014

Store values into mysql table using php:

Get values from user and insert into mysql table using php:

How to get values from textbox using php:

6 Mar 2014

24 Feb 2014

Open connection to mysql:

mysql_connect():

Select database in mysql using php:

mysql_select_db():

Connect mysql database in php:

21 Feb 2014

17 Feb 2014

Create table with auto_increment in mysql:

Change auto_increment initial value in mysql:

12 Feb 2014

How to create primary key in mysql:

How to add primary key to existing table in mysql:

How to remove primary key in mysql:

6 Feb 2014

3 Feb 2014

Select unique values in Mysql by distinct:

Select unique values in Mysql by 'Group by':

31 Jan 2014

Group by in Mysql:

Use order by before group by:

26 Jan 2014

Create table with auto_increment in mysql:

Add auto_increment to existing column in mysql:

23 Jan 2014

Create table with primary key in mysql:

How to add primary key to existing column in mysql:

19 Jan 2014

Change column NOT NULL to NULL in mysql:

Change column NULL to NOT NULL in mysql:

16 Jan 2014

10 Jan 2014

DELETE in MySql:

TRUNCATE in MySql:

DROP in MySql: