Remove record num from _link

Re: [kblm] Remove record num from _link

Wed, 12 Dec 2012 11:14:27 -0800

Hi Kevin,

What was happening is that the value stored in the database was "Acorn Lodge " but we were searching for "Acorn_Lodge", which doesn't take allow for the trailing space at the end. Using TRIM(address) removes the trailing space, so we are then comparing "Acorn Lodge" to "Acorn_Lodge". The "_" is a MySQL is a wildcard that can represent any character, in this case a space. So these two strings are now the same.

Hope this helps. Please let me know if you have any other questions.

Thanks,

Re: [kblm] Remove record num from _link

Wed, 12 Dec 2012 10:59:26 -0800

Jason thanks! That looks like it's working.

Any ideas what was happening? Still not sure I completely understand all the preg_replace code.

Thanks for the help!

-Kevin

Re: [kblm] Remove record num from _link

Wed, 12 Dec 2012 10:41:13 -0800

Hi Kevin,

There isn't a trim taking place, since the only difference between the 2 tests is the presence of a trailing space inside the record, which wouldn't appear in the query. One thing you can try is to trim the address field in the database from inside your where clause like this:

 // load records
list($listingsRecords, $listingsMetaData) = getRecords(array(
  'tableName' => 'listings',
  'where' => " TRIM(address) LIKE '".mysql_escape($title_like)."'",
  'limit' => '1',
));

This should remove any leading or trailing spaces before the LIKE comparison is done.

Hope this helps

Re: [kblm] Remove record num from _link

Wed, 12 Dec 2012 10:15:57 -0800

Jason,

Ok, with the trim NOT in the script....and the record having NO trailing spaces in the Title in the CMS.....this is the resulting query (which works):

SELECT SQL_CALC_FOUND_ROWS `property_listings`.*
FROM `cms_property_listings` as `property_listings`
WHERE (property_name LIKE 'Acorn_Lodge')
ORDER BY SUBSTRING_INDEX(property_name,'The ',-1)
LIMIT 1
tableName = 'property_listings' AND
fieldName IN ('photos') AND
recordNum IN (4)
ORDER BY `order`, num
SELECT SQL_CALC_FOUND_ROWS `accounts`.*
FROM `cms_accounts` as `accounts`
WHERE (`num` IN (1))
ORDER BY fullname, username

Now, if I just add a space to the end of the record title in the CMS....I then get this query:

SELECT SQL_CALC_FOUND_ROWS `property_listings`.*
FROM `cms_property_listings` as `property_listings`
WHERE (property_name LIKE 'Acorn_Lodge')
ORDER BY SUBSTRING_INDEX(property_name,'The ',-1)
LIMIT 1
Warning: Cannot modify header information - headers already sent by (output started at /home/lmdianet/public_html/mmp/cmsAdmin/lib/viewer_functions.php:411) in /home/lmdianet/public_html/mmp/cmsAdmin/lib/common.php on line 1063 Record not found!

Looks to me like something IS trimming this already, even though I haven't added the TRIM to the link.

-Kevin

Re: [kblm] Remove record num from _link

Wed, 12 Dec 2012 10:10:41 -0800

Hi Kevin,

To get a better idea of exactly what's happening, try putting a debugSql option in your getRecords call:

 // load records
list($listingsRecords, $listingsMetaData) = getRecords(array(
  'tableName' => 'listings',
  'where' => mysql_escapef('address LIKE ?', $title_like),
  'limit' => '1',
  'debugSql'  =>  '1',
));

This will print out the exact query being executed. If you could post this along with the exact value of the address field in the record you are expecting to retrieve. That should point us in the right direction.

Thanks

Re: [kblm] Remove record num from _link

Wed, 12 Dec 2012 09:44:37 -0800

Jason thanks.

Technically that does help. Forgot I could just trim the string. However, I think I'm thinking of this incorrectly, because even when I trim the string, the CMS still doesn't find the record. I'm guessing because the record itself HAS the spaces, therefore the LIKE isn't matching?

I guess, can someone explain to be exactly what these preg_replace statements are doing? Maybe that will help me understand how to tackle this.

These are on the list page to form the link:

$propertyLink = preg_replace('/[^a-z0-9\.\-\_]/i', '-', $record['property_name']);
$propertyLink = preg_replace("/(^-+|-+$)/", '', $propertyLink);

These are on the detail page:

$title_like = preg_replace("/_/", '\\_', @$_SERVER['QUERY_STRING']);
$title_like = preg_replace("/-/", '_', $title_like);

Thanks for any help.

-Kevin

Re: [kblm] Remove record num from _link

Tue, 11 Dec 2012 11:06:40 -0800

Hi Kevin,

You can use the PHP function trim() to do this, for example:

