Database code question

[The Wizard]

Hello:

I am still learning about php database code, and I could use some help in getting the pagination code I am using to work. I have a column called - wizard and below is the code I use to read from it.

Code Select Expand


$result = $smcFunc['db_query']('', "
SELECT wizard
FROM {db_prefix}members
WHERE id_member = {int:current_member}
LIMIT 1", array(
'current_member' => $user_info['id']
));



Below is the pagination code I have been using for other sections of my mod.

Code Select Expand


// Get the number of items available
$result = $smcFunc['db_query']('', "
SELECT COUNT(id)
FROM {db_prefix}shop_inventory
WHERE ownerid = {int:id}", array(
'id' => $user_info['id']
));
list($context['shop_inv']['item_count']) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);

// If the page is not set, assume page 1
if (!isset($_GET['page']))
$_GET['page'] = 1;
// If items per page not set, assume 15
if (!isset($modSettings['shopItemsPerPage']))
$modSettings['shopItemsPerPage'] = 15;

// The page count - Total number of items divided by number per page
$context['shop_inv']['pages']['total'] = ceil($context['shop_inv']['item_count'] / $modSettings['shopItemsPerPage']);
// First item
$firstItem = ($_GET['page'] * $modSettings['shopItemsPerPage']) - $modSettings['shopItemsPerPage'];

// Start with empty items array
$context['shop_inv']['items'] = array();

// Get all the inventory items on current page
$result = $smcFunc['db_query']('', "
SELECT it.name, it.desc, it.input_needed, it.can_use_item, it.image,
inv.amtpaid, inv.id, inv.trading, inv.tradecost
FROM {db_prefix}shop_inventory AS inv, {db_prefix}shop_items AS it
WHERE inv.ownerid = {int:ownerid} AND inv.itemid = it.id
ORDER BY it.can_use_item DESC, it.name ASC, inv.id ASC
LIMIT {int:firstitem}, {int:itemsperpage}", array(
'ownerid' => $user_info['id'],
'firstitem' => $firstItem,
'itemsperpage' => $modSettings['shopItemsPerPage']
));

// Loop through results
while ($row = $smcFunc['db_fetch_assoc']($result))
$context['shop_inv']['items'][] = array(
'id' => $row['id'],
'image' => $row['image'],
'name' => $row['name'],
'desc' => $row['desc'],
'amtpaid' => $row['amtpaid'],
'can_use_item' => $row['can_use_item'],
'input_needed' => $row['input_needed'],
'trading' => $row['trading'],
'tradecost' => $row['tradecost']
);
$smcFunc['db_free_result']($result);

// Set some miscellaneous variables
$context['shop_inv']['pages']['current'] = $_GET['page'];
$context['shop_inv']['pages']['link'] = $scripturl . '?action=shop_senditems;do=inv';


Now here is my question how do I get the pagination code to work with the wizard column?

I was thinking of trying this but I'm not sure -

Code Select Expand


// Get the number of items available
$result = $smcFunc['db_query']('', "
SELECT wizard
FROM {db_prefix}members
WHERE ownerid = {int:id}", array(
'id' => $user_info['id']
));


thanks for any help in advance.

Wiz

[margarett]

If you are reading from members table, you will always get just one record, because that table has only 1 row for each member...
Or did I get it wrong?

[The Wizard]

Hello:

In the wizard column the data looks like this - doctor01, doctor02, ect for each user. The user can add or subtract data to his or her table.

Wiz

[margarett]

I *think* I see what you mean now.

So, this "wizard" column is a text field with an imploded array? If so, you should use FIND_IN_SET:
Eg: http://stackoverflow.com/questions/10769641/php-select-a-value-from-an-imploded-array-in-mysql

[emanuele]

Something like that if I understood correctly:

Code Select Expand

// Get the number of items available
$result = $smcFunc['db_query']('', "
SELECT wizard
FROM {db_prefix}members
WHERE ownerid = {int:id}", array(
'id' => $user_info['id']
));

list($wizards) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);
$wizards_array = explode(',', $wizards);

$context['shop_inv']['item_count'] = count($wizards_array);


[The Wizard]

I pluged in what emanuele suggested and recived this error -

Unknown column 'ownerid' in 'where clause'
File: /home/content/05/9199905/html/SMF-Mod-Test-Area/Sources/Subs-Wizards_Shop_User.php
Line: 823

Below is the code I used. Anybody have a idea on how to make this work?

Code Select Expand


// Get the number of items available
$result = $smcFunc['db_query']('', "
SELECT wizard
FROM {db_prefix}members
WHERE ownerid = {int:id}", array(
'id' => $user_info['id']
));

list($wizards) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);
$wizards_array = explode(',', $wizards);

$context['shop_inv']['item_count'] = count($wizards_array);

// If the page is not set, assume page 1
if (!isset($_GET['page']))
$_GET['page'] = 1;
// If items per page not set, assume 15
if (!isset($modSettings['shopItemsPerPage']))
$modSettings['shopItemsPerPage'] = 15;

// The page count - Total number of items divided by number per page
$context['shop_inv']['pages']['total'] = ceil($context['shop_inv']['item_count'] / $modSettings['shopItemsPerPage']);
// First item
$firstItem = ($_GET['page'] * $modSettings['shopItemsPerPage']) - $modSettings['shopItemsPerPage'];

