[SOLVED] mysql select help required

[tomasd]

Hi all,

I have a simple table which I want to enhance.

USE users;

CREATE TABLE ghh_departments (
  department_id int(11) NOT NULL auto_increment,
  department_name varchar(255) default NULL,
  department_orderby int(11) NOT NULL default '0',
  
  PRIMARY KEY  (department_id)
) TYPE=MyISAM;

INSERT INTO ghh_departments VALUES ( 1, 'Accounting', 1);
INSERT INTO ghh_departments VALUES ( 2, 'Banqueting', 2);
INSERT INTO ghh_departments VALUES ( 3, 'Executive Office', 3);
INSERT INTO ghh_departments VALUES ( 4, 'Front Office', 4);
INSERT INTO ghh_departments VALUES ( 5, 'Housekeeping', 5);
INSERT INTO ghh_departments VALUES ( 6, 'Human Resources', 6);
INSERT INTO ghh_departments VALUES ( 7, 'Kitchen', 7);
INSERT INTO ghh_departments VALUES ( 8, 'Loss Prevention', ;
INSERT INTO ghh_departments VALUES ( 9, 'Mini Bar', 9);
INSERT INTO ghh_departments VALUES ( 10, 'Repairs & Maintenance', 10);
INSERT INTO ghh_departments VALUES ( 11, 'Revenue', 11);
INSERT INTO ghh_departments VALUES ( 12, 'Room Service', 12);
INSERT INTO ghh_departments VALUES ( 13, 'Sales & Marketing', 13);

I would like to add 2 more columns master_name and master_email for each department so I can set head of department.

I believe if I do that I will run into a bit of a trouble with my current mysql query:

<?php

$r_departments = mysql_query("SELECT * FROM $TABLE_DEPARTMENTS WHERE department_orderby > 0 ORDER BY department_orderby") 
			or error("Cannot load departments");
?>

How do I query for department names only?

 

Thanks for your help!

[paul2463]

$r_departments = mysql_query("SELECT department_name  FROM $TABLE_DEPARTMENTS WHERE department_orderby > 0 ORDER BY department_orderby") 
			or error("Cannot load departments");

[tomasd]

Thanks very much for your reply, I've tried your suggestion it appears to be working but not fully. Here's what's happening:

using:

<?php
$r_departments = mysql_query("SELECT * FROM $TABLE_DEPARTMENTS WHERE department_orderby > 0 ORDER BY department_orderby") 
			or error("Cannot load departments");

while ($row = mysql_fetch_array($r_departments))
{
print_r($department_array);
}
?>

output:

Array
(
    [0] => 1
    [department_id] => 1
    [1] => Accounting
    [department_name] => Accounting
    [2] => 1
    [department_orderby] => 1
)
Array
(
    [0] => 2
    [department_id] => 2
    [1] => Banqueting
    [department_name] => Banqueting
    [2] => 2
    [department_orderby] => 2
)

.... and so on...

using:

<?php
$r_departments = mysql_query("SELECT department_name FROM $TABLE_DEPARTMENTS WHERE department_orderby > 0 ORDER BY department_orderby") 
			or error("Cannot load departments");

while ($row = mysql_fetch_array($r_departments))
{
print_r($department_array);
}
?>

output:

Array
(
    [0] => Accounting
    [department_name] => Accounting
)
Array
(
    [0] => Banqueting
    [department_name] => Banqueting
)

 

What I do with * output is the following:

<?php
while ($row = mysql_fetch_array($r_departments))
{
$department_array[$row[0]] = $row[1];
}
?>

Therefore I'm getting following array when doing print_r($department_array);

Array
(
    [1] => Accounting
    [2] => Banqueting
    [3] => Executive Office
    [4] => Front Office
    [5] => Housekeeping
    [6] => Human Resources
    [7] => Kitchen
    [8] => Loss Prevention
    [9] => Mini Bar
    [10] => Repairs & Maintenance
    [11] => Revenue
    [12] => Room Service
    [13] => Sales & Marketing
)

What should be included to mysql query to also get a department_id?

[tomasd]

$r_departments = mysql_query("SELECT department_id,department_name FROM $TABLE_DEPARTMENTS WHERE department_orderby > 0 ORDER BY department_orderby")

or error("Cannot load departments");