Deleting records from database

[dodgei]

I'm using odbc to connect to my ms access database. I would like to display the records from a query and with each line
retrieved I would like to have a picture of a "x" next to the record that will delete that record if clicked.

What I dont understand is how I can single out that one record?

[Jessica]

does the row have a unique ID? Use that.

[dodgei]

It does have a unique ID. Its the id field is auto-numbered.

I'm using a loop to display all of the records so if I assign the id to a variable wont the
variable change with each new record retrieved?

The records are going to be retrieved and displayed with a loop.  What can I do so that
when I construct the delete statement it will delete that one record?

[Jessica]

When you retrieve it, save the id.
$array[$id] = $value;

Then print the $id in the link to delete.

[matto]

For each record selected from the database you will need to store the row id in a link:

eg:
[code]
<?php
$sql = "select id from table";
$results = @mysql_query($sql) or die("<tt>problem with $sql : " . mysql_error() . "</tt>\n");
while($r = @mysql_fetch_object($results)) {
 echo "<a href=\"" . $_SERVER['PHP_SELF'] . "?action=delete&row_id=" . $r->id . "><img src=\"images/x.gif\" border=\"0\"></a>\n";
}
?>
[/code]


You willl also need something like this in your page to handle the delete

[code]
if(isset($_GET['delete'])) {
 $sql = "delete from table where id = '" . $_GET['row_id'] . "'";
 @mysql_query($sql) or die("<tt>problem with $sql : " . mysql_error() . "</tt>\n");
}
[/code]

Hope this helps

;)

[dodgei]

I'm using odbc to connect to the database.  How will this affect the syntax??

Also, am I just querying the record id or can I query all the fields and display the
records with all the fields and also the picture for the delete?

[matto]

instead of using

[code]
mysql_query()
[/code]
use:
[code]
odbc_exec()
[/code]
and instead of using
[code]
mysql_fetch_object()
[/code]
use:
[code]
odbc_fetch_object()
[/code]
and for error reporting use:
[code]
odbc_errormsg()
[/code]
instead of using:
[code]
mysql_error()
[/code]



[quote]
Also, am I just querying the record id or can I query all the fields and display the
records with all the fields and also the picture for the delete?
[/quote]

Yes.....

[code]
select * from table...
[/code]

:)

[dodgei]

<?php

$odbc = odbc_connect ('accounting', 'root', '') or die('Could    Not Connect to ODBC Database!');
$sql = "select * from entry";
$results = @odbc_exec($odbc, $sql) or die("<tt>problem with $sql : " . odbc_errormsg() . "</tt>\n");
while($r = @odbc_fetch_object($results))
{
  echo "new";
  echo "<a href=\"" . $_SERVER['PHP_SELF'] . "?action=delete&row_id=" . $r->id . "><img src=\"/cal.gif\" border=\"0\"></a>\n";
 
}
odbc_close($odbc);
?>

this is what I have. I ran the page and it doesnt do anything. It doesnt even write "new". The gif file is in the same directory as the page. I'm not sure if I have the reference right on that one.

Is fetch object function similar to fetch array in that I can reference individual fields with $r['fieldname'];?

Can you see any errors in my code?

[matto]

Try this instead of fetch object:

[code]
<?php
$result = odbc_exec($odbc,$sql);
while(odbc_fetch_row($result)) {
  //as long as your first column is the id field
  $rowid = odbc_result($result,1);
  echo "<a href=\"" . $_SERVER['PHP_SELF'] . "?action=delete&row_id=" . $rowid . "><img src=\"/cal.gif\" border=\"0\">\n";
}
?>
[/code]

Sorry for the confusion, it's been a while since I used ODBC.....

:)

[dodgei]

That works for getting the right links assigned.

How can I display the fields of the records at the same time its assigning the links?

[matto]

Change you sql statment to "select * from table..."

then....

[code]
$rowid = odbc_result($result,1);
$field_2 = odbc_result($result,2);
$field_3 = odbc_result($result,3);
$field_4 = odbc_result($result,4);
$field_5 = odbc_result($result,5);
.....
[/code]