MySQL Not grabbing everything for my hack?

[Ryan Ashbrook]

This is why I hate working with MySQL.

I'm translating a hack from wBB 1.2 to vB 3.0.3 so that I can use it on my forums.

Problem is, I think I'm using the right code, but the SQL query is only grabbing one of the objects in the table, and not all of them.

$thread_sword = $DB_site->query_first("SELECT sword FROM user WHERE userid='$bbuserinfo[userid]'");
$result = $DB_site->query("SELECT id, swordname, money FROM swords ORDER BY id ASC");
while ($item = $DB_site->fetch_array($result))
{
$sword = $item[swordname];
$money = $item[money];
$id = $item[id];
}

That is the code I'm using, and when I try to get the 44 objects from the table it only shows the one with an ID of 44, instead of all of the objects.

[Andreas]

Well, this code is moreless the same as:


$thread_sword = $DB_site->query_first("SELECT sword FROM user WHERE userid='$bbuserinfo[userid]'");
$result = $DB_site->query("SELECT id, swordname, money FROM swords ORDER BY id ASC");
while ($item = $DB_site->fetch_array($result))
{
// Do nothing
}

;)


Furthermore, your code does not support table prefixes:

$thread_sword = $DB_site->query_first("SELECT sword FROM " . TABLE_PREFIX . "user WHERE userid='$bbuserinfo[userid]'");
$result = $DB_site->query("SELECT id, swordname, money FROM " . TABLE_PREFIX . "swords ORDER BY id ASC");
while ($item = $DB_site->fetch_array($result))
{
$sword = $item[swordname];
$money = $item[money];
$id = $item[id];
}
]

[Ryan Ashbrook]

I know it doesn't support prefixes, as it's custom to my forums and we don't have a prefix. ;)

[Brad]

First off why are you running this query?

$thread_sword = $DB_site->query_first("SELECT sword FROM " . TABLE_PREFIX . "user WHERE userid='$bbuserinfo[userid]'");

This will work just as well and avoids the query:

$thread_sword = $bbuserinfo['sword'];

[Ryan Ashbrook]

Thanks Brad, I've changed things around now to this.

// What Sword User has Now
$thread_sword = $bbuserinfo['sword'];
// All Items
$item_results = $DB_site->query_first("
SELECT id, swordname, money FROM swords
ORDER BY id DESC
");
while ($item = $DB_site->fetch_array($item_results))
$item['swordname'] = $item_results[swordname];
$item['money'] = intval ($item_results[money]);
$item['id'] = intval ($item_results[id]);
}

And now only the cost of the last item shows up. Nothing else.

[Andreas]

$item_results = $DB_site->query_first("
SELECT id, swordname, money FROM swords
ORDER BY id DESC
");
while ($item = $DB_site->fetch_array($item_results))
$item['swordname'] = $item_results[swordname];
$item['money'] = intval ($item_results[money]);
$item['id'] = intval ($item_results[id]);
}

What the hell are you doing here?
$item_results is an associative array (with keys id, swordname and money). You can't use this as a parameter for fetch_array(), which expects a mySQL result resource (eg. a handle which basically is an integer).

I can only guess that what you want to do is smth. like tis:

// What Sword User has Now
$thread_sword = $bbuserinfo['sword'];
// All Items
$item_results = $DB_site->query("SELECT id, swordname, money
FROM " . TABLE_PREFIX . "swords
ORDER BY id DESC
");
while ($item = $DB_site->fetch_array($item_results)) {
echo "Fetched row is id: $item[id], swordname: $item[swordname], money: $item[money]<br />";
}

[Ryan Ashbrook]

Yes, that is what I want. But it doesn't work when I replace the echo with a template. I still only get one item.

[Colin F]

be sure to fetch the template like this:
eval('$template .= "' . fetch_template('item') . '";');

with a .=

if you forget the . it just overwrites the variable.

[Ryan Ashbrook]

... a dot...

I cannot believe I spent three whole days working on this and all I needed to add was a dot...

Thanks. I'll go hit my head on something now.

* Ryan Ashbrook bangs head on desk...

That's for the help. :p

[Colin F]

no problem...

oh and... write it down for next time ;)

[Ryan Ashbrook]

Indeed, but alas I need help, yet again.

I warn you, I'm not the best PHP coder in the world, so I can take ages on the simplest things.

I'm getting a parse error for when someone attempts to purchase the item.

And when I fix the parse error, they can purchase but nothing gets updated.

// ############################### start buy weapon ###############################
if ($_REQUEST['do'] == 'buy')
{
$buyweapon = $DB_site->query_first("
SELECT swordname, money
FROM swords
WHERE id='$nr'
");
$buymoney = ($bbuserinfo[user_money] - $buyweapon[money]);
if($bbuserinfo[user_money] >= $buyweapon[money]);
{
$DB_site->query("
UPDATE user SET sword = '$buyweapon[swordname]', user_money = '$buymoney'
WHERE userid = '$bbuserinfo[userid]'
");
$_REQUEST['forceredirect'] = 1;
$url = 'main_shop.php?';
eval(print_standard_redirect('redirect_itemthanks' ));
}
if($buyweapon[swordname] == $bbuserinfo[sword])
{
$_REQUEST['forceredirect'] = 1;
$url = 'main_shop.php?';
eval(print_standard_redirect('redirect_haveitem')) ;
}
if($bbuserinfo[user_money] < $buyweapon[money]);
{
$_REQUEST['forceredirect'] = 1;
$url = 'main_shop.php?';
eval(print_standard_redirect('redirect_notenoughgi l'));
}
}

I'm about to pull my hair out here... because it's really pissing me off. >_<