Updating a mysql record with php

sliperypete · Nov 27, 2006

This is what I have so far and I cant get any data in the solution attribute in the ticket table. I am trying to insert the solution in open ticket that is selected by the tech. The ticketid is passed in the URL of the browser.

<?php
session_start();
include "level2_check.php";
include("config.php");

$status = $_POST['status'];
$solution = $_POST['solution'];

if(isset($_POST['Submit']))
{
$res = mysql_query("SELECT * FROM ticket WHERE ticket_id='$ticketid'");
if (mysql_num_rows($res) > 0)
{
// found a match
$sql="UPDATE ticket
SET solution='$solution'
WHERE ticket_id='$ticketid'";
$result = mysql_query($sql) or die(mysql_error());
echo "The ticket is now closed good work!";
} else
{
echo "There was an error trying to close this ticket please try again";
}
}
?>

<html>
<head>
<title>Close Ticket</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="style.css" rel="stylesheet" type="text/css">
<?php include ("admin_header.html") ?>
</head>
<body>
<?php echo $ticketid; ?>
<h3 align="center">Close Ticket</h3>
<table width="60%" border="1" align="center" cellpadding="1" cellspacing="1" bordercolor="#000000">
<form name="form1" method="post" action="admin_closeticket.php">
<input name="status" type="hidden" id="status" value="0">
<tr>
<td nowrap align="right">Solution:</td>
<td height="100"><textarea name="solution" id="solution"></textarea></td>

</tr>
<tr>
<td><input type="submit" value="submit"></td>
</tr>
</form>

</table>
</body>
</html>

mikeblas · Nov 28, 2006

Your code is wide-open for SQL injection attacks. You relaly need to fix it, pronto, before your site is hacked into a million pieces.

Meanwhile, I don't see a specific question in your post. What specific part of your code isn't working? are you getting an error message? A die() message? Something else?

There's no need to select before updating, by the way; UPDATE will simply touch zero rows if none match the WHERE clause you've given.

sliperypete · Nov 28, 2006

Its not a real site its for a school project and secruity isnt an issue at this time becuase its a database class the backend is really all that matters, the front end is for extra points.

For some reason I cant get the solution which is entered by the user in the form entered into the ticket table under the proper ticket. Its not giving me an error it says it goes through but never gets entered in. Im really new to this and this is my first project ever. I really appreciate any help, im so lost with this.

mikeblas · Nov 28, 2006

sliperypete said:
For some reason I cant get the solution which is entered by the user in the form entered into the ticket table under the proper ticket.

I wish I could help, but I don't know anything about solutions or tickets.

sliperypete · Nov 28, 2006

I am writing an IT ticketing system for users to submit tickets for computer problems which get assigned to techs. Techs are the only ones aloud to close the tickets. The code for the page displayed is where the tech writes up the solution to the users problem which in turn closes the ticket from the database. I cant get the text typed in the text box on that form into the solution attribute on the ticket table based on the tickets ID.

Example:

The tech logs in
He sees the tickets that are assigned to him
He clicks on the ticket he has finished and wants to close from the system (the ticketid is passed in the URL)
He clicks the link to bring up the close ticket page (the code displayed)
He types in a text box how he fixed the problem on the ticket
Clicks close

Now what is supposed to happen is the solution he types in gets insterted into the proper ticket based on the ticketid and the status is set to 0 which is a hidden form.

mikeblas · Nov 28, 2006

What query shows you that you've got two items with the same ticket ID in the tickets table? Why doesn't the tickets table have a uniqueness constraint on the ticket ID? Are you sure that the duplicate ID is not a remainder from some other experiment? The code here doesn't insert any data, and I don't see how it could cause a duplicate ticket ID. Some other code, some other actions, or some other part of the story must be missing.

sliperypete · Nov 28, 2006

There cant be a ticket with the same ticketid's, the database creates the ticketid (also the primary key) by way of auto incrementing. So the first ticket submited is ticket 1 second ticket submited is ticket 2 it is impossible to have two tickets with the same ID. All I am trying to do here is insert 2 things into a ticket that already exists in the ticket table. This is what the ticket table looks like:

ticketid - PK auto incremented by the DB
username -gets inserted automaticly when user submits a ticket
problem type - user selects this when submitting
problem description - user enters this when submitting
due date - user submits this
status - automaticly set to 1 when ticket is submitted (means the ticket is open)

