Wavesdream Blog

Tag: PHP (Page 1 of 2)

Fetch data from Google Sheet and show in HTML using PHP

May 6, 2025 /

This documentation explains how to use the provided PHP code to fetch data from Google Sheets and display it in a custom layout using HTML/CSS.

Setup Instructions

1. Get Your Google Sheet ID

  1. Open your Google Sheet in a web browser
  2. Look at the URL in the address bar – it will look like:
https://docs.google.com/spreadsheets/d/BmzaSyBzQ0cRTrbf_vxrB75nh8AoV3BtawPiiCQ/edit#gid=0

The long string between /d/ and /edit is your Sheet ID (in this example: BmzaSyBzQ0cRTrbf_vxrB75nh8AoV3BtawPiiCQ)

2. Create a Google API Key

  1. Go to the Google Cloud Console
  2. Create a new project or select an existing one
  3. Navigate to “APIs & Services” > “Library”
  4. Search for “Google Sheets API” and enable it
  5. Go to “APIs & Services” > “Credentials”
  6. Click “Create Credentials” and select “API key”
  7. Copy your new API key
  8. (Optional) Restrict the API key to only work with the Sheets API for security

3. Configure the PHP Code

Replace these values in the code:

$sheetID = "BmzaSyBzQ0cRTrbf_vxrB75nh8AoV3BtawPiiCQ"; // Your Sheet ID
$apiKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; // Your API Key
$rows_count = 8; // Number of rows to display

4. Sheet Configuration

Your Google Sheet must have a named range or sheet tab that matches what you put in the API URL (in this example: Sheet1)

Make sure your Sheet is either:

  • Publicly accessible (set to “Anyone with the link can view”)
  • Or shared with the email address associated with your API key

Here is the entire code. 

<?php
// Google Sheets API configuration
$sheetID = "BmzaSyBzQ0cRTrbf_vxrB75nh8AoV3BtawPiiCQ"; // Replace with your actual Sheet ID
$apiKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; // Replace with your API Key
$apiURL = "https://sheets.googleapis.com/v4/spreadsheets/$sheetID/values/Sheet1?key=$apiKey";

$rows_count = 8; // Number of rows to be displayed

// Fetch the Google Sheets data
$response = file_get_contents($apiURL);
$data = json_decode($response, true);
if (isset($data['values']) && count($data['values']) > 1) {
for ($i = 1; $i <= min($rows_count, count($data['values']) - 1); $i++) {	

// Get the column wise data from Google Sheet, if no value then show N/A
$column_1 = isset($data['values'][$i][0]) ? htmlspecialchars($data['values'][$i][0]) : "N/A";
$column_2 = isset($data['values'][$i][1]) ? htmlspecialchars($data['values'][$i][1]) : "N/A";
$column_3 = isset($data['values'][$i][2]) ? htmlspecialchars($data['values'][$i][2]) : "N/A";
$column_4 = isset($data['values'][$i][3]) ? htmlspecialchars($data['values'][$i][3]) : "N/A";
$column_5 = isset($data['values'][$i][4]) ? htmlspecialchars($data['values'][$i][4]) : "N/A";
?>

<?php } ?>
<div class="row">
<div><?php echo $column_1;?></div>
<div><?php echo $column_2;?></div>
<div><?php echo $column_3;?></div>
<div><?php echo $column_4;?></div>
<div><?php echo $column_5;?></div>
</div>
<?php } ?>

PHP script to download file from specific folder

March 11, 2025 / / 0 Comments

Below is a simple example of a PHP script that allows you to download file from a specific folder. You can link to this PHP file with the file name as a parameter.

Create a file named download.php and add the following code:

<?php
// Specify the folder where your files are stored
$folderPath = '/path/to/your/files/';

// Get the file name from the query parameter
if (isset($_GET['file'])) {
    $fileName = basename($_GET['file']);
    $filePath = $folderPath . $fileName;

    // Check if the file exists
    if (file_exists($filePath)) {
        // Set headers for download
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $fileName . '"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($filePath));

        // Read the file and output it to the browser
        readfile($filePath);
        exit;
    } else {
        echo 'File not found.';
    }
} else {
    echo 'File parameter missing.';
}
?>

