?
Solved

Record Update php problem - 500 pts

Posted on 2006-03-27
15
Medium Priority
?
216 Views
Last Modified: 2010-04-25
Hi

My problem is as follows

3 screens

screen1 - has two drop down menus each with three choices, user selects an option from each and hits enter which brings the user to screen 2
screen2 - displays the results of screen one with an edit link beside each record which when you clilck brings you to screen 3 - should display the all the information about the record that was selected in screen 2

My problelm is that screens 1 and 2 work but when I click on screen 3 it gives the error message "There is an error in your SQL syntax Check the manual that corresponds to your MYSQL server version for the right syntax to use"

I used the Update record server behaviour in dreamweaver mx so am unsure how the SQL could be wrong.

the error must be on screen two?   Can anyone please help as this is getting rather urgent!!!   Thank You


Heres the code for each screen


*********************************************************************
Screen 1
*********************************************************************

<?php require_once('Connections/connect_db.php'); ?><?php
mysql_select_db($database_connect_db, $connect_db);
$query_techrs = "SELECT DISTINCT TechID FROM tech";
$techrs = mysql_query($query_techrs, $connect_db) or die(mysql_error());
$row_techrs = mysql_fetch_assoc($techrs);
$totalRows_techrs = mysql_num_rows($techrs);

mysql_select_db($database_connect_db, $connect_db);
$query_rsstate = "SELECT DISTINCT Sting_Problem_Record_state FROM problem_record";
$rsstate = mysql_query($query_rsstate, $connect_db) or die(mysql_error());
$row_rsstate = mysql_fetch_assoc($rsstate);
$totalRows_rsstate = mysql_num_rows($rsstate);
?>
<title>Technician</title><style type="text/css">
<!--
body,td,th {
      color: #000000;
}
body {
      background-color: #00CCFF;
}
-->
</style>

<h2 align="center">View Problem Records for a Technician</h2>
<form name="form1" method="post" action="Tech2.php">
  <p>&nbsp;</p>
  <p align="center">Please Select a Technician ID
    <select name="select">
      <option value="">Select a Tech</option>
      <?php
do {  
?>
      <option value="<?php echo $row_techrs['TechID']?>"><?php echo $row_techrs['TechID']?></option>
      <?php
} while ($row_techrs = mysql_fetch_assoc($techrs));
  $rows = mysql_num_rows($techrs);
  if($rows > 0) {
      mysql_data_seek($techrs, 0);
        $row_techrs = mysql_fetch_assoc($techrs);
  }
?>
    </select>
</p>
  <p align="center">Please Select a Problem Record State
    <select name="select2" title="<?php echo $row_rsstate['Sting_Problem_Record_state']; ?>">
      <option value="">Select a state</option>
      <?php
do {  
?>
      <option value="<?php echo $row_rsstate['Sting_Problem_Record_state']?>"><?php echo $row_rsstate['Sting_Problem_Record_state']?></option>
      <?php
} while ($row_rsstate = mysql_fetch_assoc($rsstate));
  $rows = mysql_num_rows($rsstate);
  if($rows > 0) {
      mysql_data_seek($rsstate, 0);
        $row_rsstate = mysql_fetch_assoc($rsstate);
  }
?>
    </select>
  </p>
  <p align="center">&nbsp;</p>
  <p align="center">
    <input type="submit" name="Submit" value="Submit">
</p>
</form>
<p>&nbsp;</p>
<p>&nbsp; </p>
<?php
mysql_free_result($techrs);

mysql_free_result($rsstate);
?>

*******************************************

Screen 2
********************************************

<?php require_once('Connections/connect_db.php'); ?>
<?php require_once('Connections/connect_db.php'); ?>
<?php
mysql_select_db($database_connect_db, $connect_db);
$query_relocations = "SELECT * FROM problem_record";
$relocations = mysql_query($query_relocations, $connect_db) or die(mysql_error());
$row_relocations = mysql_fetch_assoc($relocations);
$totalRows_relocations = mysql_num_rows($relocations);

$colname_uptechrs2 = "1";
if (isset($_GET['recordID'])) {
  $colname_uptechrs2 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_connect_db, $connect_db);
