fun with Mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Pizmal
Forum Newbie
Posts: 19
Joined: Wed Jul 14, 2004 7:43 pm
Location: USA

fun with Mysql

Post 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]
Pizmal
Forum Newbie
Posts: 19
Joined: Wed Jul 14, 2004 7:43 pm
Location: USA

Post 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
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post 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.
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

On another note, you need to put quotes around "workorders" inside your mysql_select_db() function.
Image Image
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ;)
Pizmal
Forum Newbie
Posts: 19
Joined: Wed Jul 14, 2004 7:43 pm
Location: USA

Post 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
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post 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.
Post Reply