SELECT fcl.site, year(fcl.date) as year, month(fcl.date) as month, day(fcl.date) as day, hour(fcl.date) as hour, COUNT(fo.id) AS orders, SUM(fcl.w_init) as w_init, SUM(fcl.w_unique) as w_unique, SUM(fcl.w_close) as w_close
FROM `f_click_log` AS fcl
INNER JOIN `f_orders` AS fo ON fcl.site = fo.client
WHERE fcl.site='".$_val['name']."' AND fcl.date>='".$date_start." 00:00:00' AND fcl.date<='".$date_end." 23:59:59'
GROUP BY fcl.year, fcl.month, fcl.siteSELECT * FROM books
INNER JOIN books_authors ON books.id = books_authors.booksid
INNER JOIN authors ON books_authors.authorid = authors.idINSERT INTO dateplan
(id_tas_dat, date_dat)
VALUES
(16, '2015-09-14'),
(7, '2015-09-14'),
(8, '2015-09-14'),
(10, '2015-09-14'),
(11, '2015-09-14')$inserts = '';
$date = $_POST['data_oper_tas'];
for ($i = 0, $i < count($_POST['check_func']); $i++)
{
$inserts .= '('. $_POST['check_func'][$i] .','. $date .'),';
}
$insertQuery = 'INSERT INTO dateplan (id_tas_dat, date_dat) VALUES ';
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$stmt = $mysqli->prepare($insertQuery . rtrim($inserts, ','));
$stmt->execute();
$stmt->close();-- Генерируем временную таблицу с датами для примера
CREATE TEMPORARY TABLE temp_t (tdate DATE);
INSERT INTO temp_t (tdate) VALUES ('2015-09-02');
INSERT INTO temp_t (tdate) VALUES ('2015-09-02');
INSERT INTO temp_t (tdate) VALUES ('2015-09-05');
INSERT INTO temp_t (tdate) VALUES ('2015-09-05');
INSERT INTO temp_t (tdate) VALUES ('2015-09-07');
-- Пишим процедуру генерации дней. На вход идет дата и по ней генерируется таблица с 1 по последний день месяца этой даты
DROP PROCEDURE IF EXISTS buildMonthTable;
DELIMITER $$
CREATE PROCEDURE buildMonthTable(IN indate DATE)
BEGIN
DECLARE lastday DATE DEFAULT LAST_DAY(indate);
DECLARE startday DATE DEFAULT DATE_ADD(indate, INTERVAL - DAYOFMONTH(indate) +1 DAY);
DROP TABLE IF EXISTS temp_month_days;
CREATE TEMPORARY TABLE temp_month_days (mday DATE);
WHILE (lastday >= startday) DO
INSERT INTO temp_month_days VALUES (startday);
SET startday = DATE(DATE_ADD(startday, INTERVAL +1 DAY));
END WHILE;
END$$
DELIMITER;
-- Вызываем процедуру для генерации таблицы для текущего месяца
CALL buildMonthTable(CURDATE())
-- Соединяем две таблицы и группируем по датам
SELECT tm.mday, COUNT(tt.tdate) AS COUNT FROM temp_month_days AS tm
LEFT JOIN temp_t tt ON tm.mday = tt.tdate
GROUP BY tm.mdayUPDATE woodfun.toys_users AS w, ghujgu.toys_users AS g
SET
w.email = g.email,
w.name1 = g.name1,
w.name2 = g.name2,
и так далее
w.discount = g.discount
WHERE w.id = g.idSELECT CURDATE() - INTERVAL CASE WHEN DAYOFWEEK(CURDATE()) = 1 THEN 13 ELSE DAYOFWEEK(CURDATE()) + 5 END DAY -- start of the previous week
SELECT CURDATE() - INTERVAL CASE WHEN DAYOFWEEK(CURDATE()) = 1 THEN 6 ELSE DAYOFWEEK(CURDATE()) - 1 END DAY -- end of the previous weekSELECT DATE_ADD(CURDATE(), INTERVAL - DAYOFMONTH(CURDATE()) DAY) -- end of the previous month
SELECT DATE_ADD(DATE_ADD(CURDATE(), INTERVAL - DAYOFMONTH(CURDATE()) DAY), INTERVAL - DAYOFMONTH(DATE_ADD(CURDATE(), INTERVAL - DAYOFMONTH(CURDATE()) DAY)) + 1 DAY) -- start of the previous month$userArr = implode(",", $array(66,1,33,54));
SELECT s.id, s.customer_id, s.status, COUNT(s.status) AS count
FROM statuses AS s
INNER JOIN products AS p ON (p.status = s.status_name)
WHERE s.customer_id = 1 AND p.modified BETWEEN "2015-08-10" AND "2015-09-10" AND p.user_id IN ($userArr)
GROUP BY s.status/* Execute a prepared statement using an array of values for an IN clause */
$params = array(1, 21, 63, 171);
/* Create a string for the parameter placeholders filled to the number of params */
$place_holders = implode(',', array_fill(0, count($params), '?'));
/*
This prepares the statement with enough unnamed placeholders for every value
in our $params array. The values of the $params array are then bound to the
placeholders in the prepared statement when the statement is executed.
This is not the same thing as using PDOStatement::bindParam() since this
requires a reference to the variable. PDOStatement::execute() only binds
by value instead.
*/
$sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);'WHERE id in (?,?,?,?,?,?,?)'
и собачить типизированные параметры
->bindParam(1, $id, PDO::PARAM_INT);
->bindParam(2, $id, PDO::PARAM_INT);
...
Но это за тебя сделает
вот эта штука
$place_holders = implode(',', array_fill(0, count($params), '?'));
насколько я понял.-- Временная таблица для разложения qwe на буквы
CREATE TEMPORARY TABLE qwe (letter VARCHAR (10));
INSERT INTO qwe (letter) VALUES('q');
INSERT INTO qwe (letter) VALUES('w');
INSERT INTO qwe (letter) VALUES('e');
-- Временная таблица для разложения rty на буквы
CREATE TEMPORARY TABLE rty (letter VARCHAR (10));
INSERT INTO rty (letter) VALUES('r');
INSERT INTO rty (letter) VALUES('t');
INSERT INTO rty (letter) VALUES('y');
-- Временная таблица для разложения uio на буквы
CREATE TEMPORARY TABLE uio (letter VARCHAR (10));
INSERT INTO uio (letter) VALUES('u');
INSERT INTO uio (letter) VALUES('i');
INSERT INTO uio (letter) VALUES('o');
-- Тестовая таблица для поиска.
CREATE TEMPORARY TABLE abc (letter VARCHAR (10));
INSERT INTO abc (letter) VALUES('u');
INSERT INTO abc (letter) VALUES('qtiskdk');
INSERT INTO abc (letter) VALUES('eyudkdk');
INSERT INTO abc (letter) VALUES('zzzzzzzzz');
-- SELECT CONCAT(qwe.letter, rty.letter, uio.letter, '%') AS pref FROM qwe, rty, uio -- этот запрос вернет cross join для всех сочетаний букв в нужном порядке. Для всех сочетаний будет добавлен знак % в конце.
-- Теперь объединяем две таблицы и выводим только те записи которые удовлетворяют условию LIKE
SELECT * FROM abc AS t
INNER JOIN (SELECT CONCAT(qwe.letter, rty.letter, uio.letter, '%') AS pref FROM qwe, rty, uio) AS c
ON t.letter LIKE c.prefUPDATE wp_posts SET post_content = REPLACE(post_content,'www.domain.com/wp-content/uploads/sites/5','www.domain.com/wp-content/uploads');