$query_uptechrs2 = sprintf("SELECT * FROM problem_record WHERE Problem_Record_ID = %s", $colname_uptechrs2);
$uptechrs2 = mysql_query($query_uptechrs2, $connect_db) or die(mysql_error());
$row_uptechrs2 = mysql_fetch_assoc($uptechrs2);
$totalRows_uptechrs2 = mysql_num_rows($uptechrs2);
?>
<title>Select a Problem Record</title>
<title>Reports</title>
<?php
require_once('Connections/connect_db.php');
mysql_select_db($database_connect_db, $connect_db);
$TechID=$_REQUEST[select];
$state=$_REQUEST[select2];

$sql="SELECT * FROM Problem_record where Num_Tech_assigned='$TechID'AND sting_Problem_Record_state='$state'";
$result=mysql_query($sql);

?>

<style type="text/css">
<!--
body,td,th {
      color: #000000;
}
body {
      background-color: #00CCFF;
}
-->
</style><p>&nbsp;</p>
<h2 align="center">Results</h2>
<p>&nbsp;</p>
<table width="1424" border="1">
  <caption>&nbsp;
  </caption>
  <tr>
    <td width="70"><strong>Problem Record Number</strong></td>
    <td width="51"><strong>Client ID </strong></td>
    <td width="68"><strong>Severity</strong></td>
    <td width="72"><strong>Sub category </strong></td>
    <td width="92"><strong>Description </strong></td>
      <td width="189"><strong>Update </strong></td>
    <td width="195"><strong>Resolution</strong></td>
      <td width="609"><strong>Select a Record to Edit </strong></td>
      <td width="20">&nbsp;</td>
  </tr>
  <?php
while($row=mysql_fetch_array($result)) {
?>
  <tr>
    <td height="23"><?php echo $row[Problem_Record_ID]; ?></td>
    <td><?php echo $row[String_Client_ID]; ?></td>
    <td><?php echo $row[String_Problem_Record_Severity]; ?></td>
    <td><?php echo $row[String_Problem_record_subcategory]; ?></td>
    <td><?php echo $row[String_Problem_Record_Description]; ?></td>
      <td><?php echo $row[String_Problem_Record_Update]; ?></td>
    <td><?php echo $row[String_Problem_Record_Resolution]; ?></td>
   
    <td><a href="Tech3.php?recordID=<?php echo $row_uptechrs2['Problem_Record_ID']; ?>">Edit</a></td>
    <?php } ?>
</table>
<?php
mysql_free_result($relocations);

mysql_free_result($uptechrs2);
?>

************************************************************************************
Screen 3
***********************************************************************************

<?php require_once('Connections/connect_db.php'); ?>
<?php
$colname_Recordset1 = "1";
if (isset($_GET['recordID'])) {
  $colname_Recordset1 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_connect_db, $connect_db);
$query_Recordset1 = sprintf("SELECT * FROM problem_record WHERE Problem_Record_ID = %s", $colname_Recordset1);
$Recordset1 = mysql_query($query_Recordset1, $connect_db) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
Updating record for <?php echo $row_Recordset1['Problem_Record_ID']; ?>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
0
Comment
Question by:7704300
  • 6
  • 6
12 Comments
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 16304795
Hi 7704300,

It looks to me like there are several likely failure points with this script.  

Screen 1 submits two form values to Tech2.php.  But the recordset commands in Tech2.php is using $_GET instead of $_POST to pull records:

if (isset($_GET['recordID'])) {
  $colname_uptechrs2 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}

When you created that recordset, you must have selected URL parameter instead of Form Parameter.  Change the line above to use $_POST or redo the recordset wizard to use Form Parameter.

$_GET is used with the query string (i.e., tech2.php?id=1).  $_POST is for when you set values in a form.

Next:

require_once('Connections/connect_db.php');
mysql_select_db($database_connect_db, $connect_db);
$TechID=$_REQUEST[select];
$state=$_REQUEST[select2];
$sql="SELECT * FROM Problem_record where Num_Tech_assigned='$TechID'AND sting_Problem_Record_state='$state'";

1) You can delete require_once.  You already have it (twice) at the top of the file.

2) For $TechID and $state, why are you using $_REQUEST and not $_POST? Did DW write this part of the code or did you hand code it?  If it is passing the variables correctly, don't change it.  Just looks odd

3) That SQL query doesn't look right.  When you get to tech2.php in your browser, view source and see if it is pulling up the information from your table.  If recordID in this line <a href="Tech3.php?recordID=<?php echo $row_uptechrs2['Problem_Record_ID']; ?>"> is blank, then your SQL is failing and the next page will fail as well.

