MySQL Database Logging

Hi once again.

I’m giving the MySQL Database logging a go.

Currently I have it set-up to log onto my website’s database (so a remote database essentially), with the following settings:

Protocol: mysql
Host Name: nowwebsitedesign.co.uk
(as well as database, user, password etc)

I have a table set-up in the database called ‘logging’, and ID is auto_increment. Now I have the following in the ‘INSERT on Stop’ section:

INSERT into ‘logging’ (‘ID’, ‘artist,‘title’,‘length’,‘datetime played’) VALUES(’’,’%a’,’%b’,’%e’,’ %T{dd/mm/yy hh:nn:ss am/pm}’)

I could do with some assistance in perfecting this, as I reckon I have done something wrong!

Ryan.

Ryan, the correct syntax is:

INSERT INTO logging (artist, title, length, datetime played) VALUES(%a, %b, %e, %T{dd/mm/yy hh:nn:ss am/pm})

Some points:

[ul][li]You only need quotes around column names if they contain ‘special’ characters, like a space.[/li]
[li]Strictly speaking, the ‘quotes’ around MySQL identifiers should be backquotes (character to the left of the 1 key on your keyboard) as shown above.[/li]
[li]I don’t think you need to include the ID field in the INSERT statement, because SQL will provide a value for it. (I may be wrong on this one, though!)[/li]
[li]mAirList automatically adds the quotes around logging variables in a VALUES clause. Don’t add any quotes yourself.[/li][/ul]

Hope that helps.

BFN
CAD

Cheers Cad.

It seems to be working up to - %T{dd/mm/yy hh:nn:ss am/pm}

An error is returned about using the correct syntax.

Not sure what is wrong there.

Try removing the am/pm and see if that works. hh:nn:ss is 24-hour format anyway. :wink:

BFN
CAD

So it is!

I tried removing am/pm but no luck. I’ve attached the image of the error :slight_smile:


sql.jpg

OK, try this instead:

INSERT INTO logging (artist, title, length, datetime played) VALUES(%a, %b, %e, NOW())

Assuming that datetime played is a SQL date/time column, it’s better and easier to use the SQL NOW() function to insert the current date/time. I should have thought of that earlier!

BFN
CAD

Perhaps you should post the database schema (table definition) for reference.

Cad i’m unable to try out what you have suggested as i am not at home right now…i shall try tomorrow.

Torben, i’m unsure what you mean, but i have attached an image of the table structure. Also note i have changed the name of the ‘length’ field, to ‘durtation’.

Thanks.


db.GIF

You should also rename “datetime played” to “datetimeplayed” or so. It’s not a good idea to have blank characters in table or column names because it will make writing queries somewhat difficult at times. I also suggest to use only lower-case characters, i.e., rename “ID” to “id”, for the same reason.

Successfully working guys.

Cheers.