Use Variables to find Other SQL Values

[Sitehelp]

Not sure if I am on the correct forum here but I am using Dreamweaver MX. Anyway the problem is that I have a list of all the calls opened by users on the screen, all brought up from the DB using the Dynamic Table option. It all works great, I have 3 options at the end of each row: Modify, Delete and View. Modify and delete work fine, View, when clicked, is meant to bring up a page displaying the record you just clicked on in its entirety. This all works fine to an extent, it displays all the fields in the record, but however, I want it to then look at one of the fields its copied accross, clientID, and then look in abother table in the DB to find out which department they are in so it can display it along with the other details, this is a very important feature in my site. I am unsure how to do this as this code doesnt work:

SELECT clientinfo.CDept
FROM clientinfo
WHERE clientinfo.ClientID = '$ClientID'

$ClientID being the text field that displays the clientid. It may be the order I have my code in but I have tried changing it and it makes no difference, all other txt fields appear fine. I am new at all this. Hope this makes sense. Can anyone help??????????? thanks!

 

[Chacalinc]

you can issue a query to 2 tables with only one select statment, example:

table1
------
id int
name string
phone string

table2
-------
id int
dapertment string

SELECT t1.* t2.department FROM table1 t1, table2 t2 WHERE t1.id='$CLientID' AND t1.id=t2.id

or something like this...

Cheers.

 

[Sitehelp]

I have my code as:
<?php require_once('../../../Connections/MARTIN.php'); ?>
<?php
mysql_select_db($database_MARTIN, $MARTIN);
$query_ViewCall = "SELECT * FROM callinfo WHERE callinfo.CallID = '$CallID'";
$ViewCall = mysql_query($query_ViewCall, $MARTIN) or die(mysql_error());
$row_ViewCall = mysql_fetch_assoc($ViewCall);
$totalRows_ViewCall = mysql_num_rows($ViewCall);

mysql_select_db($database_MARTIN, $MARTIN);
$query_ClientDept = "SELECT clientinfo.CDept FROM clientinfo WHERE clientinfo.ClientID = '$ClientID' ";
$ClientDept = mysql_query($query_ClientDept, $MARTIN) or die(mysql_error());
$row_ClientDept = mysql_fetch_assoc($ClientDept);
$totalRows_ClientDept = mysql_num_rows($ClientDept);
?>

In order to first get the CallID that is passing over and match it to the DB to diaply the results, then I look at the clientID that has just been taken from the DB and match it to that clients Department, however it doesnt display anything in thge department text box at all???? All the information is in the same form. Any ideas, could be the layout of the code but I dont know how to improve on it? Can print more code out if necessary, thanks!

 

[sleipnir214]

I have a section on debugging database code in my FAQ on debugging PHP: faq434-2999

Want the best answers? Ask the best questions

TANSTAAFL!!

 

[Sitehelp]

I read through your FAQ and understand what its saying, however It is showing all the other fields fine its just not showing one of the text boxes data as this text box looks at the information its put in the other boxes and then from them gets the field for this text box. for example, the user clicks to delete a call, the next page shows a brief description of the call to confirm they want it deleteing (this is working fine) however it also shows an extra text box with the value stored in a different DB and from the call information it works out what department that call is in. does this make sense? This is not happening though!

 

[sleipnir214]

Could you repost your last post, but this time with more punctuation? I'm having a hard time parsing your post.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

Sorry I have a habbit of writing quickly and it ends up like this. Ok to re-iterate:

I read through your FAQ and understand what its saying, however the situation its in now is:

1) The user gets presented with a table of all open calls, next to each call is the option to view it. They click view to view more information about this call.
2) The page appears with all the information related to that call in. This appears fine on my site.
3) There is an additional field on the page that tells you the department the user who logged the call is from. This information is stored in another Database and thus joining the call information database and the database with the department is necessary.

It is not showing the department the user is located in. It brings up ALL the call information using the PHP/SQL statement:

<?php require_once('../../../Connections/MARTIN.php'); ?>
<?php
mysql_select_db($database_MARTIN, $MARTIN);
$query_ViewCall = "SELECT * FROM callinfo WHERE callinfo.CallID = '$CallID'";
$ViewCall = mysql_query($query_ViewCall, $MARTIN) or die(mysql_error());
$row_ViewCall = mysql_fetch_assoc($ViewCall);
$totalRows_ViewCall = mysql_num_rows($ViewCall);

BUT the next statement does not appear to work at all. There is no error message, instead, no data appears in the text box. The next statement is:


mysql_select_db($database_MARTIN, $MARTIN);
$query_ClientDept = "SELECT clientinfo.CDept FROM clientinfo WHERE clientinfo.ClientID = '$ClientID' ";
$ClientDept = mysql_query($query_ClientDept, $MARTIN) or die(mysql_error());
$row_ClientDept = mysql_fetch_assoc($ClientDept);
$totalRows_ClientDept = mysql_num_rows($ClientDept);
?>