Next:

1) On Tech3.php, there is no update record behavior.  You have a correct $_GET applied since you are passing Problem_Record_ID through the URL now but all this page is doing is pulling up a row from problem_record.  If $_GET['recordID'] is null or blank though, this page will die as the SQL resolves to:

SELECT * FROM problem_record WHERE Problem_Record_ID =

That could cause an invalid syntax error...






0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 16305012
Sorry, one more thought:

The problem is most likely a variable or variables not being passed correctly.  You can troubleshoot this yourself by using the echo command to print out a list of variables on each page.

So for tech2.php, add the following right after the body tag:

<?php

echo "Record ID = $_GET['recordID']";
echo "Tech ID = $TechID";
echo "State = $state";
//and so on

?>

If an expected variable comes up blank, you now know where to start looking for problems.
0
 

Author Comment

by:7704300
ID: 16310547
Hi Jason,

Good to know your on the case!  Hopefully we can get to the bottom of this one : )

In answer to your points

Screen 1 submits two form values to Tech2.php.  But the recordset commands in Tech2.php is using $_GET instead of $_POST to pull records:

if (isset($_GET['recordID'])) {
  $colname_uptechrs2 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}

When you created that recordset, you must have selected URL parameter instead of Form Parameter.  Change the line above to use $_POST or redo the recordset wizard to use Form Parameter.

$_GET is used with the query string (i.e., tech2.php?id=1).  $_POST is for when you set values in a form.

************************************************************************************
ANSWER
 - I changed $_GET to $_POST as recommended and it made no difference.  I also tried to change my uptechrs recordset to Form Paramer, this does not exist - FORM Variable does, I changed url parameter to this and again no change was visible
***********************************************************************************

NEXT
require_once('Connections/connect_db.php');
mysql_select_db($database_connect_db, $connect_db);
$TechID=$_REQUEST[select];
$state=$_REQUEST[select2];
$sql="SELECT * FROM Problem_record where Num_Tech_assigned='$TechID'AND sting_Problem_Record_state='$state'";

1) You can delete require_once.  You already have it (twice) at the top of the file.

************************************************************************************
DONE - No CHANGE in PROBLEM
************************************************************************************

2) For $TechID and $state, why are you using $_REQUEST and not $_POST? Did DW write this part of the code or did you hand code it?  If it is passing the variables correctly, don't change it.  Just looks odd

**********************************************************************************
DW DID THIS FOR ME SEEMS LIKE ITS WORKING OK
*********** ***********************************************************************


3) That SQL query doesn't look right.  When you get to tech2.php in your browser, view source and see if it is pulling up the information from your table.  If recordID in this line <a href="Tech3.php?recordID=<?php echo $row_uptechrs2['Problem_Record_ID']; ?>"> is blank, then your SQL is failing and the next page will fail as well.

************************************************************************************
Looks like this could be a problem I see the line you are referring for each record but it looks like this

a href="Tech3.php?recordID=">EDIT</a> </td>

Could this be the main problem??

**************************************************************************************

1) On Tech3.php, there is no update record behavior.  You have a correct $_GET applied since you are passing Problem_Record_ID through the URL now but all this page is doing is pulling up a row from problem_record.  If $_GET['recordID'] is null or blank though, this page will die as the SQL resolves to:

SELECT * FROM problem_record WHERE Problem_Record_ID =

That could cause an invalid syntax error...


*************************************************************************************
I edited this page to just print out the problem record id from tech2.  I just wanted to simplify it as much as possible just to narrow the problem down to a particular page

**************************************************************************************

Thanks for helping me on this one.   Let me know if you still want me to echo the statements as you suggest in your second post.  I figured there might be food for thought in my reply so I'll let you think about this first!

Thanks again

0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 16313184
Hiya again.  Assuming there are no session variables, we can fix this ;)

Okay, first  _GET vs. _POST:

Go back to the original way you had it, but change this line in the first page:

<form name="form1" method="post" action="Tech2.php">

to

<form name="form1" method="get" action="Tech2.php">

This will settle my mind AND as a bonus, you will see the selections from Tech1.php in the address bar of the browser (tech2.php?something=somthing&something2=something2)

Next:

Deleting the extra calls to connect_db.php is just good coding practice...a require_once command only needs be issued once.

