Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

MySQL right join problem... php

In a MySQL database, I have 4 tables to display their data in three list boxes using php. Please see the attached image for the tables and listboxes.

In this application, at this point, I am trying to figure out why tbd, shown in red on the bottom right corner of the attached image, is missing form Specialty list box.

When I click on "agent" Rahab, I am expecting to have tbd in Specialty list box. At this point it shows nothing. It is expected to show tbd only.

mmelib.php
style type = "text/css">
body{
  background-color: lightgray;
  color: blue;
  text-align:center
  
}
.fieldset {
	padding-bottom:0px;
	padding-right:0px;
	padding-left:0px;
	width: 140px;
	}
.instruction {
	color: darkblue;
	}

</style>

<?php

error_reporting(E_ALL & ~E_NOTICE); // kg: get rid of those pesky run-time notices

// define the $cs85Username and $cs85Password variables with valid
// values for the cs85 user
//include('../../db_connection_info.inc');


//variables   //$cs85Username  $cs85Password
$userName = 'root';//$cs85Username; //kg
$password = 'PHPprog85';//$cs85Password; //kg
$serverName = "localhost";
//$adminPassword = "absolute";  //"absolute";
// $dbName = "classes";//smc version
$dbName = "harrisChapter12"; //laptop
$dbConn = "";
//$mainProgram = "spyMaster.php";

function connectToSpy(){
  //connects to the spy DB
  global $serverName, $userName, $password, $dbName, $bolReseted; //kg: add dbName to list
  $bolReseted=false;
  $dbConn = mysql_connect($serverName, $userName);//, $password);//mme
  //$dbConn = mysql_connect($serverName, $userName);//smc version
  if (!$dbConn){
   print "<h3>problem connecting to database...</h3>\n";
  } // end if
  
  $select = mysql_select_db($dbName); //kg: use dbName variable, not literal
  if (!$select){
    print mysql_error() . "<br>\n";
  } // end if
  return $dbConn;
} // end connectToSpy

/*
function qSearchUpdate() {
global $theAgent, $theSpecialty, $theOperation, $subject;

}
*/

function qToList($query){
  //given a query, makes a quick list of data
  global $dbConn;
  $output = "";
  $result = mysql_query($query, $dbConn);

  //print "dbConn is $dbConn<br>";
  //print "result is $result<br>";

  while ($row = mysql_fetch_assoc($result)){
    foreach ($row as $col=>$val){
      $output .= "$col: $val<br>\n";
    } // end foreach
    $output .= "<hr>\n" ;
  } // end while
  return $output;
} // end qToList

