help with sql and php webform

[dtdukes86]

Hi all, so im after a little help, twice in one week I hear you say

so I have a working access database front end, and back end type. backend is running on a local networked SQL server. the front end uses ODBC data source connection from Windows admin etc. It works from my office and home if I use the wan IP, great! all good.

So what I want to be able to do, is make some changes from a mobile or tablet via a website, the way I've thought about doing this is make a simple web form that captures a small amount of data from a basic table on the SQL server, and then allows changes to one or more of those fields, in particular, the field I really want to be able to change is a check box from false to true. that basically represents a real life event happened.

in any case, I've got the local SQL server running nicely day and night and the blank webpage but for all the love in the world I cannot seem to get them to communicate, I've seen a lot of code online and managed to nearly get there I feel but I just get a trimmed out msg I'm wondering if anyone has done this before if it's possible at all and maybe someone can direct me to the correct place, id stress at this point I'm not a web developer I just have a mate that is pretty good as websites, the problem is he knows nothing about access and I know nothing about websites >< can anyone bridge the gap for us .. ?

thank you in advance, I'm no expert just trial and error my way through haha

 

[cheekybuddha]

php communication to SQLServer is not trivial to set up (though it is easy to use once set up).

Is your webserver running linux or windows?

 

[theDBguy]

I know you have PHP in your title, but have you considered trying Power Apps also?

 

[dtdukes86]

ive never even heard of it sorry , ill do some googling now

 

[dtdukes86]

i visited this site but it just tells me my email address isn't for work or school doh!

 

[dtdukes86]

php communication to SQLServer is not trivial to set up (though it is easy to use once set up).

Is your webserver running linux or windows?

windows

 

[dtdukes86]

windows

php communication to SQLServer is not trivial to set up (though it is easy to use once set up).

Is your webserver running linux or windows?

sorry when u say webserver i assumed you meant sql server but mayne u didnt

 

[cheekybuddha]

but mayne u didnt

No, I meant where will you be serving your PHP from?

If you want a web form you need a web server to serve it from.

 

[dtdukes86]

No, I meant where will you be serving your PHP from?

If you want a web form you need a web server to serve it from.

im not really sure to be honest that a little over my head, the webpage will be obviolsuy hosted online but its not me that does all that part I can ask the questions and get backto you tho , thank you for the help.

 

[cheekybuddha]

the webpage will be obviolsuy hosted online

OK, so this server will need to have the sqlsrv and pdo_sqlsrv php modules loaded to be able to communicate with your SQLServer.

On my server I had to build the sqlsrv module myself using pecl - you can linux find instructions here. There must be equvalent instructions for Windows based servers - perhaps this link will help.

I don't know what webserver you will be using (nginx, apache, IIS etc) but the instructions show how to do it for different apache and php-fpm (which you would use for nginx). I'm afraid I have no knowledge of Windows servers.

Your SQLServer server (I guess your local box at the moment) will need to be reachable from the internet (ie have a stable public IP address and a firewall port open forwarded to the SQL server port (usually 1403). Also you may need to ensure your web application can communicate on a different port to the SQLServer - some online hosts will not permit communication on ports other than the standard web serving ports.

You will need to take great care (or whoever administers your webserver will) to protect your SQLServer credentials so they can not be easily accessed on the webserver (ie do NOT hardcode them in to your php code!)

 

[GPGeorge]

ive never even heard of it sorry , ill do some googling now

You can learn more about Access and PowerApps here.

 

[dtdukes86]

Hi all so i thought id add a update for you , I managed to connect the website to my local SQL server using some code I found and a tube video , the system works perfectly in xxamp but when I dumped it onto my web hosting and tried to access via my mobile the SQL code doesn't run , Im told i need to "install" SQL drivers onto my web host, using xxamp i just copied 2 .ddl files in and it worked ill add the name of the .dll files incase anybody knows what I'm doing wrong.

php_pdo_sqlsrv_74_ts.dll & php_sqlsrv_74_ts.dll were added into the PHP folder and also the config file as an extension, and it worked perfectly i just cant get my web host to do the same thing. i ask my web host provider and he didn't seem to know either ><

 

[cheekybuddha]

OK, so this server will need to have the sqlsrv and pdo_sqlsrv php modules loaded

Kinda what I said!

Just as a side note, php 7.4 reached end of support at the end of last year.

You should try and upgrade to 8.X.

 

[dtdukes86]

@cheekybuddha I wish i knew more about these things I think it would be so useful, SQL servers and web hosting are a whole new world for me as an "old school" I.T tech. Networking and Access are fine but anything web related is a learning curve and a half.

 

[dtdukes86]

You can learn more about Access and PowerApps here.

this is very cool im certainly going to spend some time looking into this some more , just the first video I stumbled upon , scanning receipts is something I have thought about doing for a while. thank you very much .

 

[GPGeorge]

this is very cool im certainly going to spend some time looking into this some more , just the first video I stumbled upon , scanning receipts is something I have thought about doing for a while. thank you very much .

A Caveat on that. I intended to pursue that a lot further, and will eventually provide a follow up on using PowerApps Components and Data Clean-up based on it.

However, I did not anticipate the cost. An MS 365 account starts out with a certain number of "AI Builder Credits". I used that up in training the AI model on only 6 receipts. My initial model is far from robust enough to trust on a variety of receipts. However, replenishing the "AI Builder Credits" I need to train it on additional receipts would cost a minimum of $500 US. I can't throw that kind of money at a demo project unless I start getting hundreds of thousands of YouTube views to pay for it. And that ain't happening either.

So, while it's a really exciting proposition, the cost to push it further is too great to do that.

 

[dtdukes86]

@GP George thats a rell shame the idea looks very exciting, I would have been interesting in maybe donating towards the project but as you say 500 USD as a demo is a little steap . maybe get a few more people on board and we could so do 50usd each ?

 

[dtdukes86]

we did also manage to get the web SQL insert and select statements working from the website my friends had built and all seems very good my end on that front your advice @cheekybuddha was spot on. thank you very much now, delivery drivers, can add comments and close down drops from the click of a button on their phones, this info is automatically reflected on the in-house system. very clever stuff indeed !