How to connect to local MySQL

[ECNU4271]

Sorry for post another topic, I am new to programming therefore having many problems whiling I am developing this app.

I've followed http://www.vampire-tiger.info/livecode- ... bases.html.

I've run the MAMP very well. I started the server, I did learn several SQL sentences(insert, update, select from). Now that I've created the database (with 3 tables in it within data).

What should I do in LiveCode to connect this local MySQL? Could anyone give any clues?

Do I need to learn PHP code to get/post data to this database in LiveCode?

The second half of that tutorial seems complicated. I don't really understand the codes in LiveCode section.

Thank you all!
Michael

[Klaus]

Hi Michael,

use "localhost" (WITH quotes!) as the hostname parameter in "revOpenDatabase()"!


Best

Klaus

[dave_probertGA6e24]

Hi Michael,

The tutorial is really meant for us with a remote server (ie not your local computer as such) and also uses PHP as an intermediary between Livecode and the database at the server.
It's probably not what you require as a tutorial for your local use. You don't need to know PHP for local databases.

As Klaus mentioned you should really use the various revDatabase calls for access to a local (same machine) database.

There are some reasonable lessons and tutorials for those commands on the livecode site (http://lessons.runrev.com/) - this one should be a good starting point: http://lessons.runrev.com/s/lessons/m/4 ... l-database

In those lessons you can replace the "runrev.com" address they use with "localhost" - like Klaus said.

If you have any specific questions then please ask.

Cheers,
Dave
(author of the vampire-tiger tutorial )

[ECNU4271]

Hi Michael,

The tutorial is really meant for us with a remote server (ie not your local computer as such) and also uses PHP as an intermediary between Livecode and the database at the server.
It's probably not what you require as a tutorial for your local use. You don't need to know PHP for local databases.

As Klaus mentioned you should really use the various revDatabase calls for access to a local (same machine) database.

There are some reasonable lessons and tutorials for those commands on the livecode site (http://lessons.runrev.com/) - this one should be a good starting point: http://lessons.runrev.com/s/lessons/m/4 ... l-database

In those lessons you can replace the "runrev.com" address they use with "localhost" - like Klaus said.

If you have any specific questions then please ask.

Cheers,
Dave
(author of the vampire-tiger tutorial )

Hi Dave,

But my app is running on iPhone, the database is on my mac. Should I still stick to the tutorial ?
If No, I will start looking on the resources you offer.

Michael

[dave_probertGA6e24]

Hi Michael,

Aha!! (as Klaus would say )

You didn't mention that bit of information in the original post

Ok, to answer your questions will require a little bit more info from you on how you want to use the iphone and Mac. Is the App you are going to create for personal(or private business) use? or are you planning on selling it as a commercial tool?

If it's for personal use then the next question is: will the iphone always be on the same wireless connection (ie LAN) or will it be used outside the Local Area - therefore on the Internet (WAN) ?

If it's for Commercial use then is it a tool that will be sold via the App store for the general public to use? If so then it must therefore be an Internet based tool.

If the public use option is chosen then you will really need to get to grips with server side coding to a high level to cover security issues, multi-user database locking and much more. Much more than I can go into here.

Either way you will need to get to understand some protocol of data transfer across the network (like HTTP get requests) and then also to know how to have some code on the server (your Mac in this case) handle incoming requests and send back data. The Remote-Server (vampire-tiger) tutorial covers the basics of this using PHP - though you could use the Livecode Server (with Apache and MySQL which you have already installed) and write everything in Livecode language. I can't really help with the Livecode Server option because I haven't used it - I'm sure someone else will be more than willing to help with that though.

PHP is actually quite easy once you start playing with it. It just looks complicated with all those dollar signs

If you follow through my tutorial carefully it should make some sense (I hope!) and if you have any problems with it then please just ask as I am very willing to help explain it (and improve the tutorial in the process!)

Again, I hope that helps a bit.
Cheers,
Dave

[ECNU4271]

Hi Michael,

Aha!! (as Klaus would say )

You didn't mention that bit of information in the original post

Ok, to answer your questions will require a little bit more info from you on how you want to use the iphone and Mac. Is the App you are going to create for personal(or private business) use? or are you planning on selling it as a commercial tool?

If it's for personal use then the next question is: will the iphone always be on the same wireless connection (ie LAN) or will it be used outside the Local Area - therefore on the Internet (WAN) ?

If it's for Commercial use then is it a tool that will be sold via the App store for the general public to use? If so then it must therefore be an Internet based tool.

If the public use option is chosen then you will really need to get to grips with server side coding to a high level to cover security issues, multi-user database locking and much more. Much more than I can go into here.

Either way you will need to get to understand some protocol of data transfer across the network (like HTTP get requests) and then also to know how to have some code on the server (your Mac in this case) handle incoming requests and send back data. The Remote-Server (vampire-tiger) tutorial covers the basics of this using PHP - though you could use the Livecode Server (with Apache and MySQL which you have already installed) and write everything in Livecode language. I can't really help with the Livecode Server option because I haven't used it - I'm sure someone else will be more than willing to help with that though.

PHP is actually quite easy once you start playing with it. It just looks complicated with all those dollar signs

If you follow through my tutorial carefully it should make some sense (I hope!) and if you have any problems with it then please just ask as I am very willing to help explain it (and improve the tutorial in the process!)

Again, I hope that helps a bit.
Cheers,
Dave

Thanks for the reply, Dave.

My app will be used only by me actually. It's the product of one of my course. I only need to demonstrate this app on my iPhone, my Mac is in the same wireless connection with my iphone during this demon. And I only need to make this app work with the database once only, to be honest.

The above will be my background. At the moment, I've installed the MAMP, I don't have Apache and MySQL installed alone. As especially, I have no idea how to use these software.

Is there a shortcut to solve my problem or I still need to stick to your tutorial on the vampire-tiger?
Besides, when I read your tutorial, I get lost from "enter data into database" sections. Those codes below that section, I really don't know how to use them.

Is there any clue you could help me with?

Thanks a lot!
Michael

[dave_probertGA6e24]

Hi Michael,

Ok, I'm going to suggest a little phased development process here - in other words take baby steps

First of all if you have installed MAMP then you have Apache, MySQL and PHP on your machine and they are probably working fine To find out you just need to run Mamp from your Applications folder. You should get a little window appear with MAMP in BIG letters in it!!!

If that appears then it is likely to automatically start the servers (possibly asking for an admin password). If it doesn't start the servers then click the Start Servers button.

Once the servers are running (with little green blobs next to their names on the left-hand side) then click 'Open Start Page' and wait a second of two. Hopefully a web page should open in your default browser with the heading 'Welcome to MAMP'. If that does then your MAMP is running perfectly well (I think!)

Now for the fun part...
Using your favourite text editor (Textedit.app or whatever) create a new file.
type this (or copy it) into the file:

Code: Select all

<?php
print "Hi There!";
?>

now save that file into the /Applications/Mamp/htdocs folder (or /www ) as hithere.php
Go to your browser and enter the url: localhost/hithere.php
You should then get your message from above displayed. Easy as that.

If all has gone well so far then the next stage is to display that on your iphone. For this you will need to know the ip address of your Mac. Go to System Preferences (under the Apple icon at the top-left of your screen) and choose the Network icon. Make sure your WiFi or Wired connection is selected and then click the 'Advanced...' button. Choose the TCP/IP tab and take note of the IPv4 Address.

On your iPhone go to Safari and enter that IP address and the filename into the address bar ( eg. 192.168.1.2/hithere.php ). If everything is Ok then you should see the message on your iphone too.

Try editing the file and change the message, then reload the page on the iphone.

I won't go further with this yet until you let me know if any of it worked!!

Cheers,
Dave

[ECNU4271]

Hi Michael,

Ok, I'm going to suggest a little phased development process here - in other words take baby steps

First of all if you have installed MAMP then you have Apache, MySQL and PHP on your machine and they are probably working fine To find out you just need to run Mamp from your Applications folder. You should get a little window appear with MAMP in BIG letters in it!!!

If that appears then it is likely to automatically start the servers (possibly asking for an admin password). If it doesn't start the servers then click the Start Servers button.

Once the servers are running (with little green blobs next to their names on the left-hand side) then click 'Open Start Page' and wait a second of two. Hopefully a web page should open in your default browser with the heading 'Welcome to MAMP'. If that does then your MAMP is running perfectly well (I think!)

Now for the fun part...
Using your favourite text editor (Textedit.app or whatever) create a new file.
type this (or copy it) into the file:

Code: Select all

<?php
print "Hi There!";
?>

now save that file into the /Applications/Mamp/htdocs folder (or /www ) as hithere.php
Go to your browser and enter the url: localhost/hithere.php
You should then get your message from above displayed. Easy as that.

If all has gone well so far then the next stage is to display that on your iphone. For this you will need to know the ip address of your Mac. Go to System Preferences (under the Apple icon at the top-left of your screen) and choose the Network icon. Make sure your WiFi or Wired connection is selected and then click the 'Advanced...' button. Choose the TCP/IP tab and take note of the IPv4 Address.

On your iPhone go to Safari and enter that IP address and the filename into the address bar ( eg. 192.168.1.2/hithere.php ). If everything is Ok then you should see the message on your iphone too.

Try editing the file and change the message, then reload the page on the iphone.

I won't go further with this yet until you let me know if any of it worked!!

Cheers,
Dave

It all works so far!!!

[dave_probertGA6e24]

WOW!!! I wrote all that off the top of my head and just kept my fingers crossed

Moving on then...

The next stage is to simply access the database from PHP and see if we can see the data in the browser.
I'm probably going to refer to the Vampire-Tiger tutorial (or at least copy the code) as it's already written.

change the xxxx bits below to suit whatever they are on your system - refer to the Mamp Start page for a reminder.
change the 'myDataTable' to be one of your tables you have created already.
change the 'name' bit in the $row['name'] below to a column name from that table.

Modify your hithere.php (or create a new file) to be like this:

Code: Select all

<?php
$host = 'localhost';
$user = 'xxxxx';
$pass = 'xxxxx';
$dbname = 'xxxxx';

$dblinkid = mysqli_connect($host, $user, $pass, $dbname);

mysqli_select_db($dblinkid, $dbname);

$res = mysqli_query(  $dblinkid, 'SELECT * FROM myDataTable');

while($row = mysqli_fetch_assoc($res) )
{
	print $row['name'];
}
mysqli_free_result( $res );
?>

Save the file and access it from the browser. If all is Ok then you should see the data from the database.

This is all assuming that you have data already in your database!! If not then you might want to add some through whichever tool you used to create the database in the first place. Or use PHPMyAdmin (from the Mamp Start page) as an easy way to access it.

Once you are seeing some of your data then we can continue...

Cheers,
Dave

[ECNU4271]

The url is actually localhost:8888/hithere.php I figured this out myself ha

[ECNU4271]

WOW!!! I wrote all that off the top of my head and just kept my fingers crossed

Moving on then...

The next stage is to simply access the database from PHP and see if we can see the data in the browser.
I'm probably going to refer to the Vampire-Tiger tutorial (or at least copy the code) as it's already written.

change the xxxx bits below to suit whatever they are on your system - refer to the Mamp Start page for a reminder.
change the 'myDataTable' to be one of your tables you have created already.
change the 'name' bit in the $row['name'] below to a column name from that table.

Modify your hithere.php (or create a new file) to be like this:

Code: Select all

<?php
$host = 'localhost';
$user = 'xxxxx';
$pass = 'xxxxx';
$dbname = 'xxxxx';

$dblinkid = mysqli_connect($host, $user, $pass, $dbname);

mysqli_select_db($dblinkid, $dbname);

$res = mysqli_query(  $dblinkid, 'SELECT * FROM myDataTable');

while($row = mysqli_fetch_assoc($res) )
{
	print $row['name'];
}
mysqli_free_result( $res );
?>

Save the file and access it from the browser. If all is Ok then you should see the data from the database.

This is all assuming that you have data already in your database!! If not then you might want to add some through whichever tool you used to create the database in the first place. Or use PHPMyAdmin (from the Mamp Start page) as an easy way to access it.

Once you are seeing some of your data then we can continue...

Cheers,
Dave

Yes, I succeed! But the data is linked without space in the same line in the browser page.

[ECNU4271]

Hi, Dave

I viewed a lot topics in the database section of the forum.

Found this code works in LiveCode in connecting database? Could you please see if it is useful?
I kind of feel the way he used is like the way you used in the php file. Am I right?

Code: Select all

on mouseUp
    -- use a global variable to hold the connection ID so other scripts can use it
    global gConnectionID
    
    -- set up the connection parameters - edit these to suit your database
    put "localhost" into tDatabaseAddress
    put "XXXX" into tDatabaseName
    put "root" into tDatabaseUser
    put "root" into tDatabasePassword
    
    -- connect to the database
     put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword,false,"/Applications/MAMP/tmp/mysql/mysql.sock") into tResult
    
    -- check if it worked and display an error message if it didn't
    -- & set the connection ID global
    if tResult is a number then
        put tResult into gConnectionID
        answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
    else
        put empty into gConnectionID
        answer error "Unable to connect to the database:" & cr & tResult
    end if
end mouseUp

[ECNU4271]

Thank you very much for typing so much for me tonight. I really appreciate it! I don't know how to reward you!

[dave_probertGA6e24]

Hi Michael,

Glad that it worked. The data all appearing on one line was because we didn't tell it to put any '<br>' bits in the output (for HTML) - but it was just to see that it worked.

Regarding the Livecode code for the connection - that's almost exactly what you need to achieve the same thing as the PHP. Good find.

The fun part now is that you will probably want to try that code from your iphone and connect to the Mac database. There is where you will likely need the ip address of the Mac as I don't think 'localhost:8888' will work (but try it anyway - you never know!). Possibly try with localhost:8889 - it might be more correct for mySQL.

If you can get a direct connection then you are basically all the way there (apart from writing the other code!)

If you can't get a connection then you will need to either install the Livecode Server under the Apache (I can't really help there - I'm sure there is probably a tutorial regarding it though) so that you can run the Livecode code, or continue with the PHP route.

Let us know how it goes.

BTW, you don't need to quote my messages every time you reply

Cheers,
Dave

[ECNU4271]

Yes, ok, Dave!

While I am trying on my iPhone, could you please answer this question:

Code: Select all

on mouseUp
    -- check the global connection ID to make sure we have a database connection
    global gConnectionID
    if gConnectionID is not a number then
        answer error "Please connect to the database first."
        exit to top
    end if
    
    -- construct the SQL (this selects all the data from the specified table) 
    put "Table1" into tTableName    -- set this to the name of a table in your database
    put "SELECT * FROM " & tTableName into tSQL
    
    -- query the database
    put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData
    
    -- check the result and display the data or an error message
    if item 1 of tData = "revdberr" then
        answer error "There was a problem querying the database:" & cr & tData
    else
        put tData into field "Data"
    end if
end mouseUp

The code is from tutorial of LC, to get information from database.
I tried and succeed. But the data which is shown in the field is only the first data of that table. Why and how to show what I need?

ORZORZ