Solved

php delete sql help with script editing

Posted on 2013-02-01
6
344 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 108

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 108

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
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).

 

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 108

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

746 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

13 Experts available now in Live!

Get 1:1 Help Now