Replace ‘/path/to/your/files/’ with the actual path to the folder where your files are stored. Now, you can link to this script by providing the file name as a parameter, like this:

<a href=”download.php?file=myfile.txt”>Download</a>

Make sure to adjust the link and file names accordingly. Note that this is a basic example, and you may need to add additional security measures based on your specific requirements, such as checking user permissions and validating file types.

How to upload image and PDF file using PHP

February 10, 2025 / / 0 Comments

Here is a PHP script that allows users to upload image and PDF files with a maximum size of 5 MB. The uploaded files will be renamed using the current timestamp:

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_FILES["file"])) {
    $allowedExtensions = array("jpg", "jpeg", "png", "pdf");
    $maxFileSize = 5 * 1024 * 1024; // 5 MB in bytes

    $targetDirectory = "uploads/";
    $timestamp = time();
    $targetFileName = $timestamp . "_" . basename($_FILES["file"]["name"]);
    $targetPath = $targetDirectory . $targetFileName;
    
    $fileExtension = strtolower(pathinfo($targetFileName, PATHINFO_EXTENSION));

    if (in_array($fileExtension, $allowedExtensions) && $_FILES["file"]["size"] <= $maxFileSize) {
        if (move_uploaded_file($_FILES["file"]["tmp_name"], $targetPath)) {
            echo "File uploaded successfully.";
        } else {
            echo "Error uploading file.";
        }
    } else {
        echo "Invalid file. Allowed file types: jpg, jpeg, png, pdf. Max file size: 5 MB.";
    }
}
?>

This is HTML form for file upload.

<!DOCTYPE html>
<html>
<head>
    <title>File Upload</title>
</head>
<body>
    <form method="POST" enctype="multipart/form-data">
        <input type="file" name="file" accept=".jpg, .jpeg, .png, .pdf" required>
        <button type="submit">Upload</button>
    </form>
</body>
</html>

Here’s what the script does:

  1. It checks if the form has been submitted and if a file has been uploaded.
  2. It sets the allowed file extensions ($allowedExtensions) and maximum file size ($maxFileSize).
  3. It defines the target directory ($targetDirectory), generates a new file name using the current timestamp, and constructs the target path.
  4. It checks if the uploaded file has an allowed extension and if its size is within limits.
  5. If the file meets the criteria, it moves the file to the target directory using move_uploaded_file() and echoes a success message.
  6. If the file does not meet the criteria, it echoes an error message.
  7. The HTML form allows users to select a file with the accept attribute specifying the allowed file types. After submitting the form, the PHP script processes the file upload.

Get total of MySQL column and show highest totals

January 10, 2025 / / 0 Comments

To get the total of a MySQL field and then select the three highest totals from the table, you can use the following PHP script:

<?php
// Assuming you have already established a MySQL connection

// Retrieve the total of a field and select the three highest totals
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";

// Create a new MySQL connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check the connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare and execute the SQL statement to get the total and select three highest totals
$sql = "SELECT SUM(field_name) AS total FROM table_name GROUP BY field_name ORDER BY total DESC LIMIT 3";
$result = $conn->query($sql);

if ($result && $result->num_rows > 0) {
    echo "Three highest totals: <br>";
    while ($row = $result->fetch_assoc()) {
        $total = $row['total'];
        echo $total . "<br>";
    }
} else {
    echo "No records found.";
}

// Close the database connection
$conn->close();
?>

Make sure to replace ‘your_username’, ‘your_password’, ‘your_database_name’, ‘field_name’, and ‘table_name’ with the actual values for your MySQL configuration and table structure.

In this script, the SQL query uses the SUM function to calculate the total of the specified field (field_name) and groups the results by the field values. It then orders the results in descending order of the totals and limits the result set to the top three rows.

The script retrieves the three highest totals from the query results and prints them on the screen. If no records are found, it displays a message indicating that no records were found.
Please make sure to adjust the SQL query and table/column names according to your specific table structure and requirements.

Get total of a mysql field and then select 3 all records which have heighest total from the table

To get the total of a MySQL field and then select all records that have the highest total from the table, you can use the following PHP script:

