SELECT Q.*, T.`name` as `last_name`
FROM `people` as Q
RIGHT JOIN `details` as T
on Q.id = T.people_id
WHERE MATCH (Q.name) AGAINST ('jon Stephen Jack' IN NATURAL LANGUAGE MODE)
LIMIT 10
Category Archives: MySQL
Basic php crud operation to manage mysql database using pdo
<?php
class db extends PDO {
public $pid = 'id'; // $pid is a primaty key of a table
function __construct($dsn, $username, $passwd, $options = array()) {
parent::__construct($dsn, $username, $passwd, $options);
}
/*
* Function 'get_data_by_id' return single table row by an id
* Arguments:
* 1st: Table name. example: 'images'
* 2nd: Row id to get the result, id must be a primary key of a table
* 3rd: Return type 'object' or 'array', Default is object
* Returns the row if found
*
* $row = $db->get_data_by_id('images', 3, 'array');
*
*/
function get_data_by_id($tablename, $id, $output_type = 'object') {
$stmt = $this->prepare("SELECT * FROM $tablename WHERE $this->pid=?");
$stmt->execute(array($id));
if ($output_type == 'object') {
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
} else {
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
return $rows;
}
/*
* Function 'get_all_datas 'return table rows by any condition
* Arguments:
* 1st: Table name. example: 'images'
* 2nd: Take associative array of condition. example: array('id'=>3)
* 3rd: Return type 'object' or 'array'. Default is object
* Returns the rows if found
*
* $param = array(
* 'id' => 3,
* );
* $row = $db->get_all_datas('images', $param, 'array');
*
*/
function get_all_datas($tablename, $param = array(), $output_type = 'object') {
if (!empty($param)) {
$keys = '';
$values_ = array();
$i = 1;
foreach ($param as $key => $value) {
if (!empty($key)) {
($i == 1) ? $keys = $keys . "$key=?" : $keys = $keys . "AND $key=?";
}
$i++;
}
$values_ = array_values($param);
$stmt = $this->prepare("SELECT * FROM $tablename WHERE $keys");
$stmt->execute($values_);
} else {
$stmt = $this->prepare("SELECT * FROM $tablename");
$stmt->execute();
}
if ($output_type == 'object') {
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
} else {
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
return $rows;
}
/*
* Function 'insert' insert values to a table by condition
* Arguments:
* 1st: Table name. example: 'images'
* 2nd: Take associative array of values, array key must be same as table column name. example: array('city'=> 'Dhaka', 'name'=>'Mr. Jonson').
* Returns last inserted id of a table.
*
* $param = array(
* 'caption' => 'Edited',
* 'original_file_name' => 'bangladehbeautyful.png',
* 'file_name' => '54458888beautyful.png'
* );
* $last_inserted_id = $db->insert('images', $param);
*
*/
function insert($tablename, $param) {
$keys = '';
$values = '';
$values_ = array();
$i = 1;
foreach ($param as $key => $value) {
if (!empty($key)) {
($i == 1) ? $keys = $keys . "`$key`" : $keys = $keys . ", `$key`";
($i == 1) ? $values = $values . "?" : $values = $values . ", ?";
}
$i++;
}
$values_ = array_values($param);
$sql = "INSERT INTO $tablename($keys) VALUES($values)";
$stmt = $this->prepare($sql);
$stmt->execute($values_);
return $this->lastInsertId();
}
/*
* Function 'update' update values to a table by id
* Arguments:
* 1st: Table name. example: 'images'
* 2nd: Take an id of a table, id must be a primary key of a table.
* 3rd: Take associative array of values, array key must be same as table column name. example: array('city'=>'Dhaka', 'name'=>'Mr. Jonson').
* Returns the counter how much row updated.
*
* $param = array(
* 'caption' => 'Edited',
* 'original_file_name' => 'bangladehbeautyful.png',
* 'file_name' => '54458888beautyful.png'
* );
* $row_effected = $db->update('images', 2, $param);
*
*/
function update($tablename, $id, $param) {
$keys = '';
$values = '';
$values_ = array();
$i = 1;
foreach ($param as $key => $value) {
if (!empty($key)) {
($i == 1) ? $keys = $keys . "$key=?" : $keys = $keys . ", $key=?";
}
$i++;
}
$values_ = array_values($param);
array_push($values_, $id);
$sql = "UPDATE $tablename SET $keys WHERE $this->pid=?";
$stmt = $this->prepare($sql);
$stmt->execute($values_);
return $stmt->rowCount();
}
/*
* Function 'delete' delete the table row by id
* Arguments:
* 1st: Table name. example: 'images'
* 2nd: Take an id of a table, id must be a primary key of a table.
* Returns the counter how much row updated.
*
* $row_effected = $db->delete('images', 2);
*
*/
function delete($tablename, $id) {
$sql = "DELETE FROM $tablename WHERE $this->pid=:id";
$stmt = $this->prepare($sql);
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
return $stmt->rowCount();
}
}
$HostName = 'localhost';
$dbName = 'dbname';
$username = 'root';
$password = '';
$db = new db("mysql:host=$HostName;dbname=$dbName;charset=utf8", $username, $password);
//$db->id = 'id';
?>
How to create auto updated/modified and created columns of a table in mysql
Your table should look like:
CREATE TABLE `new_table` (
`id` bigint(20) NOT NULL auto_increment,
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`created` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
)
To get the modified/created column changes you need to pass null value while updating or inserting values in a table. Please see the below example to make sense
/* Insert query: */
$pdata = array(
'modified' => null,
'created' => null
);
$val = $this->db->insert('new_table', $pdata);
/* update query: */
$pdata = array(
'modified' => null
);
$this->db->where('id', $id);
$val = $this->db->update('new_table', $pdata);
How to import and export database from mysql by command prompt at windows
How to import and export database from mysql by command prompt at windows
First you need to go mysql\bin\ folder at after opeaning command prompt.
Export command :
 mysqldump -h localhost -u username -p password database_name > new_downloaded_database_name.sql
Import command :
Put your imported database to mysql\bin\ folder,then run the following command
mysql -u username -p password new_database_name < database.sql
Md. Ziyed Uddin 