Solved

php delete sql help with script editing

Posted on 2013-02-01
6
351 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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 …

726 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