12 Replies - 747 Views - Last Post: 20 April 2013 - 02:54 PM Rate Topic: -----

#1 musicgrain1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 24-September 11

Upload & Delete Script, Adding New Code.

Posted 18 April 2013 - 07:34 AM

I've been working on this for many hours with no success.
I want to add "if(unlink($img_dir.$image_name){}" to code that already deletes entrys from Mysql database, but I want to delete the Image from the server at the same time. I'm having trouble adding it with out knowing the proper placement of "{" "}". Anyone that can help me, I'd appreciated it. Also if you something else that could use some correcting that would be awesome.

index.php
$host = ''; // Host name
    $username = ''; // Mysql username
    $password = ''; // Mysql password
    $db_name = ''; // Database name
    $tbl_name = 'Vendors'; // Table name
    $pathdb = '/venderbp/200/'; //Store Images DB

// Connect to server and select databse.
    mysql_connect($host, $username, $password) or die('cannot connect');
    mysql_select_db($db_name) or die('cannot select DB');

    $sql = 'SELECT * FROM `'.$tbl_name.'` WHERE Path="'.$pathdb.'"';
    $result = mysql_query($sql);
?>
<center>
<table width="400" border="0" cellspacing="1" cellpadding="0">
    <tr>
        <td>
            <form name="form1" method="post" action="delete.php">
            <table width="400" border="1" cellpadding="3" cellspacing="1">
            <tr>
                <td colspan="5" align=center valign=middle><strong>Delete multiple rows in mysql</strong> </td>
            </tr>
            <tr>
                <td align=center valign=middle>#</td>
                <td align=center valign=middle><strong>Id</strong></td>
                <td align=center valign=middle><strong>Storename</strong></td>
                <td align=center valign=middle><strong>Image</strong></td>
                <td align=center valign=middle><strong>Email</strong></td>
            </tr>
            <?php while ($rows = mysql_fetch_array($result)): ?>
            <tr>
                <td align=center valign=middle><input name="need_delete[<? echo $rows['id']; ?>]" type="checkbox" id="checkbox[<? echo $rows['id']; ?>]" value="<? echo $rows['id']; ?>"></td>
                <td align=center valign=middle><? echo $rows['id']; ?></td>
                <td align=center valign=middle><? echo htmlspecialchars($rows['Storename']); ?></td>
                <td align=center valign=middle><img src=../venderbp/200/images/<? echo $rows['Imagename']; ?> width=100 height=80/></td>
                <td align=center valign=middle><? echo htmlspecialchars($rows['Email']); ?></td>
            </tr>
            <?php endwhile; ?>
            <tr>
                <td colspan="2" align=right valign=middle></td>
                <td align=center valign=middle><input name="delete" type="submit" id="delete" value="Delete"></td>
                <td align=center valign=middle><input type="reset" value="Reset!">
               </td>
               <td></td>
            </tr>
            </table>
            </form>
        </td>
    </tr>
</table>



I had trouble with which order the "If" statements were placed and couldn't get the error about having to many headers to stop. I've tried to have the insert.php code take me back to index and have the "echo" present its self with the correct messages to allow the user to know what went right or wrong.
The first code here is what was left out of the insert because I don't know how to put it in correctly.

Upload Original code:
<?php
$allowedExts = array("gif", "jpeg", "jpg", "png");
$extension = end(explode(".", $_FILES["file"]["name"]));
if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/jpg")
|| ($_FILES["file"]["type"] == "image/png"))
&& ($_FILES["file"]["size"] < 20000)
&& in_array($extension, $allowedExts))
  {
  if ($_FILES["file"]["error"] > 0)
    {
    echo "Return Code: " . $_FILES["file"]["error"] . "<br>";
    }
  else
    {
    echo "Upload: " . $_FILES["file"]["name"] . "<br>";
    echo "Type: " . $_FILES["file"]["type"] . "<br>";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
    echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br>";

    if (file_exists("upload/" . $_FILES["file"]["name"]))
      {
      echo $_FILES["file"]["name"] . " already exists. ";
      }
    else
      {
      move_uploaded_file($_FILES["file"]["tmp_name"],
      "upload/" . $_FILES["file"]["name"]);
      echo "Stored in: " . "upload/" . $_FILES["file"]["name"];
      }
    }
  }
