Generating .sql file with php

[watsmyname]

Hello,

well, my database has a table in which i have longblob field, where images and other files are saved in binary format. My necessity is to generate a .sql file that contains insert statement of the rows. I have used fopen and fputs function for that, but the problem is that when i  execute back the query from generated sql file, the longblob field contains plain text instead of Binary/image format. So please anybody help me with this!

 

<?
$bid_doc_sql2="SELECT * from tbl_bid_document WHERE tender_bid_id='1'";
	$brs2=mysql_query($bid_doc_sql2) or die(mysql_error());
	if(mysql_num_rows($brs2) > 0){

		$openfile=fopen("data_backups/".$filename,"w");
		while($row2=mysql_fetch_array($brs2))
		{

			$content .="insert into `tbl_bid_document_additional` (`tender_b_id`, `tender_bd_id`, `document_f`, `document_c`, `document_t`, `document_s`, `document_time`) values(`$row2[tender_b_id]`, `$row2[tender_bd_id]`, `$row2[document_f]`, `$row2[document_c]`, `$row2[document_t]`, `$row2[document_s]`, `$row2[document_time]`);\n";


		}

	}
	fputs($openfile,$content);
	fclose($openfile);

// where document_c is the longblob field
?>

 

Thanks

watsmyname

[trq]

Is there any particular reason you have chosen to use php to create these dumps? MySql is quite capable of doing this alone.

[watsmyname]

Thanks for the reply, i have backend control panel for the website. I dont have to backup whole database or even a whole table, my client just chooses a date and a ID, and rows matching his selection will be generated as SQL file with insert statement of each matching rows.

 

Hope this makes you bit clear.

[watsmyname]

Help would be much appreciated Anybody please??

[watsmyname]

Is there any particular reason you have chosen to use php to create these dumps? MySql is quite capable of doing this alone.

@thorpe

 

you seem to be online, can you help me on this, i m not being able to get it work... I also noticed that blob content exported by phpmyadmin and my code are different. When i execute the insert statment generated by phpmyadmin, it works well with blob field binary/image data

[ChaosKnight]

Try this:

<?php
  //DB Connection goes here
  $tableName  = 'table';
  $backupFile = 'backup/backup.sql';
  $query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
  mysql_query($query);
?>

You can also upload the backup back to the db:

<?php
  //DB Connection goes here
  $tableName  = 'table';
  $backupFile = 'backup/backup.sql';
  $query      = "LOAD DATA INFILE '$backupFile' INTO TABLE $tableName";
  mysql_query($query);
?>

I noticed that you mentioned the binary/image data that you have in your database as a blob, please take note that it's not a good practice to do it like that, if possible try to upload the images to your server as that will be much better in the long term... The reason why I say this, is because images are large, once your server load increases and a lot of people requests the images from the database, then your database is going to work overtime, and then you will have to scale your database and put it onto it's own server, and use another language for backend, because PHP is not such a good backend language... It's not necessary, but it's better