Custom database table issue with \IPS\DB::i()

[AuntyNem]

Hi, we're trying to update a custom bank/shop app from 3.4.7 to work with 4.1.18 after we upgrade and are having a hard time working out how to connect to the database - especially pre-existing tables created for the 3.4.7 app.

Current example, using this query:

$userMoney = iterator_to_array(\IPS\Db::i()->select('*','ssb_bankacc'));

it only displays '1' with print_r() which, to my understanding, means the array is empty. But the table is not empty.  

But if I replace 'ssb_bankacc' with 'core_groups', I do get back data from the table that displays with print_r()

Has anyone had issues with added database tables when using the \IPS\Db::i() functionality? (I have not loaded any new database, only accessing whatever is attached to that.)  What have I got wrong?

[inkredible]

7 hours ago, AuntyNem said:

Hi, we're trying to update a custom bank/shop app from 3.4.7 to work with 4.1.18 after we upgrade and are having a hard time working out how to connect to the database - especially pre-existing tables created for the 3.4.7 app.

Current example, using this query:

$userMoney = iterator_to_array(\IPS\Db::i()->select('*','ssb_bankacc'));

it only displays '1' with print_r() which, to my understanding, means the array is empty. But the table is not empty.  

But if I replace 'ssb_bankacc' with 'core_groups', I do get back data from the table that displays with print_r()

Has anyone had issues with added database tables when using the \IPS\Db::i() functionality? (I have not loaded any new database, only accessing whatever is attached to that.)  What have I got wrong?

I have already tested it with "custom tables" and I had no issues. I don't see an issue with your provided code either. The problem may be something else. You could try a couple things like accessing the first() row. You may want to check this thread: 

 

[Adriano Faria]

7 hours ago, AuntyNem said:

it only displays '1' with print_r() which, to my understanding, means the array is empty. But the table is not empty.  

You need to use it in a FORCEACH.

$userMoney = iterator_to_array(\IPS\Db::i()->select('*','ssb_bankacc'));
foreach( $userMoney as $money )
{
	//do somethimg;
}

 

[AuntyNem]

Thank you both so much for your help! I had seen that topic and it helped us a lot. Using foreach helped, but still having some issues.

After using foreach, I can confirm that there are multiple row entries that are being returned in the array. However, when I try to index them using

foreach($userMoney as $money) {
$this->output .= "Cash= ".$money['cash']."; Deposit= ".$money['deposit']."/n";
}

where cash and deposit are column titles, no values are returned in those places, though the 'Cash=' and 'Deposit=' appear multiple times. When changing the initial query to get rid of the need for an array and just make sure we're getting some sort of usable value:

$mcash = \IPS\Db::i()->select('cash','ssb_banklogs')->first();

an exception is thrown for IPS\Db "Unknown column 'cash' in 'field list'" even though we have confirmed that cash is one of the column titles in the table.

Any advice?

[Adriano Faria]

$this->output

What are you tryin to do ? Create the result as an array?

[AuntyNem]

Hi Adriano, I'm not sure how to answer, but not expecting an array.

$this->output 

is just how I add up the strings for the final output.  As a global string used across the functions to compile the final output that is then handed to \IPS\Output::i()->output = $this->output;

I hope that makes sense.

 I'm most concerned that the second bit of code says the column is unknown, when it exists in the table.

[AuntyNem]

That is strange, isn't it? We keep hitting a wall in trying to pull data from these tables. I'm at a loss.

The tables didn't originally have prefixes. tst_ssb_bankacc was originally just bankacc, but we added the prefix to work with IPS\db (because it  seemed to be pulling the tst_ prefix from config_global).  Could that possibly have anything to do with it? *grasping at straws*

[Daniel F]

29 minutes ago, AuntyNem said:

That is strange, isn't it? We keep hitting a wall in trying to pull data from these tables. I'm at a loss.

The tables didn't originally have prefixes. tst_ssb_bankacc was originally just bankacc, but we added the prefix to work with IPS\db (because it  seemed to be pulling the tst_ prefix from config_global).  Could that possibly have anything to do with it? *grasping at straws*

No, it's not strange;)
If you're using the original DB class instance from IPS4 to fetch data from custom database tables, we're expecting that the prefix is set.
Take a look at IPS\Db::select()
 

else
		{
			$query .= $this->prefix ? " FROM `{$this->prefix}{$table}` AS `{$table}`" : " FROM `{$table}`";
		}