else
  {
  echo "Invalid file";
  }
?>




insert.php Without If code:
<?php
$doc = ($_SERVER['DOCUMENT_ROOT']);
$con = mysql_connect("Host","User","Password");

mysql_select_db("Datebase", $con);

$_POST[imagename] = $_FILES["image"]["name"];
$_POST[type] = $_FILES["image"]["type"];
$_POST[size] = $_FILES["image"]["size"];

$sql="INSERT INTO Vendors 
(
Storename, 
Imagename,
Type,
Size,
Path
)

VALUES
(
'".ucwords(strtolower(trim(stripslashes(mysql_real_escape_string($_POST[storename])))))."',
'".mysql_real_escape_string($_POST[imagename])."',
'".mysql_real_escape_string($_POST[type])."',
'".mysql_real_escape_string($_POST[size])."',
'".strtolower(trim(stripslashes(mysql_real_escape_string($_POST[path]))))."'
)";

$allowedExts = array("gif", "GIF", "jpeg", "JPEG", "jpg", "JPG", "png", "PNG");
$extension = end(explode(".", $_FILES["image"]["name"]));
if ((($_FILES["image"]["type"] == "image/gif")
|| ($_FILES["image"]["type"] == "image/GIF")
|| ($_FILES["image"]["type"] == "image/jpeg")
|| ($_FILES["image"]["type"] == "image/JPEG")
|| ($_FILES["image"]["type"] == "image/jpg")
|| ($_FILES["image"]["type"] == "image/JPG")
|| ($_FILES["image"]["type"] == "image/png")
|| ($_FILES["image"]["type"] == "image/PNG"))
&& ($_FILES["image"]["size"] < 2097152)
&& in_array($extension, $allowedExts))
     {
      move_uploaded_file($_FILES["image"]["tmp_name"],
      "../venderbp/200/images/" . $_FILES["image"]["name"]);
      }
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  {
header("Location: index.php");
  }

mysql_close($con)
?>




delete.php
<?php
    $host = ''; // Host name
    $username = ''; // Mysql username
    $password = ''; // Mysql password
    $db_name = ''; // Database name
    $tbl_name = 'Vendors'; // Table name
    $pathdb = '/venderbp/200/'; //Store Images DB
    $img_dir = '../venderbp/200/images/'; 
    $image_name = $row['imagename'];

    // Connect to server and select databse.
    mysql_connect($host, $username, $password) or die('cannot connect');
    mysql_select_db($db_name) or die('cannot select DB');

    $sql = 'SELECT * FROM `'.$tbl_name.'` WHERE Path="'.$pathdb.'"';
    $result = mysql_query($sql);

                // Check if delete button active, start this
                if ( ! empty($_POST['delete'])) {
                    foreach ($_POST['need_delete'] as $id => $value) {
                        $sql = 'DELETE FROM `'.$tbl_name.'` WHERE `id`='.(int)$id;
                        mysql_query($sql);
         }						
         }
if (!$result)
  {
  die('Error: ' . mysql_error());
  }
  {
header("Location: index.php");
  }

mysql_close($con)
?>


This post has been edited by Dormilich: 20 April 2013 - 01:59 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Upload & Delete Script, Adding New Code.

#2 ChrisGulddahl  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 18
  • Joined: 17-April 13

Re: Upload & Delete Script, Adding New Code.

Posted 18 April 2013 - 11:03 AM

I don't understand what you mean by proper placement of "{" "}".
But I think I understand what you want you code to do.
Firstly, you'll want to use a single query to delete the image rows from the database
instead of making a query for each image to delete.
(A query takes a lot of time, so you should aim for executing as few as possible).
As a rule of thumb, whenever you find yourself executing queries inside a loop
you're probably doing it wrong.
In the code below you fetch the image names just before deleting the DB rows
and afterwards the files are deleted.
It's not tested so a few errors may have sneaked in - let me know if you have any problems.

