[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

MySQL & PHP Code

The attached code pulls data from my MYSQL database & displays it. The is a delete link on there so the user can delete the line. The ID is an auto-incrementing number. Actually, when I run this code, it will delete the record I need it to delete BUT it does not look good in the browser when it displays the database records & I get the following error. The code,  & screenshot after I press delete is attached.

Can someone take a look at this & let me know where I've went wrong?
<table>
      <thead>
      <tr>
            <th scope="col">ID</th>
            <th scope="col">Order No:</th>      
            <th scope="col">First Name</th>
            <th scope="col">Last Name</th>
            <th scope="col">Status</th>
            <th scope="col">Delete</th>
      </tr>      
      </thead>
      <tbody>
<?php
require('connection.php');
$query="SELECT id, order_no, fname, lname, status FROM psrinfo";
if("d"==strval($_REQUEST['op']) && is_numeric($_REQUEST['id']) )
{
	$query="DELETE FROM `psrinfo` WHERE `id`=" . $_REQUEST['id'];
}
$result = mysql_query($query) 
or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
            <td><?php echo "".$row['id']; ?></td>
            <td><?php echo "".$row['pacts']; ?></td>
            <td><?php echo "".$row['fname']; ?></td>
            <td><?php echo "".$row['lname']; ?></td>
            <td><?php echo "".$row['status']; ?></td>
                <td><a href="<?php echo $_SERVER['PHP_SELF'];?>?op=d&id=<?php echo $row['id'];?>">delete</a></td>
      </tr>
<?php } ?>            
      </tbody>
 
</table>

Open in new window

after-delete-is-clicked.JPG
b44.JPG
0
wantabe2
Asked:
wantabe2
  • 3
  • 3
  • 3
  • +5
3 Solutions
 
flubbardCommented:
One problem appears to be that you are assigning the result of the mysql query to $row, yet the delete will not have a resulting row associated with it.  You could simply run the mysql_query.

 hth - flub
0
 
Mark BradyCommented:
I'm not sure what could be causing those errors but here is how I would do it. try it this way and compare the results. I haven't tested this but the code is much easier to follow so give it a shot.

<?php // test.php
require('connection.php');
$URL = $_SERVER['PHP_SELF']; // this does not ALWAYS work as expected so be careful. If possible use the actual page name
$op = $_GET['op'];
$id = $_GET['id'];
if($op == "d" && is_numeric($id)){
mysql_query("DELETE FROM `psrinfo` WHERE `id`= "$id"); // delete the id number
}

########################## CREATE TABLE ###########################
$table = "<table>
      <thead>
      <tr>
            <th scope=\"col\">ID</th>
            <th scope=\"col\">Order No:</th>      
            <th scope=\"col\">First Name</th>
            <th scope=\"col\">Last Name</th>
            <th scope=\"col\">Status</th>
            <th scope=\"col\">Delete</th>
      </tr>
      </thead><tbody>";
###################################################################
$query="SELECT id, order_no, fname, lname, status FROM psrinfo";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$_id = "{$row['id']}";
$pacts = "{$row['pacts']}";
$fname = "{$row['fname']}";
$lname = "{$row['lname']}";
$status = "{$row['status']}";
$table .= "<tr>
<td>$_id</td>
<td>$pacts</td>
<td>$fname</td>
<td>$lname</td>
<td>$status</td>
<td><a href=\"$URL?op=d&id=$_id\"><font color=red>delete</font></a></td></tr>";
}
$table .= "</tbody></table>";
// You could echo the $table here.... or.....
// End of PHP section
?>
<?php echo $table; ?>


The main problem with your code is you have an sql query to show the table but if the delete link has been clicked, that query is over-ridden by your delete query so once clicked, it will not show the table properly. Line 20: $result = mysql_query($query).... should really be inside the "if" result brackets not outside. Anyway, try it my way and you should see the data in your table with the delete links in it. Click "Delete and you will see it dissapear! It should work but let me know if you have any probs.
0
 
etanoxCommented:
when you delete the row the line

$result = mysql_query($query) delete the record

but after that you sould make the query to obtain the records remaining to display

I would do this:


if("d"==strval($_REQUEST['op']) && is_numeric($_REQUEST['id']) )
{
      $query="DELETE FROM `psrinfo` WHERE `id`=" . $_REQUEST['id'];
}
$result = mysql_query($query)
or die(mysql_error());  

 $query="SELECT id, order_no, fname, lname, status FROM psrinfo";
$result = mysql_query($query) ;
.....
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
hieloCommented:
On your ORIGINAL post, all you need to do is change line 18 to:
mysql_query("DELETE FROM `psrinfo` WHERE `id`=" . $_REQUEST['id']) or die(mysql_error() );
0
 
Lukasz ChmielewskiCommented:
If that does not work try with $_GET instead of request

