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.

PHP Code:

$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:

PHP Code:

$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:

PHP Code:

 $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?

PHP Code:

 $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:

PHP Code:

$thread_sword = $bbuserinfo['sword']; 


[Ryan Ashbrook]

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

PHP Code:

    // 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]

PHP Code:

$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:

PHP Code:

// 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.

[high]* Ryan Ashbrook bangs head on desk...
[/high]

That's for the help.

[Colin F]

no problem...

oh and... write it down for next time