SQL resources?

[chaoskreator]

I've read all of the documentation on the dbal at wiki.phpbb.com, a bunch of topics at various support sites, and the MySQL documentation, itself, but I'm still having problems with SQL queries within phpBB3.
For example, I cannot use build_sql_query (and certain other non-SQL related functions) on a setup across sub-domains. If I include the dbal files, it, of course, gives a fatal error that you cannot redeclare the class.
Another problem is on a MOD that I've been working on for a name generator. This issue has to be with my syntax, but I can't spot it.

I don't know. I suck at SQL. Any one have some other resources I could check out?

[Obsidian]

across sub domains? What exactly do you mean by that?

Including the dbal files would probably throw an error because they are already loaded...


Can you turn on debug extra, and give us any errors you get when using dbal::build_sql_query() ?

[chaoskreator]

I'm building my own custom CMS using the phpBB core across different sub-domains (actually set up as subdirectories under the phpbb root). I've tried using the sql_build_query in the sub-domain setups, and it doesn't return anything after being passed through sql_query. The majority of the other dbal functions (fetchrow, sql_query, sql_freeresult, sql_escape) work. It's build_query and fetchrowset that I've run into that do not work.

I set my pages up with the code below, and use my own set of custom templates (though I don't use the template system used by phpBB):

Code: Select all

<?php

// define the info for phpbb
define('IN_PHPBB', true);
define('ROOT_PATH', "/home/thereal6/public_html/");

// thwart hacking attempts
if (!defined('IN_PHPBB') || !defined('ROOT_PATH')) {
    exit();
}

// page setup
$phpEx = substr(strrchr(__FILE__, '.'), 1);
$phpbb_root_path = ROOT_PATH;
include($phpbb_root_path . 'common.' . $phpEx);
include($phpbb_root_path . 'portal/includes/functions.' . $phpEx);

// user information
$user->session_begin();
$auth->acl($user->data);
$user->setup();
?>

I can't give you an example of the output of DEBUG when using build_query at the moment, because I've recoded everything to work temporarily, and I'm too lazy to get into the code, at the moment. I'll redo a snippet and post it later.

[Obsidian]

Be sure to post up some of the usage that you have for build_sql_query, we'll see what's the issue.

[chaoskreator]

Here are two examples. The first, I'm sure is a syntax error on my part, but I can't find it!

Error:

Code: Select all

SQL ERROR [ mysqli ]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND g.group_id = 9 ORDER BY u.username ASC' at line 1 [1064]

SQL

SELECT u.user_id, u.group_id as default_group, u.username, ug.user_id as ug_user_id FROM (phpbb_users u, phpbb_groups g) LEFT JOIN phpbb_user_group ug ON (ug.group_id = g.group_id AND ug.user_pending = 0 AND ug.user_id = u.user_id) WHERE AND g.group_id = 9 ORDER BY u.username ASC

Code:

Code: Select all

<?php
         $sql = $db->sql_build_query('SELECT', array(
            'SELECT'   => 'u.user_id, u.group_id as default_group, u.username, ug.user_id as ug_user_id',

            'FROM'      => array(USERS_TABLE      => 'u', GROUPS_TABLE   => 'g'),

            'LEFT_JOIN'   => array(array('FROM'   => array(USER_GROUP_TABLE => 'ug'), 'ON'   => 'ug.group_id = g.group_id AND ug.user_pending = 0 AND ug.user_id = u.user_id')),

            'WHERE'      => 'AND g.group_id = 9',

            'ORDER_BY'   => 'u.username ASC'
            ));
         $result = $db->sql_query($sql);
         
         while($row = $db->sql_fetchrow($result))
         {
            echo '<p class="ranks">'. $row['u.username'] . '</p>';
         }
         ?>
      <p class="ranks_title"><b><u>Comrades</u></b></p>
         <?php
         $sql = "SELECT DISTINCT g.user_id
               FROM " . USER_GROUP_TABLE . " g
               LEFT JOIN " . USERS_TABLE . " u ON (u.user_id = g.user_id)
               WHERE g.group_id = 7";
         $result = $db->sql_query($sql);
         while($row = $db->sql_fetchrow($result))
         {
            echo '<p class="ranks">'. $row['u.username'] . '</p>';
         }
         ?>

This second one is the one topdown provided on startrekguide.com, for both first and last name. But it doesn't display anything after the "Your name is:"...

Code: Select all

case 'user_generate' :
         $gender = request_var('gender', '');
         $align = request_var('align', '');

         $sql_array = array(
            'SELECT'           => 'u.name as name',
         
            'FROM'           => array(USER_GENERATOR => 'u'),
    
            'WHERE'           => 'u.alignment = ' . $align . '
               AND u.type        = ' . $gender . '
               AND u.name_type    = u.first',
            'ORDER_BY'      => RAND(),
         );
         $result = $db->sql_query($sql);
         $first_name = $db->sql_fetchrow($result);
         $db->sql_freeresult($result);
         
         $sql_array = array(
            'SELECT'            => 'u.name as name',
            'FROM'            => array(USER_GENERATOR => 'u'),
            'WHERE'            => 'u.alignment = ' . $align . '
               AND u.type         = ' . $gender . '
               AND u.name_type   = u.last',
            'ORDER_BY'         => RAND(),
         );
         $result = $db->sql_query($sql);
         $last_name = $db->sql_fetchrow($result);
         $db->sql_freeresult($result);
            
         $msg = 'Your username is: '.$first_name.' '.$last_name;
         trigger_error($msg);
      break;

[Obsidian]

One. Order by RAND() is not right. That is most likely DBMS specific. You may be doing this for private use, so I'll let that go for now. However, what is wrong is that it is not in quotes, instead you are relying on the PHP function rand(). Which I am sure is not going to work, right?

Two. This is invalid syntax.

Code: Select all

'WHERE'      => 'AND g.group_id = 9',

Try this:

Code: Select all

'WHERE'      => 'g.group_id = 9',

[chaoskreator]

One. Order by RAND() is not right. That is most likely DBMS specific. You may be doing this for private use, so I'll let that go for now. However, what is wrong is that it is not in quotes, instead you are relying on the PHP function rand(). Which I am sure is not going to work, right?

Ah-ha! I get it!

Two. This is invalid syntax.

Code: Select all

'WHERE'      => 'AND g.group_id = 9',

Try this:

Code: Select all

'WHERE'      => 'g.group_id = 9',

Oh, snap. I copied that code from one of my other queries that actually works, and apparently, I forgot to remove that.