function resultToListbox($tabaleName, $isEmpty, $the_Agent, $the_Specialty, $the_Operation) {
	global $dbConn;
	


	if ($isEmpty=='') {
		$from  = " FROM agent a inner join operation o ON a.operationID = o.operationID JOIN "; 
		$from .= " agent_specialty asp ON a.agentID = asp.agentID JOIN specialty s ON "; 
		$from .= " asp.specialtyID = s.specialtyID ";
	} Else {
		if ($tabaleName=='agent') {	
			  $from  = " FROM agent a right join operation o ON a.operationID = o.operationID right JOIN ";
			  $from  .= " agent_specialty asp ON a.agentID = asp.agentID right JOIN ";
			  $from  .= " specialty s ON asp.specialtyID = s.specialtyID ";	
		} elseif ($tabaleName=='specialty') {
			  $from  = " FROM specialty s right join agent_specialty asp ON asp.specialtyID = s.specialtyID right JOIN ";
			  $from  .= " agent a ON a.agentID = asp.agentID right JOIN ";
			  $from  .= " operation o ON o.operationID = a.operationID ";
		} elseif ($tabaleName=='operation') {
			$from  = " FROM operation o right join agent a ON o.operationID = a.operationID right JOIN "; 
			$from .= " agent_specialty asp ON a.agentID = asp.agentID right JOIN "; 
			$from .= " specialty s ON asp.specialtyID = s.specialtyID ";
		}
	};
	
	if ($isEmpty=='') {
		$query = "SELECT name FROM $tabaleName Order By name";
	} Else {
		if ($tabaleName=='agent') {
			$query = "SELECT ifnull(a.name, 'tbd') AS 'name' ";
		} elseif ($tabaleName=='specialty') {
			 $query = "SELECT ifnull(s.name, 'tbd') AS 'name' "; 
		} elseif ($tabaleName=='operation') {
			 $query = "SELECT ifnull(o.name, 'tbd') AS 'name' ";
		}
		
		
		$query .=  $from . " Where ";
	
		if ($the_Agent =='') {
			$query .= " a.name = a.name ";
		} else {
			$query .= " a.name = '$the_Agent' ";
		}
		if ($the_Specialty =='') {
			$query .= " And s.name = s.name ";
		} else {
			$query .= " And s.name = '$the_Specialty' ";
		}
		if ($the_Operation =='') {
			$query .= " And o.name = o.name ";
		} else {
			$query .= " And o.name = '$the_Operation' ";
		}

		if ($tabaleName=='agent') {
			$query .= " Group By a.name Order By a.name; ";
		} elseif ($tabaleName=='specialty') {
			 $query .= " Group By s.name Order By s.name; "; 
		} elseif ($tabaleName=='operation') {
			 $query .= " Group By o.name Order By o.name; ";
		}
		
	};		
		
	$result = mysql_query($query, $dbConn);
	while($row = mysql_fetch_assoc($result)){
	$currentQuery = $row['text'];
	$theDescription = $row['name'];
print <<<HERE
      <option value = "$theDescription">$theDescription</option>
HERE;
  } // end while
} // end resultToListbox
function resultTest($tabaleName, $isEmpty, $the_Agent, $the_Specialty, $the_Operation) {
	//global $dbConn;
	
	$from  = " FROM agent a inner join operation o ON a.operationID = o.operationID JOIN "; 
	$from .= " agent_specialty asp ON a.agentID = asp.agentID JOIN specialty s ON "; 
	$from .= " asp.specialtyID = s.specialtyID ";
	
	if ($isEmpty=='') {
		$query = "SELECT name FROM $tabaleName Order By name";
	} Else {
		if ($tabaleName=='agent') {
			$query = "SELECT a.name AS 'name' ";
		} elseif ($tabaleName=='specialty') {
			 $query = "SELECT s.name AS 'name' "; 
		} elseif ($tabaleName=='operation') {
			 $query = "SELECT o.name AS 'name' ";
		}
		
		$query .=  $from . " Where ";
		
		if ($the_Agent =='') {
			$query .= " a.name = a.name ";
		} else {
			$query .= " a.name = '$the_Agent' ";
		}
		if ($the_Specialty =='') {
			$query .= " And s.name = s.name ";
		} else {
			$query .= " And s.name = '$the_Specialty' ";
		}
		if ($the_Operation =='') {
			$query .= " And o.name = o.name ";
		} else {
			$query .= " And o.name = '$the_Operation' ";
		}
		
		if ($tabaleName=='agent') {
			$query .= "Group By a.name Order By a.name;";
		} elseif ($tabaleName=='specialty') {
			 $query .= "Group By s.name Order By s.name;"; 
		} elseif ($tabaleName=='operation') {
			 $query .= "Group By s.name Order By o.name;";
		}
	};					
	return $query;	
} // end resultToListbox

?>

Open in new window

 

sliceNdice.php
<?php session_start() ?>
<?php  error_reporting(E_ALL) ?>
<html>
<head>
<title>Slice & Dice</title>
<script type="text/javascript">
	function resetCriteria(pram) {

		document.getElementById('instruction').innerHTML=
		   '<h2>Click on Submit or Rest to restart.</h2><h4>(You are not allowed to make two selections in a row.)</h4>';
		
		if (pram != "agent") { 
			<?php 
				$theAgent = null; 
				$_SESSION['theAgent']= $theAgent;
			?> 
		}

		if (pram != "specialty") { 
			<?php 
				$theSpecialty = null; 
				$_SESSION['theSpecialty']= $theSpecialty;
			?> 
		}

		if (pram != "operation") { 
			<?php 
				$theOperation = null; 
				$_SESSION['theOperation']= $theOperation;
			?> 
		}
	}
</script>
<?php
  include "mmeLib.php";
  $dbConn = connectToSpy();
	
