Page 1 of 1

fun with Mysql

Posted: Mon Aug 09, 2004 7:07 pm
by Pizmal
feyd | Please use

Code: Select all

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


Having two problems.  My insert command doenst actually put the data into the data base. Also Delete doesnt recalulate the workorder_id number so i have black numbers in the count. 

ex.  If i delete 4:
Count comes up  1,2,3,5,6   is this natural?

Here is code if you have any ideas or tips would be greatful:

Code: Select all

<?php

$user = $_SERVER['PHP_AUTH_USER'];
$password = $_SERVER['PHP_AUTH_PW'];
// If id var was passed there will be from the search page or update

$link = mysql_connect( "localhost", $user, $password );
	if ( ! $link ) {
		die( "Couldnt connect to mysql db".mysql_error() );
}
mysql_select_db( workorders, $link) or die(mysql_error() );


if ($submit) {

	if ($id) {
	$sql = "UPDATE workorders SET first_name='$first',last_name='$last',building='$building',room='$room',status='$status',tech_solution='$solution' WHERE workorder_id=$id";
	// upcomdate
	
	} else {
	$sql = "INSERT INTO workorders ( workorder_date, first_name, last_name, building, room, status, problem ) values( '$date', '$first', '$last', '$building', '$room', '$status', '$problem'";

	}
	$result = mysql_query($sql);
  	echo "Record updated or created!";
	mysql_close( $link );
	return true;


} elseif ($delete) {
$sql = "DELETE FROM workorders WHERE workorder_id=$id";	
$result = mysql_query($sql);
echo "Record deleted!";
return true;

} else {

	if ($id) {

	$sql = "SELECT * FROM workorders WHERE workorder_id = $id";
	$result = mysql_query($sql);

	$num_rows = mysql_num_rows( $result );
	while ( $row = mysql_fetch_assoc( $result ) ) {
		$workorder_date = stripslashes($row['workorder_date']);
		$first = stripslashes($row['first_name']);
		$last = stripslashes($row['last_name']);
		$room = stripslashes($row['room']);
		$solution = stripslashes($row['tech_solution']);
		$problem = stripslashes($row['problem']);
		$resolved = stripslashes($row['resolved_date']);

				$building = stripslashes($row['building']);
					$buildings = array( "Office", "Shafer", "HighSchool", "Lower", "Bushkill", "Middle");
						foreach ($buildings as $val) {
							$build .= "<option value='$val'";
								if($val==$building) {
								$build .= " selected";
								}
						$build .= ">$val</option>";
						}

		$status = stripslashes($row['status']);
	
		function statusbox(){
		global $status;
		if ($status=="New"){
			echo "New: <input type=radio name=upstatus value=New checked> <br> Complete: <input type=radio name= upstatus value=Complete>";
		} ELSE {
			echo "New: <input type=radio name=upstatus value=New> <br> Complete: <input type=radio name=upstatus value=Complete checked>";
		}
		// close status box
		}

mysql_close($link);

}	
	?>
	
	<html>
	<body>
	<table border="0" width="100%" height="100%">
	<form method="POST" action="<? $_SERVER['PHP_SELF'] ?>">
	<tr>
	<td>Work Order Number: <? echo $id ?></td>
	<td>Work Order Date: <? echo date("m/d/y g:i:s", $workorder_date ) ?></td> </tr>
	<tr>
	<td>First Name: <input type=text name=first value=<? echo $first ?>></td>
	<td>Building: <select name=building><? echo $build ?></select></td>
	</tr>
	<tr>
	<td>Last Name: <input type=text name=last value=<? echo $last ?>></td>
	<td>Room: <input type=text name=room value=<? echo $room ?>></td>
	</tr>
	<tr>
	<td colspan="2">Problem:<br> <textarea rows=5 cols=60 name=problem><? echo $problem ?></textarea></td> </tr>
	<tr>
	<td colspan="2"> Solution:<br> <textarea rows=5 cols=60 name=solution><? echo $solution ?></textarea></td> </tr>
	<tr>
	<td>Status<BR> <? statusbox(); ?></td>
	<td>Date Complete: <? echo $resolved ?></td>
	</tr>
	</table>
	<input type="submit"  name="submit" value="Submit">
	<input type="submit"  name="delete" value="Delete">
	</form>
	</body>
	</html>

<?php
	
	return true;

	} ELSE {
	
?>
	<html>
	<body>
	<table border="0" width="100%" height="100%">
	<form method="POST" action="<? $_SERVER['PHP_SELF'] ?>">
	<tr>
	<td>First Name: <input type=text name=first></td>
	<td>Building: <select name="building">
				<option value="Office">Office
				<option value="Shafer">Shafer
				<option value="Lower">Lower
				<option value="Bushkill">Bushkill
				<option value="HighSchool">High School
				<option value="Middle">Middle
			   </select></td>
	</tr>
	<tr>
	<td>Last Name: <input type=text name=last></td>
	<td>Room: <input type=text name=room></td>
	</tr>
	<tr>
	<td colspan="2">Problem:<br> <textarea rows=5 cols=60 name=problem></textarea></td>
      </tr>
	</table>
	<input type="submit"  name="submit" value="Submit">
	</form>
	</body>
	</html>

<?php
	}
}

?>

feyd | Please use

Code: Select all

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

Posted: Mon Aug 09, 2004 9:16 pm
by Pizmal
fix posting problem.

I was missing a closing ) on my insert command. My question about the delete does anyone know the answer. Will I always have those missing numbers or is there a command to remove those?

Sorry about missing the tags. I will remember next time!

From
Pizmal

Posted: Mon Aug 09, 2004 9:52 pm
by andre_c
That is the correct behavior. If you want to get rid of the gaps you'll need to write some code that will read the rows and rewrite the ids.

Posted: Tue Aug 10, 2004 12:22 am
by phice
On another note, you need to put quotes around "workorders" inside your mysql_select_db() function.

Posted: Tue Aug 10, 2004 1:32 am
by timvw
And lookup what transactions are (or what functions your dbms offers to do the same).

Because, if 2 ppl delete something at the same time, the updating of the workorder_id's might go very ugly ;)

Posted: Tue Aug 10, 2004 5:58 am
by Pizmal
Thank you for your help! I will add the quotes like you said and look up what functions are with mysql. The gaps arent really bothering me yet.... (looks around) just wanted to know if that is how it is suppose to be working.

From
Pizmal

Posted: Tue Aug 10, 2004 8:53 am
by Pyrite
When deleting a MySQL record that using an ID as a primary key and is auto_incremented, this ensures that and ID # will never be used twice and once deleted, will never be used again. This helps ensure referential integrity. So yes it is very normal.