Back To Programming / Scripting / Coding Forum

Trying to join two SQL tables

  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post March 26th, 2011, 4:24 pm

I'm in the process of writing a forum for a CMS system that I'm developing. I got the main idea of how to do it and I know how, I just have this little problem of implementing the SQL to work like it needs to (Well, it works like it needs to I can't form it into what I need it).

I got 2 tables at the moment (There would be more later on)... anyway... the first table holds the categories and the second one holds the forums that are assigned to the correct category...

SQL Code: [ Select ]
CREATE TABLE IF NOT EXISTS `cms_forum_cat` (
  `catID` int(11) NOT NULL AUTO_INCREMENT,
  `catName` varchar(50) NOT NULL,
  `catDescription` varchar(100) NOT NULL,
  `forums` int(5) NOT NULL,
  PRIMARY KEY (`catID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
CREATE TABLE IF NOT EXISTS `cms_forum_forums` (
  `forumID` int(11) NOT NULL AUTO_INCREMENT,
  `catID` int(11) NOT NULL,
  `forumName` varchar(50) NOT NULL,
  `forumDescription` varchar(100) NOT NULL,
  `topics` int(11) NOT NULL,
  `posts` int(11) NOT NULL,
  `lastPoster` varchar(100) NOT NULL,
  PRIMARY KEY (`forumID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
  1. CREATE TABLE IF NOT EXISTS `cms_forum_cat` (
  2.   `catID` int(11) NOT NULL AUTO_INCREMENT,
  3.   `catName` varchar(50) NOT NULL,
  4.   `catDescription` varchar(100) NOT NULL,
  5.   `forums` int(5) NOT NULL,
  6.   PRIMARY KEY (`catID`)
  7. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
  8.  
  9. CREATE TABLE IF NOT EXISTS `cms_forum_forums` (
  10.   `forumID` int(11) NOT NULL AUTO_INCREMENT,
  11.   `catID` int(11) NOT NULL,
  12.   `forumName` varchar(50) NOT NULL,
  13.   `forumDescription` varchar(100) NOT NULL,
  14.   `topics` int(11) NOT NULL,
  15.   `posts` int(11) NOT NULL,
  16.   `lastPoster` varchar(100) NOT NULL,
  17.   PRIMARY KEY (`forumID`)
  18. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

I can select the information from each table separately but I'm trying to join the SQLs so I retrieve information from the category and all the forums that are assigned to that category.

I'll try to explain it in terms of PHP since I can't explain in English :( :lol:

Retrieve information FROM EACH category
EACH category THAT HAS forums, join those forums with the results.

So when I retrieve information from category my array would look like...
Code: [ Select ]
Array
(
  [0] => Array
    (
      [catID] => 2
      [catName] => News and Announcements
      [catDescription] => News and Announcements
      [forums] => 1
    )

  [1] => Array
    (
      [catID] => 3
      [catName] => Support Center
      [catDescription] => Support Center
      [forums] => 0
    )

  [2] => Array
    (
      [catID] => 4
      [catName] => On-Topic Discussion
      [catDescription] => On-Topic Discussion
      [forums] => 0
    )

)
  1. Array
  2. (
  3.   [0] => Array
  4.     (
  5.       [catID] => 2
  6.       [catName] => News and Announcements
  7.       [catDescription] => News and Announcements
  8.       [forums] => 1
  9.     )
  11.   [1] => Array
  12.     (
  13.       [catID] => 3
  14.       [catName] => Support Center
  15.       [catDescription] => Support Center
  16.       [forums] => 0
  17.     )
  19.   [2] => Array
  20.     (
  21.       [catID] => 4
  22.       [catName] => On-Topic Discussion
  23.       [catDescription] => On-Topic Discussion
  24.       [forums] => 0
  25.     )
  27. )

What I want it to look like at the end is...
Code: [ Select ]
Array
(
  [0] => Array
    (
      [catID] => 2
      [catName] => News and Announcements
      [catDescription] => News and Announcements
      [forums] => 1
      [forumID] => 1
      [forumName] => Policies and Procedures
      [forumDescription] => All policies relating to hosting provided, and abuse/support procedures are housed within
      [topics] => 0
      [posts] => 0
      [lastPoster] =>
    )

  [1] => Array
    (
      [catID] => 3
      [catName] => Support Center
      [catDescription] => Support Center
      [forums] => 0
    )

  [2] => Array
    (
      [catID] => 4
      [catName] => On-Topic Discussion
      [catDescription] => On-Topic Discussion
      [forums] => 0
    )
)
  1. Array
  2. (
  3.   [0] => Array
  4.     (
  5.       [catID] => 2
  6.       [catName] => News and Announcements
  7.       [catDescription] => News and Announcements
  8.       [forums] => 1
  9.       [forumID] => 1
  10.       [forumName] => Policies and Procedures
  11.       [forumDescription] => All policies relating to hosting provided, and abuse/support procedures are housed within
  12.       [topics] => 0
  13.       [posts] => 0
  14.       [lastPoster] =>
  15.     )
  17.   [1] => Array
  18.     (
  19.       [catID] => 3
  20.       [catName] => Support Center
  21.       [catDescription] => Support Center
  22.       [forums] => 0
  23.     )
  25.   [2] => Array
  26.     (
  27.       [catID] => 4
  28.       [catName] => On-Topic Discussion
  29.       [catDescription] => On-Topic Discussion
  30.       [forums] => 0
  31.     )
  32. )

I hope that made sense...
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post March 26th, 2011, 4:24 pm

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Joined: May 22, 2004
  • Posts: 3415
  • Loc: Richland, WA
  • Status: Offline

Post March 26th, 2011, 4:50 pm

SQL Code: [ Select ]
SELECT cms_forum_cat.catID,catName,catDescription,forums,forumID,forumNAME,forumDescription,topic,posts,lastPoster
FROM cms_forum_forums
LEFT JOIN cms_forum_cat ON cms_forum_forums.catID = cms_forum_cat.catID
  1. SELECT cms_forum_cat.catID,catName,catDescription,forums,forumID,forumNAME,forumDescription,topic,posts,lastPoster
  2. FROM cms_forum_forums
  3. LEFT JOIN cms_forum_cat ON cms_forum_forums.catID = cms_forum_cat.catID
#define NULL (::rand() % 2)
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post March 26th, 2011, 5:37 pm

It's throwing the following error...
Code: [ Select ]
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\CMS\includes\db.php on line 461

Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in C:\wamp\www\CMS\includes\db.php on line 677
  1. Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\CMS\includes\db.php on line 461
  3. Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in C:\wamp\www\CMS\includes\db.php on line 677
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Joined: May 22, 2004
  • Posts: 3415
  • Loc: Richland, WA
  • Status: Offline

Post March 26th, 2011, 5:40 pm

That means there is an error in the query. You need to check your log for the error to figure out whats wrong with it. Do you not have a mysql_error() function someplace in your database class?
#define NULL (::rand() % 2)
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post April 1st, 2011, 2:13 am

Ok, I got that fixed... apparently I forgot to put the mysql_error() function in the place where it actually sets the mysql result resource... my bad. Turns out I mistyped a fieldname :oops:

Thank you!
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post April 1st, 2011, 2:10 pm

I just got another problem with SQL and this time, mysql_error() isn't throwing anything... in fact... nothing is giving me any information as to why it's not working.

Well... the only error I see is the one I have my db class write into my own log

Quote:
[Fri Apr, 01 7:22:15 PM] Error setting MySQL Resource. SQL used: "INSERT INTO `cms_track` (`trackID`, `userID`, `user-agent`, `pageFrom`, `pageTo`, `timeVisited`, `userIP`) VALUES ('0', '0', 'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.16) Gecko/20110319 Firefox/3.6.16', 'null', 'C:/wamp/www/CMS/index.php', '1301685735', '127.0.0.1')"


But as to why, mysql_error() isn't throwing anything and I don't know. It had 2000+ entries in it so I know it worked and then I thought it was possible that there were just too many entries, so I truncated the table, but it still gives me the same error.

Another weird thing, that even though it's giving me the error, the SQL is still carried out which leads me to believe the error comes from my SQL function rather then the SQL itself.

Below is the function that I use:
PHP Code: [ Select ]
<?php
public function resource($sql = null, $return = false)
{
    // Checking if we are connected to MySQL
    if(!is_resource($this->mysql_link))
    {
        $this->connect();
    }
   
    // Checking if the SQL is empty
    if(is_null($sql))
    {
        $sql = $this->get_last_sql();
    }
   
    // Getting the resource into a variable
    $resource = mysql_query($sql);
   
    // Setting the last result variable
    $this->last_result = $resource;
   
    // Checking if the resource was created properly
    if(is_resource($resource))
    {
        // Checking if we are returning the result
        if($return)
        {
            return $resource;
        }
       
        // Otherwise we are returning true
        return true;
    }
    else
    {
        // Logging the error if need be
        if($this->log_errors === true)
        {
            $this->log_error('Error setting MySQL Resource. SQL used: "' . $sql . '"');
        }
       
        // Checking if we need to kill the script
        if($this->silent == false)
        {
            die(mysql_error());
        }
       
        // It was a failure... return false
        return false;
    }
}
?>
  1. <?php
  2. public function resource($sql = null, $return = false)
  3. {
  4.     // Checking if we are connected to MySQL
  5.     if(!is_resource($this->mysql_link))
  6.     {
  7.         $this->connect();
  8.     }
  9.    
  10.     // Checking if the SQL is empty
  11.     if(is_null($sql))
  12.     {
  13.         $sql = $this->get_last_sql();
  14.     }
  15.    
  16.     // Getting the resource into a variable
  17.     $resource = mysql_query($sql);
  18.    
  19.     // Setting the last result variable
  20.     $this->last_result = $resource;
  21.    
  22.     // Checking if the resource was created properly
  23.     if(is_resource($resource))
  24.     {
  25.         // Checking if we are returning the result
  26.         if($return)
  27.         {
  28.             return $resource;
  29.         }
  30.        
  31.         // Otherwise we are returning true
  32.         return true;
  33.     }
  34.     else
  35.     {
  36.         // Logging the error if need be
  37.         if($this->log_errors === true)
  38.         {
  39.             $this->log_error('Error setting MySQL Resource. SQL used: "' . $sql . '"');
  40.         }
  41.        
  42.         // Checking if we need to kill the script
  43.         if($this->silent == false)
  44.         {
  45.             die(mysql_error());
  46.         }
  47.        
  48.         // It was a failure... return false
  49.         return false;
  50.     }
  51. }
  52. ?>
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Joined: May 22, 2004
  • Posts: 3415
  • Loc: Richland, WA
  • Status: Offline

Post April 1st, 2011, 2:52 pm

You probably dont want to use die() as it exits your script. Just return false so you can handle the error later.
Are you setting silent to false?
#define NULL (::rand() % 2)
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post April 1st, 2011, 3:39 pm

For some reason, no matter whether I put silent to true or false, I just see a blank screen rather then my site like something is killing my script, but if I remove the die() out of there, it still does the same thing, so it's not because of die.

Also, I put in the die() in there (it's actually found throughout the class) for debugging reasons and only would be seeing if silent is set to false... and that would be set to false only in development stage.

And yes, I do set the silent to false to make sure I get the errors...

I don't know what could be causing the problem here...
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Joined: May 22, 2004
  • Posts: 3415
  • Loc: Richland, WA
  • Status: Offline

Post April 1st, 2011, 3:45 pm

throw the mysql_error() in with your error log and see if it gets printed out there.
#define NULL (::rand() % 2)
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post April 1st, 2011, 3:48 pm

It doesn't... I have a question.

When you INSERT something, does mysql_query() return a resource or just a boolean? Because according to what's happening, the mysql_query() is successful but fails the is_resource() function.

If that so, that's part of the problem, but then, why does it just kill the script with silent set to true?

var_dump($resource) spits out bool(true)
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post April 1st, 2011, 3:52 pm

I solved the problem by changing
PHP Code: [ Select ]
if(is_resource($resource))
to
PHP Code: [ Select ]
if(is_resource($resource) || $resource == true)


Im just wondering why the entire script was being killed when the db function was returning false... The function that actually initiates the SQL doesn't kill the script upon failure...
PHP Code: [ Select ]
<?php
private function track_user($user)
{
    global $db;
   
    // The SQL Data Table for user track
    $data_table = array("trackID"       => '0',
                        "userID"        => $this->user_id,
                        "user-agent"    => $_SERVER['HTTP_USER_AGENT'],
                        "pageFrom"      => (isset($_SERVER['HTTP_REFERER'])) ? $_SERVER['HTTP_REFERER'] : 'null',
                        "pageTo"        => (!empty($_SERVER['QUERY_STRING'])) ? $_SERVER['SCRIPT_FILENAME'] . '?' . $_SERVER['QUERY_STRING'] : $_SERVER['SCRIPT_FILENAME'],
                        "timeVisited"   => date('Y-m-d h:i:s'),
                        "userIP"        => $this->get_ip());
                       
    // Building the insert SQL to insert the user into the SQL table
    $sql = $db->build_insert(USER_TRACK, $data_table);
   
    // Inserting the user into the table
    $db->resource($sql);
}
?>
  1. <?php
  2. private function track_user($user)
  3. {
  4.     global $db;
  5.    
  6.     // The SQL Data Table for user track
  7.     $data_table = array("trackID"       => '0',
  8.                         "userID"        => $this->user_id,
  9.                         "user-agent"    => $_SERVER['HTTP_USER_AGENT'],
  10.                         "pageFrom"      => (isset($_SERVER['HTTP_REFERER'])) ? $_SERVER['HTTP_REFERER'] : 'null',
  11.                         "pageTo"        => (!empty($_SERVER['QUERY_STRING'])) ? $_SERVER['SCRIPT_FILENAME'] . '?' . $_SERVER['QUERY_STRING'] : $_SERVER['SCRIPT_FILENAME'],
  12.                         "timeVisited"   => date('Y-m-d h:i:s'),
  13.                         "userIP"        => $this->get_ip());
  14.                        
  15.     // Building the insert SQL to insert the user into the SQL table
  16.     $sql = $db->build_insert(USER_TRACK, $data_table);
  17.    
  18.     // Inserting the user into the table
  19.     $db->resource($sql);
  20. }
  21. ?>
"Bring forth therefore fruits meet for repentance:" Matthew 3:8

Page 1 of 1

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 90 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.