Threaded Mode | Linear Mode

slangnit · 10-19-2008, 03:52 AM

I am having the worst time coming up with a script to UPDATE a field where a certain id=$id. Someone please help this php newb!

***goughy000*** · (This post was last modified: 10-19-2008 09:21 AM by goughy000.)

PHP Code:

<?php
//connect as usual
mysql_connect("localhost","username","password");
mysql_select_db("mydb");

//the id we want to update
$id = "123";

//query, special notes: i always use backticks around table names (the one above tab on most keyboards) ` and i always use ' around data. also the $id is joined with . outside the " (if that all makes sense)
$query = mysql_query("UPDATE `table` SET `username` = 'I pwn you noobs' WHERE `id` = '".$id."'");

//Just so we can have some output...
if(mysql_affected_rows($query) > 0){
   echo "Rows updated";
}else{
   echo "No row with that ID exists!";
}
?>

darkfate · 10-19-2008, 03:17 PM

Assuming that $id variable is going to come from the user possibly, I would sanitize it before going into the database. Also, most times the id is an int (at least in my experience), so you don't need the quotes if the database is expecting an int.

slangnit · 10-19-2008, 04:01 PM

I think that script is going to work... I'm just having a little trouble making it work with my edit repair page page. I am trying to update the status of a field called status by selecting a certain ticket number. Heres the page that shows all the repairs.

PHP Code:

<!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=utf-8" />
<title>Edit Repairs</title>
<style type="text/css">
<!--
body {
    background-image: url(../../home/pics/content_container.jpg);
    background-repeat: repeat-x;
}
.content {
    height: auto;
    width: 2000px;
    margin-right: auto;
    margin-left: auto;
    padding-right: 10px;
    padding-left: 10px;
}

-->
</style>
</head>

<body>
<div class="content"><h1><font color="#005f9f">Next2New Repairs</font></h1>
<table border="1" cellpadding="2" cellspacing="2" style="font-weight:bold; background-color:#CCCCCC;">
  <tr>
    <td width="50"><div align="center">Ticket #</div></td>
    <td width="75"><div align="center">Submitted By</div></td>
    <td width="75"><div align="center">Time Submitted</div></td>
    <td width="150"><div align="center">Customer</div></td>
    <td width="125"><div align="center">Phone</div></td>
    <td width="200"><div align="center">Email</div></td>
    <td width="75"><div align="center">Make</div></td>
    <td width="75"><div align="center">Model</div></td>
    <td width="200"><div align="center">ESN</div></td>
    <td width="250"><div align="center">Problem Description</div></td>
    <td width="75"><div align="center">Date Shipped</div></td>
    <td width="75"><div align="center">Recieved By N2N</div></td>
    <td width="200"><div align="center">Status</div></td>
    <td width="75"><div align="center">Shipped By N2N</div></td>
    <td width="75"><div align="center">Delete All</div></td>
  </tr>
</table>
<?

mysql_connect("", "", "") or die(mysql_error()) ;
mysql_select_db("") or die(mysql_error()) ;

// if id provided, then delete that record
if (isset($_GET['ticket_number'])) {

// create query to delete record
    $query = "DELETE FROM repairs WHERE ticket_number = ".$_GET['ticket_number']; 
    
    // execute query
    if ($results=mysql_query($query)) {
    // print number of affected rows
    echo "<b>"."Repair successfully deleted."."<br>";
    }
    else {
    // print error message
    echo "Error in query: $query. ";
    }
}


//Retrieves data from MySQL
$data = mysql_query("SELECT * FROM repairs ORDER BY ticket_number DESC") or die(mysql_error());

//outputs data if the there if there is at least one object in the database
if (mysql_num_rows($data) > 0) {

//Puts it into an array
while($info = mysql_fetch_array( $data ))
{
//Output Data
 echo "<table height=100% cellpadding=2 cellspacing=2 border=1 style=background-color:#FFFFFF>";
echo "<tr>";
echo "<td width=50 valign=middle align=center><font color=\"#005f9f\">".$info['ticket_number'] . "</font></td>";
echo "<td width=75 valign=middle align=center><font color=\"#005f9f\">".$info['username'] . "</font></td>";
echo "<td width=75 valign=middle align=center><font color=\"#005f9f\">".$info['time_submitted'] . "</font></td>";
echo "<td width=150 valign=middle align=center><font color=\"#005f9f\">".$info['last_name'] . ", " .$info['first_name']. "</font></td>";
echo "<td width=125 valign=middle align=center><font color=\"#005f9f\">".$info['phone'] . "</font></td>";
echo "<td width=200 valign=middle align=center><font color=\"#005f9f\">".$info['email'] . "</font></td>";
echo "<td width=75 valign=middle align=center><font color=\"#005f9f\">".$info['make'] . "</font></td>";
echo "<td width=75 valign=middle align=center><font color=\"#005f9f\">".$info['model'] . "</font></td>";
echo "<td width=200 valign=middle align=center><font color=\"#005f9f\">".$info['esn'] . "</font></td>";
echo "<td width=250 valign=middle align=center><font color=\"#005f9f\">".$info['description'] . "</font></td>";
echo "<td width=75 valign=middle align=center><font color=\"#005f9f\">".$info['date_shipped'] . "</font></td>";
echo "<td width=75 valign=middle align=center><font color=\"#005f9f\">".$info['date_recieved_n2n'] . "</font></td>";
echo "<td width=200 valign=middle align=center><font color=\"#005f9f\"><a href='edit_repair_status.php?ticket_number=".$info['ticket_number']."'>Update</a></font></td>";
echo "<td width=75 valign=middle align=center><font color=\"#005f9f\">".$info['date_shipped_n2n'] . "</font></td>";
echo "<td width=75 valign=middle align=center><font color=\"#005f9f\"><a href=".$_SERVER['PHP_SELF']."?ticket_number=".$info['ticket_number'].">Delete</a></font></td></tr>";

}

    echo "</table>";
}
?>

I just can't seem to get it to update the status of only one ticket number? Thanks for all the help.

darkfate · 10-19-2008, 04:10 PM

I don't see an update query anywhere. Unless you don't know where to put it. I assume you want it after the get:

PHP Code:

<?php
if (isset($_GET['ticket_number'])) {
$q = "UPDATE repairs SET status = 3 WHERE id = {$_GET['ticket_number']}";
}
?>

I assume you want to update the status and not delete it.

slangnit · (This post was last modified: 10-21-2008 01:57 AM by slangnit.)

Problem solved. Thanks for all the help!

PHP Code:

if (isset($_GET['ticket_number'])) {
$query="UPDATE repairs SET status = 'Processed' WHERE ticket_number = {$_GET['ticket_number']}";

    // execute query
    if ($results=mysql_query($query)) {
    // print number of affected rows
    echo "<b>"."Status of repair successfully updated."."<br>";
    }
    else {
    // print error message
    echo "Error in query: $query. ";
    }
} 

Possibly Related Threads...
Thread:		Author	Replies:	Views:	Last Post
	PHP MYSQL help please	project_syntax	11	563	01-02-2009 05:02 PM Last Post: Josh Connerty
	[Tips]Progogre - PHP (Non-mysql)	progogre	7	284	01-22-2008 04:49 PM Last Post: goughy000
	PHP and MySQL help!	presbyran	3	341	08-10-2007 01:26 PM Last Post: Birdie