// COPY NEW VARIABLES FROM POST INTO SESSION
$_SESSION['theAgent']     = (!empty($_POST['theAgent']))     ? $_POST['theAgent']     : $_SESSION['theAgent'];
$_SESSION['theSpecialty'] = (!empty($_POST['theSpecialty'])) ? $_POST['theSpecialty'] : $_SESSION['theSpecialty'];
$_SESSION['theOperation'] = (!empty($_POST['theOperation'])) ? $_POST['theOperation'] : $_SESSION['theOperation'];	

if (!empty($_POST['foo'])) {
    $_SESSION['theAgent'] = $_SESSION['theSpecialty'] = $_SESSION['theOperation'] = NULL;
	$tempStr ='';
} else {
	
	//$tempStr .= " 3: $theAgent , ";
	if(isset($_SESSION["theAgent"])){ $theAgent=$_SESSION["theAgent"] ;}
	if(isset($_SESSION["theSpecialty"])){ $theSpecialty=$_SESSION["theSpecialty"] ;}
	if(isset($_SESSION["theOperation"])){ $theOperation=$_SESSION["theOperation"] ;}
}

	
	if ($theAgent) {$tempStr .=  (($tempStr=='')? '':', ') . $theAgent;}
	if ($theSpecialty) {$tempStr .= (($tempStr=='')? '':', ') . $theSpecialty;}
	if ($theOperation) {$tempStr .=  (($tempStr=='')? '':', ') . $theOperation;}
?>

</head>
<body>
<!--
<form action="" method="post">
-->
<form id="sliceNdiceForm" name="sliceNdiceForm" action="" method="POST">
<table border = 0 width="50%" align="center">

 <!-- -------------------------------------------------------------Agent----------------------------------------------------- -->
		<td>
			<center>
				<fieldset>
				<legend>Select Agent:</legend>
					<select name="theAgent" class="myAgent" size = 10 
						style="width:140px;" onclick="resetCriteria('agent')">>
						<?php resultToListbox("agent", $tempStr, $theAgent, $theSpecialty, $theOperation); ?>
					</select>
				</fieldset>
			</center>
		</td>
 <!-- -------------------------------------------------------------Specialty----------------------------------------------------- -->
		<td>
			<center>
				<fieldset>
				<legend>Select Specialty:</legend>
					<select name="theSpecialty" class="mySpecialty" size = 10 
						style="width:140px;" onclick="resetCriteria('specialty')">
						<?php resultToListbox("specialty",$tempStr, $theAgent, $theSpecialty, $theOperation); ?>
					</select>
				</fieldset>
			</center>
		</td>
 <!-- -------------------------------------------------------------Operation----------------------------------------------------- -->
		<td>
			<center>
				<fieldset>
				<legend>Select Operation:</legend>
					<select name="theOperation" class="theOperation" size = 10 
						style="width:140px;" onclick="resetCriteria('operation')">
						<?php resultToListbox("operation",$tempStr, $theAgent, $theSpecialty, $theOperation); ?>
					</select>
				</fieldset>
			</center>
		</td>
	</tr>
<!-- -------------------------------------------------------------Reset----------------------------------------------------- -->

	<tr>
	  <td colspan="4"><center>
		<button type="submit" >Submit</button>
		<!-- <button type="reset" >Reset</button> -->
		<input type="submit" name="foo" value="Reset" />
	  </center></td>
	</tr>
</table>

</form>
<?php echo $tempStr; ?>
<div id="instruction" class="instruction">
<?php //echo resultTest('agent','fff','Bond', '', ''); ?>
</div
</body>
</html>

Open in new window


Please note that I have used three right join queries with each agent, operation, or specialty leading. The queries are tested in phpadmin with their output included in the attached image at the bottom of the image.

Thank you
tables.png
data.png
0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 11
  • 5
1 Solution
 
sivagnanam chandrakanthCommented:
Agent_speciality table is not having any datafor Rehab(ie, agentid "5")
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
You are right. This is why I am using IFNULL() along with right join to force it to produce 'tbd' when 5 doesn't have record in specialty table.  Please see the image (bottom right) where using phpadmin the query produces 'tbd'
0
 
sivagnanam chandrakanthCommented:
Can you please print the query here?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
You have it the code submitted (built in mmelib.php) but here it is. I don't have any problem with query it works fine  (see lower-right table titled "Right Join with specialty leading"). The problem is when I am using it in php.

SELECT 
	ifnull(a.name, 'tbd') AS 'agent', 
	ifnull(o.name, 'tbd') AS 'operation', 
	ifnull(s.name, 'tbd') as 'specialty'
