[Gilmar]

I have a query like this:

Код:

SELECT name, surname  FROM `pgs`,`users` WHERE `username`='%s'

With this query I get all the pgs associated to the user account on the table users. I want to put the name and the surname on a variable (or two variables, I don't know how to do that).
When I have just one field to select I usually do:

pawn Код:

new query[90];
new result[10];
new ris;
format(query,sizeof(query),"SELECT `admin` FROM `users` WHERE `username` = '%s'", GetName(playerid));
mysql_query(query);
mysql_store_result();
if(mysql_num_rows() == 1)
{
    mysql_fetch_row_format(result);
    ris = strval(result);
}
mysql_free_result();

If I have multiple fields or multiple rows how I do that?
Thanks for the help.

[Gilmar]

pawn Код:

new str[100];
    new query[300];
    format(query, sizeof(query), "SELECT * FROM `users` WHERE `username`='%s'", GetName(playerid));
    mysql_query(query);
    mysql_store_result();
    if(mysql_retrieve_row())
    {
        mysql_get_field("username", str);
        SendClientMessage(playerid, COLOR_GREEN, str);
    }
    mysql_free_result();

I tried this but it doesn't work, any tips?

[iZN]

SELECT * FROM `users` WHERE `username`='%s' LIMIT 1

Try this query perhaps?

[Gilmar]

I added the LIMIT 1 but it's the same.

[iZN]

Quote:

Originally Posted by Gilmar I add the LIMIT 1 but it's the same.

Enable MySQL debug and post the logs here.

[Gilmar]

This is the debug log when I try the query:

Код:

[16:03:59] >> mysql_query( Connection handle: 1 )

[16:03:59] CMySQLHandler::Query(SELECT * FROM `users` WHERE `username`='Omar' LIMIT 1) - Successfully executed.

[16:03:59] >> mysql_store_result( Connection handle: 1 )

[16:03:59] CMySQLHandler::StoreResult() - Result was stored.

[16:03:59] >> mysql_retrieve_row( Connection handle: 1 )

[16:03:59] >> mysql_fetch_field_row( Connection handle: 1 )

After that the samp-server windows close itself.

EDIT:
This happens when I change the query from:

Код:

SELECT * FROM `users` WHERE `username`='%s' LIMIT 1

to:

Код:

SELECT `username` FROM `users` WHERE `username`='%s' LIMIT 1

Код:

[16:12:04] >> mysql_query( Connection handle: 1 )

[16:12:04] CMySQLHandler::Query(SELECT `username` FROM `users` WHERE `username`='Omar' LIMIT 1) - Successfully executed.

[16:12:04] >> mysql_store_result( Connection handle: 1 )

[16:12:04] CMySQLHandler::StoreResult() - Result was stored.

[16:12:04] >> mysql_retrieve_row( Connection handle: 1 )

[16:12:04] >> mysql_fetch_field_row( Connection handle: 1 )

[16:12:04] CMySQLHandler::FetchField("username") - Omar

[16:12:04] >> mysql_free_result( Connection handle: 1 )

[16:12:04] CMySQLHandler::FreeResult() - Result was successfully free'd.

[Dokins]

pawn Код:

new escape[MAX_PLAYER_NAME*2];
    mysql_real_escape_string(GetName(playerid), escape);

You should escape the string. That may be the cause, but I'm not sure entirely.

[Gilmar]

I tried a new way, for example to select two fields:

pawn Код:

new str[350];
new query[300];
new nome[100];
new banned[100];
format(query, sizeof(query), "SELECT `username`,`banned` FROM `users` WHERE `username`='%s'", GetName(playerid));
mysql_query(query);
mysql_store_result();
if(mysql_fetch_row_format(str))
{
    sscanf(str, "p|ss", nome, banned);
    SendClientMessage(playerid, COLOR_GREEN, nome);
    SendClientMessage(playerid, COLOR_GREEN, banned);
}
mysql_free_result();

I used sscanf because the string I got from mysql_fetch_row_format is formatted like this:

Код:

field1|field2|...|fieldN

With the sscanf I get the individual fields so I put them on a variable and I can use them.
If I have multiple rows I think I have just to use the mysql_retrieve_row to move the pointer to the next row.

My problem wasn't the lack of the string escape, because I usually format the query in the same way and this was the first time I had a problem with it.
I don't want to use the mysql_get_field anymore lol, because this new way seems to work as I want. I hope my solution can help someone!