Posts Tagged with MySQL

Displaying 1-10 of 27 results.
Wiki: PHP file with array with key value from MySQL table columns generator functionwiki
posted by admin on April 18, 2017

public function array_from_table_generator($table, $id, $name, $prefix = 'tbl_'){
try
{
$sql = "SELECT t.".$id.", t.".$name." FROM ".$prefix.$table. " t ORDER BY t.id DESC";
// it is select query by mysql query or mysqli or by your way
/* $results = SELECT($sql); */ //change this field by your SELECT and
// end of select query
$string = '';
$filename = '../array/'.$table.'.php';

$string .= '<?php $array = '.PHP_EOL.'array('.PHP_EOL;
foreach($results as $key => $value){
$string .= chr(9).'"' . $value[$name] . '" => ' . $value[$id] . ','.PHP_EOL;
}
$string = rtrim(trim($string),',');
$string .= PHP_EOL.');';

$handle = fopen($filename, 'w');
fwrite($handle, $string);
fclose($handle);
echo $prefix.$table . " array generated successfully!
";
}catch ( Exception $e ) {
// send error message if you can
}
}

Usage:

array_from_table_generator('device', 'id', 'name');

Result:
device.php

<?php $array =
array(
"mobile device" => 2,
"desktop" => 1
);
Read more
Resolved: LINUX How to get one database backup from all database backup file
posted by admin on February 20, 2017
I have one MySQL backup, where I have my all server database backups.
I want to get backup only for one db, for example dbname = 'mymysql'Read more
Resolved: Yii2 Migration execution Exception: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
posted by admin on February 15, 2017
I run my Migration in Yii2 from console and get rsult with errors

> create table {{%user}} ... done (time: 0.126s)
> create index index_user_user_id on {{%user}} (user_id) ... done (time: 0.027s)
> create index index_user_role_id on {{%user}} (role_id) ... done (time: 0.026s)
> create index index_user_status_id on {{%user}} (status_id) ... done (time: 0.027s)
> create table {{%status}} ... done (time: 0.033s)
> create table {{%role}} ... done (time: 0.029s)
> add foreign key fk_user_user_id: {{%user}} (user_id) references {{%user}} (id) ...Exception: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
The SQL being executed was: ALTER TABLE `tbl_user` ADD CONSTRAINT `fk_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
... some notes here
#15 {main}
*** failed to apply m170213_124818_init (time: 0.313s)


0 from 2 migrations were applied.

Migration failed. The rest of the migrations are canceled.

My migration

class m170213_124818_init extends Migration
{
public function safeUp()
{
/** User Create*/
$this->createTable('{{%'.helpers\Table::$user.'}}', [
'id' => $this->primaryKey(10)->notNull()->unsigned(),
. . .
'user_id' => $this->integer(10),
. . .
], 'ENGINE=InnoDB');
# User Create

/** User Indexes*/
$this->createIndex("index_user_user_id", '{{%'.helpers\Table::$user.'}}', 'user_id');
# User Indexes

/** User FK */
$this->addForeignKey('fk_user_user_id','{{%'.helpers\Table::$user.'}}', 'user_id', '{{%'.helpers\Table::$user.'}}', 'id', 'CASCADE', 'CASCADE');
# User FK

}

public function safeDown()
{
// to do
}
}

How to resolve Exception

Exception: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
The SQL being executed was: ALTER TABLE `tbl_user` ADD CONSTRAINT `fk_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
Read more
Resolved: PHP check time 00:00:00 from datetime
posted by admin on November 21, 2016
I have datetime from MySQL db in format Y:m:d H:i:s like

2014-11-02 02:04:05
2014-11-02 00:00:00
etc ...

I want to check, if time from datetime equal "00:00:00", then return or echo only date.
Result

2014-11-02 02:04:05
2014-11-02
Read more
Resolved: Hosting iPage.com MySQL connection error: SQLSTATE[HY000] [2002] Connection refused
posted by admin on November 11, 2016
I am trying to run my Yii 1 website and connect to DB, which is not in my hosting iPage.com and have this error:

CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2002] Connection refused

For example, my MySQL config in iPage.com working fine
protected/config/database.php

<?php
return array(
'connectionString' => 'mysql:host=*******.ipagemysql.com;dbname=somename',
'emulatePrepare' => true,
'username' => 'someuser',
'password' => 'somepassword',
'charset' => 'utf8',
);

Another server connection and error
protected/config/database.php

<?php
return array(
'connectionString' => 'mysql:host=anotherhostname;dbname=somename',
'emulatePrepare' => true,
'username' => 'someuser',
'password' => 'somepassword',
'charset' => 'utf8',
);

Error:

CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2002] Connection refused
Read more
Not Resolved: MySQL count where columns fields have same value
posted by admin on November 1, 2016
I have rows, and I want count my select with criteria IN and want to count rows, which is having same values for all rows
Example:

id name lastname age
1 Abc defghij 12
2 Def asdasd 15
3 Ghi qwewqeq 18
4 Abc zzzzz 12
5 Abc yyy 12
6 Abc uuuu 12

IN array

1, 4, 5, 6

Same fields:

`name`, `age`

I want to see RESULTS LIKE
Result SELECT:

id name lastname age
1 Abc defghij 12
4 Abc zzzzz 12
5 Abc yyy 12

Result SELECT COUNT(*):

3
Read more
Resolved: Уникальные пары MySQL из одной таблицы. Distinct pairs in MySQL join (same table)
posted by admin on March 21, 2016
Есть таблица товаров.

CREATE TABLE `goods` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8


Она содержит следующие значения.

`id` `name` 1 Яблоки 2 Яблоки 3 Груши 4 Яблоки 5 Апельсины 6 Груши


Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:

(1,2), (4,1), (2,4), (6,3)
Read more
Wiki: Задача MySQL. Выбрать из таблицы те книги, у которых два и более автораwiki
posted by admin on August 24, 2016
Таблица авторов:

CREATE TABLE `tbl_author` (
`id` int(10) NOT NULL,
`name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `tbl_author`
ADD PRIMARY KEY (`id`);


Таблица книг

CREATE TABLE `tbl_book` (
`id` int(10) NOT NULL,
`name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `tbl_book`
ADD PRIMARY KEY (`id`);


Таблица связей межу таблицей книг и авторов

CREATE TABLE `tbl_book_author` (
`id` int(10) NOT NULL,
`book_id` int(10) NOT NULL,
`author_id` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `tbl_book_author`
ADD PRIMARY KEY (`id`),
ADD KEY `book_id` (`book_id`),
ADD KEY `author_id` (`author_id`);


Решение

SELECT book.id, book.name, COUNT(ba.author_id) AS cnt FROM `tbl_book_author` ba
LEFT JOIN tbl_book book
ON book.id = ba.book_id
GROUP BY ba.book_id
HAVING cnt > 1
Read more
Wiki: Joining Three or More Tables SYNTAXwiki
posted by admin on June 18, 2016
Although each join specification joins only two tables, FROM clauses can contain multiple join specifications. This allows many tables to be joined for a single query.

The ProductVendor table of the AdventureWorks2008R2 database offers a good example of a situation in which joining more than two tables is helpful. The following Transact-SQL query finds the names of all products of a particular subcategory and the names of their vendors:

SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;
Read more
Resolved: MySQL Like string in multiple value by one script
posted by admin on June 18, 2016
I always thought that you could use OR in a LIKE statment to query things in MySQL. So, if I wanted to compare multiple fields in a row to 1 keyword or term:

SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword%';

and if I had an array of words to compare:

SELECT * FROM MyTable WHERE Column1 OR Column2 LIKE '%keyword1%'
AND Column1 OR Column2 LIKE '%keyword2%';

I don't believe that syntax is correct, however. Is there an efficient method of writing this aside from something like:

SELECT * FROM MyTable WHERE Column1 LIKE '%keyword1%' OR Column2 LIKE
'%keyword1%' AND Column1 LIKE '%keyword2%' OR Column2 LIKE '%keyword2%';
Read more