Getting a SQL table to display in a Hype element

Using JavaScript with Hype
1

example.zip (93.4 KB)
A quick summery of my project so far

Hi everyone - So I’m developing a web app that sends diabetes monitoring data to an online database. So far I’ve managed to create a user log in system and designed a system to send all the data to the database. The stage I’m at now is to create a system to allow the user to view all of their data they’ve sent to the database.

My Question

Hi guys,

I’m trying to attempt to get a table of information from mysql to display in a hype document however everything I’ve tried so far hasn’t worked.

I’m able to get a single line from a table using an example I found on this forum (Help with querying MySQL database) but I need a whole table worth of data to pull through.

I know I could potentially display the data in a iframe but the problem with this is that the user who is accessing the database has a unique code. So therefore I’m using Ajax to send the code to receive the correct data back.

Using the method I found before, I can do this with one line of information which works well. However, as the web app I’m developing is for people to see their diabetes record in a table (and hopefully in a graph! but that’s another thing) I really need all user data on the database to be shown.

Does anybody have a working example of this? Or can someone point me in the right direction? I’m hoping that a table can be displayed in a set element in hype.

Thanks for your help and time in advance,

Chris

PS -Thanks for all your help in the past! I wouldn’t have got this far without the knowledge shared on this forum. Every little bit of help goes towards helping some of the patients I work with have a better system to monitor their diabetes :slight_smile:

EDIT

I’ve added a sample project to this post. When I’ve tried it, it returns 0. I think its potentially a php issue? Does anyone have any idea? I feel I’m really close to getting this to work!

2

Maybe that helps? If you already have the data in your Hype project, then isn’t it’s just a matter of displaying that data?

3

Thanks for your reply. The problem is that the data isn’t in the hype document, its on the server. I need to pull the dat from the server to show on hype.

4

Well, that's a problem... a scary one too. This is HIPPA data no? Medical stuff needs to be extremely secure. I'm not sure what to recommend, because messing up here could be bad!

As an example, pulling data from the server would be trivial with Drupal. I'd just have it export JSON and then pretty it up with Hype... easy. But with medical data... ehhhh... I would not be comfortable doing that. There was something called "Drupalgeddon"... which was basically a zero day update. You had to update Drupal immediately. If you waited even just a day, you had to assume your website is hacked. Here's an example...

Attacks began soon after the vulnerability was announced. According to the Drupal security team, where a site was not patched within hours of the announcement, it should be considered compromised

If you're responsible for both the web server and the app, there are so many possibilities to fix this problem. Which solution is the right one? I don't know... ¯\_(ツ)_/¯

In other words – BE VERY CAREFUL WITH YOUR SOLUTION! Private data needs to be private. I'm not sure how you would do that. :man_shrugging:t2:

Let's say the data was public information, not scary medical data. Then, it's just a matter of converting the SQL data to something friendly like JSON or even a JavaScript Array. Once Hype has that, it can do a lot.

5

Hi thanks for your concerns and just as a re-assurance, We have thought about all the potential security issues and we’ve got solutions for all of them. Even to a point in which if we were hacked, it would matter as the data means nothing to anyone else than the person who needs to view it. We are using industry standard systems to store the user login (which makes the data on the database make sense) so you need both parts to have any useable data. It would basically mean someone would have to hack a very secure established system, search through that to get the code, then hack this one, find the code and then realise they only have something people currently write in a book. It would be a lot more hassle than it would be ever worth.

So to put it simply, if I could get the SQL data to something friendly like JSON to appear in a table or graph on a website then this is exactly what I need?

Using the files I uploaded earlier do you know if its possible?

6

I looked at the files. I'm even more concerned now. :scream_cat:

You attached a PHP file. If I'm understanding this correctly, the user uses the Hype generated web page to load the PHP file. The PHP file then returns data based on the username entered. Is that right?

I didn't see a password for the user. Is user validation that planned for later? Again, be very careful! Hackers are crafty! Also, what counts as PII is a very low bar. Maybe I'm overly cautious these days, but the rules have changed. There's the GDPR and lots of states in the US are getting tougher too.

I don't know for sure, as I'm having trouble getting past the security issues. AHHHH!

But in general, not considering the security issues, you're just getting data from a database and displaying it in Hype. That should be theoretically possible.

It's sorta a combination of these two tutorials...

The PHP creates the JSON, which should be trivial with json_encode()
The Hype project, using jQuery, could then get the data using getJSON

But again, medical data... AHHHH!

Searching the forums might help too...