Do you think it is because the layout of the code???? if so I have no idea how to change this as I have tried. Hope this is clearer! Cheers!

 

[sleipnir214]

I see nothing structurally wrong with your code.

It could be a bad query.

It could be the structure of your code -- frankly, you tend to structure your code like a console application, not a web application.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

Yeah I think its the whole structure of it I have problems with! Its written like this at the moment:

<?php require_once('../../../Connections/MARTIN.php'); ?>
<?php
mysql_select_db($database_MARTIN, $MARTIN);
$query_ViewCall = "SELECT * FROM callinfo WHERE callinfo.CallID = '$CallID'";
$ViewCall = mysql_query($query_ViewCall, $MARTIN) or die(mysql_error());
$row_ViewCall = mysql_fetch_assoc($ViewCall);
$totalRows_ViewCall = mysql_num_rows($ViewCall);

mysql_select_db($database_MARTIN, $MARTIN);
$query_ClientDept = "SELECT clientinfo.CDept FROM clientinfo WHERE clientinfo.ClientID = '$ClientID' ";
$ClientDept = mysql_query($query_ClientDept, $MARTIN) or die(mysql_error());
$row_ClientDept = mysql_fetch_assoc($ClientDept);
$totalRows_ClientDept = mysql_num_rows($ClientDept);
?>

<html><!-- InstanceBegin template="/Templates/Administrator HelpOnlineTemplate.dwt" codeOutsideHTMLIsLocked="false" -->
<head>
<!-- InstanceBeginEditable name="doctitle" -->
<title>HelpOnlineTemplate - Admin.gif</title>
<!-- InstanceEndEditable -->
<meta http-equiv="Content-Type" content="text/html;">
<!-- Fireworks MX Dreamweaver MX target. Created Wed Jan 21 18:10:42 GMT+0000 (GMT Standard Time) 2004-->
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) { //reloads the window if Nav4 resized
if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);

function MM_preloadImages() { //v3.0
var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
if (a.indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a;}}
}

function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers.document);
if(!x && d.getElementById) x=d.getElementById; return x;
}

function MM_nbGroup(event, grpName) { //v6.0
var i,img,nbArr,args=MM_nbGroup.arguments;
if (event == "init" && args.length > 2) {
if ((img = MM_findObj(args[2])) != null && !img.MM_init) {
img.MM_init = true; img.MM_up = args[3]; img.MM_dn = img.src;
if ((nbArr = document[grpName]) == null) nbArr = document[grpName] = new Array();
nbArr[nbArr.length] = img;
for (i=4; i < args.length-1; i+=2) if ((img = MM_findObj(args)) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = img.MM_dn = args[i+1];
nbArr[nbArr.length] = img;
} }
} else if (event == "over") {
document.MM_nbOver = nbArr = new Array();
for (i=1; i < args.length-1; i+=3) if ((img = MM_findObj(args)) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = (img.MM_dn && args[i+2]) ? args[i+2] : ((args[i+1])? args[i+1] : img.MM_up);
nbArr[nbArr.length] = img;
}
} else if (event == "out" ) {
for (i=0; i < document.MM_nbOver.length; i++) {
img = document.MM_nbOver; img.src = (img.MM_dn) ? img.MM_dn : img.MM_up; }
} else if (event == "down") {
nbArr = document[grpName];
if (nbArr)
for (i=0; i < nbArr.length; i++) { img=nbArr; img.src = img.MM_up; img.MM_dn = 0; }
document[grpName] = nbArr = new Array();
for (i=2; i < args.length-1; i+=2) if ((img = MM_findObj(args)) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = img.MM_dn = (args[i+1])? args[i+1] : img.MM_up;
nbArr[nbArr.length] = img;
} }
}
//-->
</script>
<!-- InstanceBeginEditable name="head" --> <!-- InstanceEndEditable -->
</head>
<body bgcolor="#ffffff" onLoad="MM_preloadImages('../../Templates/Admin%20Template/Buttons/Additional%20Button.gif','../../Templates/Admin%20Template/Buttons/Users%20Highlighted%20Button.gif','../../Templates/Admin%20Template/Buttons/Log%20Out%20Highlighted%20Button.gif','../../Templates/Admin%20Template/Buttons/Call%20Highlighted%20Button.gif')">
<div id="Layer1" style="position:absolute; left:22px; top:156px; width:102px; height:141px; z-index:1"><!-- #BeginLibraryItem "/Library/Administrator Buttons.lbi" --><script language="JavaScript" type="text/JavaScript">
<!--
function MM_preloadImages() { //v3.0
var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
if (a.indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a;}}
}

