I recently experimented with the OpenAI API and shared an example on our internal team forum.
While it focuses on a different use case, you can use and adapt the `getOpenAIResponse()` function to suit your needs:
(This documentation and code was generated with the help of ChatGPT
)Fun fact: To test the sample code in this example, I made 14 requests using 1130 tokens to the openAI API and it cost me only $0.01!
Purpose:
The PHP script below demonstrates how to leverage the OpenAI API to dynamically generate a MySQL query based on a natural-language question. In this example, the goal is to retrieve data—such as the total invoice amount—from a database table (named "invoice"). The script builds a prompt that combines the table's schema information with a human-like query, sends it to the OpenAI API, and then, if the response contains a valid SQL SELECT statement, executes it to fetch and display the result.
How It Works
Diagram
- API and Table Setup
API Key: The script starts by setting up your OpenAI API key (you need to replace the placeholder with your actual key). An API key can be retrieved from https://platform.openai.com/docs/overview
Table Configuration: The table name is set to "invoice". Although the table could be selected dynamically (e.g., using SHOW TABLES), here it’s hardcoded for simplicity. - Retrieving the Table Schema
The functionfetches the schema details (column names, data types, and primary key information) for the "invoice" table.Code: Select all
getFieldsWithTypes($tableName)
This schema information is later included in the prompt to help the AI understand the table structure. - Constructing the AI Prompt
An instruction is prepared that tells the AI: "Create mySQL Statement for the question below and output it without comments or anything additional. Use the table invoice and the following schema: [schema info]."
A user prompt (for example, "Get the total amount of all invoices with status 'Paid' from January.") is appended to the instruction, forming the final prompt sent to the API. - Calling the OpenAI API
The combined prompt is sent to OpenAI via thefunction.Code: Select all
getOpenAIResponse($prompt, $apiKey)
The API is expected to return a plain SQL query (e.g., a SELECT statement).
If the API returns an error, it is displayed immediately. - Handling and Displaying the AI Response
The returned response is first displayed along with some metadata (like response ID, model used, and token usage details).
Any SQL markdown fences (like "sql … ") are removed from the reply to ensure a clean query string. - Determining and Executing the SQL Query
The script checks if the AI’s reply qualifies as a SQL query using.Code: Select all
nuGetSelectType($reply)
If it is a valid query, it is executed via, which runs the query and returns a single value (for example, the total invoice amount).Code: Select all
runQueryReturnOneValue($reply)
Finally, the result of the executed query is displayed. - Additional Functions
Logging: Functions likeandCode: Select all
insertLog()are included for recording API usage and response metadata, which can help with debugging or tracking. (not included in this example)Code: Select all
updateLog()
Utility Functions:cleans up the SQL string.Code: Select all
removeSqlFences()ensures that if the query returns exactly one row, its value is retrieved and shown.Code: Select all
runQueryReturnOneValue()
This example illustrates a practical use case for integrating AI into nuBuilder. By allowing users to ask human-like questions, the script dynamically generates SQL queries tailored to the structure of your database, executes them, and returns the corresponding data. Although the code is basic, it serves as a solid starting point for building more sophisticated AI-assisted query systems.
Feel free to ask if you have any questions or need further clarification on any part of the code!
Further Steps
Consider expanding this setup by creating a nuBuilder form that includes a select object for choosing the desired database table and an input field for the user to enter their prompt. Once the user submits the form, you can call this PHP procedure to generate the SQL query and return the result directly to the user.
nuBuilder PHP procedure:
Code: Select all
// -----------------------------------------------------------
// <h3>API Key</h3>
// -----------------------------------------------------------
// $apiKey = "sk-YOUR_API_KEY_HERE";
// -----------------------------------------------------------
// <h3>Table Configuration</h3>
// -----------------------------------------------------------
// The table name can be dynamically selected (e.g., via SQL: SHOW TABLES).
// For this example, we are hardcoding the table name to 'invoice'.
// $tableName = nuGetProperty('table_name');
$tableName = 'invoice';
// -----------------------------------------------------------
// <h3>Retrieve Table Schema</h3>
// -----------------------------------------------------------
// Get the columns, data types, and primary key information for the table.
$tableSchema = getFieldsWithTypes($tableName);
// -----------------------------------------------------------
// <h3>Construct the Instruction</h3>
// -----------------------------------------------------------
// Build a general instruction for generating a MySQL statement.
// The instruction combines the table schema with the user's query.
// This prompt instructs the AI to output a pure SQL statement without extra commentary.
$instruction = 'Create mySQL Statement for the question below and output it without comments or anything additional. Use the table invoice and the following schema: ' . $tableSchema;
// Retrieve the user's prompt (e.g., from a form input).
// For demonstration purposes, a static prompt is used here.
// $userPrompt = nuGetProperty('user_prompt');
$userPrompt = 'Get the total amount of all invoices with status "Paid" from January.';
// Combine the instruction with the user's prompt.
$prompt = $instruction . $userPrompt;
// -----------------------------------------------------------
// <h3>Call the AI Service</h3>
// -----------------------------------------------------------
// Use the OpenAI API to get a response based on the constructed prompt.
$response = getOpenAIResponse($prompt, $apiKey);
// Check for errors in the API response.
if ($response['error']) {
// Display the error message and exit if an error occurs.
echo "<h3>Error:</h3><p>" . htmlspecialchars($response['message']) . "</p>";
return;
}
// -----------------------------------------------------------
// <h3>Display the AI Response</h3>
// -----------------------------------------------------------
// Example of expected AI response format:
// ```sql
// SELECT SUM(`inv_amount`) AS total_paid_amount FROM `invoice` WHERE `inv_status` = 'Paid' AND MONTH(`inv_date`) = 1;
// ```
$reply = $response['reply'];
echo "<h3>Response AI:</h3>";
echo "<p>" . htmlspecialchars($reply) . "</p>";
// Output metadata
echo "<h4>Metadata</h4>";
echo "<p>" . "Response ID: " . $response['id'] . "</p>";
echo "<p>" . "Model: " . $response['model'] . "</p>";
echo "<h4>" . "Usage Data: " ." </h4>";
print_r($response['usage']);
echo "<br>";
// Remove any SQL markdown fences from the response.
$reply = removeSqlFences($reply);
// -----------------------------------------------------------
// <h3>Determine and Execute Query</h3>
// -----------------------------------------------------------
// Identify if the reply is a SQL query.
$selectType = nuGetSelectType($reply);
echo "<br><strong>Select Type:</strong> " . $selectType . "<br>";
// If the reply is a query, execute it and display the result.
if ($selectType === 'query') {
$queryResult = runQueryReturnOneValue($reply);
echo "<h3>Query Result:</h3>";
echo "<p>" . $queryResult . "</p>";
// Callback to pass value to JS function:
//$contentBase64 = base64_encode($queryResult);
//nuJavaScriptCallback("onAIResponse('$contentBase64'); ");
}
function runQueryReturnOneValue($query) {
$stmt = nuRunQuery($query);
if (db_num_rows($stmt) == 1) {
$row = db_fetch_row($stmt);
return $row[0];
} else {
return '';
}
}
function getFieldsWithTypes($tableName) {
// Retrieve the fields, types, and primary keys arrays
list($fields, $types, $primaryKeys) = db_field_info($tableName);
$lines = [];
// Loop through each field and type
foreach ($fields as $i => $field) {
$line = $field . ', ' . $types[$i];
// Append " (and pk)" if the current field is a primary key
if (in_array($field, $primaryKeys)) {
$line .= ' (Primary Key)';
}
$lines[] = $line;
}
// Join all lines with a newline separator and return the resulting string
return implode("<br>", $lines);
}
function removeSqlFences($query) {
// Remove the starting ```sql (case-insensitive) and any trailing whitespace after it
$query = preg_replace('/^```sql\s*/i', '', $query);
// Remove the ending ``` (and any leading whitespace before it)
$query = preg_replace('/\s*```$/', '', $query);
return $query;
}
function getOpenAIResponse($prompt, $apiKey) {
// API endpoint and headers
$url = "https://api.openai.com/v1/chat/completions";
$headers = [
"Content-Type: application/json",
"Authorization: Bearer $apiKey"
];
// Prepare message payload
$messages = [
["role" => "user", "content" => $prompt]
];
// Define request data with model and parameters
$postData = [
"model" => "gpt-4o",
"messages" => $messages,
"max_tokens" => 256,
"temperature" => 0.7,
"top_p" => 1.0,
"frequency_penalty" => 0.0,
"presence_penalty" => 0.0,
];
// Initialize cURL and set options using an array
$ch = curl_init();
curl_setopt_array($ch, [
CURLOPT_URL => $url,
CURLOPT_POST => true,
CURLOPT_HTTPHEADER => $headers,
CURLOPT_POSTFIELDS => json_encode($postData),
CURLOPT_RETURNTRANSFER => true,
CURLOPT_TIMEOUT => 15,
// For local testing only – disable SSL verification
CURLOPT_SSL_VERIFYPEER => false,
CURLOPT_SSL_VERIFYHOST => false,
]);
// Execute the HTTP request
$response = curl_exec($ch);
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
// Check for a cURL-level error
if (curl_errno($ch)) {
$errorMsg = curl_error($ch);
curl_close($ch);
return [
'error' => true,
'message' => "Request Error: $errorMsg"
];
}
curl_close($ch);
// Decode the JSON response
$data = json_decode($response, true);
// Handle HTTP errors or invalid JSON response
if ($httpCode !== 200 || !$data) {
$errMessage = isset($data['error']['message']) ? $data['error']['message'] : "Unknown error or invalid JSON response";
return [
'error' => true,
'message' => "API Error (HTTP $httpCode): $errMessage"
];
}
// Extract the assistant's reply
$reply = isset($data['choices'][0]['message']['content']) ? $data['choices'][0]['message']['content'] : "No response generated.";
// Extract additional usage data and metadata if available
$responseId = isset($data['id']) ? $data['id'] : null;
$responseModel = isset($data['model']) ? $data['model'] : null;
$usage = isset($data['usage']) ? $data['usage'] : null;
return [
'error' => false,
'reply' => $reply,
'id' => $responseId,
'model' => $responseModel,
'usage' => $usage,
];
}
Example Output
Example Output: wrote:Response AI:
```sql SELECT SUM(inv_amount) AS total_paid_amount FROM invoice WHERE inv_status = 'Paid' AND MONTH(inv_date) = 1; ```
Metadata
Response ID: chatcmpl-B6CxYHsLsvDQS0FDN0SIobdaPRkqX
Model: gpt-4o-2024-08-06
Usage Data:
Array ( [prompt_tokens] => 100 [completion_tokens] => 34 [total_tokens] => 134 [prompt_tokens_details] => Array ( [cached_tokens] => 0 [audio_tokens] => 0 ) [completion_tokens_details] => Array ( [reasoning_tokens] => 0 [audio_tokens] => 0 [accepted_prediction_tokens] => 0 [rejected_prediction_tokens] => 0 ) )
Select Type: query
Query Result:
1030.50
Table used in this example:
Code: Select all
-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Mar 01, 2025 at 09:12 AM
-- Server version: 8.2.0
-- PHP Version: 8.3.4
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `nu4510`
--
-- --------------------------------------------------------
--
-- Table structure for table `invoice`
--
CREATE TABLE `invoice` (
`invoice_id` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
`inv_date` date NOT NULL,
`inv_amount` decimal(10,2) NOT NULL,
`inv_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Pending',
`inv_customer` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`inv_notes` text COLLATE utf8mb4_unicode_ci
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dumping data for table `invoice`
--
INSERT INTO `invoice` (`invoice_id`, `inv_date`, `inv_amount`, `inv_status`, `inv_customer`, `inv_notes`) VALUES
('INV00001', '2025-01-05', 150.00, 'Pending', 'John Doe', 'First invoice of the year'),
('INV00002', '2025-01-07', 230.50, 'Paid', 'Alice Smith', 'Paid via bank transfer'),
('INV00003', '2025-01-10', 99.99, 'Cancelled', 'Bob Johnson', 'Customer requested cancellation'),
('INV00004', '2025-01-15', 175.75, 'Pending', 'Emily Davis', 'Awaiting payment confirmation'),
('INV00005', '2025-01-20', 300.00, 'Paid', 'Michael Brown', 'Paid in full'),
('INV00006', '2025-01-25', 450.25, 'Pending', 'Sophia Wilson', 'Discount applied'),
('INV00007', '2025-01-30', 500.00, 'Paid', 'Liam Martinez', 'Payment received via PayPal'),
('INV00008', '2025-02-02', 275.50, 'Pending', 'Olivia Taylor', 'Payment due next week'),
('INV00009', '2025-02-07', 99.99, 'Cancelled', 'Ethan Thomas', 'Duplicate order'),
('INV00010', '2025-02-10', 123.45, 'Paid', 'Ava Anderson', 'Settled with store credit'),
('INV00011', '2025-02-12', 350.00, 'Pending', 'William Moore', 'New customer order'),
('INV00012', '2025-02-15', 600.00, 'Paid', 'Isabella Jackson', 'Bulk order discount applied'),
('INV00013', '2025-02-18', 75.99, 'Pending', 'James White', 'Small order'),
('INV00014', '2025-02-20', 200.00, 'Cancelled', 'Charlotte Harris', 'Refunded to customer'),
('INV00015', '2025-02-22', 325.75, 'Paid', 'Benjamin Clark', 'Online order'),
('INV00016', '2025-02-25', 125.00, 'Pending', 'Mia Lewis', 'Invoice under review'),
('INV00017', '2025-02-27', 410.30, 'Paid', 'Daniel Walker', 'Paid with credit card'),
('INV00018', '2025-03-01', 510.99, 'Pending', 'Amelia Hall', 'Awaiting approval'),
('INV00019', '2025-03-05', 89.99, 'Cancelled', 'Henry Allen', 'Order not processed'),
('INV00020', '2025-03-07', 150.75, 'Paid', 'Emma Scott', 'Finalized with cash payment');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `invoice`
--
ALTER TABLE `invoice`
ADD PRIMARY KEY (`invoice_id`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;