@Joseph Evert:
Thanks for the reply.
[quote]I spent a lot of time researching this and using PHP on the server and HTTP protocol is the prevailing way to do this.
Your Database is residing on a server and your PHP page or script is what will be interacting with your DB. Your Xojo application will use standard HTTP to talk to and request information from your PHP script. The PHP acts like a connector between your DB and your app.
Because of this you will want review the Xojo Docs on HTTP and POST. For example you might have your PHP interpret one parameter of the POST as a command to do something, such as write a record to the database, the other parameters being the data you want to write. Once received your PHP script would then parse the command and execute the code to write the passed parameters to the database. Another command may be to retrieve a specific record set and return the values for display in your Xojo APP.
Now, although your database is not out in the open on the net it does not mean this is secure. I’d solicit some feedback on this to ensure you are taking measures to ensure a higher level of security. For example (and I am open to more suggestions).
You really should be using the SecureSocket and SSL to communicate with your PHP. The reason is the helps prevent a man in the middle from deciphering your traffic and sending bogus requests or commands to your page.
[/quote]
Following up on the above section of your post and having gone through refreshing my mind regarding the execution of CRUD on a MySQL database I’ve got some code below which I use to read data from a table subjects.
manage_content.php:
<?php require_once("../includes/database_connection.php"); ?>
<?php require_once("../includes/functions.php"); ?>
<?php include("../includes/layouts/header.php"); ?>
<?php find_selected_page() ?>
<div id="main">
<div id="navigation">
<ul class="subjects">
<?php $subject_set = find_all_subjects(); ?>
<?php
// 3 .b. Open a while loop to use returned data if any available based on subjects query.
while($subject = mysqli_fetch_assoc($subject_set)) {
?>
<?php "<li class=\"selected\">" ; ?>
<?php // When assigning the value of <a href page using PHP page you pass the specific page id/DB value as you did in lesson 17- Using Site Navigation to Choose Content?>
<a href="manage_content.php?subject=<?php echo urlencode($subject["id"]); ?>"><?php echo $subject["menu_name"]; ?></a>
<?php $page_set = find_pages_for_subject($subject["id"]); ?>
<ul class="pages">
<?php
// 3 .b. Open a 1ST NESTED while loop to use returned data if any available based on pages query.
while($page = mysqli_fetch_assoc($page_set)) {
?>
<li><a href="manage_content.php?page=<?php echo urlencode($page["id"]); ?>"><?php echo $page["menu_name"]; ?></a></li>
<?php } ?>
<?php mysqli_free_result($page_set); ?>
</ul>
</li>
<?php } ?>
<?php mysqli_free_result($subject_set); ?>
</ul>
</div>
</div>
<br/>
<a href="new_subject.php">+ Add a subject </a>
<div id="page">
<h2>Manage Content</h2>
<?php if($current_subject) { ?>
<?php echo "Menu name in the associative array $current_subject is: " . $current_subject["menu_name"]; ?></br>
<?php } elseif($current_page) { ?>
<?php echo "Menu name in the associative array $current_page is: " . $current_page["menu_name"]; ?></br>
<?php } else { ?>
<?php echo "NO SELECTION MADE"; ?>
<?php } ?>
</div>
<?php include("../includes/layouts/footer.php"); ?>
functions.php:
<?php
function redirect_to($new_location) {
header("Location: " . $new_location);
exit;
}
function confirm_query($result_set) {
// Test for query error
if (!$result_set) {
die("DB SELECT * query failed");
} else {
//echo "<br />";
//echo "DB SELECT * query successful";
//echo "Returned result is: " . $result;
}
}
function find_all_subjects() {
global $connection;
// 2. Perform subjects database query
$query = "SELECT * ";
$query .= "FROM subjects ";
$query .= "WHERE visible = 1 ";
$query .= "ORDER BY position ASC";
// Contains reuslt in relation to selection of subjects. This variable should not be re-populated further down
// WRT using another mysqli_query() function
$subject_set = mysqli_query($connection, $query);
confirm_query($subject_set);
return $subject_set;
}
function find_pages_for_subject($subject_id) {
global $connection;
// The subject ID has been escaped so to counter SQL injection
$safe_page_id = mysqli_real_escape_string($connection, $subject_id);
// 2. Perform database query to find out which pages belong to the subjects we intend on displaying
$query = "SELECT * ";
$query .= "FROM pages ";
$query .= "WHERE visible = 1 ";
$query .= "AND subject_id = {$safe_page_id} ";
$query .= "ORDER BY position ASC";
// The subject ID has been escaped so to counter SQL injection
$safe_subject_id = mysqli_real_escape_string($connection, $subject_id);
$page_set = mysqli_query($connection, $query);
confirm_query($page_set);
return $page_set;
}
function find_subject_by_id($subject_id) {
global $connection;
// The subject ID has been escaped so to counter SQL injection
$safe_subject_id = mysqli_real_escape_string($connection, $subject_id);
// 2. Perform subjects database query
$query = "SELECT * ";
$query .= "FROM subjects ";
$query .= "WHERE id = {$safe_subject_id} ";
$query .= "LIMIT 1";
// Contains reuslt in relation to selection of subjects. This variable should not be re-populated further down
// WRT using another mysqli_query() function
$subject_set = mysqli_query($connection, $query);
confirm_query($subject_set);
// Return an associative array containing the result set data from the execution of the above query
if($subject = mysqli_fetch_assoc($subject_set)) {
return $subject;
} else {
return null;
}
}
function find_page_by_id($page_id) {
global $connection;
// The subject ID has been escaped so to counter SQL injection
$safe_page_id = mysqli_real_escape_string($connection, $page_id);
// 2. Perform database query to find out which pages belong to the subjects we intend on displaying
$query = "SELECT * ";
$query .= "FROM pages ";
$query .= "WHERE id = {$safe_page_id} ";
$query .= "LIMIT 1";
$page_set = mysqli_query($connection, $query);
confirm_query($page_set);
// Return an associative array containing the result set data from the execution of the above query
if($page = mysqli_fetch_assoc($page_set)) {
return $page;
} else {
return null;
}
}
function find_selected_page() {
global $current_subject;
global $current_page;
// At the start of the program read back the value related to the
// respective href but perfrom a check with regards to the
// setting of the variables which will hold the subject id and page
// id which will be used as part of the a href tag
if(isset($_GET["subject"])) {
$current_subject = find_subject_by_id($_GET["subject"]);
$current_page = null;
} elseif(isset($_GET["page"])) {
$current_page = find_page_by_id($_GET["page"]);
$current_subject = null;
} else {
$current_subject = null;
$current_page = null;
}
}
?>
To start with I intend on using the HTTPSocket to perform a simple read and display in a TextArea of my database content through reading what my php script returns. My attempt can be seen below. I’m I on the right path to displaying in XOJO say in a textarea the content of the div titled page in my 1st PHP script above i.e. manage_content.php?
Dim desiredsocket As New HTTPSocket
Dim desiredresponse As String
Dim desiredquery As String
desiredquery = "http://www.swim-mates.com/widget_corp/public/manage_content.php"
desiredresponse = desiredsocket.Get( desiredquery, 10 )
ResultArea.Text = DefineEncoding(desiredresponse, Encodings.UTF8)
Apologies for any basic errors i might have made in my above code.
