[MichiEXE]

Hello com,

Gota big problem with my script, i want to make a command that shows every report ticket stored in the database.
So far it works, i can read and show every field content in the dialog:
- report categorie, timestamp, userid (databaseid), id (ticketid)
Now, we wanna display the USERNAME instead of the userid (databaseid), so we need to start a second query selecting the username from the players table (the stock), but after we made our first attemps, it looked like this:

(Ignore that the buttons etc. are in german but the code is english)

The first row is perfectly fine, but then it returns NULL.

Our code:

PHP Code:

cmd:reports(playerid, params[])
{
    if(!IsAdmin(playerid, 1))return noaccess
    new query[128], str[400];
    mysql_format(dbhandle, query, sizeof(query),"SELECT * FROM tickets WHERE status = 0");
    mysql_query(dbhandle, query);
    format(str,sizeof(str),"Typ\tUsername\tTimestamp\n");
    new rows, fields, idx;
    cache_get_data(rows, fields, dbhandle); //Get rows and fields 
    if(!rows)return SendClientMessage(playerid, GRAU, "No reports in database!");
    
    while(idx<rows)
    {
        printf("idx %d", idx);
        format(str,sizeof(str),"%s%d\t%s\t%s\n",str,cache_get_field_content_int(idx, "kategorie", dbhandle),mysql_GetUserNameFromTable(cache_get_field_content_int(idx, "userid", dbhandle)),date(cache_get_field_content_int(idx, "timestamp", dbhandle)));
        idx++;
    }
    return ShowPlayerDialog(playerid, DIALOG_REPORTLIST, DIALOG_STYLE_TABLIST_HEADERS, "Report Tickets", str, "Show", "Close");
}
stock mysql_GetUserNameFromTable(userid)
{
    new uname[MAX_PLAYER_NAME];
    new query[256];
    printf("%d", userid);
    mysql_format(dbhandle, query, 256, "SELECT * FROM spieler WHERE id = %d", userid);
    mysql_query(dbhandle, query);
    cache_get_field_content(0, "name", uname, dbhandle);
    printf("%s - %s",uname, date(gettime()));
    return uname;
} 


[Stinged]

I rarely use non threaded queries, but I think you need to store the result to be able to use cache_ functions.
Try this:

Code:

mysql_GetUserNameFromTable(userid) 
{ 
    new query[50];
    printf("%d", userid); 
    mysql_format(dbhandle, query, sizeof (query), "SELECT * FROM spieler WHERE id = %d", userid); 
	
    new Cache:result = mysql_query(dbhandle, query); 
    cache_get_field_content(0, "name", query, dbhandle); 
    cache_delete(result);
	
    printf("%s - %s",query, date(gettime())); 
    return query; 
}

[zPain]

There's no need for 2 queries. You can write a JOIN statement based on the user id.

PHP Code:

SELECT
    tickets.*,
    spieler.name AS username
FROM
    tickets
INNER JOIN
    spieler ON spieler.id = tickets.userid
WHERE
    tickets.status=0 


[StreetGT]

Quote:

Originally Posted by zPain There's no need for 2 queries. You can write a JOIN statemente based on the user id. PHP Code: SELECT     tickets.*,     spieler.name AS username FROM     tickets INNER JOIN     spieler ON spieler.id = tickets.userid WHERE     tickets.status=0

Thats it! Anyway if you want to check about JOIN query's take a look!
http://dev.mysql.com/doc/refman/5.7/en/join.html

[MichiEXE]

Thank you guys, im sure the join query thing is what i need! Again thanks for all the help!

[MichiEXE]

Its me again, im stuck right now again with almost the same problem but for something other than the username.

The join query works, all Usernames are shown and the dialog is working fine! Now we have another table called "tickets_kategorien" for every category a ticket can be. So, the first TAB in the Dialog is now the category-id but
what should I do for getting the kategorie.name field content from this third table? I'm not sure if I just have to do the same thing twice in the query, or do something else, and i also don't now how the syntax should be for this second INNER JOIN thing..

Would really appreciate it when someone can help me again, im so close now