Difficulty Updating My Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mall
Forum Newbie
Posts: 3
Joined: Tue Mar 14, 2006 12:21 pm

Difficulty Updating My Database

Post by mall »

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


Hello to everyone reading this.

Im a beginner of PHP and MySQL, and i'm having a little difficulty updating one of my databases.

Basically, i have a single page consisting of a table, where i echo some information from the database. This echoing occurs without fail. 

I also have x3 drop down menus, one for a Comment, another for Progress and the last for confirming whether the job is Complete or not.

When i make my selections and press submit, it looks as though it updates the database, but it does not. Although the clear button works.

My initial thoughts were, are you able to echo information and update information in a single form? I'm not sure, although i don't see why not, and thats why i have made this such attempt.

If anyone can help, i'd be glad to hear.

Heres my code:

---------------------------

Code: Select all

<?
session_start();
?>

<?
include "conn.inc.php";
$query ="SELECT * FROM admin WHERE AdminID= '" . $_SESSION['admin_logged']."'
AND password =(password('" . $_SESSION['admin_password'] . "'));";
$result = mysql_query($query) or die(mysql_error());
$new = mysql_fetch_array($result);
?>

<html>
<head>
<title>Fault Reporting System : : Complete A Call</title>
<body background="h.gif">
<body>
<br>
<div align="center"><h2>Welcome <? echo $new['First_Name']; ?>.<br> Please Select A Call You Would Like To Complete</h2></div>
<br>

<?
include "conn1.inc.php";

$q1 = "SELECT FaultID, AdminID, StaffID, Location, Problem_Desc, 
              Date, Technician_Comment, Progress, Complete
       FROM FAULT
       WHERE AdminID= '" . $_SESSION['admin_logged']."'
       AND Progress = 'Ongoing'";

$result = mysql_query($q1) or die(mysql_error());
$ans= mysql_num_rows($result);

?>

<?
// updating the database

include "conn1.inc.php";

    if ($_POST['submit'] == "Update")
    {
    if ($_POST['AdminID'] != "" && $_POST['Technician_Comment'] != "" && $_POST['Complete'] != ""
    && $_POST['Progress'] != "")

    $q8 = "INSERT INTO Fault(Technician_Comment, Progress, Complete)
       VALUES('" . $_POST['Technician_Comment'] . "', '" . $_POST['Progress'] . "', '" . $_POST['Complete'] . "');"; 

      $result = mysql_query($q8) or die(mysql_error());
      $_SESSION['admin_logged'] = $_POST['AdminID'];
      $_SESSION['admin_password'] = $_POST['password'];
    }
?>


<?

//EOD, all table variables held between EOD statements;

$progress_table=<<<EOD
<div align="center"><h4>Here Are Your Outstanding Calls</h4></div>
<form action="Fault_All_My_Completed_Calls.php" method="post">
<table width="100%" border="5" cellpadding="1" cellspacing="1" align="center">
  <tr>
    <th>FaultID</th>
    <th>AdminID</th>
    <th>StaffID</th>
    <th>Location</th>
    <th>Problem Description</th>
    <th>Technician Comment</th>
    <th>Date</th>
    <th>Progress</th>
    <th>Complete</th>
  </tr>

EOD;

while($row = mysql_fetch_array($result))
{
    $FaultID = $row['FaultID'];
    $AdminID = $row['AdminID'];
    $StaffID = $row['StaffID'];
    $Location = $row['Location'];
    $Problem_Desc = $row['Problem_Desc'];
    $Technician_Comment = $row['Technician_Comment'];
    $Date = $row['Date'];
    $Progress = $row['Progress'];
    $Complete = $row['Complete'];

    $progress_table_info .=<<<EOD
    <tr>
      <td>$FaultID</td>
      <td>$AdminID</td>
      <td>$StaffID</td>
      <td>$Location</td>
      <td>$Problem_Desc</td>
      <td><select name="Technician_Comment">
    <option value="" SELECTED>Select Comment...</option>
    <option value="Working fine now">Working fine now</option>
    <option value="Sent back to manufacturer">Sent back to manufacturer</option>
    <option value="3rd party coming in to look at it">3rd party coming in to look at it</option>
    <option value="Need more time">Need more time</option>
          </select></td>
      <td>$Date</td>
      <td><select name="Progress">
    <option value="" SELECTED>Select Progress...</option>
    <option value="Ongoing">Ongoing</option>
    <option value="Complete">Complete</option>
          </select></td>

    <td><select name="Complete">
    <option value="" SELECTED>Complete...</option>
    <option value="Yes">Yes</option>
    <option value="No">No</option>
    </select></td>    

    </tr>   

EOD;
}
$progress_table_info .=<<<EOD


<tr>
  <td> </td>

EOD;

$progress_table_end ="</table>";
$progress =<<<PROGRESS
    $progress_table
    $progress_table_info
    $progress_table_end
PROGRESS;
echo $progress;
?>
<br>
<br>
<input type="submit" name="submit" value="Submit">
<input type="reset" value="Clear">
</body>
</html>


-----------------------

That's it.

Thankyou.

H.


feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

Clean up your code.

