WHILE Statements and MYSQL

[jonmkim]

I'm trying to loop through the a database twice, first separating results by date then grouping all the items reserved on that date (via a randomly generated ID number for those devices).

 

The result is something like this:

Feb. 10, 2009

- scissors (325889)

- pencil (325889)

 

Feb. 23, 2009

- markers (439587)

- scissors (439587)

- pencil (439587)

- scotch tape (439587)

 

etc....

 

 

I want to put a button next to each item so that each specific item can be deleted from the database.  Any ideas of how to do this?

 

 

// Select all reservations with the same ID number whose status is set to "Approved"
$list_devices = (mysql_query("SELECT * FROM reservations WHERE ID='$unique_id' AND `status`='Approved'"));

// Find and list all devices under that reservation
while ($result = mysql_fetch_array($list_devices)) {

	if (isset($_POST)) {
		// If the button is pressed, concantate the item's 4-digit tag number and the reservation ID number to create a unique identifier for that device
		echo "<input type='hidden' value='$tagID' name='$tagID'>";
		$tagID = $result['tag'] .":" . $result['ID'];
	} // end if

	// Show results for testing purposes			
	echo $result['tag']." ".$result['ID'];
	echo "<input type='hidden' value='$result[iD]' name='removeID'>";
	echo "   <input type='submit' value='x' name='submit'><br />";

} // end while

if (isset($_POST['submit'])) {			

$result = explode(":",$tagID);
mysql_query("DELETE FROM reservations WHERE tag='$result[0]' AND ID='$result[1]' LIMIT 1");
echo $result[0] . " " . $result[1];
echo " YES!<br />";

} // end if

[gevans]

You can do the above by filtering. Just get the entries from the database that you require, rather than get all of them and make php do the work.

 

As far as your delete button goes, you can echo out a link with something like the following... delete.php?id=1 (whatever your id may be)

[jonmkim]

You can do the above by filtering. Just get the entries from the database that you require, rather than get all of them and make php do the work.

 

As far as your delete button goes, you can echo out a link with something like the following... delete.php?id=1 (whatever your id may be)

 

Hey gevans, can you give an example of doing the delete button?

[gevans]

lets say we're getting a users first and second name from a database, the table would also contain a unique id field...

 

<?php
//db connection left out
$query = "SELECT fnm, snm, id FROM `user`";
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result) > 0) {
while($details = mysql_fetch_assoc($result)){
echo "{$details['fnm']} {$details['snm']} - <a href=\"delete.php?id={$details['id']}\">delete</a><br />";
}
}

[jonmkim]

lets say we're getting a users first and second name from a database, the table would also contain a unique id field...

 

<?php
//db connection left out
$query = "SELECT fnm, snm, id FROM `user`";
$result = mysql_query($query) or die(mysql_error());
if(mysql_num_rows($result) > 0) {
while($details = mysql_fetch_assoc($result)){
echo "{$details['fnm']} {$details['snm']} - <a href=\"delete.php?id={$details['id']}\">delete</a><br />";
}
}

 

I apologize if this is bothersome, but could you or someone else make this advice more explicit?

[gevans]

<?php
//db connection left out

//this is the query to get everything from the database
$query = "SELECT fnm, snm, id FROM `user`";

//this runs the query
$result = mysql_query($query) or die(mysql_error());

//if there was at least one result loop through them
if(mysql_num_rows($result) > 0) {
while($details = mysql_fetch_assoc($result)){

//echo the name followed by a link to another file delete.php
/*
* delete.php should take the id from the url and
* delete it from the database with a query like...
* $query = "DELETE FROM `table` WHERE id=$id";
*/
echo "{$details['fnm']} {$details['snm']} - <a href=\"delete.php?id={$details['id']}\">delete</a><br />";
}
}