SA-MP Forums Archive
MySQL - Gives all no value back, but database is filled - 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)
+---- Forum: Help Archive (https://sampforum.blast.hk/forumdisplay.php?fid=89)
+---- Thread: MySQL - Gives all no value back, but database is filled (/showthread.php?tid=277516)


MySQL - Gives all no value back, but database is filled - Jochemd - 18.08.2011

Hello,

Check the MySQL Log:

Код:
[18:38:33]  
[18:38:33] ---------------------------
[18:38:33] MySQL Debugging activated (08/17/11)
[18:38:33] ---------------------------
[18:38:33]  
[18:38:33] >> mysql_connect( )
[18:38:33] CMySQLHandler::CMySQLHandler() - constructor called.
[18:38:33] CMySQLHandler::CMySQLHandler() - Connecting to "184.154.33.53" | DB: "jc" | Username: "jc" ...
[18:38:33] CMySQLHandler::Connect() - Connection was successful.
[18:38:33] CMySQLHandler::Connect() - Auto-Reconnect has been enabled.
[18:38:33] >> mysql_ping( Connection handle: 1 )
[18:38:33] CMySQLHandler::Ping() - Connection is still alive.
[18:38:33] >> mysql_query( Connection handle: 1 )
[18:38:33] CMySQLHandler::Query(SELECT * FROM `HouseInfo` WHERE `ID` = '0') - Successfully executed.
[18:38:33] >> mysql_store_result( Connection handle: 1 )
[18:38:33] CMySQLHandler::StoreResult() - Result was stored.
[18:38:33] >> mysql_num_rows( Connection handle: 1 )
[18:38:33] CMySQLHandler::NumRows() - Returned 1 row(s)
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideAngle") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideAngle") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("InteriorID") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Locked") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Price") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Owner") - 
[18:38:33] >> mysql_query( Connection handle: 1 )
[18:38:33] CMySQLHandler::Query(SELECT * FROM `HouseInfo` WHERE `ID` = '1') - Successfully executed.
[18:38:33] >> mysql_store_result( Connection handle: 1 )
[18:38:33] CMySQLHandler::StoreResult() - Result was stored.
[18:38:33] >> mysql_num_rows( Connection handle: 1 )
[18:38:33] CMySQLHandler::NumRows() - Returned 1 row(s)
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideAngle") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideAngle") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("InteriorID") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Locked") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Price") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Owner") - 
[18:38:33] >> mysql_query( Connection handle: 1 )
As you see it all returns no value. This is from my LoadHouses stock. I show it:

pawn Код:
stock LoadHouses()
{
    new Store[30];
    for(new h = 0; h < MAX_HOUSES; h ++)
    {
        format(QueryString,sizeof(QueryString),"SELECT * FROM `HouseInfo` WHERE `ID` = '%d'",h);
        mysql_query(QueryString);
        mysql_store_result();
        if(mysql_num_rows() != 0)
        {
            mysql_get_field("PickupOutsidePosX",Store); HouseInfo[h][PickupOutsidePosX] = floatstr(Store);
            mysql_get_field("PickupOutsidePosY",Store); HouseInfo[h][PickupOutsidePosY] = floatstr(Store);
            mysql_get_field("PickupOutsidePosZ",Store); HouseInfo[h][PickupOutsidePosZ] = floatstr(Store);
            mysql_get_field("PickupInsidePosX",Store); HouseInfo[h][PickupInsidePosX] = floatstr(Store);
            mysql_get_field("PickupInsidePosY",Store); HouseInfo[h][PickupInsidePosY] = floatstr(Store);
            mysql_get_field("PickupInsidePosZ",Store); HouseInfo[h][PickupInsidePosZ] = floatstr(Store);
            mysql_get_field("SpawnOutsideX",Store); HouseInfo[h][SpawnOutsideX] = floatstr(Store);
            mysql_get_field("SpawnOutsideY",Store); HouseInfo[h][SpawnOutsideY] = floatstr(Store);
            mysql_get_field("SpawnOutsideZ",Store); HouseInfo[h][SpawnOutsideZ] = floatstr(Store);
            mysql_get_field("SpawnOutsideAngle",Store); HouseInfo[h][SpawnOutsideAngle] = floatstr(Store);
            mysql_get_field("SpawnInsideX",Store); HouseInfo[h][SpawnInsideX] = floatstr(Store);
            mysql_get_field("SpawnInsideY",Store); HouseInfo[h][SpawnInsideY] = floatstr(Store);
            mysql_get_field("SpawnInsideZ",Store); HouseInfo[h][SpawnInsideZ] = floatstr(Store);
            mysql_get_field("SpawnInsideAngle",Store); HouseInfo[h][SpawnInsideAngle] = floatstr(Store);
            mysql_get_field("InteriorID",Store); HouseInfo[h][InteriorID] = strval(Store);
            mysql_get_field("Locked",Store); HouseInfo[h][Locked] = strval(Store);
            mysql_get_field("Price",Store); HouseInfo[h][Price] = strval(Store);
            mysql_get_field("Owner",HouseInfo[h][Owner]);
            HouseInfo[h][VirtualWorld] = h;
            HouseInfo[h][Owned] = 1;
            if(!strlen(HouseInfo[h][Owner])) HouseInfo[h][PickupID] = CreateDynamicPickup(1273,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
            else HouseInfo[h][PickupID] = CreateDynamicPickup(1272,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
            printf("House File ID %d has been created",h);
        }
    }
}
I don't know why it all returns no value - it worked before.

Now my LoadHouses() is commented in OnGameModeInit. When I load my houses, the server crashes when someone tries to login when mysql_fetch_field_row is called.

Anyone experienced this problem?

Jochem

Re: MySQL - Gives all no value back, but database is filled - JaTochNietDan - 18.08.2011

That code is just rediculous, I mean how many SQL queries are you executing? Hundreds? Thousnands!?

Why don't you just use one query to gather all of the information you need, here is a quick example:

pawn Код:
mysql_query("SELECT * FROM `HouseInfo`");
mysql_store_result();

while(mysql_fetch_row(result))
{
    // Now this loop will run once for each row, and you can do what you want
}

mysql_free_result();
Doesn't that make more sense? Instead of running hundreds or maybe even thousands of queries, you can run just one query and gather all of the data from that.

Re: MySQL - Gives all no value back, but database is filled - Jochemd - 18.08.2011

The reason I do this, is because I don't want to fetch the line and split it. I make pretty much changes in my design.

Re: MySQL - Gives all no value back, but database is filled - JaTochNietDan - 18.08.2011

Quote:
Originally Posted by Jochemd
Посмотреть сообщение
The reason I do this, is because I don't want to fetch the line and split it. I make pretty much changes in my design.
What you're doing is insanity, it is not how SQL databases are intended to be queried at all!

Re: MySQL - Gives all no value back, but database is filled - Jochemd - 18.08.2011

Quote:
Originally Posted by JaTochNietDan
Посмотреть сообщение
What you're doing is insanity, it is not how SQL databases are intended to be queried at all!
I can believe that, TBH I only load 30 houses. This has worked before on a database in GTA Multi, and now on this database it doesn't. Could it be this database is weaker?

Edit: 'result' would put out the whole row, right? Then I got to split it, which I am trying to evade.

Re: MySQL - Gives all no value back, but database is filled - [HiC]TheKiller - 18.08.2011

Quote:
Originally Posted by Jochemd
Посмотреть сообщение
I can believe that, TBH I only load 30 houses. This has worked before on a database in GTA Multi, and now on this database it doesn't. Could it be this database is weaker?

Edit: 'result' would put out the whole row, right? Then I got to split it, which I am trying to evade.
You can use the mysql_get_field function inside the while statement so that you don't have to split the line.

pawn Код:
mysql_query("SELECT * FROM `HouseInfo`");
mysql_store_result();
new h;
while(mysql_fetch_row(result))
{
    mysql_get_field("PickupOutsidePosX",Store); HouseInfo[h][PickupOutsidePosX] = floatstr(Store);
    mysql_get_field("PickupOutsidePosY",Store); HouseInfo[h][PickupOutsidePosY] = floatstr(Store);
    mysql_get_field("PickupOutsidePosZ",Store); HouseInfo[h][PickupOutsidePosZ] = floatstr(Store);
    mysql_get_field("PickupInsidePosX",Store); HouseInfo[h][PickupInsidePosX] = floatstr(Store);
    mysql_get_field("PickupInsidePosY",Store); HouseInfo[h][PickupInsidePosY] = floatstr(Store);
    mysql_get_field("PickupInsidePosZ",Store); HouseInfo[h][PickupInsidePosZ] = floatstr(Store);
    mysql_get_field("SpawnOutsideX",Store); HouseInfo[h][SpawnOutsideX] = floatstr(Store);
    mysql_get_field("SpawnOutsideY",Store); HouseInfo[h][SpawnOutsideY] = floatstr(Store);
    mysql_get_field("SpawnOutsideZ",Store); HouseInfo[h][SpawnOutsideZ] = floatstr(Store);
    mysql_get_field("SpawnOutsideAngle",Store); HouseInfo[h][SpawnOutsideAngle] = floatstr(Store);
    mysql_get_field("SpawnInsideX",Store); HouseInfo[h][SpawnInsideX] = floatstr(Store);
    mysql_get_field("SpawnInsideY",Store); HouseInfo[h][SpawnInsideY] = floatstr(Store);
    mysql_get_field("SpawnInsideZ",Store); HouseInfo[h][SpawnInsideZ] = floatstr(Store);
    mysql_get_field("SpawnInsideAngle",Store); HouseInfo[h][SpawnInsideAngle] = floatstr(Store);
    mysql_get_field("InteriorID",Store); HouseInfo[h][InteriorID] = strval(Store);
    mysql_get_field("Locked",Store); HouseInfo[h][Locked] = strval(Store);
    mysql_get_field("Price",Store); HouseInfo[h][Price] = strval(Store);
    mysql_get_field("Owner",HouseInfo[h][Owner]);
    HouseInfo[h][VirtualWorld] = h;
    HouseInfo[h][Owned] = 1;
    if(!strlen(HouseInfo[h][Owner])) HouseInfo[h][PickupID] = CreateDynamicPickup(1273,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
    else HouseInfo[h][PickupID] = CreateDynamicPickup(1272,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
    printf("House File ID %d has been created",h);
    h++;
}
mysql_free_result();


Re: MySQL - Gives all no value back, but database is filled - Jochemd - 18.08.2011

That is what I am doing now.

Re: MySQL - Gives all no value back, but database is filled - [HiC]TheKiller - 18.08.2011

Quote:
Originally Posted by Jochemd
Посмотреть сообщение
That is what I am doing now.
You are sending multiple queries in a loop rather than one single query. Also, are you freeing all of your results? In your first code, you didn't even free the result.

Re: MySQL - Gives all no value back, but database is filled - Jochemd - 18.08.2011

Probably not, I'm not long in MySQL yet.

pawn Код:
mysql_query("SELECT * FROM `HouseInfo`");
mysql_store_result();
new h;
while(mysql_fetch_row(result))
{
    mysql_get_field("PickupOutsidePosX",Store); HouseInfo[h][PickupOutsidePosX] = floatstr(Store);
    mysql_get_field("PickupOutsidePosY",Store); HouseInfo[h][PickupOutsidePosY] = floatstr(Store);
    mysql_get_field("PickupOutsidePosZ",Store); HouseInfo[h][PickupOutsidePosZ] = floatstr(Store);
    mysql_get_field("PickupInsidePosX",Store); HouseInfo[h][PickupInsidePosX] = floatstr(Store);
    mysql_get_field("PickupInsidePosY",Store); HouseInfo[h][PickupInsidePosY] = floatstr(Store);
    mysql_get_field("PickupInsidePosZ",Store); HouseInfo[h][PickupInsidePosZ] = floatstr(Store);
    mysql_get_field("SpawnOutsideX",Store); HouseInfo[h][SpawnOutsideX] = floatstr(Store);
    mysql_get_field("SpawnOutsideY",Store); HouseInfo[h][SpawnOutsideY] = floatstr(Store);
    mysql_get_field("SpawnOutsideZ",Store); HouseInfo[h][SpawnOutsideZ] = floatstr(Store);
    mysql_get_field("SpawnOutsideAngle",Store); HouseInfo[h][SpawnOutsideAngle] = floatstr(Store);
    mysql_get_field("SpawnInsideX",Store); HouseInfo[h][SpawnInsideX] = floatstr(Store);
    mysql_get_field("SpawnInsideY",Store); HouseInfo[h][SpawnInsideY] = floatstr(Store);
    mysql_get_field("SpawnInsideZ",Store); HouseInfo[h][SpawnInsideZ] = floatstr(Store);
    mysql_get_field("SpawnInsideAngle",Store); HouseInfo[h][SpawnInsideAngle] = floatstr(Store);
    mysql_get_field("InteriorID",Store); HouseInfo[h][InteriorID] = strval(Store);
    mysql_get_field("Locked",Store); HouseInfo[h][Locked] = strval(Store);
    mysql_get_field("Price",Store); HouseInfo[h][Price] = strval(Store);
    mysql_get_field("Owner",HouseInfo[h][Owner]);
    HouseInfo[h][VirtualWorld] = h;
    HouseInfo[h][Owned] = 1;
    if(!strlen(HouseInfo[h][Owner])) HouseInfo[h][PickupID] = CreateDynamicPickup(1273,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
    else HouseInfo[h][PickupID] = CreateDynamicPickup(1272,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
    printf("House File ID %d has been created",h);
    h++;
}
mysql_free_result();
Wouldn't that do the same...