<table>
      <thead>
      <tr>
            <th scope="col">ID</th>
            <th scope="col">Order No:</th>      
            <th scope="col">First Name</th>
            <th scope="col">Last Name</th>
            <th scope="col">Status</th>
            <th scope="col">Delete</th>
      </tr>      
      </thead>
      <tbody>
<?php
require('connection.php');

if($_REQUEST['op'] == "d" && !empty($_REQUEST['id']) )
{
   $query="DELETE FROM `psrinfo` WHERE `id`=" . $_REQUEST['id'];
   $result = mysql_query($query) or die(mysql_error());  
}

$query="SELECT id, order_no, fname, lname, status FROM psrinfo";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
            <td><?php echo "".$row['id']; ?></td>
            <td><?php echo "".$row['pacts']; ?></td>
            <td><?php echo "".$row['fname']; ?></td>
            <td><?php echo "".$row['lname']; ?></td>
            <td><?php echo "".$row['status']; ?></td>
                <td><a href="<?php echo $_SERVER['PHP_SELF'];?>?op=d&id=<?php echo $row['id'];?>">delete</a></td>
      </tr>
<?php } ?>            
      </tbody>
 
</table>

Open in new window

0
 
dejaanbuCommented:
Hi,

Others are answered well-enough. Though i am suggesting little.

When seeing the b44 image, u get a warning as undefined index op.. The cause is you have to check

isset($_REQUEST('op')  first, then if that is true, continue ur code.

The 2nd pbm is, you are using $query for both operations in the next lines...

organize the code, as others suggested.. you will get it work.

Good luck!.

0
 
Ray PaseurCommented:
@wantabe2, please let me provide a word of caution to you here.  It violates the laws of the HTTP if you modify the data model on the basis of a GET request.  Here is why that is such a risk.  Let's imagine that you have a web page with a table of records that contains a link to delete each record and the delete URL looks like this:

href="delete.php?k=123"

Let's also imagine that you have all of your records displayed on this page.  Now let's further imagine what will happen to your data base when Google or some similar spider finds your page and begins fetching all of the links.  There will be a flash, and a loud bang!  When the smoke clears, your table is empty.  End of story.

The correct design pattern includes using links like "delete.php?k=123" but instead of deleting the record on the basis of the GET string, the delete.php script needs to create a form using the POST method, probably with a question like "Are you sure you want to delete record 123?"  When this form is submitted it will be safe to delete the record.
0
 
Ray PaseurCommented:
This is untested code but it illustrates what I was describing in the post at ID:35116662.  Please read it over and post back if you have questions.  All the best, ~Ray
<?php // RAY_delete_example.php
error_reporting(E_ALL);


// DEMONSTRATE HOW TO HAVE A SAFE DELETE SCRIPT
// IF THE GET AND POST KEYS MATCH, IT IS OK TO DELETE


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// GET THE KEYS FROM GET AND POST ARRAYS
$pk = (!empty($_POST["key"])) ? (int)$_POST["key"] : 0;
$gk = (!empty($_GET["key"]))  ? (int)$_GET["key"]  : 0;

// IF BOTH KEYS ARE PRESENT, DELETE THE ROW (NOTE THERE ARE NO ROWS WITH KEY=0)
if ($pk == $gk)
{
    $sql = "DELETE FROM mytable WHERE mykey = $pk LIMIT 1";
    $res = mysql_query($sql) or die( mysl_error() );
    die("KEY $pk HAS BEEN DELETED");
}



// IF ONLY THE GET KEY IS PRESENT, PUT UP A MESSAGE AND A FORM
if ( (empty($pk)) && ($gk) )
{
	$sql = "SELECT myname FROM mytable WHERE mykey = $gk LIMIT 1";
	$res = mysql_query($sql) or die( mysql_error() );
	$num = mysql_num_rows($res);
	if ($num)
	{
	    // THE ROW EXISTS, GENERATE THE FORM FOR POST-METHOD SIGNAL TO DELETE
	    $row = mysql_fetch_assoc($res);
	    $nom = $row["myname"];
	    $uri = $_SERVER["REQUEST_URI"];

	    // CREATE THE FORM USING HEREDOC SYNTAX
	    $form = <<<ENDFORM

<form method="post" action="$uri">
CONFIRM REQUEST TO DELETE THE ROW WITH MYNAME=$nom
<input type="hidden" name="key" value="$gk" />
<input type="submit" value="DELETE RECORD NUMBER $gk" />
</form>
ENDFORM;

        echo $form;
        die();
    }

    // NO RECORD WAS FOUND
    else
    {
        die("NO RECORD FOUND FOR KEY=$gk");
    }
}

Open in new window

0
 
Mark BradyCommented:
either that or you can include a hidden field in your form and check for that before deleting the records.

Ray: Great suggestion about Google bot wiping out his entire table! Google can be your friend OR can be your worst enemy if your code structure is not well thought out and solid. Very important post in my opinion. Adding a hidden field in a form will fix that issue. At this point there are several posted solutions to this question so would be nice to hear from the author and see which way they want to go.
0
 
Ray PaseurCommented:
Adding a hidden input control in a POST method form will fix that issue.  But if you have a hidden input control in a GET method form, I am not so sure that you are safe.  However, it is time to help my son understand his form V Humanities homework (relate Darwinian theory to island biodiversity in the age of extinctions), so I  have to sign off on this for now.  Best to all, ~Ray
0
 
wantabe2Author Commented:
Roads_Roads,
The code you provided works better than anything I have so far. The only issue is, I am getting the attached error. I have uploaded it as a .jpg.

I get this error when I initially go to the page in the browser but I can not successfully delete the entries without getting an error. The issue now seems to be on line 16
<table>
      <thead>
      <tr>
            <th scope="col">ID</th>
            <th scope="col">Order No:</th>      
            <th scope="col">First Name</th>
            <th scope="col">Last Name</th>
            <th scope="col">Status</th>
            <th scope="col">Delete</th>
      </tr>      
      </thead>
      <tbody>
<?php
require('connection.php');

if($_REQUEST['op'] == "d" && !empty($_REQUEST['id']) )
{
   $query="DELETE FROM `psrinfo` WHERE `id`=" . $_REQUEST['id'];
   $result = mysql_query($query) or die(mysql_error());  
}

$query="SELECT id, order_no, fname, lname, status FROM psrinfo";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
            <td><?php echo "".$row['id']; ?></td>
            <td><?php echo "".$row['pacts']; ?></td>
            <td><?php echo "".$row['fname']; ?></td>
            <td><?php echo "".$row['lname']; ?></td>
            <td><?php echo "".$row['status']; ?></td>
                <td><a href="<?php echo $_SERVER['PHP_SELF'];?>?op=d&id=<?php echo $row['id'];?>">delete</a></td>
      </tr>
<?php } ?>            
      </tbody>
 
