Solved

MySQL & PHP Code

Posted on 2011-03-11
16
425 Views
Last Modified: 2012-05-11
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
Comment
Question by:wantabe2
  • 3
  • 3
  • 3
  • +5
16 Comments
 
LVL 3

Expert Comment

by:flubbard
ID: 35112893
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
 
LVL 20

Accepted Solution

by:
Mark Brady earned 167 total points
ID: 35112929
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
 

Expert Comment

by:etanox
ID: 35113198
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
 
LVL 82

Expert Comment

by:hielo
ID: 35113482
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35114976
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
 
LVL 14

Expert Comment

by:dejaanbu
ID: 35115551
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 total points
ID: 35116662
@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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35116776
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 20

Expert Comment

by:Mark Brady
ID: 35124569
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 35124795
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
 
LVL 15

Author Comment

by:wantabe2
ID: 35127604
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35127649
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
 
LVL 15

Author Comment

by:wantabe2
ID: 35127683
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
 
LVL 3

Expert Comment

by:flubbard
ID: 35127777
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
 
LVL 27

Assisted Solution

by:Lukasz Chmielewski
Lukasz Chmielewski earned 167 total points
ID: 35127856
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
 
LVL 15

Author Comment

by:wantabe2
ID: 35128008
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now