<?php
// Assuming you have already established a MySQL connection

// Retrieve the total of a field and select all records with the highest total
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";

// Create a new MySQL connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check the connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare and execute the SQL statement to get the total and select all records with the highest total
$sql = "SELECT *, SUM(field_name) AS total FROM table_name GROUP BY id HAVING total = (SELECT MAX(total) FROM (SELECT SUM(field_name) AS total FROM table_name GROUP BY id) AS temp)";
$result = $conn->query($sql);

if ($result && $result->num_rows > 0) {
    echo "Records with the highest total: <br>";
    while ($row = $result->fetch_assoc()) {
        // Access the record data
        $id = $row['id'];
        $fieldValue = $row['field_name'];
        $total = $row['total'];

        // Process or display the record as needed
        echo "ID: $id, Field Value: $fieldValue, Total: $total <br>";
    }
} else {
    echo "No records found.";
}

// Close the database connection
$conn->close();
?>

Make sure to replace ‘your_username’, ‘your_password’, ‘your_database_name’, ‘field_name’, and ‘table_name’ with the actual values for your MySQL configuration and table structure.

In this script, the SQL query uses the SUM function to calculate the total of the specified field (field_name) for each record in the table. It then groups the results by the id column and selects only those records where the total is equal to the maximum total value across all records.
The script retrieves the records with the highest total from the query results and prints them on the screen. If no records are found, it displays a message indicating that no records were found. Please make sure to adjust the SQL query and table/column names according to your specific table structure and requirements.

Export MySQL data into CSV using PHP

November 25, 2024 / / 0 Comments

To fetch data from a MySQL database and export it to CSV using PHP, you can follow these steps:

<?php
// Database connection details
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch data from your table
$sql = "SELECT * FROM your_table";
$result = $conn->query($sql);

// Check if any rows are returned
if ($result->num_rows > 0) {
    // Define CSV filename
    $filename = "exported_data.csv";

    // Set headers for CSV download
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    // Create a file pointer connected to the output stream
    $output = fopen('php://output', 'w');

    // Output CSV header
    $header = array('ID', 'Name', 'Email');
    fputcsv($output, $header);

    // Output data from rows
    while ($row = $result->fetch_assoc()) {
        fputcsv($output, $row);
    }

    // Close the file pointer
    fclose($output);
} else {
    echo "No data found";
}

// Close the database connection
$conn->close();
?>

Explanation:

  1. Database Connection: Replace your_username, your_password, your_database_name, and your_table with your actual database credentials and table name.
  2. Fetch Data: The SQL query retrieves data from the specified table.
  3. CSV Headers: The header function is used to set headers for CSV download.
  4. CSV File Creation: We use fopen to create a file pointer connected to the output stream (php://output). Then, fputcsv is used to write the CSV header and data.
  5. Download CSV: Headers are set to prompt the user to download the CSV file with the specified filename.

When you run this script, it will fetch data from your MySQL table and export it to a CSV file, which will be downloaded by the user.

Import CSV data into MySQL using PHP

November 21, 2024 / / 0 Comments

Here’s a detailed example of how to import data from a CSV file into a MySQL database using PHP. The script processes each row one by one, displays a success message for each successfully inserted row, and stops the process if any error occurs, showing the error message.

Prerequisites:

  1. Ensure you have a MySQL database and table set up to store the CSV data.
  2. Adjust the database connection details and table schema as needed.

Database Setup:

Assume you have a MySQL table named csv_import with columns id, name, and email.

CREATE TABLE csv_import (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Here’s a PHP script that handles the CSV import process:

<?php
// Database connection details
$servername = "your_servername";
$username = "your_username";
$password = "your_password";
$dbname = "your_dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if (isset($_POST['submit'])) {
    $csvFile = $_FILES['csv']['tmp_name'];

    if (is_file($csvFile)) {
        // Open the CSV file
        if (($handle = fopen($csvFile, "r")) !== FALSE) {
            $rowNumber = 0;

            // Process each row of the CSV file
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $rowNumber++;

                // Skip the header row
                if ($rowNumber == 1) {
                    continue;
                }

                $name = $conn->real_escape_string($data[0]);
                $email = $conn->real_escape_string($data[1]);

                // Insert the data into the database
                $sql = "INSERT INTO csv_import (name, email) VALUES ('$name', '$email')";
                
                if ($conn->query($sql) === TRUE) {
                    echo "Row $rowNumber inserted successfully.<br>";
                } else {
                    echo "Error inserting row $rowNumber: " . $conn->error . "<br>";
                    break;
                }
            }

            fclose($handle);
        } else {
            echo "Error opening the CSV file.";
        }
    } else {
        echo "Invalid file.";
    }
}
$conn->close();
?>
Here the HTML to upload CSV file

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CSV Import</title>
</head>
<body>
    <form action="" method="post" enctype="multipart/form-data">
        <label for="csv">Choose CSV file:</label>
        <input type="file" name="csv" id="csv" required>
        <button type="submit" name="submit">Import CSV</button>
    </form>
