update record in mysql from php

[ckwall]

I have a script in my page calling up a loop of records, then echoing a few of the fields.

$query="SELECT * FROM request where approval is NULL";

One of the fields that is echoed is ticket_num.
This is an autoincrementing int field.

Then the script loops, displaying the next record found.
I have a form for each record that has the information that should be updated in the database. When the submit button is clicked, it calls update.php which has the script:

$query = "UPDATE request SET assigned_1='$assigned', assigned_priority='$assigned_priority', approval='$approval'";

the above script is obviously not complete. What I need to know is how can I apply that script, (or a more appropriate one) to just the found record.

Any help would be appreciated.

[fowkswe]

Are you saying you would like to execute the UPDATE query on the record that is returned from the SELECT query?

Do you have a unique KEY for your records? If so, you would get this unique value back from the SELECT statement and could use it to only UPDATE the record with that key.

create table test (
key int primary key auto_increment
assigned_1 int,
assigned_priority int
approval bool);

You could then do

$query = "UPDATE request SET assigned_1='$assigned', assigned_priority='$assigned_priority', approval='$approval' WHERE key='value returned from select'";

hope this helps...

billy

[ckwall]

Do I actualy put in the where variable: 'value returned from select'?

I do have my primary key set to the field "ticket_num" which would be pulled from my select statement of

$query="SELECT * FROM request where approval is NULL";

[fowkswe]

yes. i would do something like this:

$query="SELECT * FROM request where approval is NULL";

$result = mysql_query($query);

//  this would loop and fetch all the rows that the SELECT found

while ($row = mysql_fetch_row ($resutl, MYSQL_ASSOC))
  {

  // do what ever you do with this info
  // ...
  // ...
  // ...

  // now update the record you originally SELECT'ed

  $key = $row['ticket_num'];

  $query = "UPDATE request SET assigned_1='$assigned', assigned_priority='$assigned_priority', approval='$approval' WHERE ticket_num='$key'";
}

Hope that does what you aare looking for

[ckwall]

I must not be understanding. I have tried a few things. I am getting a parse error on the while statement. Here is where I am at:

<?

//server details
$username="***"; 
$password="***"; 
$database="***"; 
$server="localhost"; 

//Connect to server
mysql_connect($server,$username,$password); 
@mysql_select_db($database) or die( "Unable to select database");

//Retreive new work requests
$query="SELECT * FROM request where approval is NULL"; 
$result = mysql_query($query); 

//  this would loop and fetch all the rows that the SELECT found 

while ($row = mysql_fetch_row ($result, MYSQL_ASSOC)) 
  { 

echo " 

<table width=80%>
<tr><td>
<b>Ticket Number $ticket_num</b><br> 
Requested Priority: $priority <br> 
Name: $first_name $last_name <br> 
Department: $department <br> 
Extension: $ext <br> 
Email: $email <br> 
Request Summary: $summary <br> 
Request Details: $details <br> 
Requested Due Date: $day/$month/$year <br>
</td>
<td>
<form action=\"update.php\" method=\"post\">
<b>Approved:</b><br />
<input type=\"radio\" name=\"approval\" value=\"yes\">Yes
<input type=\"radio\" name=\"approval\" value=\"no\">No
<br /><br />         

<b>Assign to:</b><br />
	<select name=\"assigned_1\">
          <option value=\"\"></option>
		  <option value=\"Arthur Blakely\">Arthur Blakely</option>
          <option value=\"Bobby Thornton\">Bobby Thornton</option>
          <option value=\"Brad Allen\">Brad Allen</option>
          <option value=\"Chris Wall\">Chris Wall</option>
          <option value=\"Mark Stafford\">Mark Stafford</option>
          <option value=\"Roy Terry\">Roy Terry</option>
        </select>
		<select name=\"assigned_2\">
		  <option value=\"\"></option>
          <option value=\"Arthur Blakely\">Arthur Blakely</option>
          <option value=\"Bobby Thornton\">Bobby Thornton</option>
          <option value=\"Brad Allen\">Brad Allen</option>
          <option value=\"Chris Wall\">Chris Wall</option>
          <option value=\"Mark Stafford\">Mark Stafford</option>
          <option value=\"Roy Terry\">Roy Terry</option>
        </select>
		<select name=\"assigned_3\">
		  <option value=\"\"></option>
          <option value=\"Arthur Blakely\">Arthur Blakely</option>
          <option value=\"Bobby Thornton\">Bobby Thornton</option>
          <option value=\"Brad Allen\">Brad Allen</option>
          <option value=\"Chris Wall\">Chris Wall</option>
          <option value=\"Mark Stafford\">Mark Stafford</option>
          <option value=\"Roy Terry\">Roy Terry</option>
        </select>
		<select name=\"assigned_4\">
          <option value=\"\"></option>
		  <option value=\"Arthur Blakely\">Arthur Blakely</option>
          <option value=\"Bobby Thornton\">Bobby Thornton</option>
          <option value=\"Brad Allen\">Brad Allen</option>
          <option value=\"Chris Wall\">Chris Wall</option>
          <option value=\"Mark Stafford\">Mark Stafford</option>
          <option value=\"Roy Terry\">Roy Terry</option>
        </select>

<br /><br />

<b>Assigned Priority:</b><br />
<input type=\"radio\" name=\"assigned_priority\" value=\"1\">1
<input type=\"radio\" name=\"assigned_priority\" value=\"2\">2            

<input type=\"radio\" name=\"assigned_priority\" value=\"3\">3
<input type=\"radio\" name=\"assigned_priority\" value=\"4\">4
<br /><br />
<input name=\"update\" type=\"submit\" value=\"Update\"><br />

</form>





</td>
<tr>
</table>

<hr>
"; 

++$i; 



  $key = $row['ticket_num']; 

  $query = "UPDATE request SET assigned_1='$assigned', assigned_priority='$assigned_priority', approval='$approval' WHERE ticket_num='$key'"; 
}
?>

