The Big MySQL Questions/Problems Thread

The Big MySQL Questions/Problems Thread - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: The Big MySQL Questions/Problems Thread (/showthread.php?tid=365535)

The Big MySQL Questions/Problems Thread - Garsino - 03.08.2012

What if we had a shared thread for all the MySql problems people experience, so we could all learn from eachother and write better code & learn new ways to solve things? IMHO it would be easier than having 9999 threads about the same problem/question.

If everyone posted in the same topic, it would be easy for other people to read up on what to do and what to avoid when dealing with MySQL.

So I'll start:

Quote:

I want to log information into seperate tables in my MySQL database.

pawn Код:

stock Log(table[], text[], bool:print = true)
{
    new timestamp[128], day, month, year, hour, minute, second;
    getdate(year, month, day), gettime(hour, minute, second);
    format(timestamp, sizeof(timestamp), "%02d/%02d/%04d %02d:%02d:%02d", day, month, year, hour, minute, second);
    format(CMDSString, sizeof(CMDSString), "CREATE TABLE IF NOT EXISTS `%s` (TimeStamp varchar(255), Log varchar(255))", table);
    mysql_querry_no_callback(CMDSString);
    format(CMDSString, sizeof(CMDSString), "INSERT INTO %s (TimeStamp, Log) VALUES (%s, '%e')", table, timestamp, text);
    if(print == true)
    {
        printf("[%s] %s", timestamp, text);
    }
    return mysql_querry_no_callback(CMDSString);
}

pawn Код:

stock mysql_querry_no_callback(querry[]) return mysql_function_query(MySqlDB, querry, false, "", "");

However, it doesn't seem to create the table at all. What could be wrong?

Re: The Big MySQL Questions/Problems Thread - Misiur - 03.08.2012

Calling CREATE TABLE on each log call is not a good idea. Anyway, "TimeStamp" is a reserved mysql name, you can't use it without `` as a field name. So:

pawn Код:

format(CMDSString, sizeof(CMDSString), "CREATE TABLE IF NOT EXISTS `%s` (TimeStamp varchar(255), Log varchar(255))", table);
//Changes to
format(CMDSString, sizeof(CMDSString), "CREATE TABLE IF NOT EXISTS `%s` (`TimeStamp` varchar(255), `Log` varchar(255))", table);

I'd also suggest InnoDB engine and default charset

Re: The Big MySQL Questions/Problems Thread - StreetGT - 03.08.2012

why you dont have table already created the you use INSERT INTO then UPDATE?

Re: The Big MySQL Questions/Problems Thread - Lant - 03.08.2012

PHP код:



