Replace Record Number [num] with another field

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

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

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

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

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

Fantastic!

I had to change:-

'where'       => "`reference_number` = '$reference_number' AND `num` = '$num'",

to

'where'       => "`reference_number` = '$reference_number' OR `num` = '$num'",

and it worked a treat!

I was able to use both:-

http://www.holidaysandhomesespana.com/property/listingDetail.php?H62770-142445 (The Google link that did not work before - Listing not found! error - but now works)

and

http://www.holidaysandhomesespana.com/property/listingDetail.php?H62770-151779  (The Actual link that did work before and still works)

Both worked perfectly.

I even changed the code to:-

'where'       => "`reference_number` = '$reference_number'",

Because now that we have the "reference_number" I believe we only need to search using that now.

Also (as you confirmed) I do not need to change the list page code either now.

I am going to cary out a bit more testing and then set live tomorrow.

Thank you very much Greg - you are a star!!

Andy