Go faster - code more effiiciently, tips please

PHP
1

Hi sitepointers,

I have written a script to create an XML file. Can someone have a look at my code and tell me how I could make it more efficient and thus process more properties / avoid a time out?

Thank you very much.

34C in Southern Spain today. Come on England!!!

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
			<root>
			<kyero>
				<feed_version>1</feed_version>
			</kyero>
			
			';
			
			$query = "SELECT * FROM property, province
			WHERE property.province_id = province.province_id
			AND property_live = '1'
			
			 ";
			$result = mysql_query($query);
			while ($array = mysql_fetch_assoc($result)) {
			$property_id = $array['property_id'];
			$property_type_id = $array['property_type_id'];
			
				$properties = $properties.'<property>
				<date>'.$array['property_date_modified'].'</date>
				<ref>'.$array['property_agent_ref'].'</ref>
				<price>'.$array['property_price'].'</price>
				<price_freq>'.$array['property_price_frequency'].'</price_freq>
				<property_part_ownership>'.$array['property_part_ownership'].'</property_part_ownership>
				<property_leasehold>'.$array['property_leasehold'].'</property_leasehold>
				<type>
					<en>'.$array['[property_type_name'].'</en>
					
				</type>
				<town>'.$array['property_town'].'</town>
				<province>'.$array['province_name'].'</province>
				<location_detail>'.$array['property_province'].'</location_detail>
				<beds>'.$array['property_beds'].'</beds>
				<baths>'.$array['property_baths'].'</baths>
				<pool>'.$array['property_pool'].'</pool>
				<desc>
					<en>'.$array['property_description_en'].'</en>
					<es>'.$array['property_description_es'].'</es>
				
				</desc>
				<images>';
				/*
				//look up placeholder id
				$property_type_query = "SELECT property_type_name FROM property_type WHERE property_type_id = '$property_type_id'";
				$property_type_result = mysql_query($property_type_query);
				while ($property_type_array = mysql_fetch_assoc($property_type_result)) {
					$property_type_name = $property_type_array['property_type_name'];
				}
				
				$placeholder_query = "SELECT placeholder_id FROM placeholder WHERE placeholder_name = '$property_type_name'";
				$placeholder_result = mysql_query($placeholder_query);
				while ($placeholder_array = mysql_fetch_assoc($placeholder_result)) {
					$placeholder_id = $placeholder_array['placeholder_id'];
				}
				
				
				$placeholder_language_query = "SELECT placeholder_language_name, language_id FROM placeholder_language
				WHERE placeholder_id = '$placeholder_id'";
				$placeholder_language_result = mysql_query($placeholder_language_query);
				while ($placeholder_language_array = mysql_fetch_assoc($placeholder_language_result)) {
					$placeholder_language_name = $placeholder_language_array['placeholder_language_name'];
					$language_id = $placeholder_language_array['language_id'];
					switch ($language_id) {
					
					case "1":
					$properties = str_replace('[property_type_name_en]',$placeholder_language_name,$properties);
					break;
					
					case "2":
					$properties = str_replace('[property_type_name_es]',$placeholder_language_name,$properties);
					break;
					}
				}
				*/
				
				$counter = 1;
				while ($counter < 10) {
					$photo_query = "SELECT * FROM property_photo WHERE property_id = '$property_id' ORDER BY property_photo_main DESC";
					$photo_result = mysql_query($photo_query);
					while ($photo_array = mysql_fetch_assoc($photo_result)) {
						$images = $images.'<image id="'.$counter.'">
						<url>'.$site_url.'property_pics/'.$property_id.'/400/'.$photo_array['property_photo_filename'].'</url>';
						
						if ($photo_array['property_photo_main'] == '1') {
							$images = $images.'<primary>1</primary>';
						}
						
						$images = $images.'<title>
							<en>'.$photo_array['property_photo_name'].'</en>
						</title>
						</image>';
						
					$counter = $counter +1;
					}
				}
				$properties = $properties.$images.'</images></property>';
			}
			
			$xml = $xml.$properties.'</root>';
			$xml_path = 'xml_files/'.$now.'.xml';
			
			file_put_contents($xml_path, $xml);
			
			$output = $output.'<a target="_blank" href="'.$xml_path.'">'.$xml_path.'</a>';
2

Nesting db queries should generally be avoided, read up on [google]how to join tables in sql[/google].

3

Also look into using for loops when keep track of your loops.

4

To push things a bit further, I also think this code could benefit from some separation between the database logic, the templating, and the request handling.

Here’s an example. It’s largely the same code you had, just reorganized.

<?php

// index.php

require_once 'lib/database.php';
require_once 'lib/util.php';

// render and save live properties XML
$live_properties = find_live_properties();
$xml = render('templates/properties.xml.php', array('properties' => $live_properties));
$xml_path = 'xml_files/'.$now.'.xml';
file_put_contents($xml_path, $xml);

// render and show HTML using the generated XML path
echo render('templates/homepage.html.php', array('xml_path', $xml_path));

<?php

// lib/database.php

function find_live_properties()
{
    $result = mysql_query('
        SELECT *
        FROM property
            LEFT JOIN providence
                ON property.providence_id = providence.providence_id
            LEFT JOIN property_type
                ON property.property_type_id = property_type.property_type_id
        WHERE property_live = 1
    ');

    $properties = array();
    while ($row = mysql_fetch_assoc($result)) {
        $properties[] = $row;
    }

    return $properties;
}

function find_photos_by_property($property, $max = 10)
{
    $result = mysql_query(sprintf('
            SELECT *
            FROM property_photo
            WHERE property_id = '%s'
            ORDER BY property_photo_main DESC
            LIMIT %s
        ',
        mysql_real_escape_string($property['property_id']),
        mysql_real_escape_string($max)
    ));

    $photos = array();
    while ($row = mysql_fetch_assoc($result)) {
        $photos[] = $row;
    }

    return $photos;
}

<?php

// lib/util.php

function escape($value, $context = 'html')
{
    switch ($context) {
        case 'html':
            return htmlspecialchars($value, ENT_QUOTES);
        case 'js':
            throw new Exception('JS escaper not yet implemented');
        default:
            throw new Exception("No escaper for context $context");
    }
}

function render($__template__, $__params__)
{
    extract($__params__);

    ob_start();
    require $__template__;
    $content = ob_get_clean();

    return $content;
}

<!-- templates/properties.xml.php -->

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<root>
    <kyero>
        <feed_version>1</feed_version>
    </kyero>

    <?php foreach ($properties as $property): ?>
        <property>
            <date><?php echo escape($property['property_date_modified']) ?></date>
            <ref><?php echo escape($property['property_agent_ref']) ?></ref>
            <price><?php echo escape($property['property_price']) ?></price>
            <price_freq><?php echo escape($property['property_price_frequency']) ?></price_freq>
            <property_part_ownership><?php echo escape($property['property_part_ownership']) ?></property_part_ownership>
            <property_leasehold><?php echo escape($property['property_leasehold']) ?></property_leasehold>
            <type>
                <en><?php echo escape($property['[property_type_name']) ?></en>
            </type>
            <town><?php echo escape($property['property_town']) ?></town>
            <province><?php echo escape($property['province_name']) ?></province>
            <location_detail><?php echo escape($property['property_province']) ?></location_detail>
            <beds><?php echo escape($property['property_beds']) ?></beds>
            <baths><?php echo escape($property['property_baths']) ?></baths>
            <pool><?php echo escape($property['property_pool']) ?></pool>
            <desc>
                <en><?php echo escape($property['property_description_en']) ?></en>
                <es><?php echo escape($property['property_description_es']) ?></es>
            </desc>
            <images>
                <?php foreach (find_photos_by_property($property) as $photo): ?>
                    <image>
                        <url><?php echo escape(sprintf('%sproperty_pics/%s/400/%s', $site_url, $property['property_id'], $photo['property_photo_filename'])) ?></url>

                        <?php if ($photo['property_photo_main']): ?>
                            <primary>1</primary>
                        <?php endif ?>

                        <title>
                            <en><?php echo escape($photo['property_photo_name']) ?></en>
                        </title>
                    </image>
                <?php endforeach ?>
            </images>
        </property>
    <?php endforeach ?>
</root>

<!-- templates/homepage.html.php -->

<a target="_blank" href="<?php echo escape($xml_path) ?>"><?php echo escape($xml_path) ?></a>
5

My initial reaction, before spotting the nested loops, also included me asking myself :

“I wonder if you could set things up so that you export the data from the db as xml?” [google]mysql export as xml[/google]

"why don’t you use simplexml or, more perhaps, DOM xml to build the xml? xml manipulation

6