Hi there,
I've got this code which i execute on phpmyadmin which works 100%
Create Temporary Table Searches ( id int, dt datetime);
Create Temporary Table Searches1 ( id int, dt datetime, count int);
insert into Searches(id, dt) select a.id, now() from tblSavedSearches a;
insert into Searches1(id, dt, count)
select
b.savedSearchesId,
(select c.dt from tblSavedSearchesDetails c where b.savedSearchesId = c.savedSearchesId order by c.dt desc limit 1) as 'dt',
count(b.savedSearchesId) as 'cnt'
from tblSavedSearchesDetails b
group by b.savedSearchesId;
insert into tblSavedSearchResults(savedSearchId,DtSearched,isEnabled)
select id,now(),0 from Searches where not id in (select savedSearchId from tblSavedSearchResults);
update tblSavedSearchResults
inner join Searches1 on tblSavedSearchResults.savedSearchId = Searches1.id
Set tblSavedSearchResults.DtSearched = Searches1.dt, tblSavedSearchResults.isEnabled = 1;
However when i put the same code in php as below it generates an error
$dba = DbConnect::CreateDbaInstance();
$query = "";
$query.="Create Temporary Table Searches ( id int, dt datetime); ";
$query.="Create Temporary Table Searches1 ( id int, dt datetime, count int); ";
$query.="insert into Searches(id, dt) select a.id, now() from tblSavedSearches a; ";
$query.="insert into Searches1(id, dt, count) ";
$query.="select ";
$query.=" b.savedSearchesId, ";
$query.=" (select c.dt from tblSavedSearchesDetails c where b.savedSearchesId = c.savedSearchesId order by c.dt desc limit 1) as 'dt', ";
$query.=" count(b.savedSearchesId) as 'cnt' ";
$query.="from tblSavedSearchesDetails b ";
$query.="group by b.savedSearchesId; ";
$query.="insert into tblSavedSearchResults(savedSearchId,DtSearched,isEnabled) ";
$query.="select id,now(),0 from Searches where not id in (select savedSearchId from tblSavedSearchResults); ";
$query.="update tblSavedSearchResults ";
$query.="inner join Searches1 on tblSavedSearchResults.savedSearchId = Searches1.id ";
$query.="Set tblSavedSearchResults.DtSearched = Searches1.dt, tblSavedSearchResults.isEnabled = 1; ";
$dba->DbQuery($query) or die(mysql_error());
I get the following error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Create Temporary Table Searches1 ( id int, dt datetime, count int) insert into S' at line 1
Please if someone could assist me with this ...
Thanks