Post by Jough »

First of all, I dont see how your echoing could occur without fail. Here is a breakdown:

You are trying to fetch an array from a sql query stored in a variable ($result) when the sql query is actually an insert. Here is your code -

Code: Select all

$q8 = "INSERT INTO Fault(Technician_Comment, Progress, Complete) 
       VALUES('" . $_POST['Technician_Comment'] . "', '" . $_POST['Progress'] . "', '" . $_POST['Complete'] . "');";  

      $result = mysql_query($q8) or die(mysql_error()); 
      $_SESSION['admin_logged'] = $_POST['AdminID']; 
      $_SESSION['admin_password'] = $_POST['password']; 
    } 
?> 


<? 

//EOD, all table variables held between EOD statements; 

$progress_table=<<<EOD 
<div align="center"><h4>Here Are Your Outstanding Calls</h4></div> 
<form action="Fault_All_My_Completed_Calls.php" method="post"> 
<table width="100%" border="5" cellpadding="1" cellspacing="1" align="center"> 
  <tr> 
    <th>FaultID</th> 
    <th>AdminID</th> 
    <th>StaffID</th> 
    <th>Location</th> 
    <th>Problem Description</th> 
    <th>Technician Comment</th> 
    <th>Date</th> 
    <th>Progress</th> 
    <th>Complete</th> 
  </tr> 

EOD; 

while($row = mysql_fetch_array($result)) 
{
I am assuming the mysql_fetch_array is meant to be for the previous variable $result. Try changing your portion of the code to this:

Code: Select all

$q8 = "INSERT INTO Fault(Technician_Comment, Progress, Complete) 
       VALUES('" . $_POST['Technician_Comment'] . "', '" . $_POST['Progress'] . "', '" . $_POST['Complete'] . "');";  

      mysql_query($q8) or die(mysql_error()); 
      $_SESSION['admin_logged'] = $_POST['AdminID']; 
      $_SESSION['admin_password'] = $_POST['password']; 
    } 
?> 


<? 

//EOD, all table variables held between EOD statements; 

$progress_table=<<<EOD 
<div align="center"><h4>Here Are Your Outstanding Calls</h4></div> 
<form action="Fault_All_My_Completed_Calls.php" method="post"> 
<table width="100%" border="5" cellpadding="1" cellspacing="1" align="center"> 
  <tr> 
    <th>FaultID</th> 
    <th>AdminID</th> 
    <th>StaffID</th> 
    <th>Location</th> 
    <th>Problem Description</th> 
    <th>Technician Comment</th> 
    <th>Date</th> 
    <th>Progress</th> 
    <th>Complete</th> 
  </tr> 

EOD; 

