the_permalink in mysql query?

First, the_permalink() does not return the permalink, it displays it. Use get_permalink() to return the permalink.

Second, I don’t think you will be able to query on a pretty permalink because pretty permalinks are not stored in the database. The ‘guid’, or non-pretty link is what is stored.

Try using $post->guid instead of get_permalink().

Mysql is beyond my current skill set so please forgive me.

The following code works as intended, yet I need to dynamically generate the current url, and yes, the “pretty” url is stored in this particular table.

<?php
$result = mysql_query("SELECT link_title, link_destination, link_total_clicks
FROM wp_tracking_clicks, wp_tracking_links
WHERE wp_tracking_clicks.link_id = wp_tracking_links.link_id
AND wp_tracking_clicks.click_refer = 'https://www.website.com/category/post/'");

while ($row = mysql_fetch_assoc($result)) {
    echo $row['link_title'];
    echo $row['link_destination'];
    echo $row['link_total_clicks'];
}
?>

Using either the_permalink or get_permalink doesn’t seem to make a difference.
neither does passing these to a variable and then putting the variable in the query like the following…

<?php
$test=get_permalink();
echo $test;

$result = mysql_query("SELECT link_title, link_destination, link_unique_clicks
FROM wp_tracking_clicks, wp_tracking_links
WHERE wp_tracking_clicks.link_id = wp_tracking_links.link_id,
AND wp_tracking_clicks.click_refer = '$test'");

while ($row = mysql_fetch_assoc($result)) {
    echo $row['link_title'];
    echo $row['link_destination'];
    echo $row['link_unique_clicks'];
}
?>

Try using $post->guid instead of get_permalink().

Do you suggest I use this directly in the query?

<?php
$result = mysql_query("SELECT link_title, link_destination, link_unique_clicks
FROM wp_tracking_clicks, wp_tracking_links
WHERE wp_tracking_clicks.link_id = wp_tracking_links.link_id,
AND wp_tracking_clicks.click_refer = '$post->guid'");

while ($row = mysql_fetch_assoc($result)) {
    echo $row['link_title'];
    echo $row['link_destination'];
    echo $row['link_unique_clicks'];
}
?>

First, did this echo show you the correct link?

<?php
$test=get_permalink();
echo $test;

If so, it may be that the links in the database are encoded. Can you use phpMyAdmin or a similar tool to see what is in the DB?

And yes, putting $post->guid in the query should insert the guid. However, if the table has the pretty link in it, that will not work.

Yes I can see what’s in the database. It is the pretty link. The table is created by a plugin and not wordpress itself.

Am I correct in thinking that if you take the output from this:

<?php
$test=get_permalink();
echo $test;

and hard-code it into the query that it works?

Yes, thats correct. The problem isn’t “the_permalink”. If I hard-code the current url into the query it works. If I try to put the variable into the query it does not work. It does not matter how I define the variable either.

$test="the_permalink()"
and
$test="https://www.website.com/category/post/"
both fail.

This leads me to believe that I’m incorrectly passing the variable into the query. Google yields no answers to this issue either. And all indications are that I’m passing the variable to the query correctly.

You might want to try this to see what actual SQL is generated:

<?php
$test=get_permalink();
echo $test;

$sql = "SELECT link_title, link_destination, link_unique_clicks
FROM wp_tracking_clicks, wp_tracking_links
WHERE wp_tracking_clicks.link_id = wp_tracking_links.link_id,
AND wp_tracking_clicks.click_refer = '$test'";
echo "<p>SQL:$sql</p>";

Try this both with using $test, and with hard-coding the value to see if there is any difference.

And, I will remind you that the_permalink() does not return a value, it sends it straight to the screen output. So this will never work:

$test = the_permalink();

$test will always be null. To assign a value, you must use get_permalink().

I think I’ve stumbled into the answer. Defining the variable as…

$test=strip_tags(get_permalink());

…seems to be working. strip_tags. Should have known.

Thanks for your advice vtxyzzy.

Just read your latest and retried it. Strip_tags isn’t the answer either…. It was get_permalinks all along. Missed that permutation in the code somewhere along the line. Thanks again!

Glad you got it! Now, please use the dropdown at top right to mark this topic ‘Resolved’.