Mysql select query can extract id 6digit from a field if is a url this field of type

Mysql select query can extract id 6digit from a field if is a url this field of type

Cardetails.php?Id=123456
Cardetails.php?Id=123456&fg=true
Cardetails.php?Id=123456&par2=4&par3=5

If no 6 digit ignore row in this logs table…

And a second query insert it to 2nd table?

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 FROM logtable WHERE SUBSTRING(url,INSTR(url,'php?Id=')+7,6) REGEXP '[[:digit:]]+'

thks but

1
if many urls with same id
how group them by id and count how many they are…?

2
SUBSTRING(url,INSTR(url,‘php?Id=’)+7,6) used second time can use only id
???

  1. use GROUP BY and COUNT(*)

  2. no

This or like this should work…?


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?

you’re sure? this is, after all, mysql :smiley:

what happened when you tested it? ™

not just the 6 chars, the entire row is ignored

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);	

$q1 = “…”;

try {
	$conn->exec($ql);
	echo "success!";
}
catch(PDOException $e) {
	echo "Error: " . $e->getMessage();
}

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();

AND WORKED… THKS FOR YOUR HELP

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

IDs in range upper 230000

defaults play any role? Or exist other way?

that query you just posted is very badly broken, i.e. it will not run

also, your questions are incomprehensible (to me, anyway)

the point is other : how on select,… record and IDs with dates with Views=0 since do Not recorded

can you give any hints… to search or try?

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

what this
having
clause do?
actually that i want is in each insert the default be ID, date, 0

may date be yesterday only so for each ID if views 0 DO Not skipped but enter the entry the insert.

okay, so you want HAVING COUNT(*) = 0

I guess that wanted…

BTW what differs this ^^^ from this >>>>
HAVING COUNT(*) > 0

???