This is feasible … mysql do it easy no expensive… entries of first logs table are 100,000 per day… ? Btw same query will group by id and sum up/count in each result the number of same ids grouped, views/visits… well
SELECT SUBSTRING(url,INSTR(url,'php?Id=')+7,6) AS id, COUNT(*) AS views
FROM logtable
WHERE SUBSTRING(url,INSTR(url,'php?Id=')+7,6) REGEXP '[[:digit:]]+' GROUP BY id
the group by won’t accept the alias - you’ll need to have the substring in there as well
SELECT SUBSTRING(url,INSTR(url,'php?Id=')+7,6) AS id
, COUNT(*) AS views
FROM logtable
WHERE SUBSTRING(url,INSTR(url,'php?Id=')+7,6) REGEXP '[[:digit:]]+'
GROUP BY SUBSTRING(url,INSTR(url,'php?Id=')+7,6)
This is normal and not overload database server if run on 100,000 records (actually are more in logs table by filter of prior only day do 100,000) at 02.00 in the morning on a server 99% local traffic…?
Can combine this select with insert query to new table,
Date, id, views fields…only?
Total insert 3,000 since filtered and grouped
Run 4:00 morning
Also in query if Not digit the 6 chars in substr are ignored correct?
THE SELECT GIVES results… var_dump but when combined(how combine?) with INSERT gives “Error: SQLSTATE[HY000]: General error: trying to execute an empty query”
$conn = new PDO("mysql:host=".DB_SERVER.";dbname=".DB_NAME, DB_USER, DB_PASS);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
i CHANGE : ADD ‘’ DUE HAD AI field
SELECT ‘’, date, SUBSTRING(page,INSTR(page,‘car_id=’)+7,6) AS id, COUNT(*)
ALSO CHANGED
$stmt1 = $conn->prepare($q1);
$stmt1->execute();
currently selected IDs with dates without views not recorded … you know how in this query record and dates without views as 0 eg…
date ---- ID ---- views
12-09-2016 ---- 234563 ---- 0
is this what you’re looking for? the HAVING clause?
INSERT
INTO views
( `date`
, id
, views )
SELECT DATE(`date`)
, SUBSTRING(page,INSTR(page,'car_id=')+7,6)
, COUNT(*)
FROM bcc_logs
WHERE isbot = 0
AND SUBSTRING(page,INSTR(page,'car_id=')+7,6) REGEXP '[[:digit:]]+'
GROUP
BY DATE(date)
, SUBSTRING(page,INSTR(page,'car_id=')+7,6)
HAVING COUNT(*) > 0
LIMIT 20
please note use of DATE() function – using LEFT on a DATETIME or TIMESTAMP datatype is an abomination