while($row = mysql_fetch_array($result)) 
{
Notice how the query for the insert is no longer stored in the $result variable but actually performed on demand. Try this. If it doesnt work then I blame my inability to focus on your ugly code! :lol: j/k
mall
Forum Newbie
Posts: 3
Joined: Tue Mar 14, 2006 12:21 pm

Still no luck :(

Post by mall »

Hello,
thankyou for your reply. I had a look at what you said and tried out your method, BUT it still didn't work.

I understand what you meant in your reply, so i renamed each $result variable, in relation to each query i'v set, to a much easier representation by placing increasing numbers in front of each variable name. eg, $result1, $result2, etc.

Although i have clarified this, my database still does not update.

:(

If anyone can please help me, id be very glad to hear.

Thankyou.

:( H :(
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

More info.

Post by Jough »

I'll need more info to help. What is the structure of the 'fault' table? Is it throwing any errors?
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

Oops!!

Post by Jough »

I just realized, in your SELECT query you used the table name 'FAULT' while in the INSERT query you used 'Fault'. MySQL is case sensitive with table names!! Take this along with my previous advice and you should be gold :? maybe...
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

Wow.

Post by Jough »

Ok, so maybe a third post is a charm...

I just noticed a few more errors in your code (jeeze, man! :lol: ) Try this for your INSERT query (check capitals!):

Code: Select all

$q8 = "INSERT INTO Fault (Technician_Comment, Progress, Complete) VALUES ('" . $_POST['Technician_Comment'] . "', '" . $_POST['Progress'] . "', '" . $_POST['Complete'] . "')";
You had an extra semi-colon after the end parenthesis.
mall
Forum Newbie
Posts: 3
Joined: Tue Mar 14, 2006 12:21 pm

Thanks for the info, but still no luck

Post by mall »

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


Hi Jough,

thanks for your replies, very much appreciated. Thank you, but unfortunatley the changes still didn't do anything.

Umm, as for throwing errors, i don't get any at all which is even more weird.

Iv made the changes you suggested. I also had changed the name of FAULT to fault before you had posted your reply. Picked up on that, but i didn't know that it was case sensitive. I'll keep that in mind.   

Here's the code as it stands. I press submit, and it takes me to my history page, where the result should go after the update, but it doesn't.

Also, maybe you can clarify something for me....... in my code, is my query for updating always being confused with my query for looking for ongoing calls. The query is looking for ongoing calls from my fault table, and i want to update these calls to complete, or leave them as ongoing and just add an updated comment so someone can track its progress.

It just seems that the update query isn't doing anything at all. I had problems with my {} brackets, in terms of if statements, i dont get them to be quite honest as i have never really used them before.

Here's the code:
--------------------

Code: Select all

<?
session_start();
?>

<?
include "conn.inc.php";
$query ="SELECT * FROM admin WHERE AdminID= '" . $_SESSION['admin_logged']."'
AND password =(password('" . $_SESSION['admin_password'] . "'));";
$result = mysql_query($query) or die(mysql_error());
$new = mysql_fetch_array($result);
?>

<html>
<head>
<title>Fault Reporting System : : Complete A Call</title>
<body background="h.gif">
<body>
<br>
<div align="center"><h2>Welcome <? echo $new['First_Name']; ?>.<br> Please Select A Call You Would Like To Complete</h2></div>
<br>

<?
include "conn1.inc.php";

$q1 = "SELECT FaultID, AdminID, StaffID, Location, Problem_Desc, Date
       FROM fault
       WHERE AdminID= '" . $_SESSION['admin_logged']."'
       AND Progress = 'Ongoing'";

$result1 = mysql_query($q1) or die(mysql_error());
$ans= mysql_num_rows($result1);

?>

<?

// updating the database

include "conn1.inc.php";

    if ($_POST['submit'] == "submit")
    {
    if ($_POST['Technician_Comment'] != "" && $_POST['Progress'] != "" && $_POST['Complete'] != "")
    
    $q8 = "INSERT INTO Fault (Technician_Comment, Progress, Complete) 
	   VALUES ('" . $_POST['Technician_Comment'] . "', '" . $_POST['Progress'] . "', '" . $_POST['Complete'] . "')";  

	  $result2 = mysql_query($q8) or die(mysql_error());
	  $_SESSION['admin_logged'] = $_POST['AdminID'];
	  $_SESSION['admin_password'] = $_POST['password'];
    }
?>

<?

//EOD, all table variables held between EOD statements;

$progress_table=<<<EOD
<div align="center"><h4>Here Are Your Outstanding Calls</h4></div>
<form action="Fault_All_My_Completed_Calls.php" method="post">
<table width="100%" border="5" cellpadding="1" cellspacing="1" align="center">
  <tr>
    <th>FaultID</th>
    <th>AdminID</th>
    <th>StaffID</th>
    <th>Location</th>
    <th>Problem Description</th>
    <th>Technician Comment</th>
    <th>Date</th>
    <th>Progress</th>
    <th>Complete</th>
  </tr>

EOD;

while($row = mysql_fetch_array($result1))
{
    $FaultID = $row['FaultID'];
    $AdminID = $row['AdminID'];
    $StaffID = $row['StaffID'];
    $Location = $row['Location'];
    $Problem_Desc = $row['Problem_Desc'];
    $Date = $row['Date'];

    $progress_table_info .=<<<EOD

    <tr>
      <td>$FaultID</td>
      <td>$AdminID</td>
      <td>$StaffID</td>
      <td>$Location</td>
      <td>$Problem_Desc</td>
      <td><select name="Technician_Comment">
	<option value="" SELECTED>Select Comment...</option>
	<option value="Working fine now">Working fine now</option>
	<option value="Sent back to manufacturer">Sent back to manufacturer</option>
	<option value="3rd party coming in to look at it">3rd party coming in to look at it</option>
	<option value="Need more time">Need more time</option>
		  </select></td>
      <td>$Date</td>
      <td><select name="Progress">
	<option value="" SELECTED>Select Progress...</option>
	<option value="Ongoing">Ongoing</option>
	<option value="Complete">Complete</option>
          </select></td>

	<td><select name="Complete">
	<option value="" SELECTED>Complete...</option>
	<option value="Yes">Yes</option>
	<option value="No">No</option>
	</select></td>	

    </tr>   

EOD;
}
$progress_table_info .=<<<EOD


<tr>
  <td></td>

EOD;

$progress_table_end ="</table>";
$progress =<<<PROGRESS
    $progress_table
    $progress_table_info
    $progress_table_end
PROGRESS;
echo $progress;
?>

<br>
<br>
<input type="submit" name="submit" value="Submit">
<input type="reset" value="Clear">
</body>
</html>
------------------------------

Hope you have some more luck with this code, you seem to be my only life line at the moment. Thanks alot for your help.

H.


feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

A few questions.

Post by Jough »

From what I have gotten from your code your 'fault' table has nine fields:

FaultID
AdminID
StaffID
Location
Problem_Desc
Date
Technician_Comment
Progress
Complete

Is there any more fields in this table? What is the primary key of this table?

Is 'Fault_All_My_Completed_Calls.php' the name of this file? If not then where are you getting the Post variables to place in the INSERT query?

You wanted me to clarify something for you and mentioned the query for updating?? Is this page supposed to update the table row or insert a new one?? I see no UPDATE query right now.

What is the contents of the 'conn1.inc.php' file? Do you have access rights to use the INSERT query?

Sorry for so many questions but the answer to every one will help me find what is wrong with your entire script. The problem could be ANYTHING...
Post Reply