function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers.document);
if(!x && d.getElementById) x=d.getElementById; return x;
}

function MM_nbGroup(event, grpName) { //v6.0
var i,img,nbArr,args=MM_nbGroup.arguments;
if (event == "init" && args.length > 2) {
if ((img = MM_findObj(args[2])) != null && !img.MM_init) {
img.MM_init = true; img.MM_up = args[3]; img.MM_dn = img.src;
if ((nbArr = document[grpName]) == null) nbArr = document[grpName] = new Array();
nbArr[nbArr.length] = img;
for (i=4; i < args.length-1; i+=2) if ((img = MM_findObj(args)) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = img.MM_dn = args[i+1];
nbArr[nbArr.length] = img;
} }
} else if (event == "over") {
document.MM_nbOver = nbArr = new Array();
for (i=1; i < args.length-1; i+=3) if ((img = MM_findObj(args)) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = (img.MM_dn && args[i+2]) ? args[i+2] : ((args[i+1])? args[i+1] : img.MM_up);
nbArr[nbArr.length] = img;
}
} else if (event == "out" ) {
for (i=0; i < document.MM_nbOver.length; i++) {
img = document.MM_nbOver; img.src = (img.MM_dn) ? img.MM_dn : img.MM_up; }
} else if (event == "down") {
nbArr = document[grpName];
if (nbArr)
for (i=0; i < nbArr.length; i++) { img=nbArr; img.src = img.MM_up; img.MM_dn = 0; }
document[grpName] = nbArr = new Array();
for (i=2; i < args.length-1; i+=2) if ((img = MM_findObj(args)) != null) {
if (!img.MM_up) img.MM_up = img.src;
img.src = img.MM_dn = (args[i+1])? args[i+1] : img.MM_up;
nbArr[nbArr.length] = img;
} }
}
//-->
</script>
<body onLoad="MM_preloadImages('../Project/Templates/Admin%20Template/Buttons/Additional%20Button.gif','../Project/Templates/Admin%20Template/Buttons/Users%20Highlighted%20Button.gif','../Project/Templates/Admin%20Template/Buttons/Log%20Out%20Highlighted%20Button.gif','../Project/Templates/Admin%20Template/Buttons/Call%20Highlighted%20Button.gif','../Project/Templates/Admin%20Template/Buttons3(User)/Highlighted%20Home%20Button3.gif')">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td><a href="../WelcomeAdminPage.php" target="_top" onClick="MM_nbGroup('down','group1','Home','',1)" onMouseOver="MM_nbGroup('over','Home','../../Templates/Admin%20Template/Buttons3(User)/Highlighted%20Home%20Button3.gif','',1)" onMouseOut="MM_nbGroup('out')"><img name="Home" src="../../Templates/Admin%20Template/Buttons3(User)/Home%20Button3.gif" border="0" alt="Back to Home Page" onLoad=""></a></td>
</tr>
<tr>
<td><a href="ViewCallOptions.php" target="_top" onClick="MM_nbGroup('down','group1','Calls','',1)" onMouseOver="MM_nbGroup('over','Calls','../../Templates/Admin%20Template/Buttons/Call%20Highlighted%20Button.gif','',1)" onMouseOut="MM_nbGroup('out')"><img src="../../Templates/Admin%20Template/Buttons/Call%20Button.gif" alt="View Call Options" name="Calls" width="95" height="26" border="0" onload=""></a></td>
</tr>
<tr>
<td><a href="../Staff/ViewStaffOptions.php" target="_top" onClick="MM_nbGroup('down','group1','Staff','',1)" onMouseOver="MM_nbGroup('over','Staff','../../Templates/Admin%20Template/Buttons/Additional%20Button.gif','',1)" onMouseOut="MM_nbGroup('out')"><img name="Staff" src="../../Templates/Admin%20Template/Buttons/Additional%20Highlighted%20Button.gif" border="0" alt="View Staff Options" onLoad=""></a></td>
</tr>
<tr>
<td><a href="../Users/ViewUserOptions.php" target="_top" onClick="MM_nbGroup('down','group1','User','',1)" onMouseOver="MM_nbGroup('over','User','../../Templates/Admin%20Template/Buttons/Users%20Highlighted%20Button.gif','',1)" onMouseOut="MM_nbGroup('out')"><img name="User" src="../../Templates/Admin%20Template/Buttons/Users%20Button.gif" border="0" alt="View User Options" onLoad=""></a></td>
</tr>
<tr>
<td><a href="../../HomePage.php" target="_top" onClick="MM_nbGroup('down','group1','LogOut','',1)" onMouseOver="MM_nbGroup('over','LogOut','../../Templates/Admin%20Template/Buttons/Log%20Out%20Highlighted%20Button.gif','',1)" onMouseOut="MM_nbGroup('out')"><img name="LogOut" src="../../Templates/Admin%20Template/Buttons/Log%20Out%20Button.gif" border="0" alt="Logout" onLoad=""></a></td>
</tr>
</table>
<!-- #EndLibraryItem --></div>
<table bgcolor="#ffffff" border="0" cellpadding="0" cellspacing="0" width="799">
<!-- fwtable fwsrc="HelpOnlineTemplate.png" fwbase="HelpOnlineTemplate - Admin.gif" fwstyle="Dreamweaver" fwdocid = "742308039" fwnested="0" -->
<tr>
<td><img src="../../Templates/Admin%20Template/spacer.gif" width="121" height="1" border="0" alt=""></td>
<td><img src="../../Templates/Admin%20Template/spacer.gif" width="678" height="1" border="0" alt=""></td>
<td><img src="../../Templates/Admin%20Template/spacer.gif" width="1" height="1" border="0" alt=""></td>
</tr>

