simple message board through database
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
simple message board through database
Update: I made a database on myfreesqlhosting.net
I am working with http://lessons.livecode.com/m/4071/l/70 ... l-database to connect to the database,
however i just want one single set of data to work with so I am trying to alter script to reflect that.
I am working with http://lessons.livecode.com/m/4071/l/70 ... l-database to connect to the database,
however i just want one single set of data to work with so I am trying to alter script to reflect that.
Re: simple message board through database
I am connecting to database okay. I have a table called "Chat" with on column called "Chat Field" on the database
The error code: check the manual the corresponds to the correct syntax for '(& tFields &)' so it something to do with my syntax for accessing the column
My update button intended to get the info from mysql and display in a field on my computer
local tTableName
local tSQL
local tData
local tFields
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 "Chat" into tTableName
-- set this to the name of a table in your database
put "Chat Field" into tfields
put "SELECT * FROM " & tTableName & (" & tFields & ") 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
My submit button intended to get info from field on card and put into mysql
local tTableName
local tEnter
local tSQL
local tFields
-- 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
-- edit these variables to match your database & table
-- this assumes a table called Table1 with 3 fields
put "Chat" into tTableName
put "Chat Field" into tFields
get the text of fld "enter"
put it into tEnter
-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (tEnter)" into tSQL
-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL, "tEnter"
-- check the result and display the data or an error message
if the result is a number then
answer info "New record added."
else
answer error "There was a problem adding the record to the database:" & cr & the result
end if
end mouseUp
The error code: check the manual the corresponds to the correct syntax for '(& tFields &)' so it something to do with my syntax for accessing the column
My update button intended to get the info from mysql and display in a field on my computer
local tTableName
local tSQL
local tData
local tFields
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 "Chat" into tTableName
-- set this to the name of a table in your database
put "Chat Field" into tfields
put "SELECT * FROM " & tTableName & (" & tFields & ") 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
My submit button intended to get info from field on card and put into mysql
local tTableName
local tEnter
local tSQL
local tFields
-- 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
-- edit these variables to match your database & table
-- this assumes a table called Table1 with 3 fields
put "Chat" into tTableName
put "Chat Field" into tFields
get the text of fld "enter"
put it into tEnter
-- construct the SQL - the :1, :2 & :3 placeholders in the SQL will be filled by variables in the revExecuteSQL line
put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (tEnter)" into tSQL
-- send the SQL to the database, filling in the placeholders with data from variables
revExecuteSQL gConnectionID, tSQL, "tEnter"
-- check the result and display the data or an error message
if the result is a number then
answer info "New record added."
else
answer error "There was a problem adding the record to the database:" & cr & the result
end if
end mouseUp
Re: simple message board through database
Hi,
I would think that the syntax of your SQL command is not completely right:
If tTableName = "Chat" the above command would put "SELECT * FROM Chat & tFields &" in the tSQL Variable. Try perhaps only with and then extract the data in livecode.
Best
Oliver
I would think that the syntax of your SQL command is not completely right:
since you placed tFields into quotation marks it will take it as text, not as a variable.put "SELECT * FROM " & tTableName & (" & tFields & ") into tSQL
If tTableName = "Chat" the above command would put "SELECT * FROM Chat & tFields &" in the tSQL Variable. Try perhaps only with
Code: Select all
put "SELECT * FROM " & tTableName into tSQL
Best
Oliver
Re: simple message board through database
Thanks Oliver,
I corrected the syntax and no longer get an error but nothing happens at all. I have heard that live code code won't work with mysql unless there are some php scripts in-between. Im starting to think that is the problem.
I corrected the syntax and no longer get an error but nothing happens at all. I have heard that live code code won't work with mysql unless there are some php scripts in-between. Im starting to think that is the problem.
Re: simple message board through database
Hi Jordy, I have created endless applications with livecode that used MySQL connections and did not use a single like of php or any other language. If the answer you get from this query is empty and you dont get an error then this table is empty. Try using an application like HeidiSQL to see if there is any data in your database
Knowledge is meant to be shared.
Re: simple message board through database
Hi, MySQL works just fine. I guess it is rather a syntax problem. With MySQL you have to be very careful with commas and quotes etc. when composing the MySQL argument. Are you working with a test database on a server or with a local database on your computer? I suggest to start with a local database, the advantage is that you can check the actual content of that database in any text editor. A local database is opened like that (modify the filepath to your database):
I assume that you have a database with a table and a column. Now, when adding content to your table you have to construct the MySQL argument very carefully. I post here some sample code that has worked for me:
Check how the variable gameinfo is constructed. It is important to get it right with the single and double quotes. As an example, the variable tSQL looks like this, before revExecuteSQL is executed:
In your case the tSQL should probably look like this:
When you have constructed your tSQL variable correctly do the MySQL execution like this:
Another problem could be if the text that you want to store in your database contains quotes or tabs or returns, this messes up your MySQL argument. In such cases you have to replace those special characters in your text with something like this *TAB*.You would do it in my sample code where it is written "clean input data". Look for example here: http://forums.livecode.com/viewtopic.php?f=8&t=21920
Good luck!
Best
Oliver
Code: Select all
## Connect to the database
## Save the connection id
put specialFolderPath("desktop") & "/Beginners Course/Week 5/Sample Stacks/emailaddresses.sqlite" into tDatabasePath
put revOpenDatabase("sqlite",tDatabasePath,,,,) into sDatabaseID
Code: Select all
on mouseUp
global connectID
local gameinfo, tSQL
put empty into gameinfo
put empty into tSQL
-- check if database is connected
if connectID is "" then
answer information "No Database is Connected!"
exit mouseUp
end if
-- Collect game data
put "'" & field "name" & "','" & field "date" & "','" & field "time" & "','" & field "city" & "','" & field "country" & "','" & field "location" & "','" & field "address" & "','" & field "context" & "','" & field "info" & "'" into gameinfo
-- clean input data
-- Construct SQL
put "INSERT INTO games(gamename,date,time,city,country,location,address,context,info) VALUES(" & gameinfo & ")" into tSQL
-- Execute SQL
revExecuteSQL connectID,tSQL
put the result into tTmp
-- check for error
handleRevDBerror tTmp
if the result is not empty then
answer warning the result
exit mouseUp
end if
end mouseUp
Code: Select all
INSERT INTO games(gamename,date,time,city,country,location,address,context,info) VALUES('Goat Simulator','23.10.2012','17:00-19:00','Rijeka','Croatia','Molekula','Titosquare 1','Festival (annual)','live like a goat')
Code: Select all
INSERT INTO chat(chat field) VALUES('chatcontent')
Code: Select all
revExecuteSQL gConnectionID, tSQL
Good luck!
Best
Oliver
Re: simple message board through database
Hi,
Just a hint - I often use this, after constructing a query:
during the development. This way I see the result of my query (in msgbox), and can check the real SQL-string in my editor (via paste) ;-)
For working with SQL strings, using specialized tools like MSAccess, HeidiSQL or such can be most helpful, to construct and test those.
Have fun!
Just a hint - I often use this, after constructing a query:
Code: Select all
set the clipboarddata to MyStr
get revdb_execute(MyDBID,MyStr) -- or revDataFromQuery() ...
put it
For working with SQL strings, using specialized tools like MSAccess, HeidiSQL or such can be most helpful, to construct and test those.
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
Re: simple message board through database
Thanks everyone!
I remade the sample app using the code from "connecting to a mysql database" LiveCode example.
i got it to work to some extent:
put "INSERT INTO " & tTableName & " VALUES (1)" into tSQL
will put a 1 into the table
put "INSERT INTO " & tTableName & " VALUES (tEnter)" into tSQL
gives error cause you need to surround text in single quotes (otherwise it thinks its a column)
put "INSERT INTO " & tTableName & " VALUES ('tEnter')" into tSQL
would put tEnter into the table
anyone know how to get my tEnter to put the value of my tEnter variable into there?
I remade the sample app using the code from "connecting to a mysql database" LiveCode example.
i got it to work to some extent:
put "INSERT INTO " & tTableName & " VALUES (1)" into tSQL
will put a 1 into the table
put "INSERT INTO " & tTableName & " VALUES (tEnter)" into tSQL
gives error cause you need to surround text in single quotes (otherwise it thinks its a column)
put "INSERT INTO " & tTableName & " VALUES ('tEnter')" into tSQL
would put tEnter into the table
anyone know how to get my tEnter to put the value of my tEnter variable into there?
Re: simple message board through database
I figured it out
put "INSERT INTO " & tTableName & " VALUES (:1)" into tSQL
Then add the variable you want to be added to your table at the end of next statement like so...
revExecuteSQL gConnectionID, tSQL, "tEnter"
put "INSERT INTO " & tTableName & " VALUES (:1)" into tSQL
Then add the variable you want to be added to your table at the end of next statement like so...
revExecuteSQL gConnectionID, tSQL, "tEnter"