</table>

Open in new window

ttty1.JPG
0
 
Lukasz ChmielewskiCommented:
Try this

<table>
      <thead>
      <tr>
            <th scope="col">ID</th>
            <th scope="col">Order No:</th>      
            <th scope="col">First Name</th>
            <th scope="col">Last Name</th>
            <th scope="col">Status</th>
            <th scope="col">Delete</th>
      </tr>      
      </thead>
      <tbody>
<?php
require('connection.php');

if($_GET['op'] == "d" && !empty($_GET['id']) )
{
   $query="DELETE FROM `psrinfo` WHERE `id`=" . $_GET['id'];
   $result = mysql_query($query) or die(mysql_error());  
}

$query="SELECT id, order_no, fname, lname, status FROM psrinfo";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
            <td><?php echo "".$row['id']; ?></td>
            <td><?php echo "".$row['pacts']; ?></td>
            <td><?php echo "".$row['fname']; ?></td>
            <td><?php echo "".$row['lname']; ?></td>
            <td><?php echo "".$row['status']; ?></td>
                <td><a href="<?php echo $_SERVER['PHP_SELF'];?>?op=d&id=<?php echo $row['id'];?>">delete</a></td>
      </tr>
<?php } ?>            
      </tbody>
 
</table>

Open in new window


And mind what Ray suggested - it is - well, almost - safe in a local environment but putting it online can cause indexing with links directly to record deletion. Use form with post variable then.

The error says that there is no $_REQUEST['op'] variable
0
 
wantabe2Author Commented:
Still getting the same thing on the same line...I don't have anything in my table named "op" but I do have a field named "id"

What is the "op" trying to refer to?



0
 
flubbardCommented:
In Roads response, 'op' would be the variable passed to your script from a php form through the GET function.  You would need to pass a value for OP when calling this script.

  - flub
0
 
Lukasz ChmielewskiCommented:
This is from your original code:
if("d"==strval($_REQUEST['op']) && is_numeric($_REQUEST['id']) )

From what it seems op is the "operation" shortcut, when you click this:
<td><a href="<?php echo $_SERVER['PHP_SELF'];?>?op=d&id=<?php echo $row['id'];?>">delete</a></td>
      </tr>

So this should work:
if($_GET['op'] == "d" && !empty($_GET['id']) )

Take a look at your addressbar: is there something like blablabla?op=d&id=somenumber ?
0
 
wantabe2Author Commented:
I was able to fix this by adding:

if (isset($_GET['op']) && $_GET['op'] == "d")

above

if($_GET['op'] == "d" && !empty($_GET['id']) )

This seem to have taken care of my problem. I can't thank all of you enough for your assistance on this.  
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now