Page 1 of 1

php & MySQL SELECT query Help PLEASE!

Posted: Sat Apr 17, 2010 8:38 am
by JonnySnip3rz
Hey guys i hope someone can help. Ok i have a database with a subjects table and a pages table, i have linked them together with a subject_if field. I have 2 combo boxes the code is below.

Code: Select all

<select name="position">
                           <?php
                                
                           $result = mysql_query("SELECT * FROM subjects");
                        
                            while($row = mysql_fetch_array($result)){
                                
                                echo "<option value=\"{$row['id']}\"";
								echo ">{$row['menu_name']}</option>";
                            }
                 
                        echo "</select>";
                      
                        echo "<select name=\"position\">";
                           
                                
                           $query = " NEED HELP HERE ";
               
                           $result = mysql_query($query);
                            
                            while($row2 = mysql_fetch_array($result)){
                                
                                echo "<option value=\"{$row2['subject_id']}\"";
								echo ">{$row2['menu_name']}</option>";
                            }
                                
                           ?>
this will echo in the first option field the stuff stored in my menu_name table which is: php, html/css and java this is all fine. i want a statment so when i select php for example in the second combo box will list what ever is stored in the pages table for php i would do this somthing like this.

when subject_id in (pages table) matches id in (subjects table) then list all items in that table.

im sorry if this sounds german and if you need me to go over anything i will. below is sql dump.

Code: Select all

CREATE TABLE IF NOT EXISTS `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject_id` int(11) NOT NULL,
  `menu_name` varchar(30) NOT NULL,
  `position` int(3) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `pages`
--

INSERT INTO `pages` (`id`, `subject_id`, `menu_name`, `position`, `visible`, `content`) VALUES
(1, 1, 'Variables', 1, 1, 'arrays lol...'),
(2, 1, 'Arrays', 2, 1, 'random rubbish'),
(3, 2, 'borders in css', 1, 1, 'Great for nice looking layout');

-- --------------------------------------------------------

--
-- Table structure for table `subjects`
--

CREATE TABLE IF NOT EXISTS `subjects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `menu_name` varchar(30) NOT NULL,
  `position` int(3) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `subjects`
--

INSERT INTO `subjects` (`id`, `menu_name`, `position`, `visible`) VALUES
(1, 'php', 1, 1),
(2, 'html/css', 2, 1),
(3, 'java', 3, 1);

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `hashed_password` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `users`
--


Re: php & MySQL SELECT query Help PLEASE!

Posted: Sat Apr 17, 2010 10:31 pm
by Christopher
You probably want "SELECT * FROM subjects JOIN pages ON subjects.id=pages.subject_id WHERE something". You will probably need to use AS to alias some of the field names such as menu_name.