<?php
    $host = ''; // Host name
    $username = ''; // Mysql username
    $password = ''; // Mysql password
    $db_name = ''; // Database name
    $tbl_name = 'Vendors'; // Table name
    $pathdb = '/venderbp/200/'; //Store Images DB
    $img_dir = '../venderbp/200/images/'; 
    //$image_name = $row['imagename']; // is this used?

    // Connect to server and select databse.
    $con = mysql_connect($host, $username, $password) or die('cannot connect');
    mysql_select_db($db_name) or die('cannot select DB');

    
	//What are these 2 lines for?
	//$sql = 'SELECT * FROM `'.$tbl_name.'` WHERE Path="'.$pathdb.'"';
    //$result = mysql_query($sql);

                // Check if delete button active, start this
                if ( ! empty($_POST['delete'])) {
					if(isset($_POST['need_delete']) && size($_POST['need_delete']) > 0){
						$ids = array();
						foreach ($_POST['need_delete'] as $id => $value) {
							$ids[] = (int)$id; // make sure all ids are numbers (and not harmful injections)
						}
						$ids_string = implode(',', $ids); // comma-separated string of ids to delete
						// Execute a SELECT and DELETE statement in one transaction
						$sql = 'SELECT `imagename` FROM `'.$tbl_name.'` WHERE `id` IN ('.$ids_string.');'
							.'DELETE FROM `'.$tbl_name.'` WHERE `id` IN ('.$ids_string.')';
						$result = mysql_query($sql);
						if (!$result){
							die('Error: ' . mysql_error());
						}
						else{
							while ($row = mysql_fetch_array($result)){
								if(!unlink($img_dir.$row['imagename'])){
									// Failed to delete image file
								}
							}
							header("Location: index.php");
						}
					}					
         }
mysql_close($con)
?>


Good luck!

// Chris
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,876
  • Joined: 12-December 12

Re: Upload & Delete Script, Adding New Code.

Posted 18 April 2013 - 11:33 AM

Quote

As a rule of thumb, whenever you find yourself executing queries inside a loop
you're probably doing it wrong.

I wouldn't use this as a "rule of thumb" as it isn't the case with prepared statements, which the OP should look into, as well as not using the deprecated mysql library - see my signature.

Choosing an API
Was This Post Helpful? 0
  • +
  • -

#4 ChrisGulddahl  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 18
  • Joined: 17-April 13

Re: Upload & Delete Script, Adding New Code.

Posted 18 April 2013 - 01:23 PM

Good point - that rarely holds for prepared statements. However even with prepared statements every execute() causes a query to hit the database, so for large enough number of queries there will be significant performance gains with bulk queries.
That being said I agree with you - in this case there shouldn't be noticeable performance hits with prepared statements executed in a loop (after all no sane person would check more than 100 checkboxes before hitting submit!).

This post has been edited by Dormilich: 20 April 2013 - 01:53 PM
Reason for edit:: please do not quote unnecessarily

Was This Post Helpful? 0
  • +
  • -

#5 musicgrain1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 24-September 11

Re: Upload & Delete Script, Adding New Code.

Posted 18 April 2013 - 10:57 PM

I put the code in and got an error:
Fatal error: Call to undefined function size() in delete.php on line 22

Not Used, was going to try to use it in code before.
//$image_name = $row['imagename']; // is this used?

Not Used, I guess it doesn't need to be in the code from what I can see.
//What are these 2 lines for?
//$sql = 'SELECT * FROM `'.$tbl_name.'` WHERE Path="'.$pathdb.'"';
//$result = mysql_query($sql);

Thank You for your time, this is a great help!

This post has been edited by Dormilich: 20 April 2013 - 01:52 PM
Reason for edit:: please do not quote unnecessarily

Was This Post Helpful? 0
  • +
  • -

#6 ChrisGulddahl  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 18
  • Joined: 17-April 13

Re: Upload & Delete Script, Adding New Code.

Posted 18 April 2013 - 11:14 PM