$query_string = trim ( preg_replace('/[^a-z0-9\.\-\_]/i', '-', $record['title']) );

Hope this helps,

Re: [kblm] Remove record num from _link

Tue, 11 Dec 2012 06:54:10 -0800

Hey Chris,

Your code is working great. I do have one more question.....is there anyway to have the code strip out or ignore trailing spaces in the title field? We have some clients that inadvertently space an extra space at the end....this causes this link method to not find the record.

Can this be done in the preg_replace.....or is there a way in the admin to strip out any trailing spaces?

Thanks!

-Kevin

Re: [Chris] Remove record num from _link

Mon, 26 Nov 2012 19:27:30 -0800

I'll have to test some more but right now it looks to be working. Thanks Chris!!

-Kevin

Re: [kblm] Remove record num from _link

Mon, 26 Nov 2012 19:23:20 -0800

Hi Kevin,

Thanks for the details! I think what's happening here is that the adjacent space and number sign (" #") in "1234 Main Street #3" are getting turned into a single hyphen ("-") in the link, and that's being turned into a LIKE expression which is only looking for one character there (e.g. LIKE "1234_Main_Street_3").

I think the simplest solution here is to not truncate adjacent hyphens into one. Wherever you're generating links, remove the plus sign from this regular expression:

$query_string = preg_replace('/[^a-z0-9\.\-\_]+/i', '-', $record['title']);

It should look like this instead:

$query_string = preg_replace('/[^a-z0-9\.\-\_]/i', '-', $record['title']);

That should cause your link to have an extra hyphen in it, like this: /home-listings-detail.php?1234-Main-Street--3

Does that help?

Re: [Chris] Remove record num from _link

Mon, 26 Nov 2012 19:07:49 -0800

Can't give a URL right now because I'm on a protected server.

I can give you my details.

I have 2 pages, a listings page and a detail page.

On the listings page, within the foreach loop I create a link as follows:

$propertyLink = preg_replace('/[^a-z0-9\.\-\_]+/i', '-', $record['address']);
$propertyLink = preg_replace("/(^-+|-+$)/", '', $propertyLink);
?>

I then for each record create the manual URL like this:

LINK

For a record that has a property address of 1234 Main Street, I get the nice URL of this: /home-listings-detail.php?1234-Main-Street

On the detail page, at the top I have the following:


  //CODE USED TO REMOVE THE RECORD num from the URL
  $title_like = preg_replace("/_/", '\\_', @$_SERVER['QUERY_STRING']); 
  $title_like = preg_replace("/-/", '_', $title_like); 

  // load records
  list($listingsRecords, $listingsMetaData) = getRecords(array(
    'tableName'   => 'listings',
    'where'       => mysql_escapef('address LIKE ?', $title_like),
    'limit'       => '1',
  ));
  $listingsRecord = @$listingsRecords[0]; // get first record

  // show error message if no matching record is found
  if (!$listingsRecord) {
    header("HTTP/1.0 404 Not Found");
    print "Record not found!";
    exit;
  }

?>

For that test URL of /home-listings-detail.php?1234-Main-Street, the code works fine. It's loads the detail page.

However, if I change the property address to 1234 Main Street #3, the url it wants to go to is: /home-listings-detail.php?1234-Main-Street-3

For the detail page I get the error thrown from the CMS: Record not found!

Hope that helps.....let me know.

-Kevin

Re: [kblm] Remove record num from _link

Mon, 26 Nov 2012 18:59:24 -0800

Hi kblm,

What code exactly are you using at the top of your page, and can you provide the example URL that is being generated for "1 Main Street #3" and failing to find the record?

Thanks,
Chris

Re: [Chris] Remove record num from _link

Mon, 26 Nov 2012 18:53:45 -0800

Resurrecting this old thread because I've run into an issue that I'm wondering if it can be solved.

This code works fine.....as long as the record title doesn't end in a number.

I'm running across this issue with some real estate clients. We might have a record like: 1 Main Street #3

In this case, the code fails, I'm guessing because now it's looking for a number.

Any ideas if there is a workaround?

Thanks!

-Kevin

Re: [Jason] Remove record num from _link

Wed, 27 Oct 2010 12:01:22 -0700

That did the trick....Thanks for your help Jason.

Re: [rgc] Remove record num from _link

Wed, 27 Oct 2010 11:51:39 -0700

Hi,

What's happening is you are echoing $query_string in 2 places, where you only need to be echoing it in one.

Remove the line indicated below:


$query_string = preg_replace('/[^a-z0-9\.\-\_]+/i', '-', $record['title']);
$query_string = preg_replace("/(^-+|-+$)/", '', $query_string);
echo $query_string;
?>
|

Hope this helps.

Re: [Jason] Remove record num from _link