</body>
</html>

Explanation:

  1. Database Connection: Establish a connection to the MySQL database using the mysqli extension.
  2. Form Handling: The script checks if the form is submitted and processes the uploaded CSV file.
  3. CSV File Processing: It opens the CSV file and processes each row one by one using a while loop.
    It skips the header row.
    It escapes the data using $conn->real_escape_string to prevent SQL injection.
    It inserts the data into the csv_import table.
    It shows a success message for each inserted row.
    If any error occurs, it shows an error message and stops the process.
  4. HTML Form: The form allows the user to upload a CSV file.

Print total of a variable inside while loop outside of the loop

November 21, 2024 / / 0 Comments

To accumulate values from a MySQL query inside a while loop and then calculate the total value outside the loop, you can use a variable to store the total value while iterating through the records. Here’s an example in PHP:

<?php
// Your database connection parameters
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create a connection to the MySQL database
$conn = new mysqli($servername, $username, $password, $database);

// Check the connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$totalValue = 0; // Initialize total value

// Your SQL query
$sql = "SELECT value_column FROM your_table_name WHERE your_conditions";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        // Access value column and accumulate the values
        $value = $row['value_column'];
        $totalValue += $value;
        
        // You can also store values in an array if needed
        // $valuesArray[] = $value;
    }
    
    echo "Total value: $totalValue"; // Print total value
    // If you stored values in an array, you can print or manipulate the array here
    // print_r($valuesArray);
} else {
    echo "No records found.";
}

// Close the database connection
$conn->close();
?>

This PHP script fetches records from the database, accumulates the values from a specific column (value_column), and calculates the total value by adding up these values inside the while loop. After the loop, it prints the total value obtained. If you need to store these values in an array for further processing, you can uncomment and modify the $valuesArray[] = $value; line accordingly.

PHP function to get difference between dates

July 15, 2023 / / 0 Comments

Below is a PHP function that calculates and displays the difference in days between two dates. This function will take two date strings as inputs, convert them to DateTime objects, and then calculate the difference in days.

Here’s how you can define and use the function:

<?php
// Calculate day difference
function calculateDayDifference($date1, $date2) {
    // Create DateTime objects for the two dates
    $datetime1 = new DateTime($date1);
    $datetime2 = new DateTime($date2);

    // Calculate the difference
    $interval = $datetime1->diff($datetime2);

    // Get the difference in days
    $days_interval = $interval->days;

    return  $days_interval;
}

// Example usage
$date1 = "2023-07-01";
$date2 = "2023-07-15";

echo calculateDayDifference($date1, $date2);
?>

Explanation:

Creating DateTime Objects:

new DateTime($date1): This converts the first date string into a DateTime object.

new DateTime($date2): This converts the second date string into a DateTime object.

Calculating the Difference:

$datetime1->diff($datetime2): This calculates the difference between the two DateTime objects. The result is a DateInterval object.

Getting the Difference in Days:

$interval->days: This retrieves the difference in days from the DateInterval object.

Generate random number in PHP

March 31, 2023 / / 0 Comments

In PHP, you can generate a random number between 1 and 9 using the rand() or mt_rand() functions. Here are examples of both:

Using rand() Function:

