simple message board through database

[Jordy]

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.

[Jordy]

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

[okk]

Hi,
I would think that the syntax of your SQL command is not completely right:

put "SELECT * FROM " & tTableName & (" & tFields & ") into tSQL

since you placed tFields into quotation marks it will take it as text, not as a variable.

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

and then extract the data in livecode.

Best
Oliver

[Jordy]

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.

[zaxos]

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

[okk]

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):

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

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:

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

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:

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')

In your case the tSQL should probably look like this:

Code: Select all

INSERT INTO chat(chat field) VALUES('chatcontent')

When you have constructed your tSQL variable correctly do the MySQL execution like this:

Code: Select all

 revExecuteSQL gConnectionID, tSQL

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

[AxWald]

Hi,

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

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!

[Jordy]

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?

[Jordy]

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"