Solved

php delete sql help with script editing

Posted on 2013-02-01
6
348 Views
Last Modified: 2013-02-05
Hi I have been working at this for a while now and am stuck. SQL database layout

Database name: test
Database table: teacher
Rows : id & name

First script is a form called (delete1.php) in it uses a php script (php.php) to gadder the names from a SQL database and put them into a drop down menu this all works.

Delete1.php
<html> 
<head><title>Teacher</title></head> 

<body> 
<h2 align=center><font color=blue>Delete a Student Record</font> </h2><p> 

Enter the student number of the student to be deleted:<br> 
<?php include('php.php'); ?>
<form action="delete1.php" method="POST"> 

Student Number: <select name="[<?php echo $i ?>]"><?php echo $options ?></select><p> 

<input type="submit" value="Delete"> 
<input type="reset" value="Reset"> 

</form>  
</body> 
</html>

Open in new window


php.php
<?php 

// Connect to server 
// Replace username and password by your details  

$db = @mysql_connect("localhost","root","iqonr301"); 
if (!$db) 
{ 
        do_error("Could not connect to the server"); 
} 


// Connect to the database 
// Note that your database will be called username 

@mysql_select_db("test",$db)or do_error("Could not connect to the database"); 

//connect to db first
$options = '';
$teachers = mysql_query('SELECT * FROM teacher ORDER BY name ASC');
while($teacher = mysql_fetch_array($teachers)) {
     $options .= sprintf("<option value='%s'>%s</option>", $teacher['name'], $teacher['name']);
}
$class_options = '';
$classes = mysql_query('SELECT * FROM class');
while($class = mysql_fetch_array($classes)) {
     $class_options .= sprintf("<option value='%s'>%s</option>", $class['name'], $class['name']);
}
$room_options = '';
$rooms = mysql_query('SELECT * FROM room');
while($room = mysql_fetch_array($rooms)) {
     $room_options .= sprintf("<option value='%s'>%s</option>", $room['number'], $room['number']);
}
$subject_options = '';
$subjects = mysql_query('SELECT * FROM subject');
while($subject = mysql_fetch_array($subjects)) {
     $subject_options .= sprintf("<option value='%s'>%s</option>", $subject['name'], $subject['name']);
}
?>

Open in new window


delete1.php
<html> 
<body> 

<?php 
// Read student_no from form using $_POST (safest) 

$id=$_POST["name"]; 

// Connect to server 
// Replace username and password by your details  

$db = @mysql_connect("localhost","root","iqonr301"); 
if (!$db) 
{ 
        do_error("Could not connect to the server"); 
} 


// Connect to the database 
// Note that your database will be called username 

@mysql_select_db("test",$db)or do_error("Could not connect to the database"); 

// Run query 

$sql="DELETE FROM teacher WHERE name=$id"; 

$result=mysql_query($sql,$db); 

// Check that query resulted in a deleted record 

$affected=mysql_affected_rows(); 

if ($affected) 
{ 

        // $affected is non-zero 

        echo "Record: $id has been deleted."; 

} 
else 
{ 

        // $affected is zero so record does not exist 

        do_error("No such record"); 

} 

function  do_error($error) 
{ 
        echo  $error; 
        die; 
} 

?> 

</body> 
</html>

Open in new window













It’s the 3th script I need help
With as I can’t get a link between choosing a name from the drop down menu from the form to be deleted in the delete1.php

Could someone help me with the editing of the script thanks
0
Comment
Question by:paddy086
  • 3
  • 3
6 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 38845183
Before you do anything else, learn about this function.  With the script posted above a hacker could wipe out your data base in a matter of seconds!
http://www.php.net/manual/en/function.mysql-real-escape-string.php

This explains why you're at risk when you use external data in a query.
http://xkcd.com/327/
0
 

Author Comment

by:paddy086
ID: 38845248
so what do i do from here then. do i edit the whole scripts or not
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 38845274
This shows the general design pattern that you might use to follow the data from the data base, to the dropdown select, to the action script that creates a delete query.  Look it over and post back if you still have any questions.  
http://www.laprbass.com/RAY_temp_paddy086.php

<?php // RAY_temp_paddy086.php
error_reporting(E_ALL);

// THIS SIUMLATES THE CONTENTS OF A DATA BASE QUERY RESULTS SET
$rows = array
( array('id' => 1, 'name' => 'Ray')
, array('id' => 2, 'name' => 'Joe')
, array('id' => 3, 'name' => 'Bob')
)
;