[fowkswe]

well...

its not usually my practice to echo() a bunch of html - that just makes for a messy string to try to ensure all quotes are closed.

i would first start by printing out the row to make sure you mysql statements are correct:

while ($row = mysql_fetch_row ($result, MYSQL_ASSOC))  

  {
  print "<pre>";
  print_r ($row);
  print "</pre>";
  }

once you are sure all the database guts are working i would, instead of one big echo statement, escape from PHP to type your html like this:

while ($row = mysql_fetch_row ($result, MYSQL_ASSOC))  

  {
?>

<table width=80%>
<tr><td>
<b>Ticket Number 
<? echo $row['ticket_nuim']; ?>
</b><br>
Requested Priority: <? echo $row['priority']; ?>
..
..
..
<?
  }
?>

note the in line php statements. This is my method, other people would disagree, its a matter of option.

its just that it sounds like that big echo statement is causing you the problem. The best way to debug is to break things down into small chunks - go slowly and don't try to do every at once.

[ckwall]

Now I am even more confused.
no matter what I try, I still get the error message on the while statement.

Warning: Wrong parameter count for mysql_fetch_row() in /home/httpd/vhosts/clearview-cs.com/httpdocs/tracker/test.php on line 15

<?
//server details
$username="***"; 
$password="***"; 
$database="***"; 
$server="localhost"; 

//Connect to server
mysql_connect($server,$username,$password); 
@mysql_select_db($database) or die( "Unable to select database");

//Retreive new work requests
$result = mysql_query($query); 

//  this would loop and fetch all the rows that the SELECT found 
while ($row = mysql_fetch_row ($result, MYSQL_ASSOC)) 
    { 
  print "<pre>"; 
  print_r ($row); 
  print "</pre>"; 
	}
  $key = $row['ticket_num']; 
  $query = "UPDATE request SET assigned_1='$assigned', assigned_priority='$assigned_priority', approval='$approval' WHERE ticket_num='$key'"; 
?>

[fowkswe]

oh no, im sorry

i didnt test that code, i typed it in with out looking over it very well.

I would HIGHLY reccomend downloading the PHP documentation. There is a chapter with a function reference. When you see an error like that you should consult the reference to see what you shold pass to the function.

in this case

mysql_fetch_row() only accepts a result identifier. I actually meant to use mysql_fetch_array() which has an addtional parameter which indicates the type of array you want to fetch.

i urge you to download the windows cfm file from here:

PHP Reference

and get VERY familiar with it. its an extremely valuable tool - for free!

At any rate you can either change the line to:

$row = mysql_fetch_row($result);

or

$row = mysql_fetch_array ($result, MYSQL_ASSOC);

(i would reccomend the later 😉)

Billy

[ckwall]

ahhhh much better.

ya, I downloaded the manual. Ijust get tossed into some of these projects where I want to build a complex project with a kindergarten like knowledge of php.

Thanks for the help.

I may come back to you one more time. I am getting any of the data from the database back. I am working on that now.

[fowkswe]

keep it up and you'll have a college graduate's knowlede!

good luck but ill lend a hand if you need it.

Billy

[ckwall]

so if I am using a form to enter the changes that need to be updated:

<form action=\"" method=\"post\">
can I put the query...
$query = "UPDATE request SET assigned_1='$assigned', assigned_priority='$assigned_priority', approval='$approval' WHERE ticket_num='$key'";

...in there some how? Otherwise how do I get that submit button to call the update script?

[fowkswe]

im not sure i understand what you are asking...

the way you submit form data to php is to specify an action:

<form method="post" action="handleFormData.php" name="formName">

this will submit the form data to handleFormData.php

you can start by printing it out to make sure it was correctly submitted:

<?

print "<pre>";
print_r ($_POST);
print "</pre>";

// $_POST is an array that contains all the data submitted from the form

Billy