Script with SQL for second database

V5 Question
1

Am looking at having a start on a synced customer/loyalty system for the two properties of the hotel.
I eventually want to intergrate with a user section on the website but website is barely started yet so will be a while for that.
I dont really want to mess with actual samba database so recon simplest option for now is to use a seccond database on the hotels server as a centeral point which both systems sync with.
Was thinking of doing this with mysql database on website hosting but given samba already has good ms sql capabilities vs mysql ms sql should be easier.

So the question is what needs to be done in the sql query to make it refer to a seccond database (in case of one property that being an external one - but can sort ports and firewall etc myself its just what to change in the query)?

@QMcKay your the SQL master, care for a quick pointer?

2

You will need to set up a separate Connection in JScript.

We talked about this a while back - the basic jist is in the following Topic…

This discusses connection to MSSQL and MySQL Databases using a connection made in JScript using IIRC ActiveX Object. Today, there will be better ways than ActiveX, like using host.lib or host.type helpers, but the theory is similar. If you stick with MSSQL, you will probably be better off since C# and .NET will have native support for MSSQL and that is where host.lib/type come in.

1 Like
3

Arr, a topic of mine :slight_smile: will have a recap, thanks.

Unless you have any better suggestions?
Was just reluctant to do too much direct sql and open up too much remote access…
I guess alternative is to use Graph API and treat one as a master?

4

The DB can be on the same machine, in the same instance, using the same ports even. It’s just like changing the Connection String to attach to a different DB.

But you need to set up that Connection first… and yes, you will need to use SQL to read/write stuff, but after that, you can use SambaPOS API and/or GraphQL helpers in the script to talk to SambaPOS DB.

5

Thinking about it I might go down the mySQL route and make a temporaty database on hosting.
Rather than directly connect to mysql which found clumbersome before using the up/down/right/left type commands and instead do similar to the mini api i did for the dev website for getting rates;

<?php
$servername = "localhost";
$username = "########";
$password = "#######";
$dbname = "#######";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM Customers";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
	$array = array();
	while($r = mysqli_fetch_assoc($result)) {
		$array[] = $r;
	}
	print json_encode($array);
} else {
    echo "0 results";
}
$conn->close();
?>

A request api and an update api should be enough to get the job done then just process the output json in samba jscript which the PMS setup left me fairly fluent with…

Recond thats a fair plan?

That way it should be allot simpler to adjust to link with new website which will have a users table in the database anyway and then just add some custom columns for the additional data.

Outputs nice bit of JSON for the table samba can chew through in no time.

image.png1273×189 13.6 KB

Sure there would be better ways but know I can get that to work.
Obviously thats a very crude proof of concept but shouldnt be more than a few evenings tinkering to get up and running.

6

Great thing about posting to a PHP site and getting a response is that you don’t need to worry about setting up a remote DB connection and then worry about it being broken. The DB connection details are server-side.

But you should be sure to authenticate in some fashion, using whatever method you choose, to ensure not just anyone can grab data… it could be a simple passphrase that you hard-code and pass that with your request, and then server side, PHP checks for a match… somewhat like an API Key.

That aside, no connection to worry about - you just make the post, wait for the reply, and then go about your business processing, letting the connection drop.

Of course you need to consider timeouts with no response etc, but you have handled that scenario before no doubt.

1 Like
7

Great, seems like thats the route Ill take.

Yes, did exactly that with my check prices API script using a password/phrase and a request type to allow the different functions using a single php file/script.

Thanks Q, look forward to sharing the results.

8

Then again found this bookmark from me previous research.
Might spend a few hours tinkering with this first and see if I can utilise its UI and database structure. Plus it has Json based API built in.
This is what I was looking at for the future wifi login/registration system which would be nice to be hooked in to the loyalty system aswell.

http://www.userfrosting.com

1 Like
9

Well userfrosting seems to be allot more than a user framework…
Is more like a blank bootstrap site with user system premade.
Also nightmare to get going as you can’t install on hosting directly, you need to compile the files locally then upload.
Although would be nice think I’m going to opt for something a little more basic.

10

@QMcKay any tips on method for SQL to ‘join’ user info where the additional info is stored in a seperate keyed type table?

Main users table;

Keyed profile info table;

Obviously its not a join as details table is not columed but rowed for fields…

11 
SELECT u.*, p.*
FROM guestlin_joom316.joscy_users u
JOIN guestlin_joom316.joscy_user_profiles p on p.user_id = u.id

But I suppose you want that rotated to turn the rows in the Profile Table into Columns so you have a single row returned for each User?

1 Like
12

:slight_smile: indeed… got as bar as that untill I smashed my head on the table to for realising the issue before trying a normal join LOL

13

This is the example I am trying to replicate at the minute;

SELECT
    u1.id,
    u1.login,
    u1.password,
    u1.email,
    m1.meta_value AS firstname,
    m2.meta_value AS lastname,
    m3.meta_value AS country
FROM wp_users u1
JOIN wp_usermeta m1 ON (m1.user_id = u1.id AND m1.meta_key = 'first_name')
JOIN wp_usermeta m2 ON (m2.user_id = u1.id AND m2.meta_key = 'last_name')
JOIN wp_usermeta m3 ON (m3.user_id = u1.id AND m3.meta_key = 'country')
WHERE
14

:slight_smile: getting somewhere;

SELECT users.id,
users.name,
users.username,
users.email,
users.password,
users.block,
users.registerDate,
address1.profile_value AS address1
FROM guestlin_joom317.joscy_users users
JOIN guestlin_joom317.joscy_user_profiles address1 ON (users.id=address1.user_id AND address1.profile_key='profile.address1')

image.png1014×558 26.7 KB

Would be nice if it was dynamic but not going to even try that LOL

15

What type of DB is that?

Here is an article on SO using SQL PIVOT, but don’t know if your DB has that command …

If you can’t use that ^ then there are other ways but it can get messy… let me know if it works for you.

1 Like
16

its MySQL, will take a look

17

Think the profile.value key might become an issue…

18

I guess it doesn’t have PIVOT, but something similar called group_concat() …

I don’t have a running MySQL environment (converted all my DBs to MSSQL over a year ago), so I can’t test anything, meaning you’re on your own.

1 Like
19

That is looking promising although it lumps them into a single cell;

image.png913×498 22.8 KB

20

hmmm, but stumbled on a simple way to make into JSON… whis everyone loves :slight_smile:

SELECT users.name, group_concat('{"',TRIM(LEADING 'profile.' FROM profiles.profile_key),':',profiles.profile_value,'}') AS profile_json
from guestlin_joom317.joscy_users users
LEFT JOIN guestlin_joom317.joscy_user_profiles profiles on profiles.user_id = users.id
GROUP BY users.id

Also trimmed the leading profile. from key values.

image.png1153×488 23.3 KB