ok i have 2 tables
table 1 called employees with columns called name, email, phone, country
Table 2 called Country with columns called id, country, ico2, iso3, noc
what i have so far is a list of peoples names. then they click the name to get further information on them. i can get table 1 information to appear and now what i want is table to information to appear however only information that matches between table 1 column country and table 2 column country to apprear.
this is only a test for a website i'm designing. that is still in early stages. which will need to beable to get information from more than 1 table.
let me know if you have any questions.
Thanks
get info from 2 tables
Moderator: General Moderators
Re: get info from 2 tables
post your code. (not all but only the part of your problem)
Re: get info from 2 tables
Code: Select all
<?php require_once('Connections/testingdb.php'); ?>
<?php
$maxRows_viewrec = 10;
$pageNum_viewrec = 0;
if (isset($_GET['pageNum_viewrec'])) {
$pageNum_viewrec = $_GET['pageNum_viewrec'];
}
$startRow_viewrec = $pageNum_viewrec * $maxRows_viewrec;
$colname_viewrec = "1";
if (isset($_GET['name'])) {
$colname_viewrec = (get_magic_quotes_gpc()) ? $_GET['name'] : addslashes($_GET['name']);
}
mysql_select_db($database_testingdb, $testingdb);
$query_viewrec = sprintf("SELECT * FROM employees WHERE name = '%s'", $colname_viewrec);
$query_limit_viewrec = sprintf("%s LIMIT %d, %d", $query_viewrec, $startRow_viewrec, $maxRows_viewrec);
$viewrec = mysql_query($query_limit_viewrec, $testingdb) or die(mysql_error());
$row_viewrec = mysql_fetch_assoc($viewrec);
if (isset($_GET['totalRows_viewrec'])) {
$totalRows_viewrec = $_GET['totalRows_viewrec'];
} else {
$all_viewrec = mysql_query($query_viewrec);
$totalRows_viewrec = mysql_num_rows($all_viewrec);
}
$totalPages_viewrec = ceil($totalRows_viewrec/$maxRows_viewrec)-1;
$maxRows_reccountry = 10;
$pageNum_reccountry = 0;
if (isset($_GET['pageNum_reccountry'])) {
$pageNum_reccountry = $_GET['pageNum_reccountry'];
}
$startRow_reccountry = $pageNum_reccountry * $maxRows_reccountry;
$colname_reccountry = "1";
if (isset($_POST['country'])) {
$colname_reccountry = (get_magic_quotes_gpc()) ? $_POST['country'] : addslashes($_POST['country']);
}
mysql_select_db($database_testingdb, $testingdb);
$query_reccountry = sprintf("SELECT country, iso2 FROM countries WHERE country = '%s'", $colname_reccountry);
$query_limit_reccountry = sprintf("%s LIMIT %d, %d", $query_reccountry, $startRow_reccountry, $maxRows_reccountry);
$reccountry = mysql_query($query_limit_reccountry, $testingdb) or die(mysql_error());
$row_reccountry = mysql_fetch_assoc($reccountry);
if (isset($_GET['totalRows_reccountry'])) {
$totalRows_reccountry = $_GET['totalRows_reccountry'];
} else {
$all_reccountry = mysql_query($query_reccountry);
$totalRows_reccountry = mysql_num_rows($all_reccountry);
}
$totalPages_reccountry = ceil($totalRows_reccountry/$maxRows_reccountry)-1;
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<table border="0">
<tr>
<td>name</td>
<td>email</td>
<td>phone</td>
<td>country</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_viewrec['name']; ?></td>
<td><?php echo $row_viewrec['email']; ?></td>
<td><?php echo $row_viewrec['phone']; ?></td>
<td><?php echo $row_viewrec['country']; ?></td>
</tr>
<?php } while ($row_viewrec = mysql_fetch_assoc($viewrec)); ?>
</table>
<table border="0">
<?php do { ?>
<tr>
<td><img src="/websites/testwebsites/flags/<?php echo $row_reccountry['iso2']; ?>.gif"></td>
<td><?php echo $row_reccountry['country']; ?></td>
</tr>
<?php } while ($row_reccountry = mysql_fetch_assoc($reccountry)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($viewrec);
mysql_free_result($reccountry);
?>