Insert to database variable with single quotes

Hello i have problem with insert in sql .
I get string from variable and can have quotes or somting else special symbol.
Example : 006. Christina Novelli - It’ll End In Tears
i upload 1000 file names, can’t change one by one and need to be with original dir path after call it.

Thanks.

You need to look into “Prepared Statements”. In this procedure, you do not actually alter the data before storing it. It protects you from the data and protects your database from quotes. Another way would be to changes them all into HTML entities. This would change them into hex codes. But, I think you need to look into prepared statements.

  //      function button1() {
//			$dir1 = "C:\\\\xampp\\\\htdocs\\\\php\\\\playlist1";   
//			$files = scandir($dir1);
//			foreach($files as $filename) {
//			$ext = pathinfo($filename, PATHINFO_EXTENSION);
//				 if($filename != '.' && $filename != '..' && $ext == 'mp3'  ){		 
//			     $GLOBALS['dirg'] = $dir1. "<br>"; 
//				 $dbdir1=$dir1."\\\\".$filename;
//				 $name = $filename;
//				
//
//
//include 'config.php';
//$sql = "INSERT INTO  playlist (name, dir)
//VALUE ('$name','$dbdir1')";
//
//if ($conn->query($sql) === TRUE) {
//    echo "record inserted successfully";
//} else {
 //   echo "Error: " . $sql . "<br>" . $conn->error;
//}

just when I think I’m one step ahead, I find myself five steps back

if($filename != ‘.’ && $filename != ‘…’

First, do not use fancy quotes. Turn them off in your editor. Which editor are you using?
Next, check filename for one dot and two dots, not three.
Lastly, to use prepared statements, you set up, or, PREPARE, the insert operation. Then for each file, execute the insert. Something, loosely like this:

$stmt = $conn->prepare("INSERT INTO playlist (name, dir) VALUES ( ?, ? )");
$stmt->bind_param("s", $filename, $dbdir1);

// set parameters and execute
$dir1 = “C:\xampp\htdocs\php\playlist1”;
$files = scandir($dir1);
foreach($files as $filename) {
$ext = pathinfo($filename, PATHINFO_EXTENSION);
if($filename != "." && $filename != ".." && $ext ==  "mp3" ){
    $dbdir1 = $dir1 . "\" . $filename;
    $name = $filename;
    $stmt->execute();
}

Untested, just set up to loop thru the filenames without fancy-quotes and PREPARED-STATEMENTs.

I read Prepared Statements and help me ! Thanks again :slight_smile:

I work with Notepad++ :smiley:

Glad I could help… Oh, also, when you use prepared statements, you don’t actually touch the data, it gets stored as it is. So, you can prepare it once for loops like this and then reuse the prepared statement over and over using the same variables. Handy…

Now everything work but time for insert is very big 300 records for 2 min. Its normal ?

These are not editor problems. It is this forum software, which since you are a moderator on should know that it ‘publishes’ text when you don’t tell it to treat it as code.

@samoraj, I have edited your code above (left all the extra \\ and //) to add bbcode [code][/code] tags so that it will be highlighted and formatted as code.

        function button1() {
			$dir1 = "C:\\xampp\\htdocs\\php\\playlist1";   
			$files = scandir($dir1);
			foreach($files as $filename) {
			$ext = pathinfo($filename, PATHINFO_EXTENSION);
				 if($filename != '.' && $filename != '..' && $ext == 'mp3'  ){		 
			     $GLOBALS['dirg'] = $dir1. "<br>"; 
				 $dbdir1=$dir1."\\".$filename;
				 $name = $filename;
				


include 'config.php';

$stmt = $conn->prepare("INSERT INTO playlist (name, dir) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $dbdir1);
$stmt->execute();

				 
	 }
 }
 
        }

Well, you don’t need to include the config.php file every time you insert anything, just move it right after the function line.

Again its slow… Dont andarstent why.

        function button1()
		{
			include 'config.php';
			$dir1 = "C:\\xampp\\htdocs\\php\\playlist1";   
			$files = scandir($dir1);
			foreach($files as $filename) {
			$ext = pathinfo($filename, PATHINFO_EXTENSION);
				 if($filename != '.' && $filename != '..' && $ext == 'mp3'  ){		  
				 $dbdir1=$dir1."\\".$filename;
				 $filename;
				




$stmt = $conn->prepare("INSERT INTO playlist (name, dir) VALUES (?, ?)");
$stmt->bind_param("ss", $filename, $dbdir1);
$stmt->execute();	


				 
	 }
 }
 
        }

Question? What do you mean by slow? If the page itself loads slowly, you can use AJAX to load the rest of the page depending on what you actually mean by slow.

I have found that if you are using a display of many images, it can be slow. But, using PHP server-side before the page actually is viewed, it should not be slow. Especially if you are just showing a list of file names.

What is running slow in your opinion? Just curious…

I refresh phpMyAdmin and recording on database its slow. 2 min for 300 records

Afret use Prepared Statements goin slower.

Hmmm… If you use a program inserting data, you don’t monitor it using phpMyAdmin. That would interrupt the process while it refreshes. But, 300 records in 2 minutes is a problem. I will take a guess that you have incorrectly indexed the database table. You should have a primary index on the id column. And, limit all the other indexes. In most simple tables, all you need is the one primary index. Is your table indexed?

yes i have id AI . I can to set limit for INT and Varchar to be a small.

45 sec for 300 records with minimal size of int and varchar…

Sponsor our Newsletter | Privacy Policy | Terms of Service