// IF ANYTHING HAS BEEN POSTED
if (!empty($_POST))
{
    // USE MYSQL_REAL_ESCAPE_STRING ON THE $POST FIELDS!
    $id  = $_POST['id'];

    // CREATE THE QUERY USING THE ESCAPED DATA
    $sql = "DELETE FROM myTable WHERE id = $id LIMIT 1";

    // RUN THE QUERY HERE
    var_dump($sql);
}

// IF NOTHING HAS BEEN POSTED YET, USE THE SIMULATED QUERY RESULTS SET
// TO CREATE THE DROPDOWN SELECT BOX
$select = '<select name="id">' . PHP_EOL;
$select .= '<option value="0" selected>Choose</option>' . PHP_EOL;

// ADD THE OPTIONS, USING THE SIMULATED DB RESULTS
foreach ($rows as $row)
{
    $select .= '<option value="' . $row['id'] . '">' . $row['name'] . '</option>' . PHP_EOL;
}

// CLOSE OFF THE SELECT CONTROL
$select .= '</select>' . PHP_EOL;

// RENDER THE FORM USING HEREDOC NOTATION
$form = <<<EOD
<form method="post">
$select
<input type="submit" />
</form>
EOD;

echo $form;

Open in new window

Best, ~Ray
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.

 

Author Comment

by:paddy086
ID: 38851872
Hi Ray sorry its been a few days since my last post. i get he following when i use your script
string(40) "DELETE FROM teacher WHERE id = 1 LIMIT 1"

<?php // RAY_temp_paddy086.php
error_reporting(E_ALL);
$db = @mysql_connect("localhost","myuser","mypassword"); 
if (!$db) 
{ 
        do_error("Could not connect to the server"); 
} 

// Connect to the database 
// Note that your database will be called username 

@mysql_select_db("test",$db)or do_error("Could not connect to the database"); 

// THIS SIUMLATES THE CONTENTS OF A DATA BASE QUERY RESULTS SET
$rows = array
( array('id' => 1, 'name' => 'Ray')
, array('id' => 2, 'name' => 'Joe')
, array('id' => 3, 'name' => 'Bob')
)
;

// IF ANYTHING HAS BEEN POSTED
if (!empty($_POST))
{
    // USE MYSQL_REAL_ESCAPE_STRING ON THE $POST FIELDS!
    $id  = $_POST['id'];

    // CREATE THE QUERY USING THE ESCAPED DATA
    $sql = "DELETE FROM teacher WHERE id = $id LIMIT 1";

    // RUN THE QUERY HERE
    var_dump($sql);
}

// IF NOTHING HAS BEEN POSTED YET, USE THE SIMULATED QUERY RESULTS SET
// TO CREATE THE DROPDOWN SELECT BOX
$select = '<select name="name">' . PHP_EOL;
$select .= '<option value="0" selected>Choose</option>' . PHP_EOL;

// ADD THE OPTIONS, USING THE SIMULATED DB RESULTS
foreach ($rows as $row)
{
    $select .= '<option value="' . $row['id'] . '">' . $row['name'] . '</option>' . PHP_EOL;
}

// CLOSE OFF THE SELECT CONTROL
$select .= '</select>' . PHP_EOL;

// RENDER THE FORM USING HEREDOC NOTATION
$form = <<<EOD
<form method="post">
$select
<input type="submit" />
</form>
EOD;

echo $form; 

Open in new window


the script only seems to use your simulated data of names and not look at the DATABASE (test)   TABLE (teacher)   row (id) row (name)

any sugestions thanks
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38854597
any sugestions thanks
Yes.  Get this book.  From the dialog here it is obvious to me that you need some foundation in how PHP and SQL work together.  You need this background knowledge so that you can take a teaching example and modify it to work with your data, test it with your test data set, etc.  This is not rocket science but it takes some time and some structured learning to grasp the concepts.  If you study the SitePoint book and follow the excellent examples you will put yourself a year ahead in a matter of weeks, I promise!
http://www.sitepoint.com/books/phpmysql5/

the script only seems to use your simulated data of names and not look at the DATABASE (test)   TABLE (teacher)   row (id) row (name)
Yes, of course.  I do not have your test data set, so I cannot test with actual data base queries.  It would be up to you to remove the simulation and replace it with your own SELECT query that created the data for the $rows variable.

Best regards and best of luck with the project, ~Ray
0
 

Author Closing Comment

by:paddy086
ID: 38856794
Thanks Ray
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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
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.

773 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