You could set Db::i()->prefix to '' , run your query and then reset Db->prefix OR create a second db connection which would work only with your database tables without the prefix (probably the cleanest solution)

[AuntyNem]

Hi, Daniel. Thanks.

The prefix isn't the strange thing though. I was just throwing that out there in a 'hail mary I have no idea what's going wrong' kind of way. 

The strange thing is why the second bit of code posted below would result in "Unknown column 'cash' in 'field list'" when that column is definitely in the table.

Any ideas what might be going wrong? Or what we might try to help figure it out?

 

On 3/25/2017 at 10:50 AM, AuntyNem said:

After using foreach, I can confirm that there are multiple row entries that are being returned in the array. However, when I try to index them using

foreach($userMoney as $money) {
$this->output .= "Cash= ".$money['cash']."; Deposit= ".$money['deposit']."/n";
}

where cash and deposit are column titles, no values are returned in those places, though the 'Cash=' and 'Deposit=' appear multiple times. When changing the initial query to get rid of the need for an array and just make sure we're getting some sort of usable value:

$mcash = \IPS\Db::i()->select('cash','ssb_banklogs')->first();

an exception is thrown for IPS\Db "Unknown column 'cash' in 'field list'" even though we have confirmed that cash is one of the column titles in the table.

Any advice?

 

 

[Martin A.]

Can you post the table structure?

[AuntyNem]

Sure. Just this?

+---------+---------+------+-----+---------+----------------+

| Field   | Type    | Null | Key | Default | Extra          |

+---------+---------+------+-----+---------+----------------+

| acc_id  | int(11) | NO   | PRI | NULL    | auto_increment |

| mem_id  | int(11) | NO   |     | 0       |                |

| mgroup  | int(11) | NO   |     | 0       |                |

| cash    | int(11) | NO   |     | 0       |                |

| deposit | int(11) | NO   |     | 29580   |                |

+---------+---------+------+-----+---------+----------------+

 

Also, I was just wondering about the utf8 converter that the upgrader ran when going from 3.4.7 to 4.1.18... that wouldn't have touched these tables probably? Could that be an issue? The table is currently myisam, utf8_general_ci

 

[bfarber]

If you are running this:

$mcash = \IPS\Db::i()->select('cash','ssb_banklogs')->first();

And getting an error "Unknown column 'cash' in 'field list'", then this essentially means within the same database as your Community Suite installation there is a table "(prefix)ssb_banklogs" and it does not have a column named 'cash'.

Is the table you are trying to query in the same database or in an external database? If an external database, you need to use a separate instance of \IPS\Db to connect to the external database.

Are you using a table prefix? In conf_global.php is any value set for "sql_prefix"? If yes, do you have both a ssb_banklogs AND a (prefix)ssb_banklogs table by chance, which may be where the confusion is stemming from?

