Vain teksti | Teksti ja kuvat

Simple Machines Community Forum

Customizing SMF => SMF Coding Discussion => Aiheen aloitti: The Wizard - helmikuu 07, 2014, 07:00:18 AP

Otsikko: Database / select box issue
Kirjoitti: The Wizard - helmikuu 07, 2014, 07:00:18 AP
Hello:

I am trying to add a select box to be able to sort the selected data. Except I am getting the following error -

LainaaThe database value you're trying to insert does not exist: clause

Below is the database code I am using.

Does anybody know what i did wrong?

Wiz

Koodi [Valitse]

// Get all the toys in the toy show
$result = $smcFunc['db_query']('', "
SELECT it.name, it.desc, it.image, inv.id, m.real_name, inv.tradecost, inv.category
FROM {db_prefix}shop_inventory AS inv
INNER JOIN {db_prefix}shop_items AS it ON inv.itemid = it.id
INNER JOIN {db_prefix}members AS m ON m.id_member = inv.ownerid
WHERE inv.trading = 1 AND inv.itemid = it.id
AND m.id_member = inv.ownerid
{raw:clause}
ORDER BY {raw:sort} {raw:sortdir}", array());
// Loop through all items
while ($row = $smcFunc['db_fetch_assoc']($result))
$context['toy_shop_trade_items'][] = array(
'id' => $row['id'],
'name' => $row['name'],
'desc' => $row['desc'],
'image' => $row['image'],
'realName' => $row['real_name'],
'tradecost' => $row['tradecost'],
'clause' => $catClause,
'sort' => $sortQuery,
'sortdir' => $sortDirQuery
);
$smcFunc['db_free_result']($result);
Otsikko: Re: Database / select box issue
Kirjoitti: margarett - helmikuu 07, 2014, 07:32:55 AP
This:
Koodi [Valitse]

{raw:clause}
ORDER BY {raw:sort} {raw:sortdir}", array());

Needs an array of parameters to be passed. (the last empty array you are giving needs to have the variables used in brackets)
Otsikko: Re: Database / select box issue
Kirjoitti: The Wizard - helmikuu 07, 2014, 08:10:59 AP
I changed the code to -

Koodi [Valitse]

// Get all the toys in the toy show
$result = $smcFunc['db_query']('', "
SELECT it.name, it.desc, it.image, inv.id, m.real_name, inv.tradecost, inv.category
FROM {db_prefix}shop_inventory AS inv
INNER JOIN {db_prefix}shop_items AS it ON inv.itemid = it.id
INNER JOIN {db_prefix}members AS m ON m.id_member = inv.ownerid
WHERE inv.trading = 1 AND inv.itemid = it.id
AND m.id_member = inv.ownerid
ORDER BY {raw:clause}, {raw:sort}, {raw:sortdir}", array(
'clause' => $catClause,
'sort' => $sortQuery,
'sortdir' => $sortDirQuery
));

// Loop through all items
while ($row = $smcFunc['db_fetch_assoc']($result))
$context['toy_shop_trade_items'][] = array(
'id' => $row['id'],
'name' => $row['name'],
'desc' => $row['desc'],
'image' => $row['image'],
'realName' => $row['real_name'],
'tradecost' => $row['tradecost'],
);
$smcFunc['db_free_result']($result);


Now I get this error -

LainaaYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE category = 500, name, ASC' at line 7

So where am I going wrong?

Wiz

I have included more of the code to help.

Koodi [Valitse]

