MySQL retrieve data from column?

MySQL retrieve data from column? - 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: MySQL retrieve data from column? (/showthread.php?tid=356833)

MySQL retrieve data from column? - DBan - 05.07.2012

Is there anyways to retrieve the data from a whole column and somehow store it into a variable and display it in a dialog?

Here's what I have:

pawn Код:

CMD:watchlist(playerid, params[])
{
    new result[24]; 
    mysql_query("SELECT * FROM `watchlist`"); // select all from watchlist
    mysql_store_result(); // store the result
    if(mysql_retrieve_row())
    {
        mysql_fetch_field_row(result, "player"); 
        printf("Result: %s", result); // Attempting to display the result (which only displays the first row)
        mysql_fetch_field_row(result, "player");
        printf("Result: %s", result); // Has the same result as above (same name shows)
    }
    mysql_free_result();
    return 1;
}

This command basically reads the table 'watchlist' from MySQL, and im hoping to display it in a dialog, but just can't find the right method.

I've thought of just making new columns (up to 5) instead of storing all the names in 1 column but that would make it so inefficient and requires alot more code.

Re: MySQL retrieve data from column? - iGetty - 05.07.2012

Something along these lines:

pawn Код:

mysql_query("SELECT Username, Fines FROM `Accounts` WHERE `Username` = '%s'", NameEntered);
mysql_store_result( );
if( mysql_num_rows( ) )
{
    new Field[ 10 ];
    mysql_fetch_row_format( query, "|" );
    mysql_fetch_field_row( Field, "Fines" );
    
    format(string, sizeof(string), "Fines: %d", strval( Field ) );
    ShowPlayerDialog(playerid, 14214, DIALOG_STYLE_MSGBOX, "MDC Beta", string, "OK", "");
}
mysql_free_result( );

Re: MySQL retrieve data from column? - DBan - 05.07.2012

Quote:

Originally Posted by iGetty

Something along these lines:

pawn Код:

mysql_query("SELECT Username, Fines FROM `Accounts` WHERE `Username` = '%s'", NameEntered);
mysql_store_result( );
if( mysql_num_rows( ) )
{
    new Field[ 10 ];
    mysql_fetch_row_format( query, "|" );
    mysql_fetch_field_row( Field, "Fines" );
    
    format(string, sizeof(string), "Fines: %d", strval( Field ) );
    ShowPlayerDialog(playerid, 14214, DIALOG_STYLE_MSGBOX, "MDC Beta", string, "OK", "");
}
mysql_free_result( );

Hey Callum

Oh, I don't think you understood my dilemma. In your code, your query selects 1 row, then you store it and display it. What my problem is, is that I need all the rows from a whole column to display. I just can't find the right method to do just that.

Thanks for your help anyways

Re: MySQL retrieve data from column? - Sinner - 05.07.2012

use:

pawn Код:

while(mysql_retrieve_row())

Re: MySQL retrieve data from column? - ReneG - 05.07.2012

Don't use a wildcard(*) when you want to select a whole collumn of data. Wildcard selects every collumn, i.e waste of memory.

Код:

SELECT collumn_name FROM `watchlist`

Replace "collumn_name" with whatever collumn you're trying to return, and that query will return every thing in the specified collumn.

To access it, here is an example.

pawn Код:

CMD:watchlist(playerid, params[])
{
    mysql_query("SELECT * FROM `watchlist`"); // select all from watchlist
    mysql_store_result(); // store the result
    
    new 
        result[24];

    while(mysql_retrieve_row())
    {
        mysql_fetch_field_row(result, "player");  
        printf("Result: %s", result); // Attempting to display the result (which only displays the first row)
    }
    mysql_free_result();
    return 1;
}

Since result is only one string, you'll need to store the results into a 2d array.

pawn Код:

CMD:watchlist(playerid, params[])
{
    mysql_query("SELECT * FROM `watchlist`"); // select all from watchlist
    mysql_store_result(); // store the result
    
    new 
        result[mysql_num_rows()][24],
        i=0;
    
    while(mysql_retrieve_row())
    {
        mysql_fetch_field_row(result[i], "player");  
        printf("Result: %s", result); // Attempting to display the result (which only displays the first row)
        i++;
    }
    mysql_free_result();
    return 1;
}