// Start with empty items array
$context['shop_inv']['items'] = array();

// Get all the inventory items on current page
$result = $smcFunc['db_query']('', "
SELECT it.name, it.desc, it.input_needed, it.can_use_item, it.image,
inv.amtpaid, inv.id, inv.trading, inv.tradecost
FROM {db_prefix}shop_inventory AS inv, {db_prefix}shop_items AS it
WHERE inv.ownerid = {int:ownerid} AND inv.itemid = it.id
ORDER BY it.can_use_item DESC, it.name ASC, inv.id ASC
LIMIT {int:firstitem}, {int:itemsperpage}", array(
'ownerid' => $user_info['id'],
'firstitem' => $firstItem,
'itemsperpage' => $modSettings['shopItemsPerPage']
));

// Loop through results
while ($row = $smcFunc['db_fetch_assoc']($result))
$context['shop_inv']['items'][] = array(
'id' => $row['id'],
'image' => $row['image'],
'name' => $row['name'],
'desc' => $row['desc'],
'amtpaid' => $row['amtpaid'],
'can_use_item' => $row['can_use_item'],
'input_needed' => $row['input_needed'],
'trading' => $row['trading'],
'tradecost' => $row['tradecost']
);
$smcFunc['db_free_result']($result);

// Set some miscellaneous variables
$context['shop_inv']['pages']['current'] = $_GET['page'];
$context['shop_inv']['pages']['link'] = $scripturl . '?action=shop_senditems;do=inv';


[margarett]

Because in members there is no column "owner_id", just your "wizard" one. You copied the example from your "shop_inventory" table, but the fields are, of course, different...

[The Wizard]

Hello:

Below is my latest attempt at creating pagination from the wizard column. All the data shows up and the pagination links as well, but it shows all the items, and not the 12 items per page.
I'm sure the problem is the Limit 1 array but I just don't know what to replace it with?

Code Select Expand


// Get the number of items available
$result = $smcFunc['db_query']('', "
SELECT wizard
FROM {db_prefix}members
WHERE id_member = {int:current_member}
LIMIT 1", array(
'current_member' => $user_info['id']
));

list($wizards) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);
$wizards_array = explode(',', $wizards);

$context['shop_inv']['item_count'] = count($wizards_array);

// If the page is not set, assume page 1
if (!isset($_GET['page']))
$_GET['page'] = 1;
// If items per page not set, assume 15
if (!isset($modSettings['shopItemsPerPage']))
$modSettings['shopItemsPerPage'] = 15;

// The page count - Total number of items divided by number per page
$context['shop_inv']['pages']['total'] = ceil($context['shop_inv']['item_count'] / $modSettings['shopItemsPerPage']);
// First item
$firstItem = ($_GET['page'] * $modSettings['shopItemsPerPage']) - $modSettings['shopItemsPerPage'];

// Start with empty items array
$context['shop_inv']['items'] = array();

// Get all the inventory items on current page
$result = $smcFunc['db_query']('', "
SELECT it.name, it.desc, it.input_needed, it.can_use_item, it.image,
inv.amtpaid, inv.id, inv.trading, inv.tradecost
FROM {db_prefix}shop_inventory AS inv, {db_prefix}shop_items AS it
WHERE inv.ownerid = {int:ownerid} AND inv.itemid = it.id
ORDER BY it.can_use_item DESC, it.name ASC, inv.id ASC
LIMIT {int:firstitem}, {int:itemsperpage}", array(
'ownerid' => $user_info['id'],
'firstitem' => $firstItem,
'itemsperpage' => $modSettings['shopItemsPerPage']
));

// Loop through results
while ($row = $smcFunc['db_fetch_assoc']($result))
$context['shop_inv']['items'][] = array(
'id' => $row['id'],
'image' => $row['image'],
'name' => $row['name'],
'desc' => $row['desc'],
'amtpaid' => $row['amtpaid'],
'can_use_item' => $row['can_use_item'],
'input_needed' => $row['input_needed'],
'trading' => $row['trading'],
'tradecost' => $row['tradecost']
);
$smcFunc['db_free_result']($result);

// Set some miscellaneous variables
$context['shop_inv']['pages']['current'] = $_GET['page'];
$context['shop_inv']['pages']['link'] = $scripturl . '?action=shop_senditems;do=inv';


[margarett]

The first query will only return 1 result, because there is only 1 row for the id_member. But that single result is an array of multiple items.
So you need to take care of limit the next query and not the first one. That will always return 1 result, either you limit it or not (and it would be really bad if it didn't )

edit: you don't use your "$wizards_array" variable in the second query.

If I'm looking correctly at this, you want to show the "wizards" of the current user, with a limit of "xx" per page, is it right?

[The Wizard]

If I'm looking correctly at this, you want to show the "wizard" of the current user, with a limit of "xx" per page, is it right?

correct

[margarett]

You should add the array you fetched in the WHERE condition. And I *think* you should be JOINing your tables (not sure here)

, but it shows all the items, and not the 12 items per page.

Does it show all user items or all items in the "shop_items" table?