Wed, 27 Oct 2010 11:45:31 -0700

I appreciate your help Jason....we are almost there.
The correct link is now appearing, however i can not figure out what is causing the page title to be duplicated, first just title no link, then title with the link, looks like this :
Page-One | Page-One Page-Two | Page-Two Page-Three | Page-Three

How do i correct this? Thanks!

Here is my latest body code:

$query_string = preg_replace('/[^a-z0-9\.\-\_]+/i', '-', $record['title']);
$query_string = preg_replace("/(^-+|-+$)/", '', $query_string);
echo $query_string;
?>
|

Re: [rgc] Remove record num from _link

Wed, 27 Oct 2010 11:28:29 -0700
Hi,

The code to create the $query_string variable needs to take place inside the foreach loop like this:

$query_string = preg_replace('/[^a-z0-9\.\-\_]+/i', '-', $record['title']); $query_string = preg_replace("/(^-+|-+$)/", '', $query_string); |

Hope this helps.

Re: [Jason] Remove record num from _link

Wed, 27 Oct 2010 11:19:07 -0700
All the navigation links are now appearing the same - http://www.mylcient.com/about.htm?

What needs to be changed or added to get the title field to display after the ?
Here is my complete page code:

$query_string = preg_replace('/[^a-z0-9\.\-\_]+/i', '-', $record['title']);
$query_string = preg_replace("/(^-+|-+$)/", '', $query_string);
echo $query_string;
?>
// load viewer library
$libraryPath = 'cm/lib/viewer_functions.php';
$dirsToCheck = array('/usr2/home/myclient.com/htdocs/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

// load records
list($aboutRecords, $aboutMetaData) = getRecords(array(
'tableName' => 'about',
));

?>

Untitled Document

|

Re: [rgc] Remove record num from _link

Wed, 27 Oct 2010 10:38:12 -0700
Hi,

You would use the same code that Chris posted to just use the title for your link:
$query_string = preg_replace('/[^a-z0-9\.\-\_]+/i', '-', $record['title']); $query_string = preg_replace("/(^-+|-+$)/", '', $query_string); ?>

The variable $query_string is what you would append to your .php page in the link like this:

Link

Hope this helps.

Re: [kblm] Remove record num from _link

Wed, 27 Oct 2010 09:52:24 -0700
This worked well on my clients site also. However on the actual detail page, I have a viewer displaying links to other related sub pages - what code do i use to modify the links so it will display the link without the number as well?:

This is the code i have used to display navigation links to the other pages in the about section:
// load viewer library
$libraryPath = 'cm/lib/viewer_functions.php';
$dirsToCheck = array('/usr2/home/clientsdomain.com/htdocs/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

// load records
list($aboutRecords, $aboutMetaData) = getRecords(array(
'tableName' => 'about',
));

?>

|

Re: [chris] Remove record num from _link

Tue, 12 Oct 2010 09:08:22 -0700
Chris,

Thanks. This worked.

-Kevin

Re: [kblm] Remove record num from _link

Mon, 11 Oct 2010 23:58:34 -0700
Hi Kevin,

You'll need to manually construct the link, yes.

One thing to keep in mind is that when CMS Builder generates those URLs, it replaces all characters that are not in the set [a-z0-9._] with hyphens, therefore, (theoretically) many different titles could turn into "ADAMS-ELEMENTARY-SCHOOL".

You can use this code to generate a link like CMS Builder does:

$query_string = preg_replace('/[^a-z0-9\.\-\_]+/i', '-', $record['title']); $query_string = preg_replace("/(^-+|-+$)/", '', $query_string); echo $query_string; ?>

Then, on your detail page, you'll need to replace the hyphens with underscores to be able to match any character in their place (but not before escaping your existing underscores:)

$title_like = preg_replace("/_/", '\\_', @$_SERVER['QUERY_STRING']); $title_like = preg_replace("/-/", '_', $title_like); list($sampleRecords, $sampleMetaData) = getRecords(array( 'tableName' => 'sample', 'where' => mysql_escapef('title LIKE ?', $title_like), )); $sampleRecord = @$sampleRecords[0]; // get first record

I hope this helps! Please let me know if you have any questions.

Remove record num from _link

Mon, 11 Oct 2010 17:10:24 -0700
If I have a multi record section where the Title field has to be unique, is there anyway to not have the -num be appended to the url string?

So I have a page with a bunch of local school listings. Instead of having the url be:

/schooldetail.php?ADAMS-ELEMENTARY-SCHOOL-1

I'd like it to be:
schooldetail.php?ADAMS-ELEMENTARY-SCHOOL

Is this possible? Do I have to manually construct the link? Since I am making the Title field unique, I'm hoping I can use just the Title in the URL.

Thanks.

-Kevin