Oh, an error did sneak in - size() should be sizeof() (or count() - they're the same).

You're welcome!

This post has been edited by Dormilich: 20 April 2013 - 01:52 PM

Was This Post Helpful? 0
  • +
  • -

#7 musicgrain1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 24-September 11

Re: Upload & Delete Script, Adding New Code.

Posted 19 April 2013 - 08:17 AM

It only deletes entry in database and still leaves file on server.

I got this far and still having an error:

Quote

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in delete.php on line 31

Warning: Cannot modify header information - headers already sent by (output started at delete.php:31) in delete.php on line 37

<?php
    $host = ''; // Host name
    $username = ''; // Mysql username
    $password = ''; // Mysql password
    $db_name = ''; // Database name
    $tbl_name = 'Vendors'; // Table name
    $pathdb = '/venderbp/200/'; //Store Images DB
    $img_dir = '../venderbp/200/images/'; 
    $image_name = $row['imagename']; // Image Name

    // Connect to server and select databse.
    $con = mysql_connect($host, $username, $password) or die('cannot connect');
    mysql_select_db($db_name) or die('cannot select DB');

                // Check if delete button active, start this
                if ( ! empty($_POST['delete'])) {
		    if(isset($_POST['need_delete']) && count($_POST['need_delete']) > 0){
			$ids = array();
			foreach ($_POST['need_delete'] as $id => $value) {
			$ids[] = (int)$id; // make sure all ids are numbers (and not harmful injections)
			}
			$ids_string = implode(',', $ids); // comma-separated string of ids to delete
			// Execute a SELECT and DELETE statement in one transaction
			$sql = 'SELECT `'.$image_name.'` FROM `'.$tbl_name.'` WHERE `id` IN('.$ids_string.')';
                        $sql = 'DELETE FROM `'.$tbl_name.'` WHERE `id` IN('.$ids_string.')';
			
			$result = mysql_query($sql);
			if (!$result){
			    die('Error: ' . mysql_error());
			}else{
			    while ($row = mysql_fetch_array($result)){
				if(!unlink($img_dir.`'.$image_name.'`)){
				    // Failed to delete image file
			        }
			    }
			    header("Location: index.php");
		        }
                    }					
         }
mysql_close($con)
?>


This post has been edited by Dormilich: 20 April 2013 - 01:52 PM
Reason for edit:: please do not quote unnecessarily

Was This Post Helpful? 0
  • +
  • -

#8 ChrisGulddahl  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 18
  • Joined: 17-April 13

Re: Upload & Delete Script, Adding New Code.

Posted 19 April 2013 - 10:12 AM

All right I tested this locally so it shouldn't give you errors.
Interestingly it seems you can't put multiple queries inside a mysql_query() function (I'm not sure about this, but using multi_queries solved the issue).
So you need to use the mysqli extension instead of the (deprecated) mysql extension.
Also beware that if the mysql-connection fails and prints out error messages the user won't be redirected to the index.php page, so maybe you shouldn't print out error messages. Your call.
<?php
    $host = ''; // Host name
    $username = ''; // Mysql username
    $password = ''; // Mysql password
    $db_name = ''; // Database name
    $tbl_name = 'Vendors'; // Table name
    $img_dir = '../venderbp/200/images/';

    // Connect to server and select databse.
    $link = mysqli_connect($host, $username, $password, $db_name);
	if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
			
            // Check if delete button active, start this
                if ( ! empty($_POST['delete'])) {
					if(isset($_POST['need_delete']) && count($_POST['need_delete']) > 0){
						$ids = array();
						foreach ($_POST['need_delete'] as $id => $value) {
							$ids[] = (int)$id; // make sure all ids are numbers (and not harmful injections)
						}
						$ids_string = implode(',', $ids); // comma-separated string of ids to delete
						// Execute a SELECT and DELETE statement in one transaction
						$sql = 'SELECT `imagename` FROM `'.$tbl_name.'` WHERE `id` IN ('.$ids_string.');'
							.'DELETE FROM `'.$tbl_name.'` WHERE `id` IN ('.$ids_string.');';
						if(mysqli_multi_query($link, $sql)){
							$result = mysqli_store_result($link);
							while ($row = mysqli_fetch_row($result)){
								if(!unlink($img_dir.$row[0])){
									// Failed to delete image file
								}
							}
							mysqli_free_result($result);
							header("Location: index.php");
						}
					}					
         }
mysqli_close($link);
?>

Was This Post Helpful? 0
  • +
  • -

#9 musicgrain1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 24-September 11

Re: Upload & Delete Script, Adding New Code.

Posted 19 April 2013 - 11:52 PM

Now when I check a box then click delete and it goes to delete.php. Its just blank white page and when I hit back and refresh the page nothing was deleted.

