Mysql insert multiple/batch of rows using single query

You don't need to loop a mysql insert query to add multiple records to a table. mysql insert support multiple inserts in one query.

Let say you have a table called students with two fields fname and age.

insert into students (fname, age) values
('Kusal', '26'),
('Tom', '18'),
('Ann', '23')

Remember to give the column names in the query.

Best way to get all rows in a mysql table - mysql_num_rows() or count()

Normally there are two ways to get the total number of rows in a mysql table.

$result = mysql_query("select count(id) from table");
$data = mysql_fetch_array($result);
$all_rows = $data[0];

Or

$result = mysql_query("select id from table");
$all_rows = mysql_num_rows($result);

As you have guessed the best server resource friendly method is to use mysql count() function instead of returning all the rows as a result-set to use in php mysql_num_rows() function.

Mysql Joins - All records from left, One record from right table

t1 table


+----+--------+-------+
| id | name   | age   |
+-------------+-------+
|  1 |  kusal | 25    |
|  2 |  saman | 20    |
+----------+----+-----+


t2 table


+----------+----+------------+
| image_id | id | image_path |
+----------+----+------------+
|        1 |  1 | path1      |
|        2 |  1 | path2      |
|        3 |  1 | path 3     |
|        4 |  2 | path 1     |
|        5 |  1 | path 4     |
|        6 |  2 | path 5     |
+----------+----+------------+


SELECT * FROM t1 a left join t2 b on a.id = b.id group by b.id



+----+-------+-----+----------+------+------------+
| id | name  | age | image_id | id   | image_path |
+----+-------+-----+----------+------+------------+
|  1 | kusal |  25 |        1 |    1 | path1      |
|  2 | saman |  21 |        4 |    2 | path 1     |
+----+-------+-----+----------+------+------------+

Using group by for the right table id, it will only get the one result from right table.

phpMyAdmin mysql table overhead

If you are using phpmyadmin to manage mysql databases and tables you have probably seen a red figure for overhead in the space usage section in a table's detailed view.

This is a usual thing when you have lot of deletes and updates in your table fields. Think overhead as empty space and mysql want it to be defragmented. You can simply use the Optimize table link to get rid of the overhead.

How to change Mysql database storage engine

The default Mysql database storage engine is MyISAM. But if you want to implement foreign key constraints you should have the InnoDB storage engine. Lets see how we can change it. I will use the phpMyAdmin to do the operation.



Select the table you want and press the "Operations" tab.






Now select a Storage Engine you like from the drop down menu and press "Go" button.

Right date format to store in mysql - YYYY-MM-DD HH:MM:SS

When using the php date function you can format the date in lot of ways.
But when you store date and time in mysql it is really convenient to store date in the mysql friendly format YYYY-MM-DD HH:MM:SS
If you use the above format you can easily use mysql date functions to manipulate and do calculation on stored dates without using php functions.

Example functions
DATEDIFF() - Subtract two dates
ADDDATE() - Add dates
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

How to list all tables in a mysql database using php

Following code will show you how to get all the table names inside a given mysql database. to do this you only need know about the mysql query "SHOW TABLES".
following example show you how to retrieve query the result using php.

<?php

//set your DB connection
mysql_connect('localhost', 'root', '');
//select your DB
mysql_select_db('test');

$query = "SHOW TABLES";
$result = mysql_query($query);

while($data = mysql_fetch_array($result))
{
 echo $data[0];
 echo '<br />';
}

?>

How to find the table structure of a mysql table in php

You can use sql command describe to get the table structure of a mysql table.
It will return six information for each table attribute (column).
Field, Type, Null, Key, Default, Extra

<?php

//include your DB connection

$query = "describe your_table_name";
$result = mysql_query($query);

//loop through each table column attribute
while($data = mysql_fetch_array($result))
{  
 echo $data['Field'].', '; //field name
 echo $data['Type'].', ';//feild type
 echo $data['Null'].', ';// null or not
 echo $data['Key'].', ';//primary key or not
 echo $data['Default'].', ';//default value
 echo $data['Extra'];// like auto increment
 echo '<br />';
}

