Page 1 of 2

Help: file write

Posted: Sat Aug 13, 2005 10:27 am
by raghavan20
I am trying to read all databases, tables and fields and write into a file myfile.sql.
Instead of opening the file multiple times while appending text, I want to open the file once and append all the text necessary and close the file at the end of append session.
I come up with error message saying " Warning: fwrite(): supplied argument is not a valid stream resource"
The file at action: http://raghavan20.allhyper.com/backupDatabase.php
//backupDatabase.php

Code: Select all

<?php
	//database connection provided
?>


<?php
//File functions
//open a file and return the pointer
function openFile($fileName){
	return fopen($fileName, 'r+') or die("can't open file"); 
}

//append to file
function appendToFile($str, $filePointer){
	fwrite($filePointer, $str);
}

//change file permission type
function changeFilePointerType($fileName, $accessType){
	return fopen($fileName, $accessType) or die("can't open file"); 
}

//close file
function closeFile($filePointer){
	fclose($filePointer);
}
?>
<?php
//open file
$filePointer = openFile("myfile.sql");

//change file permission to append
$filePointer = changeFilePointerType("myfile.sql", "a");

//start of file
echo "$$$"."<br />";

//find all databases
$query = "show databases";
$result = mysql_query($query);
if (is_resource($result)){
	if (mysql_num_rows($result) > 0){
		while ($row = mysql_fetch_row($result)){
			//allDatabases[i] =  $row[0]; //append every database to the array
			echo "##$row[0]##"."<br />"; //print database
			$str = "##$row[0]##";
			appendToFile($str, $filePointer); //append string to a file
			findAllTables($row[0], $filePointer);
		}
		
	}else{
		echo "No database found!!!";
	}
}
//end of file
echo "$$$"."<br />";

//close file
closeFile($filePointer);
?>

<?php
//find all tables
function findAllTables($db, &$filePointer){
		$query = "show tables from $db";
		$result = mysql_query($query);
		if (is_resource($result)){
			if (mysql_num_rows($result)){
				while ($row1 = mysql_fetch_row($result)){
					//allDatabases[i] =  $row1[0]; //append every database to the array
					echo "#$row1[0]#"."<br />"; //print table
					$str = "#$row[0]#";
					appendToFile($str, $filePointer); //append string to a file
					findAllFields($row1[0], $db, $filePointer);
				}
			}
		}
}

?>

<?
//find all fields
function findAllFields($table, $db, &$filePointer){
	$query = "show columns from $db.$table";
	$result = mysql_query($query);
	if (is_resource($result)){
		if (mysql_num_rows($result)){
			while ($row1 = mysql_fetch_row($result)){
				echo "-"."$row1[0]"."-"."<br />";
				$str = "$row[0]";
				appendToFile($str, $filePointer); //append string to a file
			}
		}
	}
}
?>

Posted: Sat Aug 13, 2005 10:41 am
by feyd
looks like the variables are getting switched around..

Posted: Sat Aug 13, 2005 10:43 am
by josh
I don't see where you are calling appendToFile()

Posted: Sat Aug 13, 2005 10:45 am
by raghavan20
appendToFile() is used to append each database, table and field values on to the end of the file

Posted: Sat Aug 13, 2005 11:14 am
by josh
Yes but where are you calling it?

Posted: Sat Aug 13, 2005 11:31 am
by raghavan20
I am calling appendToFile() at three places
1. when all databases are read
2. inside findAllTables()
3. inside findAllFields()

Posted: Sat Aug 13, 2005 12:58 pm
by raghavan20
somehow I managed the write the file in a different way, its not so slow to what I expected.
Anybody can help with the command to delete a file?
The original file has been modified so if anybody want to help with the earlier thing I was asking you, you can use the code I gave in the first post.

Posted: Sat Aug 13, 2005 1:29 pm
by josh
unlink() deletes

Posted: Sun Aug 14, 2005 7:21 am
by raghavan20
Which is the best way to add spaces between words while writing into a file?
\s or &nbsp;(doesnot seem to work as i think its only for html formatting) or I have to leave deliberate number of spaces by moving space bar.

Posted: Sun Aug 14, 2005 8:04 am
by feyd
\s is for regular expressions. It won't help here. If there's a specific number you need of spaces, str_repeat() may help.

Posted: Sun Aug 14, 2005 10:56 am
by raghavan20
I am creating table structures during backup.
I have got multiple databases and all have to backed up into a single file

This syntax does not work

Code: Select all

create table <dbname>.<tablename>(
....

)

Code: Select all

do I have to do something like this???
do I have to 'use database' before I create every table for a database?

Code: Select all

$query = "use $dbname";
mysql_query($query);
$createQuery = "create table <dbname>.<tablename>(..);";
mysql_query($createQuery);

Posted: Sun Aug 14, 2005 11:35 am
by John Cartwright

Posted: Sun Aug 14, 2005 11:52 am
by raghavan20
I was not actually looking for the create table syntax.

I am looking take out all the attributes of the table when forming the query for create table.

I used mysql_field_flags() to find whether a field has the value primary_key and I added
the following stmt:
PRIMARY KEY (<the field which had the primary_key attribute>)

Now, I am looking for ways to extract and foreign keys

Lets say, we have two tables created:

Code: Select all

CREATE TABLE parent(id INT NOT NULL,
                    PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) TYPE=INNODB;
But when I had the look at the field, parent_id of the table 'CHILD' using mysql_field_flags()
it displayed only

"`parent_id` INT(11) MULTIPLE_KEY, "

Now how do I extract more information on the foreign key???

Posted: Sun Aug 14, 2005 7:42 pm
by feyd

Code: Select all

DESCRIBE `tableName`

Posted: Mon Aug 15, 2005 6:40 am
by raghavan20
for the child table it displays as

Code: Select all

Field  Type  Null  Key  Default  Extra  
id int(11) YES   NULL   
parent_id int(11) YES MUL NULL
It has got the value MUL for the field key.
How do we list all the constraints for a table?