I saw that I might be able to add "@" to "@mysql_query" if I want to prevent errors from printing.

Your continued help is greatly appreciated.

This post has been edited by Dormilich: 20 April 2013 - 01:51 PM
Reason for edit:: please do not quote unnecessarily

Was This Post Helpful? 0
  • +
  • -

#10 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3541
  • View blog
  • Posts: 10,236
  • Joined: 08-June 10

Re: Upload & Delete Script, Adding New Code.

Posted 20 April 2013 - 02:06 AM

View PostChrisGulddahl, on 18 April 2013 - 10:23 PM, said:

Good point - that rarely holds for prepared statements. However even with prepared statements every execute() causes a query to hit the database, so for large enough number of queries there will be significant performance gains with bulk queries.

hm, thatís not really the case. Prepared Statements do not issue a new query each time the execute() method is called (that is why they are faster than lots of regular queries). PSís execution only submits data to the DB and there the DB engine just puts the data in (the SQL statement is already parsed, optimised and ready to go). from the amount of transferred data there is insignificant difference between a bulk query and a PS. but the bulk query still has to be secured against SQL Injection.
Was This Post Helpful? 1
  • +
  • -

#11 musicgrain1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 24-September 11

Re: Upload & Delete Script, Adding New Code.

Posted 20 April 2013 - 10:14 AM

For the problem I had with the code not going any further from the white blank page I looked once more at the code and realized that I left out the Database name. I sometimes feel like an idiot when I overlook something so little. LOL!

As for anything else my next step is to impalement a start page that creates the database - data with php script form, creates standerd user and password to get in to Admin page with session with timestamp timer to logout user and password change option, plus logout button, preventing access to php files referred directly, also use #id at end of addresses to make one page for hyperlinks with if statments on the page. Don't have a clue on most of these, but with spend hours researching and adding them in.

Thanks for your continued help on this code.

This post has been edited by Dormilich: 20 April 2013 - 01:50 PM
Reason for edit:: please do not quote unnecessarily

Was This Post Helpful? 0
  • +
  • -

#12 ChrisGulddahl  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 18
  • Joined: 17-April 13

Re: Upload & Delete Script, Adding New Code.

Posted 20 April 2013 - 02:23 PM

View PostDormilich, on 20 April 2013 - 10:06 AM, said:

View PostChrisGulddahl, on 18 April 2013 - 10:23 PM, said:

Good point - that rarely holds for prepared statements. However even with prepared statements every execute() causes a query (data packets) to hit the database, so for large enough number of queries there will be significant performance gains with bulk queries (maybe due to the latency introduced with each round-trip).

hm, that’s not really the case. Prepared Statements do not issue a new query each time the execute() method is called (that is why they are faster than lots of regular queries). PS’s execution only submits data to the DB (exactly!) and there the DB engine just puts the data in (the SQL statement is already parsed, optimised and ready to go). from the amount of transferred data there is insignificant difference between a bulk query and a PS(agreed). but the bulk query still has to be secured against SQL Injection.

Oh yes, my statement was indeed worded quite badly. Even though approximately the same amount of data is transferred, the network latency (and maybe some tiny overhead with putting in the data) introduced with each round-trip (caused by PDOStatement::execute) will start to add up for large number of inserts/updates/deletes.

To convince myself of this I did some testing.
Here are the results
Spoiler

The conclusion I drew from this was that lots of PS executes can be significantly slower than ordinary mysqli bulk inserts.
Btw, I have no idea why inserting 1 row with mysqli batch is so slow! (this happened consistently every time). Also I tested inserts because it's easier, and thus these trends may or may not apply for deletes as well (intuitively they should though).
Lastly, the amount of data per insert was tiny in this test. Larger amounts of data could yield different results.

And here is the test code:
Spoiler


Also I realize this is getting off-topic - sorry about that.

This post has been edited by ChrisGulddahl: 20 April 2013 - 02:24 PM

Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,876
  • Joined: 12-December 12

Re: Upload & Delete Script, Adding New Code.

Posted 20 April 2013 - 02:54 PM

@Chris Shouldn't you be comparing multiple inserts against multiple executions of a single prepared statement? Comparing a single insert, created using string concatenation, seems a little unfair :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1