?>

InstantRails 2.0 - Default Database is SQLite3, How to change into mysql

InstantRails 2.0 or higher versions comes with the SQLite3 as the default database,
So a command like this rails my_app will take SQLite3 as the default database for the Rails application.

To use mysql try the following command:
rails -d mysql my_app

extra parameter -d will define database

Get Free Sinhala IT Learning Videos Kuppiya.com

How to convert Unix Timestamp to Mysql Timestamp - Time() to String using php

If you want to convert the Unix timestamp into a string like mysql timestamp you can take a look at following code. Unix timestamp is not human readable so there are situations where you need to convert it into a human readable form.

<?php

$time_now = time();

echo $time_now; //output example: 1219848654
echo '<br />';

$str_time = date('Y-m-d H:i:s', $time_now);

//look at php date function for more options 

echo $str_time; // 2008-08-27 14:50:54

?>

Get Free Sinhala IT Learning Videos Kuppiya.com

how to get a specific row in a large result set in mysql using php

When you work with a large result set you may need to get a specific row from the result set. When in a such situation you can use mysql_result().

$result = mysql_query('SELECT * FROM tbl_name');
echo mysql_result($result, 5); // output 6th row 

Get Free Sinhala IT Learning Videos Kuppiya.com

 Subscribe To My Blog

How to get Max ID row from mysql table using php

Do you need to find the max id of a mysql table? check out the following code.
simply use the sql MAX function to find the max id.

<?php

mysql_connect("localhost","root","");
mysql_select_db(test);

$q = "select MAX(id) from tbl_name";
$result = mysql_query($q);
$data = mysql_fetch_array($result);

echo $data[0];

?>

How Mysql TIME data type works with php

Lets see what is the format that is accepted by Mysql TIME data type.
Normal format: HH:MM:SS
Extended hour format: HHH:MM:SS - This is larger than 23 hours because its used to store time between two points.

It's better if you use above formats to store your time manually

INSERT INTO your_tbl VALUES ('14:26:05')

Get Free Sinhala IT Learning Videos Kuppiya.com

 Subscribe To My Blog

How to get the date from MySql TIMESTAMP column using php

In mysql you can use a TIMESTAMP type to store both date and time in a table column in this format YYYY-MM-DD HH:MM:SS
Sometimes you need to get only the date part from the TIMESTAMP.
You can use mysql date() function to do this

<?php

$today = date("Y-m-d"); //todays date

$query = "select * from tbl where DATE(c_timstp) =  $today";

$result = mysql_query($query);

?>

Get Free Sinhala IT Learning Videos Kuppiya.com

How to find duplicate rows in mysql using php

There are times when you need to find duplicate rows or records in a mysql table.
This mostly happens when you import data sources from out side(CSV,EXCEL,SQL)
Following code will show you how to find duplicates,

select count(*) as num, ID, Name from table
group by ID,Name
having count(*) > 1

This code will group the given column and see if the grouped columns have more than one record

+-----+------+------------+
| num |ID    | Name       |
+-----+------+------------+
|   3 |    1 | Kuppiya    |
+-----+------+------------+
|   2 |    3 | Apple      |
+-----+------+------------+

How to get distinct(unique) rows from mysql table using php

The simple way to get the unique values from a mysql table is to use DISTINCT keyword in your mysql query.

SELECT DISTINCT col_name FROM tbl_name

OR

SELECT DISTINCT col_name, col_name2 FROM tbl_name

How to select a random row from mysql table

Are you looking a way to get a random record from a mysql table?, want to do it by single and a simple query? if the answer is yes, here it is

SELECT * FROM table_name ORDER BY RAND() LIMIT 0,1

You can use mysql rand() function to easily get a random data row.
but be warned that for large data tables this function is said to be slow.

If you like to learn more about SLIIT please visit www.sliit.lk

 Subscribe To My Blog