// Get an array of all the categories
function getCatList()
{
global $smcFunc;

// Start with an empty array
$cats = array();

// Get all the categories
$result = $smcFunc['db_query']('', "
SELECT id, name, count
FROM {db_prefix}shop_categories
ORDER BY name ASC", array());

// Loop through all the categories
while ($row = $smcFunc['db_fetch_assoc']($result))
{
// Let's add this to our array
$cats[] = array(
'id' => $row['id'],
'name' => $row['name'],
'count' => $row['count']
);
}

$smcFunc['db_free_result']($result);

// Return the array
return $cats;
}

// If the sort method isn't defined, assume a default
if (!isset($_GET['sort']))
$_GET['sort'] = '0';
if (!isset($_GET['sortDir']))
$_GET['sortDir'] = '0';

// Get the sort type
switch ($_GET['sort'])
{
case 0:
$context['toy_shop_inv']['sort_type'] = $txt['toy_shop_name'];
$sortQuery = 'name';
break;
case 1:
$context['toy_shop_inv']['sort_type'] = $txt['toy_shop_price'];
$sortQuery = 'price';
break;
case 2:
$context['toy_shop_inv']['sort_type'] = $txt['toy_shop_description'];
$sortQuery = '`desc`';
break;
case 3:
$context['toy_shop_inv']['sort_type'] = $txt['toy_shop_stock'];
$sortQuery = 'stock';
break;
default:
fatal_error($txt['toy_shop_invalid_sort']);
break;
}

// And the direction
switch ($_GET['sortDir'])
{
case 0:
$context['toy_shop_inv']['sort_dir'] = $txt['toy_shop_asc'];
$sortDirQuery = 'ASC';
break;
case 1:
$context['toy_shop_inv']['sort_dir'] = $txt['toy_shop_desc'];
$sortDirQuery = 'DESC';
break;
default:
fatal_error($txt['toy_shop_invalid_sort_direction']);
break;
}

// Are we only displaying a certain category?
if (isset($_GET['cat']) && $_GET['cat'] != -1)
{
$context['toy_shop_inv']['category'] = (int) $_GET['cat'];
$catClause = 'WHERE category = ' . $context['toy_shop_inv']['category'];
}
else
{
$context['toy_shop_inv']['category'] = -1;
$catClause = '';
}

// List of all categories
$context['toy_shop_inv']['categories'] = getCatList();

// Get the number of items available


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

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

// Visiting the Toy Show

$context['toy_shop_trade_items'] = array();
$alternating = 'windowbg2';

// Get all the toys in the toy show
$result = $smcFunc['db_query']('', "
SELECT it.name, it.desc, it.image, inv.id, m.real_name, inv.tradecost, inv.category
FROM {db_prefix}shop_inventory AS inv
INNER JOIN {db_prefix}shop_items AS it ON inv.itemid = it.id
INNER JOIN {db_prefix}members AS m ON m.id_member = inv.ownerid
WHERE inv.trading = 1 AND inv.itemid = it.id
AND m.id_member = inv.ownerid
ORDER BY {raw:clause}, {raw:sort}, {raw:sortdir}", array(
'clause' => $catClause,
'sort' => $sortQuery,
'sortdir' => $sortDirQuery
));

// Loop through all items
while ($row = $smcFunc['db_fetch_assoc']($result))
$context['toy_shop_trade_items'][] = array(
'id' => $row['id'],
'name' => $row['name'],
'desc' => $row['desc'],
'image' => $row['image'],
'realName' => $row['real_name'],
'tradecost' => $row['tradecost'],
);
$smcFunc['db_free_result']($result);
Otsikko: Re: Database / select box issue
Kirjoitti: margarett - helmikuu 07, 2014, 09:48:20 AP
That's really how you are building the query
Koodi [Valitse]
ORDER BY {raw:clause}, {raw:sort}, {raw:sortdir}", array(
'clause' => $catClause,
'sort' => $sortQuery,
'sortdir' => $sortDirQuery
Look at $catClause above:
Koodi [Valitse]
$catClause = 'WHERE category = ' . $context['toy_shop_inv']['category'];

ORDER and WHERE in the same line can't coexist ;)
Otsikko: Re: Database / select box issue
Kirjoitti: The Wizard - helmikuu 07, 2014, 11:09:53 AP
Below is my latest attempt and the error I'm getting -

LainaaYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE category = 501
ORDER BY name ASC' at line 7

Koodi [Valitse]

// Get all the toys in the toy show
$result = $smcFunc['db_query']('', "
SELECT it.name, it.desc, it.image, inv.id, m.real_name, inv.tradecost
FROM {db_prefix}shop_inventory AS inv
INNER JOIN {db_prefix}shop_items AS it ON inv.itemid = it.id
INNER JOIN {db_prefix}members AS m ON m.id_member = inv.ownerid
WHERE inv.trading = 1 AND inv.itemid = it.id
AND m.id_member = inv.ownerid
{raw:clause}
ORDER BY {raw:sort} {raw:sortdir}", array(
'clause' => $catClause,
'sort' => $sortQuery,
'sortdir' => $sortDirQuery
));


Can someone put me out of my misery and give me the correct code   :'(

Wiz
Otsikko: Re: Database / select box issue
Kirjoitti: margarett - helmikuu 07, 2014, 12:23:35 IP
The correct code actually depends on what you want to do :P

