Extracting Data from 3 tables?

russellbcv

Is it possible to extract data from 3 different tables in one php script?

I have at present a php script which connects to table 1 for authentication, then table 2 for the main content and I would like to extract data from a third table but I'm not sure how to do this?

travelbuff

I strongly suggest a tutorial on joins.

"SELECT table1.columnname, table2.columnname FROM table1, table2 WHERE table1.columnname = 'somecondition' AND table2.columnname = table1.columname"

russellbcv

Would this work?

SELECT custs_uk.name, passwords.username FROM table custs_uk, passwords WHERE custs_uk = 'username' AND passwords.username = custs_uk.name"

I have the username field in all tables.

I want to extract the 'name' field from the 'custs_uk' table

russellbcv

The only thing I am missing is the 'firstname & lastname' to go after the WELCOME

<h2 class="style21">Welcome <span class="style19">

Both first & last names are located in my 'custs_uk' table.

Where within the code do I extract this?

<?php
session_start();
if ($_SESSION['logged_in'] != "true"){
     $username = $_POST['username'];
     $password = $_POST['password'];
}
else {$username = $_SESSION['username'];
     $password = $_SESSION['password'];
 }

$self = $_SERVER['PHP_SELF'];
$referer = 'index.php';

if( ( !$username ) or ( !$password ) )
{ header( "Location:$referer" ); }

$conn = @mysql_connect( "localhost", "xxxx", "xxxx" )
or die ( "Could not connect" );
$rs = @mysql_select_db( "rates", $conn )
or die( "Could not select database" );
$sql="select * from custs_uk where username = '" . $username . "'
and password = '". $password . "'";
$rs = mysql_query( $sql, $conn )
or die(mysql_error() . "Could not execute query" );
$num = mysql_numrows( $rs );

if( $num != 0 ){
$_SESSION['logged_in'] = "true";
$_SESSION['username'] = $username;
$_SESSION['password'] = $password;

$rs = @mysql_select_db( "rates", $conn )
or die( "Could not select database" );
$sql = "select * from cdruk where username = '" . $username . "'";
$rs = mysql_query( $sql, $conn )
or die(mysql_error() . "Could not execute query" );

$list = "<table class=\"tblist\" border=\"0\" cellpadding=\"5\">";
$list .= "<tr><th>Cli</th>";
$list .= "<th>Date</th>";
$list .= "<th>Time</th>";
$list .= "<th>Number</th>";
$list .= "<th>Duration</th>";
$list .= "<th>Cost</th></tr>";
$total_duration = 0;
$total_cost = 0;
while( $row = mysql_fetch_array( $rs ) )
{
$total_duration += $row['duration'];
$total_cost += $row['cost'];
$list .= "<tr>";
$list .= "<td>".$row["cli"]."</td>";
$list .= "<td>".$row["date"]."</td>";
$list .= "<td>".$row["time"]."</td>";
$list .= "<td>".$row["number"]."</td>";
$list .= "<td>".$row["duration"]."</td>";
$list .= "<td>".$row["cost"]."</td>";
$list .= "</tr>";
$list .= "</tr>";
}
$list .= "</table>";
}
else{
header("Location: index.php");
}
?>

<html>
<!-- DW6 -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<link rel="stylesheet" href="emx_nav_left.css" type="text/css">
<script type="text/javascript">
<!--
var time = 3000;
var numofitems = 7;

//menu constructor
function menu(allitems,thisitem,startstate){
callname= "gl"+thisitem;
divname="subglobal"+thisitem;
this.numberofmenuitems = 7;
this.caller = document.getElementById(callname);
this.thediv = document.getElementById(divname);
this.thediv.style.visibility = startstate;
}

//menu methods
function ehandler(event,theobj){
for (var i=1; i<= theobj.numberofmenuitems; i++){
var shutdiv =eval( "menuitem"+i+".thediv");
shutdiv.style.visibility="hidden";
}
theobj.thediv.style.visibility="visible";
}