The error is fairly clear, and is a direct MySQL error (not one we've defined). What you can do is this:

$mcash = \IPS\Db::i()->select('cash','ssb_banklogs');
var_dump( $mcash );
exit;

This will output the \IPS\Db object, including the query that was generated. Take that query and go run it in phpmyadmin to see the result, and perhaps this will help you identify where the problem is.

[AuntyNem]

Shoot, I just knew there was probably an error or typo that was keeping that from working and there was. It was pulling from the wrong table. Our error. Thanks for helping me identify that. 

So fixed and now we are able to get a direct value from that query. However, we still have the issue that when we try to get multiple rows of the table and iterate through them, we aren't seeming to get any output. I'm sure it is again something that we are just doing incorrectly coding-wise, but we can't see it. This is what we've done...

$userMoney = iterator_to_array(\IPS\Db::i()->select('*','ssb_banklogs')); foreach( $userMoney as $money ) { $this->output .= "Cash= ".$money['cash']."; Deposit= ".$money['deposit']." "; }

When this outputs, it outputs the 'Cash=' and the 'Deposit=' as expected, but there are no values. There are eight iterations of this, so there seem to be rows. Should we be calling the array values differently? We've tried using var_dump() but when we do, nothing appears at all, no matter what we do it on.

Any idea what are we doing wrong?

[Adriano Faria]

I still don't understand why do you simply don't create an array and use it where you want.

$userMoney = iterator_to_array(\IPS\Db::i()->select( '*', 'core_widgets' ) );
foreach( $userMoney as $money )
{
	$something[] = array( 'money' => $money['key'], 'deposit' => $money['app'] );
}

Results:

Array
(
    [0] => Array
        (
            [money] => activeUsers
            [deposit] => core
        )

    [1] => Array
        (
            [money] => announcements
            [deposit] => core
        )

    [2] => Array
        (
            [money] => stats
            [deposit] => core
        )

    [3] => Array
        (
            [money] => relatedContent
            [deposit] => core
        )

    [4] => Array
        (
            [money] => recentStatusUpdates
            [deposit] => core
        )

    [5] => Array
        (
            [money] => topContributors
            [deposit] => core
        )

    [6] => Array
        (
            [money] => whosOnline
            [deposit] => core
        )

    [7] => Array
        (
            [money] => members
            [deposit] => core
        )

    [8] => Array
        (
            [money] => forumStatistics
            [deposit] => forums
        )

    [9] => Array
        (
            [money] => hotTopics
            [deposit] => forums
        )

    [10] => Array
        (
            [money] => topicFeed
            [deposit] => forums
        )

    [11] => Array
        (
            [money] => postFeed
            [deposit] => forums
        )

    [12] => Array
        (
            [money] => poll
            [deposit] => forums
        )

    [13] => Array
        (
            [money] => blogStatistics
            [deposit] => blog
        )

    [14] => Array
        (
            [money] => entryFeed
            [deposit] => blog
        )

    [15] => Array
        (
            [money] => blogCommentFeed
            [deposit] => blog
        )

    [16] => Array
        (
            [money] => blogs
            [deposit] => blog
        )

    [17] => Array
        (
            [money] => todaysBirthdays
            [deposit] => calendar
        )

    [18] => Array
        (
            [money] => upcomingEvents
            [deposit] => calendar
        )

    [19] => Array
        (
            [money] => recentReviews
            [deposit] => calendar
        )

    [20] => Array
        (
            [money] => chatwhoschatting
            [deposit] => chat
        )

    [21] => Array
        (
            [money] => Database
            [deposit] => cms
        )

    [22] => Array
        (
            [money] => Wysiwyg
            [deposit] => cms
        )

    [23] => Array
        (
            [money] => DatabaseFilters
            [deposit] => cms
        )

    [24] => Array
        (
            [money] => Categories
            [deposit] => cms
        )

    [25] => Array
        (
            [money] => Blocks
            [deposit] => cms
        )

    [26] => Array
        (
            [money] => RecordFeed
            [deposit] => cms
        )

    [27] => Array
        (
            [money] => Rss
            [deposit] => cms
        )

    [28] => Array
        (
            [money] => topSubmitters
            [deposit] => downloads
        )

    [29] => Array
        (
            [money] => downloadStats
            [deposit] => downloads
        )

    [30] => Array
        (
            [money] => topDownloads
            [deposit] => downloads
        )

    [31] => Array
        (
            [money] => fileFeed
            [deposit] => downloads
        )

    [32] => Array
        (
            [money] => downloadsCommentFeed
            [deposit] => downloads
        )

    [33] => Array
        (
            [money] => downloadsReviewFeed
            [deposit] => downloads
        )

    [34] => Array
        (
            [money] => galleryStats
            [deposit] => gallery
        )

    [35] => Array
        (
            [money] => recentComments
            [deposit] => gallery
        )

    [36] => Array
        (
            [money] => imageFeed
            [deposit] => gallery
        )

    [37] => Array
        (
            [money] => recentImageReviews
            [deposit] => gallery
        )

    [38] => Array
        (
            [money] => pendingActions
            [deposit] => nexus
        )

    [39] => Array
        (
            [money] => donations
            [deposit] => nexus
        )

)

 

[AuntyNem]

Thanks, that may come in handy and end up being a way we do it. 

Right now we're just starting to get a handle on how this system works and don't understand why the code we're trying doesn't get any results.  

$userMoney = iterator_to_array(\IPS\Db::i()->select('*','ssb_banklogs')); foreach( $userMoney as $money ) { $this->output .= "Cash= ".$money['cash']."; Deposit= ".$money['deposit']." "; }

Should that not work? Are we indexing something wrong? (Or are you saying an array is the only way to get results with this?)

[newbie LAC]

Hello,

Hire someone who can make debug for you.

[bfarber]

It should have results (assuming there are results in the category).

Try var_dump( $userMoney ); after the iterator_to_array() call to see what you've pulled from the database.

[AuntyNem]

var_dump wasn't showing any results either.

Anyway, we've given up on understanding what's going wrong there and are going ahead with an array (thanks, Adriano).  We're getting somewhere now at least. Phew. 

Thank you so much to everyone for your help!