Solved

MySQL & PHP Code

Posted on 2011-03-11
16
427 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 109

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 109

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
 
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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Changing Two Areas of a Page 5 17
Save ms data to server side. 19 44
Little bit of help styling my heading 3 20
CSS: How do I override in-line styling 11 15
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
In this tutorial viewers will learn how to style a corner ribbon overlay for an image using CSS Create a new class by typing ".Ribbon":  Define the class' "display:" as "inline-block": Define its "position:" as "relative": Define its "overflow:" as …
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

777 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