Solved

MySQL right join problem... php

Posted on 2013-01-15
16
372 Views
Last Modified: 2013-01-16
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
Comment
Question by:Mike Eghtebas
  • 11
  • 5
16 Comments
 
LVL 12

Expert Comment

by:sivagnanam chandrakanth
ID: 38781376
Agent_speciality table is not having any datafor Rehab(ie, agentid "5")
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38781451
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
 
LVL 12

Expert Comment

by:sivagnanam chandrakanth
ID: 38781465
Can you please print the query here?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38781491
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38781529
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38781559
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
 
LVL 12

Expert Comment

by:sivagnanam chandrakanth
ID: 38781564
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
 
LVL 12

Expert Comment

by:sivagnanam chandrakanth
ID: 38781567
call resultToListbox("specialty",$tempStr, $theAgent, $theSpecialty, $theOperation); outside select tag to see the printed query
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38781608
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38781621
It will be nice to have both the query and its data to be printed if possible.
0
 
LVL 12

Accepted Solution

by:
sivagnanam chandrakanth earned 500 total points
ID: 38781758
echo $query; exit; change this to return $query; exit; in memlib.php on line no 145
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38783327
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38783394
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38783539
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 38783562
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
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 38783566
Thank you.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now