• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

php delete sql help with script editing

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
paddy086
Asked:
paddy086
  • 3
  • 3
1 Solution
 
Ray PaseurCommented:
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
 
paddy086Author Commented:
so what do i do from here then. do i edit the whole scripts or not
0
 
Ray PaseurCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
paddy086Author Commented:
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
 
Ray PaseurCommented:
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
 
paddy086Author Commented:
Thanks Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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