https://forums.tumult.com/search?q=php%20json

I'm not sure if you'll find an exact match for the solution you're looking for, but this post kinda echos the general idea here.

  • User posts to the PHP file from the Hype project...
  • PHP generates the JSON...
  • jQuery eats up the JSON...
  • :yum: nom nom nom...
  • Elements in the Hype project are changed based on the retrieved data

That's the general idea... but again... THE PROJECT NEEDS TO BE SECURED TOO! Gotta watch out for SQL Injections and other nasty stuff.

But going back to the beginning. You said... "can someone point me in the right direction" ...does that work for you? :smile:

7

@Photics although I appreciate you relying to this feed, I feel you are totally missing the point. All I want to do is know how to pull data from mysql to display in hype in a table. That is all. The files I uploaded return a 0. I want to know how to stop this and display the table from the database. There is absolutely no need for me to use passwords at this point.

Please stop being concerned about the security of the data. There is no patient identifiable data in the system I have set up and there never will be. The company I work for are fully supporting me on the security side of things and the method I am using is approved in the UK.

8

The two linked tutorials don't help? :thinking:

9

not really. I can get data from a database. I’ve already achieved this and it works extremely well however I’ve only been able to get one line of data from the database. What I need if a full table to display in hype.

I basically need someone to look at the files I uploaded and inform me of why it’s returning ‘0’ than the full table.

10

Well, there was a reason I linked to those two tutorials. From what I saw in the PHP file, you're doing the HTML formatting in the PHP file. You might be able to get that to work, but I'm not sure that's "best practice". That's where JSON comes in. With that approach, only pure data is being output by the database / PHP file. The HTML formatting, such as building a table or populating elements, could be handled in the Hype project.

Here's another way of looking at it...

...the elements in Hype are changed based on two things... the user action (button press) and the data in the two-dimensional array.

In the Multilingual example, it's self-contained, so it's not using JSON. That's why I linked to the two tutorials. JSON can be created by the PHP file and then the jQuery in Hype project can process that data. Once you have the data, you can use "innerHTML" to change the elements in your Hype project.

That seems to be the common way of doing this. No?

UPDATE: I read the linked thread and @MaxZieb is basically saying the same thing...

Great post! He mentions JSON and sanitizing your inputs / risk of being hacked.

11

Thx for the mention @Photics
This also comes to mind

12

@Chriswhardy

Not withstanding the arguments reflecting security.

Here’s an approach to get the results that you want from the files you’ve provided.

These are the changes needed for the PHP

<?php

$host = "localhost";
$user = "";
$pass = "";
$databaseName = "";
$userName = "'" . $_POST['user'] . "'"; // from the AJAX call in Hype. Also notice the added single quotes for SQL correct syntax

$con = mysqli_connect($host, $user, $pass, $databaseName);
 
// Check connection
if($con === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Attempt select query execution
$sql = "SELECT * FROM Users WHERE userName=$userName";
if($result = mysqli_query($con, $sql)){
    if(mysqli_num_rows($result) > 0){
        echo '<table style="width: 99%">'; // added a width here for the look.
            echo "<tr>";
                echo "<th>ID</th>";
                echo "<th>userName</th>";
                echo "<th>BMReading</th>";
                echo "<th>Insulin</th>";
            echo "</tr>";
        while($row = mysqli_fetch_array($result)){
            echo "<tr>";
                echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . $row['userName'] . "</td>";
                echo "<td>" . $row['BMReading'] . "</td>";
                echo "<td>" . $row['Insulin'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        // Free result set
        mysqli_free_result($result);
    } else{
        echo "No records matching your query were found.";
    }
} else{
    echo "ERROR: Was not able to execute $sql. " . mysqli_error($con);
}
 
// Close connection
mysqli_close($con);
?>

These needed for the Hype function

$.ajax({
	url: 'data.php',
	type: 'POST',
	dataType: 'HTML',
	data: {
		user: $('#user').val()
	},
	success: function (res) {  
		$('#resultBox').html(res);
	}
});
4 Likes
13

Thanks for this. Works really well!

And thanks everyone for the points about security. At this early stage I’m only prototyping this idea with the vision to make it fully usable in the future. This code allows me to do that :slight_smile:

I’ve got a big task this coming week to add this to what I’ve already develop and start designing the user interface. I’ll be sharing my work once its all done for you to all look at

Chris

14

Please at least consider using some sanitization on $userName
http://us1.php.net/manual/en/filter.filters.sanitize.php

further more

3 Likes