SQL:

This is the problem.  Obviously, if the $row_uptechrs2['Problem_Record_ID'] is not evaluating, then the script falls apart at this point and you will get a SQL syntax error on the recordset in Tech3.  So now we need to figure out what is not working in Tech2.

Make the change in the tech1 form tag, and add the echo statements in tech 2 and post back.
0
 

Author Comment

by:7704300
ID: 16324360
Hi Jason,

tech 1 form has been updated and is still working correctly

for the echo statements in tech 2 does this existing part in tech 2 cover this??

Let me know what you think.   Thanks for the help.

body,td,th {
     color: #000000;
}
body {
     background-color: #00CCFF;
}
-->
</style><p>&nbsp;</p>
<h2 align="center">Results</h2>
<p>&nbsp;</p>
<table width="1424" border="1">
  <caption>&nbsp;
  </caption>
  <tr>
    <td width="70"><strong>Problem Record Number</strong></td>
    <td width="51"><strong>Client ID </strong></td>
    <td width="68"><strong>Severity</strong></td>
    <td width="72"><strong>Sub category </strong></td>
    <td width="92"><strong>Description </strong></td>
     <td width="189"><strong>Update </strong></td>
    <td width="195"><strong>Resolution</strong></td>
     <td width="609"><strong>Select a Record to Edit </strong></td>
     <td width="20">&nbsp;</td>
  </tr>
  <?php
while($row=mysql_fetch_array($result)) {
?>
  <tr>
    <td height="23"><?php echo $row[Problem_Record_ID]; ?></td>
    <td><?php echo $row[String_Client_ID]; ?></td>
    <td><?php echo $row[String_Problem_Record_Severity]; ?></td>
    <td><?php echo $row[String_Problem_record_subcategory]; ?></td>
    <td><?php echo $row[String_Problem_Record_Description]; ?></td>
     <td><?php echo $row[String_Problem_Record_Update]; ?></td>
    <td><?php echo $row[String_Problem_Record_Resolution]; ?></td>
   
    <td><a href="Tech3.php?recordID=<?php echo $row_uptechrs2['Problem_Record_ID']; ?>">Edit</a></td>
    <?php } ?>
</table>
<?php
mysql_free_result($relocations);

mysql_free_result($uptechrs2);
?>
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 16325311
Mostly.

I want to know what this resolves to when you first hit tech2.php:

$_GET['recordID']

Can you add an echo statement for it?
0
 

Author Comment

by:7704300
ID: 16345318
Hi Jason,

Ok heres what I did in this section

td height="23"><?php echo $row[Problem_Record_ID]; ?></td>
    <td><?php echo $row[String_Client_ID]; ?></td>
    <td><?php echo $row[String_Problem_Record_Severity]; ?></td>
    <td><?php echo $row[String_Problem_record_subcategory]; ?></td>
    <td><?php echo $row[String_Problem_Record_Description]; ?></td>
     <td><?php echo $row[String_Problem_Record_Update]; ?></td>
    <td><?php echo $row[String_Problem_Record_Resolution]; ?></td>
   
    <td><a href="Tech3.php?recordID=<?php echo $row_uptechrs2['Problem_Record_ID']; ?>">Edit</a></td>


I added the link <td><?php echo "Record ID" = $_GET['recordID']; ?>

I added this after this line

td><?php echo $row[String_Client_ID]; ?></td>


Now tech2 shows up blank.   Do you know what the problem is??

Thanks for the help!!!!
0
 

Author Comment

by:7704300
ID: 16345332
CORRECTION

This is what I added

<td><?php echo "Record ID" = $_GET['recordID']; ?></td>


Thanks
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 16346584
I'm having a hard time following the page code now (piecemeal over several posts).  Repost tech2 in full.  If the whole page is showing up blank, you have a misplaced <?php  or ?> bracket somewhere and your entire page is being parsed as php code.

I'm pretty sure the problem on tech3 is related to how you are passing the variables from tech1 to the recordset calls in tech2.  At some point, you have an incorrect variable assignment or call and that produces the blank query string to tech3.

Also, this is wrong:

<?php echo "Record ID" = $_GET['recordID']; ?>

Should be

<?php echo "Record ID = $_GET['recordID'];" ?>
0
 

Author Comment

by:7704300
ID: 16349032
Hi Jason,

