Solved

php delete sql help with script editing

Posted on 2013-02-01
6
352 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 110

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 110

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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 110

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

695 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