U54 U54 - 6 months ago 18
MySQL Question

YOURLS plugin to filter API call for specific MySQL column

I have a website which has a feature to allow users to create shortened URLs. To handle the URL shortening I am using YOURLS (an open-source URL shortener) which I run on a different server of mine and connect the two sites through API calls.

I have created a YOURLS plugin with some custom API actions so when a user creates a short URL it will attach their user ID from my website to the yourls_url table as shown below:

enter image description here

When a user visits their "My Links" page I need to make an API call to return all of the short URL's created by that user. To do this it should just be as simple as creating a filter for "api_result_stats".

This is the filter I have created which is suppose to only return the URLs created by the specified user:

yourls_add_filter( 'api_result_stats', 'separate_users_api_stats' );

function separate_users_api_stats() {
global $ydb;

$user = ( isset( $_REQUEST['user'] ) ? $_REQUEST['user'] : '' );

$table = YOURLS_DB_TABLE_URL;
$result = $ydb->query("SELECT * FROM `$table` WHERE `user` = '" . $user . "'");

return $result > 0;

}


To test this I can try
http://example.com/yourls-api.php?action=stats&user=test123&limit=10&filter=last&signature=xxxxxxxxxx
but unfortunately all I receive is:

enter image description here

I'm not too familiar with PHP/MYSQL so I've been stuck on this for quite some time now, if anyone has any ideas as to where I'm going wrong or could offer an example it would be greatly appreciated!

U54 U54
Answer

Here is what solved this for me:

yourls_add_filter( 'api_result_stats', 'separate_users_api_stats' );

function separate_users_api_stats() {
    global $ydb;

    $user = ( isset( $_REQUEST['user'] ) ? $_REQUEST['user'] : '' );

    $table = YOURLS_DB_TABLE_URL;

    $query = "SELECT * FROM `$table` WHERE `user` = '" . $user . "'";

    $result = $ydb -> get_results($query);

    return $result;

}