$randomNumber = rand(1, 9);
echo “Random number between 1 and 9: ” . $randomNumber;

Using mt_rand() Function:

$randomNumber = mt_rand(1, 9);
echo “Random number between 1 and 9: ” . $randomNumber;

Both functions will generate a random integer between the specified range, inclusive of both 1 and 9.

Here’s how you might use this in a simple PHP script:

<?php
// Generate a random number between 1 and 9 using rand()
$randomNumberRand = rand(1, 9);
echo "Random number using rand(): " . $randomNumberRand . "<br>";

// Generate a random number between 1 and 9 using mt_rand()
$randomNumberMtRand = mt_rand(1, 9);
echo "Random number using mt_rand(): " . $randomNumberMtRand;
?>

Why Use mt_rand()?

While both rand() and mt_rand() are suitable for generating random numbers, mt_rand() is generally preferred because it is based on the Mersenne Twister algorithm, which is faster and has a better distribution of numbers. Feel free to use either function depending on your requirements.

Convert Indian currency from numeric to words in PHP

September 30, 2022 / / 0 Comments

To convert a numeric value to Indian currency format (words), you can use a custom function. Here’s an example of how you can achieve this:

This function convertToIndianCurrencyWords() converts a numeric value into its Indian currency format representation in words.

<?php
function convertToIndianCurrencyWords($number) {
    $ones = array(
        0 => '', 1 => 'One', 2 => 'Two', 3 => 'Three', 4 => 'Four',
        5 => 'Five', 6 => 'Six', 7 => 'Seven', 8 => 'Eight', 9 => 'Nine'
    );

    $teens = array(
        11 => 'Eleven', 12 => 'Twelve', 13 => 'Thirteen', 14 => 'Fourteen',
        15 => 'Fifteen', 16 => 'Sixteen', 17 => 'Seventeen', 18 => 'Eighteen',
        19 => 'Nineteen'
    );

    $tens = array(
        1 => 'Ten', 2 => 'Twenty', 3 => 'Thirty', 4 => 'Forty', 5 => 'Fifty',
        6 => 'Sixty', 7 => 'Seventy', 8 => 'Eighty', 9 => 'Ninety'
    );

    $hundreds = array(
        '', 'Hundred', 'Thousand', 'Lakh', 'Crore'
    );

    $words = array();

    if ($number < 0) {
        $words[] = 'Minus';
        $number = abs($number);
    }

    $numString = (string)$number;

    $numDigits = strlen($numString);
    $numChunks = ceil($numDigits / 2);
    $numChunkLen = $numDigits % 2 ?: 2;
    $numChunkPos = 0;

    for ($i = 0; $i < $numChunks; ++$i) {
        $numChunk = substr($numString, $numChunkPos, $numChunkLen);
        $numChunk = (int)$numChunk;

        if ($numChunk != 0) {
            $numChunkWords = array();

            if ($numChunk >= 10 && $numChunk <= 19) {
                $numChunkWords[] = $teens[$numChunk];
            } elseif ($numChunk >= 20) {
                $tensDigit = (int)($numChunk / 10);
                $numChunkWords[] = $tens[$tensDigit];

                $onesDigit = $numChunk % 10;
                if ($onesDigit != 0) {
                    $numChunkWords[] = $ones[$onesDigit];
                }
            } elseif ($numChunk != 0) {
                $numChunkWords[] = $ones[$numChunk];
            }

            if (!empty($numChunkWords)) {
                $words = array_merge($words, $numChunkWords);
                if ($numChunkPos > 0) {
                    $words[] = $hundreds[$i];
                }
            }
        }

        $numChunkPos += $numChunkLen;
        $numChunkLen = 2;
    }

    return implode(' ', $words);
}

$number = 123456789; // Example number
$words = convertToIndianCurrencyWords($number);

echo ucfirst($words) . ' Rupees Only'; // Output: Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundred Eighty Nine Rupees Only
?>

Adjust the code as needed for different ranges or specific formatting requirements. The example provided here is a basic implementation for Indian currency representation.

« Older posts

© 2025 Wavesdream Blog

Theme by Anders Norén — Up ↑