if mysql_query or die???

[Nodral]

Hi All

 

I'm using a script to upload details from a csv file into a DB, however for security, I want to prevent an incorrect file being uploaded.  How can I use the mysql_query($sql) function to decide whether to continue with the script?  for exampl, if the function runs correctly then do one thing, however if the query falls over, do something else.

 

I know you can use or die, however I'm not sure how to use this other than output a message.

[WebStyles]

mysql_affected_rows() will give you a count of the changed rows.

[Nodral]

And if this returns as 0 then it's fallen over, however if this is 1 or more then run the INSERT statement?

[AyKay47]

without seeing code it's hard to tell what will suit your needs here, but you will probably want something like this...

 

$query = mysql_query($sql);

if(!$sql){
      die("There was a Mysql Error:<br />".mysql_error());
}else{
      // run normal code
}

[premiso]

Or die is not a valid way to throw errors or "do something else". Errors should be handled properly, whether it be checking if it failed in an if statement and then displaying a nice error message or throwing an exception or triggering an error. I would suggest reading or die must die for a bit more information on it.

[trq]

This is a question I wish more people would ask. Using die is often overkill as it kills your application completely, learning to handle situations is often the better choice.

 

And the syntax is simple.

 

For queries that are expected to return data, check that they return data:

 

if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    // $result has data and is good to use.
  } else {
    // no data found.
  }
} else {
  // query failed.
}

 

For queries that change data.

 

 

if ($result = mysql_query($sql)) {
  if (mysql_affected_rows($result)) {
    // data changed
  } else {
    // nothing changed
  }
} else {
  // query failed.
}

[Nodral]

I have the 'or die' statement in my code at the moment which is fine for testing the sql processing.  However I now need something to prevent a user trying to upload an incorrect csv file, which at the moment would throw out a 'die' error.

 

eg if my column names are name, DOB, phone number and someone tries to upload a csv which has 6 columns I want to simply output a message saying "There is a problem with your csv file, please check and retry"  However at the moment I get the Warning and blah blah about sql errors which my users will panic about.

[Nodral]

Sorry, I think we both posted at the same time.

 

Great answer, just implementing now.

[AyKay47]

implementing thorpe's or my code, instead of using the die function and mysql_error(), if your query fails, simply output a custom error message to the user(s)

[Nodral]

Ok I've tried this and it's not giving me the correct response.  Because the user could be uploading a file which does affect a row before the sql statement falls over, thorpe's code will not work.

 

Again with AyKay's code, it is possible to upload part of a file before the code falls over so this is not great either.

 

Please see my code below.  I want to upload a full csv (possibly a few hundred lines) but only if every line is correct.  Is this possible, or do I need to serously rejig things?

 

I'd also like to be able to detect the headers of the columns in the file and check these match prior to upload as currently I'm manually deleting the headers so they don't get uploaded as a record.  Any ideas to this?

 

<?php
session_start();
include_once('connect.php');
include_once('functions.php');
include('is_logged_in.php');
$host  = $_SERVER['HTTP_HOST'];
$uri   = rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
$filename="users\user_import.csv";

if(move_uploaded_file($_FILES["fileToUpload"]["tmp_name"],
  "$filename")){
//read csv file and sort names out.

$handle=fopen("$filename",r);
$user=file($filename);
foreach($user as $value){
$value=str_replace('"','',$value);
//echo $value . "<br>";
$user_upload=explode(",",$value);
$surname_upload=cleanInput(ucfirst(strtolower(trim($user_upload[0]))));
$firstname_upload=cleanInput(strtolower($user_upload[1]));
$firstname_upload=cleanInput(ucfirst(trim($firstname_upload)));
$user_upload[2]=cleanInput(trim($user_upload[2]));
$ed_upload=cleanInput(str_pad($user_upload[2], 5 , "0", STR_PAD_LEFT));
$password=md5(cleanInput(strtolower($surname_upload)));
//insert into user table
$sql="INSERT INTO pfp_user (ed, firstname, lastname, role, complete, password) VALUES ('$ed_upload', '$firstname_upload', '$surname_upload', 'user', 'no', '$password')";

$result = mysql_query($sql) ;
	if (!$sql){
	$_SESSION['output']='<div class="warning">There is an error with your csv file<br>Please check and try again</div>';
			fclose($handle);
			unlink($filename);
			$extra = 'admin_users.php';
			header("Location: http://$host$uri/$extra");
}


		  
 else {
	 $_SESSION['output']='<div class="pagetext">Users Successfully Uploaded</div>';
		fclose($handle);
		unlink($filename);
		$extra = 'admin_users.php';
		header("Location: http://$host$uri/$extra");
			  
			}
}				
}else {   

			$extra = 'admin_users.php';
			header("Location: http://$host$uri/$extra");  
			}

?>

 

 

[premiso]

If you are messing with csv files, why not use fgetcsv. This should make working with the file much easier and not have the need to using explode etc.

[Nodral]

Hi

 

The explode thing is due to the format of the information in the csv file, the information as actuall saved as "SURNAME,FIRSTNAME",NUMBER in 2 columns.

 

So first I have to break it apart and format it in a usable way, there's also trailing spaces etc and the number needs to be a 5 digit number, but there are some which have the zeros included and some without.

 

Good call tho!!