Using select for GET variables

Question

I have transfered variable values to another page, but now I'm trying to show the database content relevant to this value.

Workflow:

1. There's a form that I enter a new Company. I have entered Company1. It's in companies database.
2. There's another form, that I list in an OPTION tag all the recorded companies. In this form, I record the company's employees information, including the chosen companies option.
3. There's a page that lists all the recorded companies. Each one has a link (company.php?name=$companyName).
4. The companies' names are passed via GET to another page.
5. This other page is called "company.php" and need to show the company's information (other fields in database).
6. It also has to show other table record, like "employees". It must list all the employees that has de same company's name, passed via GET.

The question is: how do I proceed with step 5 and 6? I tried using this select, but didn't work:

$query = mysql_query("SELECT employeeName FROM employeeForm  WHERE companyName='$employeeName'") or die(mysql_error());

It's really difficult for me to be clear at this, I'm sorry if it's not understandable. 'Been working around this issue for hours.

I appreciate any help!

EDIT: Used method:

include("connect.php");
$companyName=$_GET['companyName'];
$result = mysql_query("SELECT * FROM employeeList WHERE company = '$companyName'");

while($result = mysql_fetch_array($query))
{
echo    '<div class="form">'.$result['employeeName'].'</div>';
}

Answer 1

ANSWER:

your code:

         while($result = mysql_fetch_array($query))
         {
      echo    '<div class="form">'.$result['employeeName'].'</div>';
        }

should be:

        while($res = mysql_fetch_array($result)) //changed to the name of the variable that stores the query  (can't store the array in a variable named after the query so i've changed it to $res...)
     {
       echo    '<div class="form">'.$res['employeeName'].'</div>';
       }

OLD: Are you retrieving the result of the query? To return just one result use the mysql_fetch_assoc() function and to return an entire row (or more variables) use mysql_fetch_array().

Example:

     $test = mysql_fetch_assoc($query);

     $array = mysql_fetch_array($query); 

Ps. To Select an array use a syntax similar to :

    "SELECT * FROM table WHERE var = condition"

PPs. This was taken from php.net:

 This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API. 

EDIT:

Your code:

   $query = mysql_query("SELECT employeeName FROM employeeForm  WHERE companyName='$employeeName'") or die(mysql_error());

You treat the variable as text (variable name not content :) )... use:

       WHERE '".$employeeName."' 

to parse variables into query... (observe the double quotes)

Also... to access the results of the query use something like:

    $array['COLUMN NAME'];

..assuming you use : $array = mysql_fetch_array($query);

Answer 2

I'd highly recommend you use PDO, here's a sample using PDO.

<?php

$dsn      = 'mysql:dbname=so;host=localhost';
$user     = 'root';
$password = '';


// try to connect.
try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    exit(1);
}

// the sql which gets all employees for this company.
$getEmployeesSQL = '';
$getEmployeesSQL .= ' SELECT c.name AS company_name, e.name AS employee_name, e.salary, c.location ';
$getEmployeesSQL .= ' FROM company c';
$getEmployeesSQL .= ' LEFT JOIN employee e';
$getEmployeesSQL .= ' ON c.id = e.company_id';
$getEmployeesSQL .= ' WHERE c.name = :cname';

// sql to get company data.
$companyDataSQL = "SELECT * FROM company WHERE name = :cname";

// prepare the sql.
$stmtOne = $dbh->prepare($getEmployeesSQL);
$stmtTwo = $dbh->prepare($companyDataSQL);

// execute
$stmtOne->execute(array(':cname' => $_GET['company']));
$stmtTwo->execute(array(':cname' => $_GET['company']));

// print results.
$employees   = $stmtOne->fetchAll(PDO::FETCH_ASSOC);
$companyData = $stmtTwo->fetchAll(PDO::FETCH_ASSOC);

/* * ****************************************** */
/*       Iterate and print HTML table.          */
/* * ****************************************** */


$html = '<table border="1">';
foreach ($companyData as $company) {
    foreach ($company as $field => $value) {
        $html.= "<tr><td>$field</td><td>$value</td></tr>";
    }
}
$html . '</table>';
echo $html;

$html = '<table border="1">';
foreach ($employees as $row) {
    $html.= '<tr><td>' . implode('</td><td>', array_values($row)) . '</td></tr>';
}
$html . '</table>';
echo $html;

What this snippet does is that it prints the company data in a table and then gets employee data using company name.

The table structure I'm using is as follows:

CREATE TABLE `company` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `location` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `salary` bigint(12) DEFAULT NULL,
  `company_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_company` (`company_id`),
  CONSTRAINT `fk_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This returns data as follows: (Not pretty, but this'll have to do for now.)

Answer 3

As I understand it you have two tables - one for companies and one for employees, with the obvious possibility that there are multiple employees for each company. If what you're trying to do is produce a list of employees for a given company, you need to use the Company Name (assuming it's unique) in your SQL query.

$companyName = $_GET('companyName');
$result = mysql_query("SELECT * FROM employeeList WHERE company = '$companyName'");

That will give you a list of employees (with all their info) associated with that particular company.

EDIT:

To loop through the query and print out the results, you can use something like to following (along with what's already above):

echo "<table><tbody>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>" . $row['employee_first_name'] . "&nbsp;" . $row['employee_last_name'] .  "</td></tr>";
}
echo "</tbody></table>