***now the ticket is created in the DB***

Fields not yet filled in:

solution
close date

Fields that the script posted above is supposed to UPDATE

solution - with what the tech types in the form
status - automaticly gets updated to 0 (which means it is closed)
close date entered by the DB

sliperypete · Nov 28, 2006

I know my problem exactly but need assistance in fixing it:

mysql_query ("UPDATE ticket
SET status='$status',solution='$solution'
WHERE ticket_id='$ticketid'") or die(mysql_error());
echo "The ticket is now closed good work!";

In the WHERE clause of this UPDATE statement I am doing WHERE ticket_id ='$ticketid'

$ticketid is a PHP variable am I aloud to use a PHP variable in a sql query like that ?

v6maro · Nov 28, 2006

try doing this

$sql = " *put your sql statement in here* ";

echo $sql;

it should show you what exact query you are running

If it's not putting the $var values in the string, that could be a problem #1

also, structure your phpsql like this:

Code:

//set the db
$db = mysql_connect("localhost", "uname", "pass") or die ("I cannot connect to the database because: " . mysql_error());

//select the db
mysql_select_db ("db_name") or die("cannot select db_name db"); 

//write the query
$sql = "UPDATE `myTableName` SET `fieldid1` = '$value' WHERE `id` = '$id';

//execute the query
$result = mysql_query($sql) or die('Query failed : ' . mysql_error() . ' : ' . $sql);

//close the connection
mysql_close($db);

v6maro · Nov 28, 2006

sliperypete said:
I know my problem exactly but need assistance in fixing it:

mysql_query ("UPDATE ticket
SET status='$status',solution='$solution'
WHERE ticket_id='$ticketid'") or die(mysql_error());
echo "The ticket is now closed good work!";

In the WHERE clause of this UPDATE statement I am doing WHERE ticket_id ='$ticketid'

$ticketid is a PHP variable am I aloud to use a PHP variable in a sql query like that ?

yes you are allowed to use the var like that

Do you have mysql admin? Try running your query against the database NOT in your php code, do you get results?

v6maro · Nov 28, 2006

oh yeah, I see your problem, you are doing a select statement here:
$res = mysql_query("SELECT * FROM ticket WHERE ticket_id='$ticketid'");
if (mysql_num_rows($res) > 0)

Why? Who cares if there is a result??

when you are doing the update, you are doing it based on the ticket_id, so if that ticket id does not exist, it wont update anything. So really you should only have to do your update statement.

sliperypete · Nov 28, 2006

Yeah I had it like that a while ago this is the newest code:

<?php
session_start();
include "level2_check.php";
include("config.php");
print_r($_POST);
$status = $_POST['status'];
$solution = $_POST['solution'];

if(isset($_POST['submit']))
{
$query = "UPDATE ticket SET status='0',solution='this sucks' WHERE ticket_id ='$ticketid'";
mysql_query($query) or die('Error, query failed');
echo "The ticket is now closed good work!";
}

?>

<html>
<head>
<title>Close Ticket</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="style.css" rel="stylesheet" type="text/css">
<?php include ("admin_header.html") ?>
</head>
<body>
<?php echo $ticketid; ?>
<h3 align="center">Close Ticket</h3>
<table width="60%" border="1" align="center" cellpadding="1" cellspacing="1" bordercolor="#000000">
<form name="form1" method="post" action="admin_closeticket.php">

<td><input type="submit" value="submit" name="submit"></td>
</tr>
</form>
</body>
</html>

mikeblas · Nov 28, 2006

I don't see where you added code to echo your query string. Are you sure that you've got the quotes right to do variable substitution? That '$name' will correctly expand a variable, even though the token begins with an apostrophe and not a dollar?

sliperypete · Nov 28, 2006

v6maro said:
yes you are allowed to use the var like that

Do you have mysql admin? Try running your query against the database NOT in your php code, do you get results?

I use phpMyAdmin

I can run the query against my DB if I use static values instead of the variables.

sliperypete · Nov 28, 2006

mikeblas said:
I don't see where you added code to echo your query string. Are you sure that you've got the quotes right to do variable substitution? That '$name' will correctly expand a variable, even though the token begins with an apostrophe and not a dollar?

It seems that you are talking a bit over my head in most of your posts, This is my first project ever ....

Tweakin · Nov 28, 2006

When you say $ticketid is passed in the get query string I assume, from looking at your code, that register_globals are turned on in PHP. However, this is not PHP's normal behavior, so unless you changed it, you need to access $ticketid with $_GET.

Code:

$ticketid = $_GET['ticketid'];

Otherwise, there is a large amount of unknown information in your code/question. You are including a couple files we have not seen, there is at least one page before this script passing information which we have not seen, etc. If the above is not your problem, try providing a lot more detail about your problem and code, and please use the

Code:

tags[/B] available on the forums.

generelz · Nov 28, 2006

Generally in MySQL your primary key column (id) will be some sort of integer value - and it appears you are wrapping it in quotes. Perhaps that is the issue? It would help to see the mysql error message.

sliperypete · Nov 28, 2006

generelz said:
Generally in MySQL your primary key column (id) will be some sort of integer value - and it appears you are wrapping it in quotes. Perhaps that is the issue? It would help to see the mysql error message.

The ID is an integer value and it doesnt give a mysql error message the only message I get is:

The ticket is now closed good work!

which is only supposed to output after it is done successfuly

Fark_Maniac · Nov 28, 2006

Tweakin said:

... and please use the

Code:

tags[/B] available on the forums.[/QUOTE] and if we are doing PHP, we can have our code color coded using the [ PHP ] tag. [php]<?php session_start(); include "level2_check.php"; include("config.php"); print_r($_POST); $status = $_POST['status']; $solution = $_POST['solution']; if(isset($_POST['submit'])) { $query = "UPDATE ticket SET status='0',solution='this sucks' WHERE ticket_id ='$ticketid'"; mysql_query($query) or die('Error, query failed'); echo "The ticket is now closed good work!"; } ?>[/php]

sliperypete · Nov 28, 2006

Alright the $ticketid = $_GET['ticketid'] did not work. I will provide the page before it.

PHP:

[CODE]<?php
session_start();
include "level2_check.php";  THIS HERE IS JUST TO MAKE SURE AN ADMIN IS LOGGED IN THIS WORKS
include("config.php"); THIS IS THE DATABASE CONNECTION FILE THIS WORKS
print_r($_POST);   THIS IS HERE TO SEE THE ARRAY BEING POPULATED
$ticketid = $_GET['ticketid'];   THIS DID NOT WORK
$status = $_POST['status'];
$solution = $_POST['solution'];

if(isset($_POST['submit']))
{
    $query  = "UPDATE ticket SET status='0',solution='this sucks' WHERE ticket_id ='$ticketid'";I INSERTED VALUES HERE ONCE IT WORKS I WILL ADD VARIABLES
    mysql_query($query) or die('Error, query failed'); 
        echo "The ticket is now closed good work!";
}

?>

<html>
<head>
<title>Close Ticket</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="style.css" rel="stylesheet" type="text/css">
<?php include ("admin_header.html") ?>
</head>
<body>
<?php echo $ticketid; ?>
<h3 align="center">Close Ticket</h3>
<table width="60%" border="1" align="center" cellpadding="1" cellspacing="1" bordercolor="#000000">
<form name="form1" method="post" action="admin_closeticket.php">
   
  
          <td><input type="submit" value="submit" name="submit"></td>
    </tr>
     </form>
</body>
</html>[/CODE]

So that is the page that isnt working...Here is the page that links to it:

PHP:

[CODE]<?php 
session_start();
include "level2_check.php";
include("config.php");

$user = $_SESSION['username'];
$query = "SELECT * FROM `ticket` WHERE ticket_id = '$ticketid' ";
$results = mysql_query($query);



?>

<html>
<head>
<title>Edit Tickets</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="style.css" rel="stylesheet" type="text/css">
<?php include ("admin_header.html") ?>
</head>
<body>
<h3 align="center">View Ticket</h3>
<table width="60%" border="1" align="center" cellpadding="1" cellspacing="1" bordercolor="#000000">

<?php

while ($row = mysql_fetch_array($results))
{
extract($row);
?>
  <tr>
  <td width="20%"><div align="left"><strong>Ticket ID:</strong></div></td>
  <td width="20%"><div align="left"><strong><?php echo $ticket_id;?></strong></div></td>
  </tr>
  <tr>
  <td width="20%"><div align="left"><strong>User Name:</strong></div></td>
  <td width="20%"><div align="left"><strong><?php echo $user_name;?></strong></div></td>
  </tr>
  <tr>
  <td width="20%"><div align="left"><strong>Problem Category:</strong></div></td>
  <td width="20%"><div align="left"><strong><?php echo $problem_category;?></strong></div></td>
  </tr>
  <tr>
  <td width="20%"><div align="left"><strong>Problem Description:</strong></div></td>
  <td width="20%"><div align="left"><strong><?php echo $problem_description;?></strong></div></td>
  </tr>
  <tr>
  <td width="20%"><div align="left"><strong>Due Date:</strong></div></td>
  <td width="20%"><div align="left"><strong><?php echo $due_date;?></strong></div></td>
  </tr>
  <tr>
  <td width="20%"><div align="left"><strong>Open Date:</strong></div></td>
  <td width="20%"><div align="left"><strong><?php echo $open_date;?></strong></div></td>
  </tr>
   <tr>
   <td width="20%"><div align="left"><strong>Tech Assigned:</strong></div></td>
   <td width="20%"><div align="left"><strong><?php echo $tech_name;?></strong></div></td>
  </tr>

<?php

}
?>
<td width="20%"><div align="center"><strong><a href="admin_closeticket.php?ticketid=<?php echo $ticket_id;?>" </a>Close Ticket</strong></div></td>       HERE IS THE LINK TO THE PAGE THAT DOESNT WORK
</table>
[/CODE]

</body>

</html>

sliperypete · Nov 28, 2006

Would someone be as kind as helping over either aim or msn to figure this out, I am desperate at this point I have to present this for my class on Thursday and its not close to done. I only need help fixing this one part.........

v6maro · Nov 29, 2006

did you ever put echo $query; in your code? (this will show you what you are passing to the db)
Does the Id get put in that query string properly? ( or does it show $ticket_id)

Does the database HAVE that id # on the ticket table?

In this part of your code (on the link page)

PHP:

<td width="20%"><div align="center"><strong><a href="admin_closeticket.php?ticketid=<?php echo $ticket_id;?>" </a>Close Ticket</strong></div></td>

When you highlight the ticket id link, can you copy the shortcut and paste it here? (right click on the link and click Copy Shortcut)

I have a feeling that when you call this on your second page:
$ticket_id = $_GET['ticketid'];

it comes up empty because nothing is in the URL/GET.

You can try to echo $ticket_id at that point to see what value is there. If the value is there, something else is wrong and I cant see it yet.

sliperypete · Nov 29, 2006

FIXED !!! thanks everyone for the help...

v6maro · Nov 29, 2006

what was the problem?

Tweakin · Nov 29, 2006

v6maro said:
what was the problem?

We can assume it was the mistyped HTML anchor that you pointed out.

A good lesson for the OP though- when you need help with a script, post the whole script

mgz · Nov 30, 2006

I took the libery to help him over AIM, He had the $ticket_id field in the query but didn't set it properly to equal $_GET[ticketid] from the URL with a couple other minor things we adjusted.

sliperypete · Nov 30, 2006

mgz said:
I took the libery to help him over AIM, He had the $ticket_id field in the query but didn't set it properly to equal $_GET[ticketid] from the URL with a couple other minor things we adjusted.

yup so once I got the proper lesson / page re coded I was good to go. I now understand it and was able to finish my project !!! Thanks all that help / followed the thread

Updating a mysql record with php

Limp Gawd

[H]ard|DCer of the Month - May 2006

Limp Gawd

[H]ard|DCer of the Month - May 2006

Limp Gawd

[H]ard|DCer of the Month - May 2006

Limp Gawd

Limp Gawd

[H]ard|Gawd

[H]ard|Gawd

[H]ard|Gawd

Limp Gawd

[H]ard|DCer of the Month - May 2006

Limp Gawd

Limp Gawd

[H]ard|Gawd

Limp Gawd

Limp Gawd

2[H]4U

Limp Gawd

Limp Gawd

[H]ard|Gawd

Limp Gawd

[H]ard|Gawd

[H]ard|Gawd

n00b

Limp Gawd