Can anyone help (php, json and mysqli)

[Jam0r]

I was pointed in this direction by a friend so firstly, hello

 

I'm having some teething problems (basically can't get my head around it) and wondered if anyone would be able to have a look at a script for me (beer money may be provided)

 

I'm currently using a php script which grabs information from a API with curl (JSON format). I'm limited to a number of requests an hour so my plan was to read the file, insert the data into a mysql table so that I could re-read over and over again with no limitations. Then use a script to run every few hours to grab the data.

 

The script I created works however it's a bit buggy with regards to what it does if information is already in the table. If there isn't a previous entry then it inserts everything fine.

 

The JSON data is a multi depth array, and deals with images as well as data so for me, it's rather complex.

 

What I want the script to do is this. Each item is a property (letting agent website)

 

Check to see if the property exists in the database and the JSON. If there is no record in the database, then insert in. If there is a record in the database, update that record. If there is a property in the database which isn't in the JSON file, then delete it from the database.

 

This is basically the same plan for each sub array (pictures, floorplans, etc) however the images are copied from the url given (hosted on amazon) to my own host in order to speed up loading times, etc.

 

i've added a copy of the code but obviously its going to be stupidly hard to understand.

 

Is there any way this could be split into different functions to try and sort it out? haha

<?php


  	$ch = curl_init(); /* Section 1 */
  	curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
  	curl_setopt($ch, CURLOPT_POST, 1);
  	curl_setopt($ch, CURLOPT_URL, 'URL');
  	//prepare the field values being posted to the service
  	$data = array("appkey" => "KEY","account" => "ACCOUNT" );
  	curl_setopt($ch, CURLOPT_POSTFIELDS, $data);

  	//make the request
  	$result = curl_exec($ch);
	$properties = json_decode($result, true);
	$properties = $properties['properties'];
	//pr($parsed_json);
	
	
	foreach($properties as $key => $value) {


		/* Set the vars */
		$microTime = microtime();
		$time = time();
		$cat_id = $value['category_id'];
		$price = $value['price'];
		if ($value['category_id'] == "1") { $price_after = $value['qualifier_name']; } else { $price_after = $value['freq_name']; }
		$full_address = $value['full_address'];
		$address_1 = $value['address_1'];
		$address_2 = $value['address_2'];
		$address_3 = $value['address_3'];
		$address_4 = $value['address_4'];
		$address_5 = $value['address_5'];
		$bedrooms = $value['bedrooms'];
		$bathrooms = $value['bathrooms'];
		$summary = $value['summary'];
		$description = $value['description'];
		$type_name = $value['type_name'];
		$status = $value['status'];
		$feature_1 = $value['feature_1'];
		$feature_2 = $value['feature_2'];
		$feature_3 = $value['feature_3'];
		$feature_4 = $value['feature_4'];
		$lat = $value['lat'];
		$long = $value['lng'];
		
		
		/* Delete the properties from the database which can't be found in the file */
		$searchProp = "SELECT property_id FROM props WHERE property_address_full = '$full_address' AND property_catid = '$cat_id'";
		if ($result = mysqli_query ($linkq, $searchProp)) {
			if (!$whereStatement) { $whereStatement = "WHERE property_id != $row[property_id]"; } else { $whereStatement .= " OR property_id != $row[property_id]"; } // Set the whereStatement for deleting properties with no ID
			$deleteProp = "DELETE FROM props $whereStatement";
			if ($result = mysqli_query ($linkq, $deleteProp)) {
				$totalPropsDeleted++;
				echo "Properties deleted $deleteProp";

				$deleteimg = "DELETE FROM props_images $whereStatement";
				if ($result = mysqli_query ($linkq, $deleteimg)) {
					echo ("Images Deleted Successfully<br />\n");
				} else {
					echo ("Error Deleting");
				}
	
				$deleteepc = "DELETE FROM props_epcs $whereStatement";
				if ($result = mysqli_query ($linkq, $deleteepc)) {
					echo ("EPCs Deleted Successfully<br />\n");
				} else {
					echo ("Error Deleting");
				}
	
				$deletepdf = "DELETE FROM props_pdf $whereStatement";
				if ($result = mysqli_query ($linkq, $deletepdf)) {
					echo ("PDFs Deleted Successfully<br />\n");
				} else {
					echo ("Error Deleting");
				}
	
				$deletefloorplan = "DELETE FROM props_floorplans $whereStatement";
				if ($result = mysqli_query ($linkq, $deletefloorplan)) {
					echo ("Floorplans Deleted Successfully<br />\n");
				} else {
					echo ("Error Deleting");
				}
				
			} else {
				echo "Nothing to delete";
			}
		}
		
		
		echo "<br />";
		
		
		/* Check to see if the property exists currently */
		$existCheck = "SELECT * FROM props WHERE property_address_full LIKE '%$full_address%' AND property_catid = '$cat_id'";
		if ($result = mysqli_query ($linkq, $existCheck)) {
			$row_cnt = mysqli_num_rows($result);
			if ($row_cnt) {
				while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
					echo "Property Exists - $full_address";
					
                    
					if (($row[property_price] != $price) || ($row[property_summary] != $summary) || ($row[property_description] != $description)) {
						$dateUpdated = "property_updated = $microTime,";
					} else {
						$dateUpdated = "property_updated = property_updated,";
					}
														
					/* Property exists so update the property info */							
					$updateProp = "UPDATE props SET property_advertised = property_advertised,
					$dateUpdated
					property_catid = '$cat_id',
					property_price = '$price',
					property_price_after = '$price_after',
					property_address_full = '$full_address',
					property_address_1 = '$address_1',
					property_address_2 = '$address_2',
					property_address_3 = '$address_3',
					property_address_4 = '$address_4',
					property_address_5 = '$address_5',
					property_bedrooms = '$bedrooms',
					property_bathrooms = '$bathrooms',
					property_summary = '$summary',
					property_description = '$description',
					property_type = '$type_name',
					property_status = '$status',
					property_feature_1 = '$feature_1',
					property_feature_2 = '$feature_2',
					property_feature_3 = '$feature_3',
					property_feature_4 = '$feature_4',
					property_lat = '$lat',
					property_long = '$long' WHERE property_id = '$row[property_id]'";
					if (mysqli_query ($linkq, $updateProp)) {
						$totalPropsUpdated++;
						echo (" & Updated");
					} else {
						echo (" & Error Updating");
					}			
				
					echo ("<br />\n");
					
					/* Delete all the photos for this property as it doesn't matter if they are added again */
					$deletePhotos = mysqli_query ($linkq, "DELETE FROM props_images WHERE property_id = $row[property_id] OR property_id = ''");
					printf("Images Deleted: %d\n", mysqli_affected_rows($linkq));
					printf("<br />\n");
					
					/* Loop the current images and add back to the database and asign the property id */
					$primaryimg = 0;
					$totalimg = count($value['images']);
					for ($x = 0; $x < $totalimg; $x++) {
												
						$imagename = $value['images'][$x];	
						if (!$primaryimg) {	$image_main = "1"; } else { $image_main = "0"; }		
						$insertphoto = "INSERT into props_images VALUES ('', '$row[property_id]', '$imagename', '$image_main', '')";
						if (mysqli_query ($linkq, $insertphoto)) {
							echo ("- Inserted ($row[property_id]) ($imagename) ($image_main)\n");
														
							if (!file_exists('images/props/'. $imagename .'.jpg')) {
							
								/* Try and copy the image from the url */
								$file = 'https://utili-media.s3-external-3.amazonaws.com/stevemorris/images/' . $imagename . '_1024x1024.jpg';
								$newfile = 'images/props/' . $imagename . '.jpg';

								if (!copy($file, $newfile)) {
    								echo "- Failed to Copy\n";
								} else {
									echo "- Image Copied";
									/* Resize the image and create a thumb nail */
									$image = new SimpleImage();
									$image->load('images/props/' . $imagename . '.jpg');
									$image->resizeToWidth(240);
									$image->save('images/props/thumb/' . $imagename . '.jpg');
								}
								
							} else {
								echo ("- Nothing to upload, image exists");
							}
							
							echo "<br />\n";							
							
						} else {
							echo ("- Image Insert Error\n");
						}
						
						$primaryimg++;

					} /* close the image loop */
					
					/* Delete all the epcs for this property as it doesn't matter if they are added again */
					$deleteEpcs = mysqli_query ($linkq, "DELETE FROM props_epcs WHERE property_id = $row[property_id]");
					printf("EPCs Deleted: %d\n", mysqli_affected_rows($linkq));
					printf("<br />\n");
					
					/* Loop the current EPCs and insert into the database */
					$totalepc = count($value['epcs']);
					for ($y = 0; $y < $totalepc; $y++) {
												
						$epcname = $value['epcs'][$y];	
						$insertepc = "INSERT into props_epcs VALUES ('', '$new_property_id', '$epcname')";
						if (mysqli_query ($linkq, $insertepc)) {
							echo ("- Inserted EPC ($new_property_id) ($epcname)<br />\n");
						} else {
							echo ("- EPC Insert Error\n");
						}

					} /* close the epc loop */
					
					/* Delete all the pdfs for this property as it doesn't matter if they are added again */
					$deletePdfs = mysqli_query ($linkq, "DELETE FROM props_pdf WHERE property_id = $row[property_id]");
					printf("PDFs Deleted: %d\n", mysqli_affected_rows($linkq));
					printf("<br />\n");
					
					/* Loop the current PDFs and insert into the database */
					$totalpdf = count($value['pdfs']);
					for ($v = 0; $v < $totalpdf; $v++) {
												
						$pdfname = $value['epcs'][$v];	
						$insertpdf = "INSERT into props_pdf VALUES ('', '$new_property_id', '$pdfname')";
						if (mysqli_query ($linkq, $insertpdf)) {
							echo ("- Inserted PDF ($new_property_id) ($pdfname)<br />\n");
						} else {
							echo ("- PDF Insert Error\n");
						}

					} /* close the PDF loop */
					
					/* Delete all the pdfs for this property as it doesn't matter if they are added again */
					$deleteFloorplan = mysqli_query ($linkq, "DELETE FROM props_floorplans WHERE property_id = $row[property_id]");
					printf("Floorplans Deleted: %d\n", mysqli_affected_rows($linkq));
					printf("<br />\n");
					
					/* Loop the current Floorplans and insert into the database */
					$totalfloorplan = count($value['floorplans']);
					for ($v = 0; $v < $totalfloorplan; $v++) {
												
						$floorplanname = $value['floorplans'][$v];	
						$insertfloorplan = "INSERT into props_floorplans VALUES ('', '$new_property_id', '$floorplanname')";
						if (mysqli_query ($linkq, $insertfloorplan)) {
							echo ("- Inserted Floorplan ($new_property_id) ($floorplanname)<br />\n");
						} else {
							echo ("- Floorplan Insert Error\n");
						}
						
					} /* close the Floorplan loop */
					
				}	 /* close the property while loop */

			} else { /* close the if row exists loop */
		
				/* Insert the property as it doesn't exist in the database */			
				$insert = "INSERT INTO props VALUES ('', '$time', '', '$cat_id', '$price', '$price_after', '$full_address', '$address_1', '$address_2', '$address_3', '$address_4', '$address_5', '$bedrooms', '$bathrooms', '$summary', '$description', '$type_name', '$status', '$feature_1', '$feature_2', '$feature_3', '$feature_4', '$lat', '$long', '', '')";
				if (mysqli_query ($linkq, $insert)) {
					$totalPropsAdded++;
					echo ("Property Inserted - $value[full_address]<br />");
					
					/* get the property ID from the newly added property */
					$getPropID = mysqli_query ($linkq, "SELECT property_id FROM props ORDER BY property_id DESC LIMIT 1");
					while ($rowNew = $getPropID->fetch_array(MYSQLI_ASSOC)) {
						$new_property_id = $rowNew[property_id];
					}	
														
					/* Loop the current images and add to the database and asign the property id */
					$primaryimg = 0;
					$totalimg = count($value['images']);
					for ($x = 0; $x < $totalimg; $x++) {
												
						$imagename = $value['images'][$x];	
						if (!$primaryimg) {	$image_main = "1"; } else { $image_main = "0"; }			
						$insertphoto = "INSERT into props_images VALUES ('', '$new_property_id', '$imagename', '$image_main', '')";
						if (mysqli_query ($linkq, $insertphoto)) {
							echo ("- Inserted Image ($new_property_id) ($imagename) ($image_main)<br />\n");
						} else {
							echo ("- Image Insert Error\n");
						}
						
						$primaryimg++;

					} /* close the images loop */
					
					/* Loop the current epcsand add to the database and asign the property id */
					$totalepc = count($value['epcs']);
					for ($y = 0; $y < $totalepc; $y++) {
												
						$epcname = $value['epcs'][$y];	
						$insertepc = "INSERT into props_epcs VALUES ('', '$new_property_id', '$epcname')";
						if (mysqli_query ($linkq, $insertepc)) {
							echo ("- Inserted EPC ($new_property_id) ($epcname)<br />\n");
						} else {
							echo ("- EPC Insert Error\n");
						}

					} /* close the epcs loop */
					
					/* Loop the current pdfs and add to the database and asign the property id */
					$totalpdf = count($value['pdfs']);
					for ($v = 0; $v < $totalpdf; $v++) {
												
						$pdfname = $value['epcs'][$v];	
						$insertpdf = "INSERT into props_pdf VALUES ('', '$new_property_id', '$pdfname')";
						if (mysqli_query ($linkq, $insertpdf)) {
							echo ("- Inserted PDF ($new_property_id) ($pdfname)<br />\n");
						} else {
							echo ("- PDF Insert Error\n");
						}

					} /* close the pdfs loop */
					
					/* Loop the current floorplans and add to the database and asign the property id */
					$totalfloorplan = count($value['floorplans']);
					for ($v = 0; $v < $totalfloorplan; $v++) {
												
						$floorplanname = $value['floorplans'][$v];	
						$insertfloorplan = "INSERT into props_floorplans VALUES ('', '$new_property_id', '$floorplanname')";
						if (mysqli_query ($linkq, $insertfloorplan)) {
							echo ("- Inserted Floorplan ($new_property_id) ($floorplanname)<br />\n");
						} else {
							echo ("- Floorplan Insert Error\n");
						}

					} /* close the floorplans loop */
			
				} else {	
							
					echo ("Insert Error");
					
				} /* close the insert property */
			
				
			}  /* close the row count loop */
			
			
		} /* close the property exists loop */
		
	}
			

	/* close everything */
	curl_close($ch);	
	mysqli_close ($linkq);
	
?>

Edited December 8, 2014 by Jam0r

[boompa]

Given this:

 

 

Check to see if the property exists in the database and the JSON. If there is no record in the database, then insert in. If there is a record in the database, update that record. If there is a property in the database which isn't in the JSON file, then delete it from the database.

, why not simplify by doing the following:

 

1. DELETE all records from the database.

2. INSERT new records based on the JSON.

[Jam0r]

I did use that method however I have status' based on when the property was added and when it was updated.

 

So deleting everything and adding it all as new would mean everything shows up as new.. when it isn't.

 

Instead I add, update and delete which i know is a pain in the arse but it means I can show things as new if they are newer then 14 days for example

[Barand]

MySQL has an "INSERT ... ON DUPLICATE KEY UPDATE ... " option so you can update records where on with the same key already exists and insert a new record when it doesn't.

 

http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

[QuickOldCar]

You can also save the json and cache them with expire times

If cache file doesn't exist or exists and past the expire time...do the api request and create one for next time

 

some examples from stackoverflow

 

I'm sure that's easier than what are trying to do with inserts.