Imam jedan kod:
$result = mysqli_query($con,"select *
FROM xlpix_votes
INNER JOIN (
SELECT MAX(ip) AS lastIp, option_id, date, MAX(id) as maxId
FROM xlpix__votes
GROUP BY ip, DATE_FORMAT(DATE(xlpix_votes.date), '%Y-%m-%d')
HAVING COUNT(*) > 1
) duplicate ON duplicate.lastIp = xlpix_votes.ip
where DATE_FORMAT(DATE(xlpix_votes.date), '%Y-%m-%d') = DATE_FORMAT(DATE(duplicate.date), '%Y-%m-%d')
AND maxId > xlpix_votes.id
AND xlpix_votes.ip != 123");
Ovaj kod mi ispravno pokazuje sve duplikate, a ako na početku umjesto SELECT * stavim DELETE, neče da obriše ove duplikate.
Pa gdje je greška?
A greška koja se ispiše je... ?
Pozdrav Dario ...
Ništa, potpuno prazan monitor
Ajd mi molim te eksportiraj te tablice iz query-ja pa mi ih zakači ovdje u attachment da ja to poledam malo bolje :)
Evo u privitku.
Nešto sam mislio, da ne bi radio delete, več update option_id sa večanjem vrijednosti option_id + 1000. Ako ima sada 50, onda bi bio option_id 1050. Ovo bi bilo puno bolje
Za brisanje bi ispravan query bio
DELETE FROM xlpix_acepolls_votes WHERE id IN (
SELECT id FROM (
SELECT id FROM xlpix_acepolls_votes
INNER JOIN (
SELECT MAX(ip) AS lastIp, option_id, date, MAX(id) as maxId
FROM xlpix_acepolls_votes
GROUP BY ip, DATE_FORMAT(DATE(xlpix_acepolls_votes.date), '%Y-%m-%d')
HAVING COUNT(*) > 1
) duplicate ON duplicate.lastIp = xlpix_acepolls_votes.ip
WHERE DATE_FORMAT(DATE(xlpix_acepolls_votes.date), '%Y-%m-%d') = DATE_FORMAT(DATE(duplicate.date), '%Y-%m-%d')
AND maxId > xlpix_acepolls_votes.id
AND xlpix_acepolls_votes.ip != 123
) AS x
);
Citat: Dario u 01. Travanj 2015, 11:26
Za brisanje bi ispravan query bio
DELETE FROM xlpix_acepolls_votes WHERE id IN (
SELECT id FROM (
SELECT id FROM xlpix_acepolls_votes
INNER JOIN (
SELECT MAX(ip) AS lastIp, option_id, date, MAX(id) as maxId
FROM xlpix_acepolls_votes
GROUP BY ip, DATE_FORMAT(DATE(xlpix_acepolls_votes.date), '%Y-%m-%d')
HAVING COUNT(*) > 1
) duplicate ON duplicate.lastIp = xlpix_acepolls_votes.ip
WHERE DATE_FORMAT(DATE(xlpix_acepolls_votes.date), '%Y-%m-%d') = DATE_FORMAT(DATE(duplicate.date), '%Y-%m-%d')
AND maxId > xlpix_acepolls_votes.id
AND xlpix_acepolls_votes.ip != 123
) AS x
);
Update/Delete nemože radit samo tako ko select ako radiš oboje na istoj tablici....
Uglavnom za update ti je isti princip samo radiš update umjesto delete...
hm, nešto mi ne radi ..
Parse error: syntax error, unexpected 'FROM' (T_STRING) in .....
A neznam kaj da ti velim... ja sam testirao s SQL-om koji si mi dao i sad sam s foruma kopirao query (za svaki slucaj)...
(http://i58.tinypic.com/rc0iuv.png)
Onda sm ja zabrkljao ... ja sam ovo sa php ne phpmyadmin ???
Hvala Dario..
Se izvinjavam... Na kraju zaboravio "
heheh, bitno da radi :)