function closesubnav(event){
if ((event.clientY <48)||(event.clientY > 107)){
for (var i=1; i<= numofitems; i++){
var shutdiv =eval('menuitem'+i+'.thediv');
shutdiv.style.visibility='hidden';
}
}
}
// -->
</script>
<style type="text/css">
<!--
body {
background-color: #808080;
}
.style4 {font-size: x-small}
.style8 {color: #21536A}
.tblist {
font-family: Arial, Helvetica, sans-serif;
font-size: x-small;
}
.style14 {
	font-size: 12px;
	font-family: Arial, Helvetica, sans-serif;
}
.style15 {font-family: Arial, Helvetica, sans-serif}
.style16 {
	color: #21536A;
	font-size: medium;
	font-weight: bold;
}
.style17 {
	color: #000000;
	font-family: Arial, Helvetica, sans-serif;
	font-size: 14px;
}
.style18 {font-size: 12px}
.style19 {color: #FF6600}
.style21 {font-family: Arial, Helvetica, sans-serif; font-weight: bold; }
.style22 {color: #FF6600; font-weight: bold; }
-->
</style>
</head>
<body onmousemove="closesubnav(event);">
<div class="skipLinks">skip to: <a href="#content">page content</a> | <a href="pageNav">links on this page</a> | <a href="#globalNav">site navigation</a> | <a href="#siteInfo">footer (site information)</a> </div>
<div id="masthead">
<h1 id="siteName">&nbsp;</h1>
<div id="subglobal1" class="subglobalNav">
<a href="#">subglobal1 link</a> | <a href="#">subglobal1 link</a> | <a href="#">subglobal1
link</a> | <a href="#">subglobal1 link</a> | <a href="#">subglobal1 link</a> | <a href="#">subglobal1
link</a> | <a href="#">subglobal1 link</a>
</div>
<div id="subglobal2" class="subglobalNav">
<a href="#">subglobal2 link</a> | <a href="#">subglobal2 link</a> | <a href="#">subglobal2
link</a> | <a href="#">subglobal2 link</a> | <a href="#">subglobal2 link</a> | <a href="#">subglobal2
link</a> | <a href="#">subglobal2 link</a>
</div>
<div id="subglobal3" class="subglobalNav">
<a href="#">subglobal3 link</a> | <a href="#">subglobal3 link</a> | <a href="#">subglobal3
link</a> | <a href="#">subglobal3 link</a> | <a href="#">subglobal3 link</a> | <a href="#">subglobal3
link</a> | <a href="#">subglobal3 link</a>
</div>
<div id="subglobal4" class="subglobalNav">
<a href="#">subglobal4 link</a> | <a href="#">subglobal4 link</a> | <a href="#">subglobal4
link</a> | <a href="#">subglobal4 link</a> | <a href="#">subglobal4 link</a> | <a href="#">subglobal4
link</a> | <a href="#">subglobal4 link</a>
</div>
<div id="subglobal5" class="subglobalNav">
<a href="#">subglobal5 link</a> | <a href="#">subglobal5 link</a> | <a href="#">subglobal5
link</a> | <a href="#">subglobal5 link</a> | <a href="#">subglobal5 link</a> | <a href="#">subglobal5
link</a> | <a href="#">subglobal5 link</a>
</div>
<div id="subglobal6" class="subglobalNav">
<a href="#">subglobal6 link</a> | <a href="#">subglobal6 link</a> | <a href="#">subglobal6
link</a> | <a href="#">subglobal6 link</a> | <a href="#">subglobal6 link</a> | <a href="#">subglobal6
link</a> | <a href="#">subglobal6 link</a>
</div>
<div id="subglobal7" class="subglobalNav">
<a href="#">subglobal7 link</a> | <a href="#">subglobal7 link</a> | <a href="#">subglobal7
link</a> | <a href="#">subglobal7 link</a> | <a href="#">subglobal7 link</a> | <a href="#">subglobal7
link</a> | <a href="#">subglobal7 link</a>
</div>
<div id="subglobal8" class="subglobalNav">
<a href="#">subglobal8 link</a> | <a href="#">subglobal8 link</a> | <a href="#">subglobal8
link</a> | <a href="#">subglobal8 link</a> | <a href="#">subglobal8 link</a> | <a href="#">subglobal8
link</a> | <a href="#">subglobal8 link</a>
</div>
</div>
<!-- end masthead -->
<div id="pagecell1">
<!--pagecell1-->
<span class="style21"><strong><img alt="" src="tl_curve_white.gif" height="6" width="6" id="tl"> <img alt="" src="tr_curve_white.gif" height="6" width="6" id="tr">
</strong>
</span>
<div class="style21" id="breadCrumb">
</div>
<div class="style22" id="pageName">
<h2 class="style21">Welcome <span class="style19"><?php echo $username?>
</span></h2>
</div>
<div id="pageNav">
<div class="style4" id="sectionLinks"><a href="/cview/invoices.php">Invoices</a><a href="/cview/payments.php">Payments</a><a href="/cview/personal.php">Personal</a><a href="logoff.php">Log Off</a></div>
</div>
<div id="content">
<div class="story">
<p class="style15"><span class="style16">Customer Call Record </span><span class="style18">(current month)</span> </p>
<p class="style8 style14"><span class="style19"><span class="style8">Name:</span></span><br>
  <span class="style19"><span class="style8">Username:</span><?php echo $username?></span></p>
<p class="style8"><span class="style17">Duration</span> <?php echo round($total_duration,2); ?> <span class="style17">Cost</span> <?php echo round($total_cost,2); ?> </p>
<p class="style8 style14">*Note &gt; Any discounts & credits will be calculated at the end of month (if applicable).</p>
<p>
<font face="Arial, Helvetica, sans-serif"><?php
echo( $list );
?> </p>
</div>
</div>
</div>
<!--end pagecell1-->
<br>
<script type="text/javascript">
<!--
</script>
</body>
</html>
// --> 
</script> 
</body> 
</html>

bradgrafelman

Are 'firstname & lastname' column names in your MySQL table? If so, just add this to your SELECT query: "custs_uk.firstname, custs_uk,lastname"

russellbcv

Select query > do you mean here...

$conn = @mysql_connect( "localhost", "xxxx", "xxxx" ) 
or die ( "Could not connect" ); 
$rs = @mysql_select_db( "rates", $conn ) 
or die( "Could not select database" ); 
$sql="select * from custs_uk where username = '" . $username . "' 
and password = '". $password . "'"; 
$rs = mysql_query( $sql, $conn ) 
or die(mysql_error() . "Could not execute query" ); 
$num = mysql_numrows( $rs );

bradgrafelman

I would think so... your entire method of MySQL queries is very strange to me.

I would have someone else take a look at it, because I would simply have rewritten the code from scratch.

russellbcv

Thanks for that - I will give this a go!

Could somebody see if they can get into my website:-

http://www.midastelecom.com

Thanks

Russ