Pushing Podio
Search

Searching Podio Lightning Fast Using Multiple Parameters MySQL Integration

Thanks to a brand new option in ProcFu that allows you to write your own custom PHP functions that accept custom arguments and return the result, it has allowed me to uncover a completely new way of searching your Podio data and getting the results back into GlobiFlow quickly and easily. It involves the built-in GF to MySQL feature, that you can access in any app by clicking the gear icon and selecting MySQL.

The attached video takes you through how to set up a connection to MySQL using GlobiFlow, and with ProcFu, how to access that data with MySQL queries, get the results into GlobiFlow as a JSON object, and from there, the sky’s the limit.

This is immensely helpful if you have to search Podio apps that contain tens of thousands of records and you want the results fast.

If you’re interested in learning more about how this code actually works, I highly recommend reading through the PHP and SQL tutorials on W3Schools.

CODE EXAMPLES

getAllProjectsJSON:

function getDatabase() {
    $servername = "servername";
    $username = "username";
    $password = "password";
    $dbname = "database";

    $conn = mysqli_connect($servername, $username, $password, $dbname);
    
    return $conn;
}
    
$conn = getDatabase();
$sql = "SELECT id, name, category, startDate, endDate FROM projectstest";
$result = $conn->query($sql);
    
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        array_push($array, [
            'id' => $row['id'],
            'name' => $row['name'],
            'category' => $row['category'],
            'startDate' => date("Y-m-d", strtotime($row['startDate'])),
            'endDate' => date("Y-m-d", strtotime($row['endDate']))
        ]);
    }
} else {
    $array = [];
};
    
mysqli_close($conn);

return json_encode($array);

getAllProjectsTable:

function getDatabase() {
    $servername = "servername";
    $username = "username";
    $password = "password";
    $dbname = "database";

    $conn = mysqli_connect($servername, $username, $password, $dbname);
    
    return $conn;
}

$conn = getDatabase();

$sql = "SELECT id, name, category, startDate, endDate FROM projectstest";
$result = $conn->query($sql);

$table = '
  <table style="width: 100%">
  
  <thead>
    <th>Name</th>
    <th>Category</th>
    <th>Start Date</th>
    <th>End Date</th>
  </thead>';

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $table = $table . '
          <tr>
            <td>' . $row['name'] . '</td>
            <td>' . $row['category'] . '</td>
            <td>' . date("Y-m-d", strtotime($row['startDate'])) . '</td>
            <td>' . date("Y-m-d", strtotime($row['endDate'])) . '</td>
          </tr>';
    }
} else {
    $table = '0 Results';
};

if ($table != '0 Results') {
    $table = $table . '</table>';
};

mysqli_close($conn);

return $table;

getAllProjectsJSONByCategory:

$category = json_decode($pf_payload,true)['category'];

function getDatabase() {
    $servername = "servername";
    $username = "username";
    $password = "password";
    $dbname = "database";

    $conn = mysqli_connect($servername, $username, $password, $dbname);
    
    return $conn;
}

$conn = getDatabase();

$sql = "SELECT id, name, category, startDate, endDate FROM projectstest WHERE category='". $category . "'";
$result = $conn->query($sql);

$array = [];

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        array_push($array, [
            'id' => $row['id'],
            'name' => $row['name'],
            'category' => $row['category'],
            'startDate' => date("Y-m-d", strtotime($row['startDate'])),
            'endDate' => date("Y-m-d", strtotime($row['endDate']))
        ]);
    }
} else {
    $array = [];
}

mysqli_close($conn);

return json_encode($array);

getAllProjectsJSONBetweenDates:

$startdate = json_decode($pf_payload,true)['startDate'];
$enddate = json_decode($pf_payload,true)['endDate'];

function getDatabase() {
    $servername = "servername";
    $username = "username";
    $password = "password";
    $dbname = "dbname";

    $conn = mysqli_connect($servername, $username, $password, $dbname);
    
    return $conn;
}

$conn = getDatabase();

$sql = "SELECT id, name, category, startDate, endDate FROM projectstest WHERE startDate BETWEEN '" . $startdate . "' AND '" . $enddate . "'";
$result = $conn->query($sql);

$array = [];

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        array_push($array, [
            'id' => $row['id'],
            'name' => $row['name'],
            'category' => $row['category'],
            'startDate' => date("Y-m-d", strtotime($row['startDate'])),
            'endDate' => date("Y-m-d", strtotime($row['endDate']))
        ]);
    }
} else {
    $array = [];
}

mysqli_close($conn);

return json_encode($array);

(c) 2024 Globi Web Solutions | Join the Discussion in our Podio Workspace.

x