I *think* that you need to:
Koodi [Valitse]
$catClause = 'AND category = ' . $context['toy_shop_inv']['category'];
Otsikko: Re: Database / select box issue
Kirjoitti: The Wizard - helmikuu 07, 2014, 02:42:47 IP
Lainaa$catClause = 'AND category = ' . $context['toy_shop_inv']['category'];

tried this. I receive the following error -

LainaaColumn 'category' in where clause is ambiguous

any ideas?

wiz
Otsikko: Re: Database / select box issue
Kirjoitti: The Wizard - helmikuu 07, 2014, 03:47:55 IP
Ok I found the answer to this error -
LainaaColumn 'category' in where clause is ambiguous

Answer: - Its just like the error is saying that your field 'category' in where clause is ambiguous, this means that it doesnt know wich field you are using because the same field is in both tables, so you need to tell to mysql witch one u want.

Knowing what the issue is can someone figure out how to write the code? I'm out of depth here.

Wiz
Otsikko: Re: Database / select box issue
Kirjoitti: margarett - helmikuu 07, 2014, 10:03:42 IP
Yeah, probably more than one of those tables have a category column. So you need to specify in that variable.
inv.category?
m.category?

...
Otsikko: Re: Database / select box issue
Kirjoitti: The Wizard - helmikuu 08, 2014, 12:09:50 AP
Hello:

It took awhile, but I finally got it to work. Below is the correct code.

Thanks margarett for all the help.

Wiz

Koodi [Valitse]

// Get an array of all the categories
function getCatList()
{
global $smcFunc;

// Start with an empty array
$cats = array();

// Get all the categories
$result = $smcFunc['db_query']('', "
SELECT id, name, count
FROM {db_prefix}shop_categories
ORDER BY name ASC", array());

// Loop through all the categories
while ($row = $smcFunc['db_fetch_assoc']($result))
{
// Let's add this to our array
$cats[] = array(
'id' => $row['id'],
'name' => $row['name'],
'count' => $row['count']
);
}

$smcFunc['db_free_result']($result);

// Return the array
return $cats;
}

// If the sort method isn't defined, assume a default
if (!isset($_GET['sort']))
$_GET['sort'] = '0';
if (!isset($_GET['sortDir']))
$_GET['sortDir'] = '0';

// Get the sort type
switch ($_GET['sort'])
{
case 0:
$context['toy_shop_inv']['sort_type'] = $txt['toy_shop_name'];
$sortQuery = 'name';
break;
case 1:
$context['toy_shop_inv']['sort_type'] = $txt['toy_shop_price'];
$sortQuery = 'price';
break;
case 2:
$context['toy_shop_inv']['sort_type'] = $txt['toy_shop_description'];
$sortQuery = '`desc`';
break;
case 3:
$context['toy_shop_inv']['sort_type'] = $txt['toy_shop_stock'];
$sortQuery = 'stock';
break;
default:
fatal_error($txt['toy_shop_invalid_sort']);
break;
}

// Are we only displaying a certain category?
if (isset($_GET['cat']) && $_GET['cat'] != -1)
{
$context['toy_shop_inv']['category'] = (int) $_GET['cat'];
$catClause = 'AND category =' . $context['toy_shop_inv']['category'];
}
else
{
$context['toy_shop_inv']['category'] = -1;
$catClause = '';
}

// List of all categories
$context['toy_shop_inv']['categories'] = getCatList();

// 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['toy_shop_inv']['item_count']) = $smcFunc['db_fetch_row']($result);

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

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

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

// Get information on the item
$result = $smcFunc['db_query']('', "
SELECT inv.image, inv.name, inv.tradecost, inv.id, inv.category, m.real_name
FROM {db_prefix}shop_inventory AS inv
INNER JOIN {db_prefix}members AS m ON m.id_member = inv.ownerid
WHERE inv.trading = 1
AND m.id_member = inv.ownerid
{raw:clause}
ORDER BY {raw:sort} ASC
LIMIT {int:firstitem}, {int:itemsperpage}", array(
'clause' => $catClause,
'sort' => $sortQuery,
'firstitem' => $firstItem,
'itemsperpage' => $modSettings['shopItemsPerPage']
));

// Loop through results
while ($row = $smcFunc['db_fetch_assoc']($result))
$context['toy_shop_trade_items'][] = array(
'id' => $row['id'],
'name' => $row['name'],
'desc' => $row['desc'],
'image' => $row['image'],
'realName' => $row['real_name'],
'tradecost' => $row['tradecost']
);
$smcFunc['db_free_result']($result);
Vain teksti | Teksti ja kuvat