format( sql, sizeof( sql ), "UPDATE `seaded` SET hetkeTund = %d", ServeriAndmed[ hetkeTund ] );
format(sql, sizeof(sql), "UPDATE oautod SET Omatud = %d, Omanik = %d, Model = %d, Hind = %d,Varv1 = %d, Varv2 = %d,     AsuX = '%f', AsuY = '%f', AsuZ = '%f', AsuA = '%f', GPS = %d, Autopood = %d  WHERE CarId = %d",
format(sql, sizeof(sql), "UPDATE tanklad SET Owned = '%d', Owner = '%d', Locked = '%d', Bank = '%d', Kytusehind = '%d', MaksimumKytus = '%d', Kytust = '%d', KytuseOhind = '%d'  WHERE id = '%d'",
format( sql, sizeof( sql ), "UPDATE kopterid SET Color1 = %d, Color2 = %d  WHERE KopterId = %d", KopterInfo[ i ][ cColor1 ],KopterInfo[ i ][ cColor2 ], i );
format(sql, sizeof(sql), "UPDATE kopterid SET Owned = '%d', Owner = '%d', Parked = '%d', ParkLocationx = '%f', ParkLocationy = '%f', ParkLocationz = '%f', ParkAngle = '%f', GPS = '%d', HeliKeyGiven = '%d' WHERE KopterId = '%d'",
format( sql, sizeof( sql ), "UPDATE paadid SET Color1 = %d, Color2 = %d  WHERE paId = %d", PaadiInfo[ i ][ paColor1 ], PaadiInfo[ i ][ paColor2 ], i );
format( sql, sizeof( sql ), "UPDATE motikad SET Color1 = %d, Color2 = %d  WHERE BikeId = %d", MotikaInfo[ i ][ bikeColor1 ], MotikaInfo[ i ][ bikeColor2 ], i );
format(sql, sizeof(sql), "UPDATE treilerid SET Owned = '%d', Owner = '%d', Parked = '%d', ParkLocationx = '%f', ParkLocationy = '%f', ParkLocationz = '%f', ParkAngle = '%f', GPS = '%d', TreilerKeyGiven = '%d' WHERE TreilerId = '%d'",
format(sql, sizeof(sql), "UPDATE acars SET Owned = '%d', Owner = '%d', Parked = '%d', ParkLocationx = '%f', ParkLocationy = '%f', ParkLocationz = '%f', ParkAngle = '%f', GPS = '%d'  WHERE CarId = '%d'",
format(sql, sizeof(sql), "UPDATE acars SET Pagass1 = '%d', Pagass1Kuulid = '%d' , Pagass2 = '%d', Pagass2Kuulid = '%d' , Pagass3 = '%d', Pagass3Kuulid = '%d' WHERE CarId = '%d'",
format(sql, sizeof(sql), "UPDATE acars2 SET Pagass1 = '%d', Pagass1Kuulid = '%d' , Pagass2 = '%d', Pagass2Kuulid = '%d' , Pagass3 = '%d', Pagass3Kuulid = '%d' WHERE CarId = '%d'",
format(sql, sizeof(sql), "UPDATE acars2 SET Owned = '%d', Owner = '%d', Parked = '%d', ParkLocationx = '%f', ParkLocationy = '%f', ParkLocationz = '%f', ParkAngle = '%f', GPS = '%d'  WHERE CarId = '%d'",
format(sql, sizeof(sql), "UPDATE motikad SET Owned = '%d', Owner = '%d', Parked = '%d', ParkLocationx = '%f', ParkLocationy = '%f', ParkLocationz = '%f', ParkAngle = '%f', Locationx = '%f', Locationy = '%f', Locationz = '%f', Angle = '%f', GPS = '%d', BikeKeyGiven = %d, Autopood = %d WHERE BikeId = '%d'",
format(sql, sizeof(sql), "UPDATE paadid SET Owned = '%d', Owner = '%d', Parked = '%d', ParkLocationx = '%f', ParkLocationy = '%f', ParkLocationz = '%f', ParkAngle = '%f', KeyGiven = '%d' WHERE paId = '%d'",
format(sql, sizeof(sql), "UPDATE firmad SET Owned = '%d', Owner = '%d', Locked = '%d', Bank = '%d', TeenuseHind = '%d', MaxProdukte = '%d', Produkte = '%d', ProduktiHind = '%d', Prygi = '%d', Prygi = '%d'  WHERE id = '%d'",
format(sql, sizeof(sql), "UPDATE majad SET InteriorX = '%f',InteriorY = '%f',InteriorZ = '%f',InteriorId = '%d', Owned = '%d', Owner = '%d', Locked = '%d', Rentable = '%d', Rent = '%d', Bank = '%d', Narko = '%d', Mats1 = '%d', Mats2 = '%d', Mats3 = '%d', Myyk = '%d' WHERE id = '%d'",
format(sql, sizeof(sql), "UPDATE majad SET Relvakapp = '%d', Relv1 = '%d', Kuulid1 = '%d', Relv2 = '%d', Kuulid2 = '%d', Relv3 = '%d', Kuulid3  = '%d' WHERE id = '%d'",
format(sql, sizeof(sql), "UPDATE garaazid SET Ostetud = '%d', Omanik = '%d', Kirjeldus = '%s', Hind = '%d'  WHERE gaID = '%d'",
format(sql, sizeof(sql), "UPDATE oautod SET AsuX = '%f', AsuY = '%f', AsuZ = '%f', AsuA = '%f' WHERE CarId = '%d'",
format( sql, sizeof( sql ), "UPDATE acars SET License = '%s' WHERE CarId = '%d' ", tekst, i );
format( sql, sizeof( sql ), "UPDATE acars2 SET License = '%s' WHERE CarId = '%d' ", tekst, i );

Someone create those sql files for me, so I can import em' ? Please.

Re: The Big MySQL Questions/Problems Thread - StreetGT - 03.08.2012

create you...

Re: The Big MySQL Questions/Problems Thread - Vince - 03.08.2012

Quote:

Originally Posted by Misiur

Anyway, "TimeStamp" is a reserved mysql name, you can't use it without `` as a field name.

Yes, you can.

Quote:

MySQL permits some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:

ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP

http://dev.mysql.com/doc/refman/5.0/...ved-words.html

Re: The Big MySQL Questions/Problems Thread - Misiur - 03.08.2012

Dammit, I'm wrong again (didn't know they allowed some). What is happing today with me? Anyway: OP, to store date I suggest using mysql functions. I have Datetime type field, then simply you pass gettime() with

Код:

`your_field` = FROM_UNIXTIME('%i')

and fetch with UNIX_TIMESTAMP. Also you in your inserty you are missing '

Код:

VALUES (%s, '%e')
//To
VALUES ('%s', '%e')

Re: The Big MySQL Questions/Problems Thread - Garsino - 03.08.2012

Quote:

Originally Posted by Misiur

Dammit, I'm wrong again (didn't know they allowed some). What is happing today with me? Anyway: OP, to store date I suggest using mysql functions. I have Datetime type field, then simply you pass gettime() with

Код:

`your_field` = FROM_UNIXTIME('%i')

and fetch with UNIX_TIMESTAMP. Also you in your inserty you are missing '

Код:

VALUES (%s, '%e')
//To
VALUES ('%s', '%e')

Thank you