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:

There's a form that I enter a new Company. I have entered Company1. It's in companies database.
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.
There's a page that lists all the recorded companies. Each one has a link (company.php?name=$companyName).
The companies' names are passed via GET to another page.
This other page is called "company.php" and need to show the company's information (other fields in database).
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>';
}

given the way you're naming things, do you really thing that there's a companyName = 'Fred Smith' in your database? shouldn't it be more like companyName = 'Acme Inc.' — Marc B
– Marc B, Commented Nov 25, 2013 at 17:07
Instead of companyName = '$employeeName' why not companyName = '$comopanyName'? — Explosion Pills
– Explosion Pills, Commented Nov 25, 2013 at 17:08
I don't see how could that be helpful at all. That's not how it's named in main code, it's meant to simplify. @ExplosionPills to show the the employee's name according to the company's name. But it's aparently messy... — Anna
– Anna, Commented Nov 25, 2013 at 17:16
@EdMarty yes, I'm aware of that! I just tried to simplify the lines, I'm using mysqli as suggested by some others users. Thankyou! — Anna
– Anna, Commented Nov 25, 2013 at 17:19

SpiderLinked · Accepted Answer · 2013-11-25 18:13:01Z

2

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);

edited Nov 25, 2013 at 18:13

answered Nov 25, 2013 at 17:10

SpiderLinked

3733 gold badges6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Anna Over a year ago

Yes, I'm using something like: while($array = mysql_fetch_array($query)) Looping the row content. But I can't get this to work.

SpiderLinked Over a year ago

do you get any errors or the problem is that the content isn't written on the page?

briosheje Over a year ago

Can you please post the part of your code concerning the whole cycle please?

Anna Over a year ago

Like this? $result = mysql_query("SELECT * FROM employeeList WHERE companyName = ' ".$employeeName" ' ");

SpiderLinked Over a year ago

that's one thing...look at the first 3 lines (under the ANSWER header)

|

adeelx · Accepted Answer · 2013-11-25 18:06:53Z

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.)

Result

You're welcome, if you're just beginning with PHP, please take some time to read phptherightway.com, it'll help you quite a lot.

jackel414 · Accepted Answer · 2013-11-25 17:47:46Z

0

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>

edited Nov 25, 2013 at 17:47

answered Nov 25, 2013 at 17:30

jackel414

1,6862 gold badges14 silver badges31 bronze badges

5 Comments

Anna Over a year ago

That's exactly what I need, but I can't make it work. How do I loop the results? I used something like "while($result = mysql_fetch_array($result))" but returned an error.

SpiderLinked Over a year ago

"while($result = mysql_fetch_array($result))"- Anna you need to fetch the result of the query not variable name. Use : while($result = mysql_fetch_array($query))

Anna Over a year ago

Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in **company.php on line 52 I edited the post with the code.

jackel414 Over a year ago

I just updated my answer to include code to print out the results. I didn't realize that's where you were getting stuck.

Anna Over a year ago

@jackel414, I really appreciate all your attention! Thankyou for your time, I finally solved the issue.

Collectives™ on Stack Overflow

Using select for GET variables

3 Answers 3

7 Comments

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related