Replace Record Number [num] with another field

7 posts by 2 authors in: Forums > CMS Builder
Last Post: March 5, 2014   (RSS)

By andybarn - March 3, 2014

Hi

I'm downloading real estate data via XML on a weekly basis and have to do a full refresh of the data every time.

That means the default search/display field Record Number [num] changes weekly for each listing record. This is creating problems for bookmarking records, searching, Google listings etc.

How do I change it so one of my own (constant) numbered fields (i.e. field name "reference_number") is used at the end of the URL for finding/searching instead of num?

I searched this on your forums and came across http://www.interactivetools.com/forum/forum-posts.php?postNum=2218902#post2218902 

I think the answer in this thread will solve my problem but am unclear exactly what to do.

The header of my listingDetail.php file is :-

<?php
  /* STEP 1: LOAD RECORDS - Copy this PHP code block to the TOP of your page BEFORE anything else. */
  require_once "init.php";

  list($listingRecords, $listingDetails) = getRecords(array(
    'tableName'   => 'listings',
    'joinTable'   => 'homepages',
    'where'       => whereRecordNumberInUrl(1),
    'limit'       => '1',
  ));
  $listing = @$listingRecords[0];


  if (!$listing) { print "Listing not found!"; exit; }

?>


from the forum thread mentioned above,  I believe I need to change the code above to get the result I want. Could you show me what needs to be changed please and if I need to do anything else.

Thanks for your help

Andy

By gregThomas - March 4, 2014

Hi Andy,

It looks like you're heading in the right direction, the key to getting this working is to change the field that the getRecords searches on, something like this would probably work:

  //Get the last number from the URL
  $urlNumValue = getLastNumberInUrl();

  //Get the record based on it's reference_number
  list($listingRecords, $listingDetails) = getRecords(array(
    'tableName'   => 'listings',
    'joinTable'   => 'homepages',
    'where'       => "`reference_number` = '$urlNumValue'",
    'limit'       => '1',
  ));
  $listing = @$listingRecords[0];


  if (!$listing) { print "Listing not found!"; exit; }

Note: This code won't work if your reference number also contains alpha characters. So the code works by getting the last number from the URL and setting it to $urlNumValue, then a custom where statement is used to search the database and return the record with the matching reference number.

You'll also need to change your record list page to pass the correct value, I'm guessing that your list page has link to the detail page that has a link something like this?

<a href="<?php echo $record['link']; ?>" ><?php echo $record['title']; ?></a>

You'll need to create a custom detail page link that passes the reference number:

<?php $shareLink = 'http://yoursitename.com/your-detail-page.php?'.urlencode($record['title']).'-'.intval($record['reference_number']); ?>
<a href="<?php echo $shareLink; ?>" ><?php echo $record['title']; ?></a>

So this code creates a custom link that has the reference number at the end of it as opposed to the num value. 

Let me know if you have any questions on how this system works, or any issues getting it up and running. 

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By andybarn - March 4, 2014

Hi Greg

Thank you for your detailed reply - it looks great and just what I need!

The only problem is with your note though (and I suspect you guessed it may be a problem) .

Your note said :-  "This code won't work if your reference number also contains alpha characters."

Unfortunately it does contain alpha characters - is there anyway round this as your solution sounds great and should solve my problem.

Thanks

Andy

By gregThomas - March 4, 2014

Hi Andy,

It's not too difficult to modify the code to get around this, it just means you won't be able to have as smart looking URL's. So you need to update the code on the list page to this:

<?php $shareLink = 'http://yoursitename.com/your-detail-page.php?reference_number='.$record['reference_number']; ?>
<a href="<?php echo $shareLink; ?>" ><?php echo $record['title']; ?></a>

So now you're just sending over the reference number in a standard URL string format. Then the $where variable on the detail page needs to look like this:

  //Get the last number from the URL
  $urlNumValue = mysql_escape_string(@$_REQUEST['reference_number']);

  //Get the record based on it's reference_number
  list($listingRecords, $listingDetails) = getRecords(array(
    'tableName'   => 'listings',
    'joinTable'   => 'homepages',
    'where'       => "`reference_number` = '$urlNumValue'",
    'limit'       => '1',
  ));
  $listing = @$listingRecords[0];


  if (!$listing) { print "Listing not found!"; exit; }

This is example code, so you might have to make a few changes to get it working with your site.

So we're just getting the reference number from the URL, MySQL escaping it, then finding the matching record from the home pages section. 

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By andybarn - March 5, 2014

Hi Greg

I believe we are getting really close, thanks.

The main problem I have is that on Google the listing detail is listed as:-

http://www.holidaysandhomesespana.com/property/listingDetail.php?H62770-142445

But the actual URL now that the site has been refreshed from the XML is:-

http://www.holidaysandhomesespana.com/property/listingDetail.php?H62770-151779

You will see that after the ? in the URL we have the field "reference_number" followed by the "num" and the only difference between the two is the "num"

Therefore, the question is, can we not capture the "reference_number" from the URL and in the " 'where'    => "  statement we add the "reference_number" instead of "whereRecordNumberInUrl(1)" which I guess is the "num"

Maybe better than that, could we not then make the " 'where'    => " statememt equal to "reference_number" OR "whereRecordNumberInUrl(1)" and we would then not need to change the list page which is  working OK anyway using "whereRecordNumberInUrl(1)" .

Please forgive my ignorance here if what I am say does not make saense, but if it is possible could you give me the code/syntax to do this as I do not know how to a) capture the "reference_number" from the URL coming from Google or how to right the syntax for the " 'where'    => " OR statement?

Thanks for your patience with this - your help is much appreciated.

Andy

By gregThomas - March 5, 2014

Hi Andy,

The previous code I gave you should be fine for adding/capturing the reference number from the URL. It would convert the URL in the post above to:

http://www.holidaysandhomesespana.com/property/listingDetail.php?reference_number=H62770

I hadn't realised that you already had the reference number in the URL, you could try using the following code on your detail page to extract it:

  //Sanity check: if there is no request in the url exit.
  if(!@$_REQUEST){ echo "No data in URL"; exit; }

  //Get the array keys
  $arrayKeys = array_keys($_REQUEST);

  //If the first entry doesn't contain a dash, exit
  if (strpos($arrayKeys[0],'-') == false) { echo "No dash found"; exit; }

  //Explode the array key into it's two separate parts. 
  list($reference_number, $num) = explode('-' ,$arrayKeys[0]);

  //Get the record based on it's reference_number
  list($listingRecords, $listingDetails) = getRecords(array(
    'tableName'   => 'listings',
    'joinTable'   => 'homepages',
    'where'       => "`reference_number` = '$reference_number' AND `num` = '$num'",
    'limit'       => '1',
  ));
  $listing = @$listingRecords[0];

I've not tested this code, so you might have to make a few changes to get it working. The advantage of this code is you don't have to make any changes to the current list page code. 

The explode function takes the string from the URL, and makes an array of the items delimited on the dash. The list function takes each element from the array, and assigns it to the listed variables. 

Let me know if you have any questions.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com