Heres the updated code as requested.  I'm still getting the blank page even through I've made the update you requested.  Would you mind taking a look and let me know what you think.

Thanks for your help!!!


<?php require_once('Connections/connect_db.php'); ?>
<?php
mysql_select_db($database_connect_db, $connect_db);
$query_relocations = "SELECT * FROM problem_record";
$relocations = mysql_query($query_relocations, $connect_db) or die(mysql_error());
$row_relocations = mysql_fetch_assoc($relocations);
$totalRows_relocations = mysql_num_rows($relocations);

$colname_uptechrs2 = "1";
if (isset($_GET['recordID'])) {
  $colname_uptechrs2 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_connect_db, $connect_db);
$query_uptechrs2 = sprintf("SELECT * FROM problem_record WHERE Problem_Record_ID = %s", $colname_uptechrs2);
$uptechrs2 = mysql_query($query_uptechrs2, $connect_db) or die(mysql_error());
$row_uptechrs2 = mysql_fetch_assoc($uptechrs2);
$totalRows_uptechrs2 = mysql_num_rows($uptechrs2);
?>
<title>Select a Problem Record</title>
<title>Reports</title>
<?php
('Connections/connect_db.php');
mysql_select_db($database_connect_db, $connect_db);
$TechID=$_REQUEST[select];
$state=$_REQUEST[select2];

$sql="SELECT * FROM Problem_record where Num_Tech_assigned='$TechID'AND sting_Problem_Record_state='$state'";
$result=mysql_query($sql);

?>

<style type="text/css">
<!--
body,td,th {
      color: #000000;
}
body {
      background-color: #00CCFF;
}

-->
</style><p>&nbsp;</p>
<h2 align="center">Results</h2>
<p>&nbsp;</p>
<table width="1424" border="1">
  <caption>&nbsp;
  </caption>
  <tr>
    <td width="70"><strong>Problem Record Number</strong></td>
    <td width="51"><strong>Client ID </strong></td>
    <td width="68"><strong>Severity</strong></td>
    <td width="72"><strong>Sub category </strong></td>
    <td width="92"><strong>Description </strong></td>
      <td width="189"><strong>Update </strong></td>
    <td width="195"><strong>Resolution</strong></td>
      <td width="609"><strong>Select a Record to Edit </strong></td>
      <td width="20">&nbsp;</td>
  </tr>


  <?php
while($row=mysql_fetch_array($result)) {
?>
  <tr>
    <td height="23"><?php echo $row[Problem_Record_ID]; ?></td>
      <td><?php echo $row[String_Client_ID]; ?></td>
    <td><?php echo $row[String_Problem_Record_Severity]; ?></td>
      <td><?php echo "Record ID =$_GET['recordID'];" ?></td>
    <td><?php echo $row[String_Problem_record_subcategory]; ?></td>
    <td><?php echo $row[String_Problem_Record_Description]; ?></td>
      <td><?php echo $row[String_Problem_Record_Update]; ?></td>
    <td><?php echo $row[String_Problem_Record_Resolution]; ?></td>
   
    <td><a href="Tech3.php?recordID=<?php echo $row_uptechrs2['Problem_Record_ID']; ?>">Edit</a></td>
    <?php } ?>
      </table>
<?php
<?php echo "Record ID" = $_GET['recordID']; ?>
mysql_free_result($relocations);

mysql_free_result($uptechrs2);
?>
0
 

Author Comment

by:7704300
ID: 16349068
UPDATE -
Apologies again here is the actual code currently

<?php require_once('Connections/connect_db.php'); ?>
<?php
mysql_select_db($database_connect_db, $connect_db);
$query_relocations = "SELECT * FROM problem_record";
$relocations = mysql_query($query_relocations, $connect_db) or die(mysql_error());
$row_relocations = mysql_fetch_assoc($relocations);
$totalRows_relocations = mysql_num_rows($relocations);

$colname_uptechrs2 = "1";
if (isset($_GET['recordID'])) {
  $colname_uptechrs2 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_connect_db, $connect_db);
