| Server IP : 104.21.17.213 / Your IP : 216.73.217.141 Web Server : Apache System : Linux hosting01.arsenalhost.com 4.18.0-425.13.1.lve.el8.x86_64 #1 SMP Mon Feb 27 15:23:24 EST 2023 x86_64 User : corbizre ( 1013) PHP Version : 7.4.33 Disable Function : exec,passthru,shell_exec,system MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /home/corbizre/rentalssearch.com/ |
Upload File : |
<?php
include_once "config.php";
require_once "lib/dbmysql.class.php";
error_reporting(E_ALL);
$db = new DbMySQL(DB_HOST, DB_USER, DB_NAME, DB_PWD);
$db->connect();
$db2 = new DbMySQL(DB_HOST2, DB_NAME2, DB_USER2, DB_PWD2);
$db2->connect();
$sql = "SELECT * FROM realty ORDER BY last_update DESC LIMIT 1";
$last_update = $db->select_row($sql);
echo "LAST UPDATE: ".$last_update['last_update'];
echo "\n";
//$last_update['last_update'] = '2011-08-30 59:59:59';
echo $sql = "SELECT count(*) as houses_count FROM houses WHERE last_update>='".$last_update['last_update']."'";
echo "\n";
$houses_count = $db2->select_row($sql);
echo "HOUSES for UPDATE:".$houses_count['houses_count']."\n";
echo "\n";
$processing = 10000;
echo $iterations_number = ceil($houses_count['houses_count'] / $processing);
$i=0;
$sql = 'UPDATE realty SET city = SUBSTRING(city, INSTR(city, "\n")) WHERE INSTR(city, "\n")';
$db->query($sql);
$sql = 'UPDATE realty SET city = REPLACE(city,"\n"," ") WHERE INSTR(city, "\n")';
$db->query($sql);
$sql = 'UPDATE realty SET city = REPLACE(city," "," ") WHERE INSTR(city, " ")';
$db->query($sql);
error_reporting(E_ALL);
for ($iteration = 0; $iteration < $iterations_number+1; $iteration++) {
$sql1 = "SELECT * FROM houses WHERE last_update>='".$last_update['last_update']."' ORDER BY last_update ASC LIMIT ".($iteration * $processing).",".$processing;
$houses = $db2->select($sql1);
echo "HOUSES".$iteration."-".count($houses)."\n";
if($iteration==2){
exit;
}
foreach($houses as $key=>$house){
$sql = "INSERT INTO realty
(ID,
valid,
site_index,
MLSID,
REOID,
status,
status_type,
use_type,
image_path,
image_url,
type,
address,
country,
county,
city,
state,
zip,
full_address_md5,
url,
agent,
price,
area,
area_desc,
unit_price,
bedrooms,
bathrooms,
garages,
brokerage_firm,
brokerage_address,
brokerage_city,
brokerage_state,
brokerage_zip,
brokerage_phone,
brokerage_email,
phone,
email,
created_date,
last_update,
additional_info,
building_name,
submarket_type,
sale_terms,
MSA,
tax_id,
zoning,
living_area,
buildings,
tenancy,
year,
year_renovated,
stories,
construction,
parking_ratio,
parking_type,
parking_spaces,
elevators_passenger,
elevators_freight,
fire_sprinklers,
fire_alarms,
head_source,
head_type,
broadband_internet,
source,
floors,
market,
GBA,
services,
managing_agent,
owner,
office_tenants,
retail_tenants,
floor_sizes
)
VALUES
(
'".mysql_escape_string($house['ID'])."',
'".(int) mysql_escape_string($house['valid'])."',
'".mysql_escape_string($house['site_index'])."',
'".mysql_escape_string($house['MLSID'])."',
'".mysql_escape_string($house['REOID'])."',
'".mysql_escape_string($house['status'])."',
'".mysql_escape_string($house['status_type'])."',
'".mysql_escape_string($house['use_type'])."',
'".mysql_escape_string($house['image_path'])."',
'".mysql_escape_string($house['image_url'])."',
'".mysql_escape_string($house['type'])."',
'".mysql_escape_string($house['address'])."',
'".mysql_escape_string($house['country'])."',
'".mysql_escape_string($house['county'])."',
'".mysql_escape_string($house['city'])."',
'".mysql_escape_string($house['state'])."',
'".mysql_escape_string($house['zip'])."',
'".mysql_escape_string($house['full_address_md5'])."',
'".mysql_escape_string($house['url'])."',
'".mysql_escape_string($house['agent'])."',
'".mysql_escape_string($house['price'])."',
'".mysql_escape_string($house['area'])."',
'".mysql_escape_string($house['area_desc'])."',
'".mysql_escape_string($house['unit_price'])."',
'".mysql_escape_string($house['bedrooms'])."',
'".mysql_escape_string($house['bathrooms'])."',
'".mysql_escape_string($house['garages'])."',
'".mysql_escape_string($house['brokerage_firm'])."',
'".mysql_escape_string($house['brokerage_address'])."',
'".mysql_escape_string($house['brokerage_city'])."',
'".mysql_escape_string($house['brokerage_state'])."',
'".mysql_escape_string($house['brokerage_zip'])."',
'".mysql_escape_string($house['brokerage_phone'])."',
'".mysql_escape_string($house['brokerage_email'])."',
'".mysql_escape_string($house['phone'])."',
'".mysql_escape_string($house['email'])."',
'".mysql_escape_string($house['created_date'])."',
'".mysql_escape_string($house['last_update'])."',
'".mysql_escape_string($house['additional_info'])."',
'".mysql_escape_string($house['building_name'])."',
'".mysql_escape_string($house['submarket_type'])."',
'".mysql_escape_string($house['sale_terms'])."',
'".mysql_escape_string($house['MSA'])."',
'".mysql_escape_string($house['tax_id'])."',
'".mysql_escape_string($house['zoning'])."',
'".mysql_escape_string($house['living_area'])."',
'".mysql_escape_string($house['buildings'])."',
'".mysql_escape_string($house['tenancy'])."',
'".mysql_escape_string($house['year'])."',
'".mysql_escape_string($house['year_renovated'])."',
'".mysql_escape_string($house['stories'])."',
'".mysql_escape_string($house['construction'])."',
'".mysql_escape_string($house['parking_ratio'])."',
'".mysql_escape_string($house['parking_type'])."',
'".mysql_escape_string($house['parking_spaces'])."',
'".mysql_escape_string($house['elevators_passenger'])."',
'".mysql_escape_string($house['elevators_freight'])."',
'".mysql_escape_string($house['fire_sprinklers'])."',
'".mysql_escape_string($house['fire_alarms'])."',
'".mysql_escape_string($house['head_source'])."',
'".mysql_escape_string($house['head_type'])."',
'".mysql_escape_string($house['broadband_internet'])."',
'".mysql_escape_string($house['source'])."',
'".mysql_escape_string($house['floors'])."',
'".mysql_escape_string($house['market'])."',
'".mysql_escape_string($house['GBA'])."',
'".mysql_escape_string($house['services'])."',
'".mysql_escape_string($house['managing_agent'])."',
'".mysql_escape_string($house['owner'])."',
'".mysql_escape_string($house['office_tenants'])."',
'".mysql_escape_string($house['retail_tenants'])."',
'".mysql_escape_string($house['floor_sizes'])."'
) ON DUPLICATE KEY UPDATE
ID = '".mysql_escape_string($house['ID'])."',
valid = '".mysql_escape_string($house['valid'])."',
site_index = '".mysql_escape_string($house['site_index'])."',
MLSID = '".mysql_escape_string($house['MLSID'])."',
REOID = '".mysql_escape_string($house['REOID'])."',
STATUS = '".mysql_escape_string($house['status'])."',
status_type = '".mysql_escape_string($house['status_type'])."',
use_type = '".mysql_escape_string($house['use_type'])."',
image_path = '".mysql_escape_string($house['image_path'])."',
image_url = '".mysql_escape_string($house['image_url'])."',
type = '".mysql_escape_string($house['type'])."',
address = '".mysql_escape_string($house['address'])."',
country = '".mysql_escape_string($house['country'])."',
county = '".mysql_escape_string($house['county'])."',
city = '".mysql_escape_string($house['city'])."',
state = '".mysql_escape_string($house['state'])."',
zip = '".mysql_escape_string($house['zip'])."',
full_address_md5 = '".mysql_escape_string($house['full_address_md5'])."',
url = '".mysql_escape_string($house['url'])."',
agent = '".mysql_escape_string($house['agent'])."',
price = '".mysql_escape_string($house['price'])."',
area = '".mysql_escape_string($house['area'])."',
area_desc = '".mysql_escape_string($house['area_desc'])."',
unit_price = '".mysql_escape_string($house['unit_price'])."',
bedrooms = '".mysql_escape_string($house['bedrooms'])."',
bathrooms = '".mysql_escape_string($house['bathrooms'])."',
garages = '".mysql_escape_string($house['garages'])."',
brokerage_firm = '".mysql_escape_string($house['brokerage_firm'])."',
brokerage_address = '".mysql_escape_string($house['brokerage_address'])."',
brokerage_city = '".mysql_escape_string($house['brokerage_city'])."',
brokerage_state = '".mysql_escape_string($house['brokerage_state'])."',
brokerage_zip = '".mysql_escape_string($house['brokerage_zip'])."',
brokerage_phone = '".mysql_escape_string($house['brokerage_phone'])."',
brokerage_email = '".mysql_escape_string($house['brokerage_email'])."',
phone = '".mysql_escape_string($house['phone'])."',
email = '".mysql_escape_string($house['email'])."',
created_date = '".mysql_escape_string($house['created_date'])."',
last_update = '".mysql_escape_string($house['last_update'])."',
additional_info = '".mysql_escape_string($house['additional_info'])."',
building_name = '".mysql_escape_string($house['building_name'])."',
submarket_type = '".mysql_escape_string($house['submarket_type'])."',
sale_terms = '".mysql_escape_string($house['sale_terms'])."',
MSA = '".mysql_escape_string($house['MSA'])."',
tax_id = '".mysql_escape_string($house['tax_id'])."',
zoning = '".mysql_escape_string($house['zoning'])."',
living_area = '".mysql_escape_string($house['living_area'])."',
buildings = '".mysql_escape_string($house['buildings'])."',
tenancy = '".mysql_escape_string($house['tenancy'])."',
year = '".mysql_escape_string($house['year'])."',
year_renovated = '".mysql_escape_string($house['year_renovated'])."',
stories = '".mysql_escape_string($house['stories'])."',
construction = '".mysql_escape_string($house['construction'])."',
parking_ratio = '".mysql_escape_string($house['parking_ratio'])."',
parking_type = '".mysql_escape_string($house['parking_type'])."',
parking_spaces = '".mysql_escape_string($house['parking_spaces'])."',
elevators_passenger = '".mysql_escape_string($house['elevators_passenger'])."',
elevators_freight = '".mysql_escape_string($house['elevators_freight'])."',
fire_sprinklers = '".mysql_escape_string($house['fire_sprinklers'])."',
fire_alarms = '".mysql_escape_string($house['fire_alarms'])."',
head_source = '".mysql_escape_string($house['head_source'])."',
head_type = '".mysql_escape_string($house['head_type'])."',
broadband_internet = '".mysql_escape_string($house['broadband_internet'])."',
source = '".mysql_escape_string($house['source'])."',
floors = '".mysql_escape_string($house['floors'])."',
market = '".mysql_escape_string($house['market'])."',
GBA = '".mysql_escape_string($house['GBA'])."',
services = '".mysql_escape_string($house['services'])."',
managing_agent = '".mysql_escape_string($house['managing_agent'])."',
owner = '".mysql_escape_string($house['owner'])."',
office_tenants = '".mysql_escape_string($house['office_tenants'])."',
retail_tenants = '".mysql_escape_string($house['retail_tenants'])."',
floor_sizes = '".mysql_escape_string($house['floor_sizes'])."'
";
if($db->query($sql)){
}else{
//echo $sql;
echo $house['ID']."-".($i++)."\n";
exit;
}
//exit;
//FIX DATA
$sql="update realty set city='".mysql_escape_string(urldecode($house['city']))."', address='".mysql_escape_string(urldecode($house['address']))."' WHERE ID='".$house['ID']."' and (url_path='' or url_path is null)";
$db->query($sql);
//SET - URL
if($house['address']!=''&&$house['state']!=''&&$house['zip']!=''&&$house['city']!=''){
$sql="update realty set url_path='".str_replace(" ","-",preg_replace("|[^[:alnum:]\s]|","",unhtmlspecialchars($house['address'])))."-".str_replace(" ","-",preg_replace("|[^[:alnum:]\s]|","",unhtmlspecialchars($house['city'])))."-".$house['state']."-".$house['zip']."' where ID='".$house['ID']."'";
if($db->query($sql)){
}
else{
// echo "ERROR:1 \n";
// echo $sql = "SELECT * FROM houses WHERE
// address like '".str_replace(" ","%",str_replace(".","",str_replace("#","",$house['address'])))."%' AND
// city= '".mysql_escape_string($house['city'])."' AND
// state= '".mysql_escape_string($house['state'])."' AND
// zip= '".mysql_escape_string($house['zip'])."'
// ORDER BY ID ASC";
//
// $duplicates = $db2->select($sql);
// //echo "<pre>";
// unset($duplicates[count($duplicates)-1]);
// print_r($duplicates);
// foreach ($duplicates as $key=>$value){
// echo $value['ID']."\n";
// $db3->query("delete from houses where ID=".$value['ID']);
// $db->query("delete from realty where ID=".$value['ID']);
// }
//
}
}
//echo "<br/>";
}
}
echo "UPDATING MROfficeSpace houses...\n";
$sql = "UPDATE realty SET url_path=ID WHERE site_index=2 and address!='' and city!='' and state!=''";
$db->query($sql);
echo "UPDATING Space table\n...";
$sql = "INSERT INTO rentalssearch.spaces
(ID,
house_id,
price,
minimum_divisible,
max_contiguous,
date_available,
lease_term,
conference_rooms,
description,
`status`,
`type`,
`floor`,
suite,
`area`,
rent,
avail,
elec,
sublet_direct,
notes,
contact,
sublease,
for_info
)
(SELECT * FROM hrpars2.spaces)
ON DUPLICATE KEY UPDATE
ID = hrpars2.spaces.ID,
house_id = hrpars2.spaces.house_id,
price = hrpars2.spaces.price,
minimum_divisible = hrpars2.spaces.minimum_divisible,
max_contiguous = hrpars2.spaces.max_contiguous,
date_available = hrpars2.spaces.date_available,
lease_term = hrpars2.spaces.lease_term,
conference_rooms = hrpars2.spaces.conference_rooms,
description = hrpars2.spaces.description,
`status` = hrpars2.spaces.status,
`type` = hrpars2.spaces.type,
`floor` = hrpars2.spaces.floor,
suite = hrpars2.spaces.suite,
`area` = hrpars2.spaces.area,
rent = hrpars2.spaces.rent,
avail = hrpars2.spaces.avail,
elec = hrpars2.spaces.elec,
sublet_direct = hrpars2.spaces.sublet_direct,
notes = hrpars2.spaces.notes,
contact = hrpars2.spaces.contact,
sublease = hrpars2.spaces.sublease,
for_info = hrpars2.spaces.for_info
";
$db->query("DELETE FROM spaces");
$db->query($sql);
$sql="INSERT INTO rentalssearch.agents
(id,
house_id,
`name`,
address,
city,
state,
zip,
company_name,
phone,
fax,
email
)
(SELECT * FROM hrpars2.agents)
ON DUPLICATE KEY UPDATE
id = hrpars2.agents.id,
house_id = hrpars2.agents.house_id,
`name`=hrpars2.agents.name,
address = hrpars2.agents.address,
city = hrpars2.agents.city,
state = hrpars2.agents.state,
zip = hrpars2.agents.zip,
company_name = hrpars2.agents.company_name,
phone = hrpars2.agents.phone,
fax = hrpars2.agents.fax,
email = hrpars2.agents.email
";
$db->query("DELETE FROM agents");
$db->query($sql);
$sql = "INSERT INTO rentalssearch.floor_sizes
(id,
house_id,
floors,
size
)
(SELECT * FROM hrpars2.floor_sizes)
ON DUPLICATE KEY UPDATE
id = hrpars2.floor_sizes.id,
house_id = hrpars2.floor_sizes.house_id,
floors = hrpars2.floor_sizes.floors,
size = hrpars2.floor_sizes.size
";
$db->query("DELETE FROM floor_sizes");
$db->query($sql);
$sql = "INSERT INTO rentalssearch.tenants
(id,
house_id,
`name`,
`type`
)
(SELECT * FROM hrpars2.tenants)
ON DUPLICATE KEY UPDATE
id = hrpars2.tenants.id,
house_id = hrpars2.tenants.house_id,
`name` = hrpars2.tenants.name,
`type` = hrpars2.tenants.type
";
$db->query("DELETE FROM tenants");
$db->query($sql);
$sql = 'UPDATE realty SET url_path = REPLACE(url_path,"\n","") WHERE INSTR(url_path, "\n")';
$db->query($sql);
//$query = "SELECT * FROM spaces";
//$spaces = $db2->select_row($spaces);
//$sql = "SELECT count(*) as houses_count FROM houses";
//$houses_count = $db2->select_row($sql);
//
//$processing = 1000;
//
//echo $iterations_number = ceil($houses_count['houses_count'] / $processing);
//$i=0;
//for ($iteration = 0; $iteration < $iterations_number+1; $iteration++) {
// $sql = "SELECT * FROM houses LIMIT ".($iteration * $processing).",".$processing;
//
print_r($houses_count);
function unhtmlspecialchars( $string )
{
$string = str_replace ( '&', '&', $string );
$string = str_replace ( ''', '\'', $string );
$string = str_replace ( '"', '\"', $string );
$string = str_replace ( '<', '<', $string );
$string = str_replace ( '>', '>', $string );
$string = str_replace ( '#', '', $string );
return $string;
}
?>