php & MySQL SELECT query Help PLEASE!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
JonnySnip3rz
Forum Newbie
Posts: 4
Joined: Sat Apr 17, 2010 8:28 am

php & MySQL SELECT query Help PLEASE!

Post 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`
--

User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: php & MySQL SELECT query Help PLEASE!

Post 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.
(#10850)
Post Reply