$query_uptechrs2 = sprintf("SELECT * FROM problem_record WHERE Problem_Record_ID = %s", $colname_uptechrs2);
$uptechrs2 = mysql_query($query_uptechrs2, $connect_db) or die(mysql_error());
$row_uptechrs2 = mysql_fetch_assoc($uptechrs2);
$totalRows_uptechrs2 = mysql_num_rows($uptechrs2);
?>
<title>Select a Problem Record</title>
<title>Reports</title>
<?php
('Connections/connect_db.php');
mysql_select_db($database_connect_db, $connect_db);
$TechID=$_REQUEST[select];
$state=$_REQUEST[select2];

$sql="SELECT * FROM Problem_record where Num_Tech_assigned='$TechID'AND sting_Problem_Record_state='$state'";
$result=mysql_query($sql);

?>

<style type="text/css">
<!--
body,td,th {
     color: #000000;
}
body {
     background-color: #00CCFF;
}

-->
</style><p>&nbsp;</p>
<h2 align="center">Results</h2>
<p>&nbsp;</p>
<table width="1424" border="1">
  <caption>&nbsp;
  </caption>
  <tr>
    <td width="70"><strong>Problem Record Number</strong></td>
    <td width="51"><strong>Client ID </strong></td>
    <td width="68"><strong>Severity</strong></td>
    <td width="72"><strong>Sub category </strong></td>
    <td width="92"><strong>Description </strong></td>
     <td width="189"><strong>Update </strong></td>
    <td width="195"><strong>Resolution</strong></td>
     <td width="609"><strong>Select a Record to Edit </strong></td>
     <td width="20">&nbsp;</td>
  </tr>


  <?php
while($row=mysql_fetch_array($result)) {
?>
  <tr>
    <td height="23"><?php echo $row[Problem_Record_ID]; ?></td>
     <td><?php echo $row[String_Client_ID]; ?></td>
    <td><?php echo $row[String_Problem_Record_Severity]; ?></td>
     <td><?php echo "Record ID =$_GET['recordID'];" ?></td>
    <td><?php echo $row[String_Problem_record_subcategory]; ?></td>
    <td><?php echo $row[String_Problem_Record_Description]; ?></td>
     <td><?php echo $row[String_Problem_Record_Update]; ?></td>
    <td><?php echo $row[String_Problem_Record_Resolution]; ?></td>
   
    <td><a href="Tech3.php?recordID=<?php echo $row_uptechrs2['Problem_Record_ID']; ?>">Edit</a></td>
    <?php } ?>
     </table>
<?php
mysql_free_result($relocations);

mysql_free_result($uptechrs2);
?>
0
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 2000 total points
ID: 16356172
Okay, I finally had a chance to sit down and recreate the pages.  Most, if not all of your problems, stem from this section in tech2:

-------
mysql_select_db($database_connect_db, $connect_db);
$TechID=$_REQUEST[select];
$state=$_REQUEST[select2];
$sql="SELECT * FROM Problem_record where Num_Tech_assigned='$TechID'AND sting_Problem_Record_state='$state'";
$result=mysql_query($sql);
-------

The SQL isn't really correct.  There were so many little errors that I lost track as I fixed them.  Here are your first two pages again.  Go over them and compare your original code above with my code below:

Tech1.php:
-------
<?php require_once('Connections/connect_db.php'); ?><?php
mysql_select_db($database_connect_db, $connect_db);
$query_techrs = "SELECT * FROM tech";
$techrs = mysql_query($query_techrs, $connect_db) or die(mysql_error());
$row_techrs = mysql_fetch_assoc($techrs);
$totalRows_techrs = mysql_num_rows($techrs);

mysql_select_db($database_connect_db, $connect_db);
$query_rsstate = "SELECT DISTINCT Sting_Problem_Record_state FROM problem_record";
$rsstate = mysql_query($query_rsstate, $connect_db) or die(mysql_error());
$row_rsstate = mysql_fetch_assoc($rsstate);
$totalRows_rsstate = mysql_num_rows($rsstate);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Tech 1</title>
<style type="text/css">
<!--
body,td,th {
     color: #000000;
}
body {
     background-color: #00CCFF;
}
-->
</style>
</head>

<body>
<h2 align="center">View Problem Records for a Technician</h2>
<form name="form1" method="GET" action="tech2.php">
  <p>&nbsp;</p>
  <p align="center">Please Select a Technician ID
    <select name="select">
      <option value="">Select a Tech</option>
      <?php
