Sorry for double posting. It wasn’t intentional. It didn’t seem to take so I double clicked again and then it went through but twice. Sorry. Now It is doing it again. So I hope it doesn’t post twice again.
I wasn’t sure if you needed the square brackets around the php. Yes, I have multilple tables. This code is for a relational database. The company table gets data from the type table (and others) via selecting from drop down lists. Look below for the company variable at the bottom. I flagged it for you using ################### The companies variable contains id which displays integers. I want to display the cooresponding type as a string.
My type table has this structure:
¦id¦type¦
¦1 ¦ commercial ¦
¦2 ¦ residential ¦
php
error_reporting(E_ALL ^ E_NOTICE);
// Step 1: Display search form. Include 1 to many & many to Many
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/inc_db_ciq_connection.php’;
// Get the basic data from the type table
$result = mysqli_query($link, 'SELECT id, type FROM type');
if (!$result)
{
$error = 'Error fetching type from database!';
include '../errorfetchingbasics.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
$types[] = array('id' => $row['id'],'type' => $row['type']);
}
// Get the basic data from the company status table
$result = mysqli_query($link, 'SELECT id, compstatus FROM compstatus');
if (!$result)
{
$error = 'Error fetching compstatus from database!';
include '../errorfetchingcompstats.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
$compstatuss[] = array('id' => $row['id'],'compstatus' => $row['compstatus']);
}
// Get the basic data from the servicstate table
$result = mysqli_query($link, 'SELECT id, servicestate FROM servicestate');
if (!$result)
{
$error = 'Error fetching state from database!';
include '../errorfetchingbasics.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
$servicestates[] = array('id' => $row['id'],'servicestate' => $row['servicestate']);
}
// Get the basic data from the commodity table
$result = mysqli_query($link, 'SELECT id, commodity FROM commodity');
if (!$result)
{
$error = 'Error fetching commodity from database!';
include '../errorfetchingbasics.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
$commoditys[] = array('id' => $row['id'],'commodity' => $row['commodity']);
}
// Get the basic data from the LDC table
$result = mysqli_query($link, 'SELECT id, ldc FROM ldc');
if (!$result)
{
$error = 'Error fetching ldc from database!';
include '../errorfetchingbasics.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
$ldcs[] = array('id' => $row['id'],'ldc' => $row['ldc']);
}
include 'searchform.html.php';
// Step 2: build a list of companies that satisfies the criteria specified in the search form. 1 to 1 and 1 to many
if (isset($_GET['action']) and $_GET['action'] == 'search')
{
include $_SERVER['DOCUMENT_ROOT'] . '/includes/inc_db_ciq_connection.php';
$SELECT = 'SELECT id, name';
$FROM = ' FROM company';
$WHERE = ' WHERE 1';
// Check each of the possible constraints selected on the search form, and adjust our SQL query. Need to do both 1 to many and many to many!
//Checking to see if a type was specified in the search query
$typeid = mysqli_real_escape_string($link, $_GET['type']);
if ($typeid != '') // A type is selected
{
$WHERE .= " AND typeid = '$typeid'";
}
// Checking to see if a status was specified in the search querry
$compstatusid = mysqli_real_escape_string($link, $_GET['compstatus']);
if ($compstatusid != '') // A company status is selected
{
$WHERE .= " AND compstatusid = '$compstatusid'";
}
// Checking to see if a service state was specified in the search querry
$servicestateid = mysqli_real_escape_string($link, $_GET['servicestate']);
if ($servicestateid != '') // A state is selected
{
$FROM .= 'INNER JOIN companystate ON id = companyid ';
-$WHERE .= " AND servicestateid = '$servicestateid'";
}
// Checking to see if a commodity was specified in the search querry
$commodityid = mysqli_real_escape_string($link, $_GET['commodity']);
if ($commodityid != '') // A state is selected
if ($commodityid != '') // A state is selected
{
$FROM .= 'INNER JOIN companycommodity ON id = companyid ';
$WHERE .= " AND commodityid = '$commodityid'";
}
// Checking to see if an LDC was specified in the search querry
$ldcid = mysqli_real_escape_string($link, $_GET['ldc']);
if ($ldcid != '') // An LDC is selected
{
$FROM .= 'INNER JOIN companyldc ON id = companyid ';
$WHERE .= " AND ldcid = '$ldcid'";
}
// Retrieve and display the results.
$result = mysqli_query($link, "SELECT * FROM company $WHERE");
echo "There are ";
$NumResults = $result->num_rows;
echo "<b>$NumResults companies meeting your search criteria";
if (!$result)
{
$error = 'Error fetching companies.';
include '../erronomatches.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
###################
$companies[] = array('id' => $row['id'], 'typeid' => $row['typeid'],'compstatusid' => $row['compstatusid'], 'name' => $row['name'],'address' => $row['address'], 'city' => $row['city'], 'state' => $row['state'],'zip' => $row['zip'], 'signatory' => $row['signatory'], 'designation' => $row['designation']);
}
include 'companies.html.php';
exit();
}
/PHP