FROM 
	specialty s right join agent_specialty asp      
ON 
	asp.specialtyID = s.specialtyID	
right JOIN	
	agent a  
ON 
	a.agentID = asp.agentID
right JOIN
	operation o
ON
	o.operationID = a.operationID

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
sivagnanam_c,

I need to embed echo .... to print query results to screen so I can locate the problem. I think you gave me something to do that a few months ago. But I don't remember it now.

I am trying to locate it.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I found this and put  it after line 146 (mmelib.php):

      $result = mysql_query($query, $dbConn);
      
//$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);

echo "$num_rows Rows\n";

but it doesn't print the result to the screen.
0
 
sivagnanam chandrakanthCommented:
If worked in PHPMYADMIN then it should also work in PHP..

May be there is a error in the way query is build, there are lots of if else statements.

So check whether you are building required query in PHP by printing it.

print this on line 145 in mmelib.php and check

echo $query; exit;
0
 
sivagnanam chandrakanthCommented:
call resultToListbox("specialty",$tempStr, $theAgent, $theSpecialty, $theOperation); outside select tag to see the printed query
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I tried the following but nothing shows up on the screen.

 <!-- -------------------------------------------------------------Agent----------------------------------------------------- -->
		<td>
			<center>
				<fieldset>
				<legend>Select Agent:</legend>
					<select name="theAgent" class="myAgent" size = 10 
						style="width:140px;" onclick="resetCriteria('agent')">>
						<?php resultToListbox("agent", $tempStr, $theAgent, $theSpecialty, $theOperation); ?>
					</select>
					<?php echo resultToListbox("agent", $tempStr, $theAgent, $theSpecialty, $theOperation); ?>
				</fieldset>
			</center>
		</td>

Open in new window


Starting tomorrow 5 am I will start from chapter one of my book to review it all. What little I have learnt, almost already forgotten.

Question: Why the following line doesn't work?

</select>
<?php echo resultToListbox("agent", $tempStr, $theAgent, $theSpecialty, $theOperation); ?>
</fieldset>
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
It will be nice to have both the query and its data to be printed if possible.
0
 
sivagnanam chandrakanthCommented:
echo $query; exit; change this to return $query; exit; in memlib.php on line no 145
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thanks for the tip. I was able to have the following on the screen:

SELECT IFNULL( a.name,  'tbd' ) AS  'name'
FROM agent a
RIGHT JOIN operation o ON a.operationID = o.operationID
RIGHT JOIN agent_specialty asp ON a.agentID = asp.agentID
RIGHT JOIN specialty s ON asp.specialtyID = s.specialtyID
WHERE a.name =  'Rahab'
AND s.name = s.name
AND o.name = o.name
GROUP BY a.name
ORDER BY a.name
LIMIT 0 , 30

which is expected to return the following single record:

Rehab

But it doesn't.

My last post and my last question: What is wrong with this query?

Thank you,

Mike
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Even with WHERE clause removed I done get Rehab:

SELECT IFNULL( a.name,  'tbd' ) AS  'name'
FROM agent a
RIGHT JOIN operation o ON a.operationID = o.operationID
RIGHT JOIN agent_specialty asp ON a.agentID = asp.agentID
RIGHT JOIN specialty s ON asp.specialtyID = s.specialtyID
GROUP BY a.name
ORDER BY a.name
LIMIT 0 , 30

Open in new window


So, my right joins are not correct.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
This works without WHERE clause:

SELECT ifnull(a.name, 'tbd') AS 'name'
FROM agent a
right join operation o ON a.operationID = o.operationID
left JOIN agent_specialty asp ON a.agentID = asp.agentID
left JOIN specialty s ON asp.specialtyID = s.specialtyID  
Group By a.name
Order By a.name;


Where a.name = 'Rahab'
And s.name = s.name
And o.name = o.name
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
this works:

SELECT IFNULL( a.name,  'tbd' ) AS  'name'
FROM agent a
RIGHT JOIN operation o ON a.operationID = o.operationID
LEFT JOIN agent_specialty asp ON a.agentID = asp.agentID
LEFT JOIN specialty s ON asp.specialtyID = s.specialtyID
WHERE a.name =  'Rahab'
GROUP BY a.name
ORDER BY a.name
LIMIT 0 , 30
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now