do {  
?>
      <option value="<?php echo $row_techrs['TechID']?>"><?php echo $row_techrs['Name']?></option>
      <?php
} while ($row_techrs = mysql_fetch_assoc($techrs));
  $rows = mysql_num_rows($techrs);
  if($rows > 0) {
      mysql_data_seek($techrs, 0);
        $row_techrs = mysql_fetch_assoc($techrs);
  }
?>
    </select>
</p>
  <p align="center">Please Select a Problem Record State
    <select name="select2" title="<?php echo $row_rsstate['Sting_Problem_Record_state']; ?>">
      <option value="">Select a state</option>
      <?php
do {  
?><option value="<?php echo $row_rsstate['Sting_Problem_Record_state']?>"><?php echo $row_rsstate['Sting_Problem_Record_state']?></option>
      <?php
} while ($row_rsstate = mysql_fetch_assoc($rsstate));
  $rows = mysql_num_rows($rsstate);
  if($rows > 0) {
      mysql_data_seek($rsstate, 0);
        $row_rsstate = mysql_fetch_assoc($rsstate);
  }
?>
    </select>
  </p>
  <p align="center">&nbsp;</p>
  <p align="center">
    <input type="submit" name="Submit" value="Submit">
</p>
</form>
<p>&nbsp;</p>
<p>&nbsp; </p>
</body>
</html>
<?php
mysql_free_result($techrs);

mysql_free_result($rsstate);
?>

--------
Tech2.php
-------
<?php require_once('Connections/connect_db.php'); ?>
<?php
mysql_select_db($database_connect_db, $connect_db);
$query_relocations = "SELECT * FROM problem_record";
$relocations = mysql_query($query_relocations, $connect_db) or die(mysql_error());
$row_relocations = mysql_fetch_assoc($relocations);
$totalRows_relocations = mysql_num_rows($relocations);

$colname_uptechrs2 = "1";
if (isset($_GET['recordID'])) {
  $colname_uptechrs2 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_connect_db, $connect_db);
$query_uptechrs2 = sprintf("SELECT * FROM problem_record WHERE Problem_Record_ID = %s", $colname_uptechrs2);
$uptechrs2 = mysql_query($query_uptechrs2, $connect_db) or die(mysql_error());
$row_uptechrs2 = mysql_fetch_assoc($uptechrs2);
$totalRows_uptechrs2 = mysql_num_rows($uptechrs2);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<?php
mysql_select_db($database_connect_db, $connect_db);
$TechID=$_GET['select'];
$state=$_GET['select2'];
$sql=sprintf("SELECT * FROM problem_record WHERE Num_Tech_assigned = %s AND sting_Problem_Record_state = '%s'", $TechID, $state);
$result=mysql_query($sql, $connect_db) or die(mysql_error());
$row = mysql_fetch_assoc($result);
?>
</head>

<body>
<h2 align="center">Results</h2>
<table width="1424" border="1">
  <caption>&nbsp;
  </caption>
  <tr>
    <td width="70"><strong>Problem Record Number</strong></td>
    <td width="51"><strong>Client ID </strong></td>
    <td width="68"><strong>Severity</strong></td>
    <td width="72"><strong>Sub category </strong></td>
    <td width="92"><strong>Description </strong></td>
     <td width="189"><strong>Update </strong></td>
    <td width="195"><strong>Resolution</strong></td>
     <td width="609"><strong>Select a Record to Edit </strong></td>
     <td width="20">&nbsp;</td>
  </tr>



  <?php do { ?>
    <tr>
      <td height="23"><?php echo $row[Problem_Record_ID]; ?></td>
      <td><?php echo $row[String_Client_ID]; ?></td>
      <td><?php echo $row[String_Problem_Record_Severity]; ?></td>
      <td><?php echo $_GET['recordID']; ?></td>
      <td><?php echo $row[String_Problem_record_subcategory]; ?></td>
      <td><?php echo $row[String_Problem_Record_Description]; ?></td>
      <td><?php echo $row[String_Problem_Record_Update]; ?></td>
      <td><?php echo $row[String_Problem_Record_Resolution]; ?></td>
      <td><a href="tech3.php?recordID=<?php echo $row_uptechrs2['Problem_Record_ID']; ?>">Edit</a></td>
      <?php } while ($row = mysql_fetch_assoc($result)); ?></table>

</body>
</html>
<?php
mysql_free_result($relocations);

mysql_free_result($uptechrs2);
?>
----------

Tech 3 as you post it is fine.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 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