<tr>
<td colspan="2"><img name="HelpOnlineTemplate2020Admin_r1_c1" src="../../Templates/Admin%20Template/HelpOnlineTemplate%20-%20Admin_r1_c1.gif" width="799" height="89" border="0" alt=""></td>
<td><img src="../../Templates/Admin%20Template/spacer.gif" width="1" height="89" border="0" alt=""></td>
</tr>
<tr>
<td><img name="HelpOnlineTemplate2020Admin_r2_c1" src="../../Templates/Admin%20Template/HelpOnlineTemplate%20-%20Admin_r2_c1.gif" width="121" height="510" border="0" alt=""></td>
<td valign="top" bgcolor="#ffffff"><!-- InstanceBeginEditable name="MainCenter" -->
<p><font size="5">View Call</font></p>
<form name="form1" method="post" action="">
<table width="87%" border="0" align="center">
<tr>
<td width="46%">Call ID:</td>
<td width="54%"><input name="CallID" type="text" disabled="true" id="CallID" value="<?php echo $HTTP_GET_VARS['CallID']; ?>" readonly="true"></td>
</tr>
<tr>
<td>Client who logged call:</td>
<td><input name="ClientID" type="text" id="ClientID" value="<?php echo $row_ViewCall['ClientID']; ?>"></td>
</tr>
<tr>
<td>Department:</td>
<td><input name="CDept" type="text" id="CDept" value="<?php echo $row_ClientDept['CDept']; ?>"></td>
</tr>
<tr>
<td>Call Status</td>
<td><input name="CStatus" type="text" disabled="true" id="CStatus" value="<?php echo $row_ViewCall['CallStatus']; ?>" readonly="true"></td>
</tr>
<tr>
<td>Staff assigned to call:</td>
<td><input name="StaffID" type="text" disabled="true" id="StaffID" value="<?php echo $row_ViewCall['StaffID']; ?>" readonly="true"></td>
</tr>
<tr>
<td>Call Header:</td>
<td><input name="CHeader" type="text" disabled="true" id="CHeader" value="<?php echo $row_ViewCall['Call Header']; ?>" readonly="true"></td>
</tr>
<tr>
<td>Date Logged:</td>
<td><input name="CDate" type="text" disabled="true" id="CDate" value="<?php echo $row_ViewCall['CallDateandTime']; ?>" readonly="true"></td>
</tr>
<tr>
<td>Priority:</td>
<td><input name="CPriority" type="text" disabled="true" id="CPriority" value="<?php echo $row_ViewCall['Priority']; ?>" readonly="true"></td>
</tr>
<tr>
<td>Call Description:</td>
<td><textarea name="CDescription" cols="50" rows="" disabled="disabled" readonly="readonly" id="CDescription"><?php echo $row_ViewCall['Call Description']; ?></textarea></td>
</tr>
</table>
</form>
<p><a href="DeleteCall.php">Back</a></p>
<p align="center"></p>
<!-- InstanceEndEditable --></td>
<td><img src="../../Templates/Admin%20Template/spacer.gif" width="1" height="510" border="0" alt=""></td>
</tr>
</table>
</body>
<!-- InstanceEnd -->
</html>
<?php
mysql_free_result($ViewCall);

mysql_free_result($ClientDept);
?>


I agree this does look a bit confusing! would this layout effect the department from appearing????????????????

 

[sleipnir214]

WWWebDesign:
I don't know what you hoped to accomplish by posting ~200 lines of Dreamweaver-generated code. No offence intended, but I'm certainly not going to plow through it to see what's going on.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

Whoops sorry I had no idea how much I posted it was only supposed to be the first quarter! anyways it doesnt matter now I have the solution.

I decided to post the clientID across as well as the CallID in the URL as these arnt meant to be secure! this works great, I think I am going to look into layout of php code as I think this is a good step to go in order to solve some of my problems! thanks and sorry about the many lines I just sent, wasnt intended! cheers!