Php / Mysql issue

rob_illinois

I have a website hosted on a linux server (not mine) and I am running a php/mysql db. I have a script that lets me browse and upload a .txt file and it deletes current data from the mysql db and replaces with the new data I am uploading. The issue is the file size is to big now for the limits of the third party host and they will not change the restriction of 2meg. My file size is now at 5meg. I wrote a line of code and saved it as a .cnf file put it in the same dir as the .php file but no luck the line of code I used was:
upload_max_filesize = 4MB

I tried to override what they had, they said it would work but no luck. can someone please help I need to get this working by weeks end data has not been updated for over 2 weeks now.

Thanks

rachel2004

How about breaking your file into pieces and uploading the pieces? Copy 'n paste?

rob_illinois

Nope I can not do this due to the fact when I upload it the .txt file deletes everything in the current db and replaces it with the new information.

rachel2004

So baically this is a file with a series of SQL statements, right? Let me know... there shouldn't be a problem with separating SQL statements across multiple files. If all of your DROPs and then CREATEs occur in the first file, it shouldn't matter how many follow-up INSERT statements you have and whether they appear in that same file.

rob_illinois

It is just data in a text file. I have a php script done that I use to browse find the file and upload it, but this deletes all in the db and replaces with the new information from the text file. If I do a double on this the I would only get half of the information to the mysql db and that would not work. I thought about a cron job and script to do it auto but not good with it enough to try.

rachel2004

Then something, somewhere needs to issue SQL statements. You say that there is just "data in a text file" so your PHP script must be issuing the SQL statements. For argument's sake, if all you have is "data" in your text file, and your PHP script is reading this file and issuing statements to "delete" (you'll need to translate "delete" to an SQL command --delete a row or do you mean dropping a table? -- so I understand more clearly what you consider it to mean -- it means DROP to me) and then issuing INSERT statement. If that is the case, then you need to set a flag (either a checkbox or radio button) that your PHP script knows to look for and it it sees that it is enabled, then to issue the DROP statements. If you had broken up your text file into at least three files, this would be useful for the first file parsing. After that first DROPping of tables or the entire database, you can deselect that flag so your subsequent uploads of "just data" in the remaining text files will be put into the database with only INSERT statements since you no longer need to issue DROP statements.

rob_illinois

Yes the php script does issue sql statments to delete and insert.

this is the index.php I made to manually do this. I now need to have it so I can run a cron job and run the script and have it pull the file and upload it for me.

index.php:

<?php
//Check to see if the form was submited and the file was uploaded
if($POST['submit'] && $sql_file != 'none'){
//File Path and File Name
$fileName = listings.txt';
$path = $SERVER['DOCUMENT_ROOT']."/test/admin/".$fileName;

//copy the uploaded file to the server
$tempName = $HTTP_POST_FILES['sql_file']['tmp_name'];
move_uploaded_file($tempName,$path);

//Create MySQL Class Instance
$MySQL = new MySQL;

//Delete records in the database
$MySQL->query_mysql("DELETE FROM MK_LISTINGS");

//Open and Read File
$handle = fopen($path, "r");

while (!feof($handle)) {
	//Read in each line of the file and put it into buffer
	$buffer = fgets($handle, 4096);

	//Create MySQL Insert Query
	$result = "INSERT INTO `MK_LISTINGS` VALUES (\"\",$buffer)";

	//Check to see if the buffer is not empty, if not insert the record
	if($buffer != ''){
		$MySQL->query_mysql($result);
	}
}
fclose($handle);

//Delete the file on the server
@unlink($path);

}
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
if($_POST['submit']){
if($sql_file != 'none')
echo '<hr width="50%"><p align="center">Database was successfully updated!</p><hr width="50%">';
else
echo '<hr width="50%"><p align="center">Error: There was no file uploaded!</p><hr width="50%">';
}

echo "<p align=\"center\">\n";
echo "Please select a file you would like to upload.<br><br>";
echo "<form action=\"".$_SERVER['PHP_SELF']."\" method=\"post\" enctype=\"multipart/form-data\">\n";
echo "<input style=\"height=25\" type=\"file\" name=\"sql_file\" value=\"\">\n";
echo "<input style=\"height=25\" type=\"submit\" name=\"submit\" value=\"Upload File\">\n";
echo "</form>\n";
echo "</p>\n";
?>
</body>
</html>

rachel2004

Originally posted by rob_illinois
I now need to have it so I can run a cron job and run the script and have it pull the file and upload it for me.

I don't mean to sound patronizing (things always sound amplified in e-mails and newsgroups/forums), but what is it that you wish to do with the cron job? Explain the logic to me and and how this fits into solving the original problem you were having with file uploads being too large as set forth by the php.ini file. I can probably help, but I just need to see where you're going with this.

rob_illinois

Ok From a web page I have a limit file size to 2meg only but if I have a script that grabs the file off the ftp server in a folder and does it direct then it can by pass the file size limit. This is what I was told from the server hosting department. So I need a script to do this in php and the the command to execute it in cron.

🙂

tsinka

Hi,

is PHP running as apache module or as CGI on the server ?

Thomas

rob_illinois

apache module.

rachel2004

Originally posted by rob_illinois
So I need a script to do this in php and the the command to execute it in cron.

What are you stuck on?
ftp
A simple Google search will probably retun more than several links to "how-to" create a cron job.

rob_illinois

I want to have it so the cron job runs and executes a php script on the webserver then that php script will execute and grab the text file and import it in to the mysql db.

If there is a way to do this with phpmyadmin I can also do that "sql queries". I am some what fimiliar with phpmyadmin.

Thanks

rachel2004

I don't use phpmyadmin so maybe someone knows the answer to your question. Listen, make life easy on yourself. FTP your file up to your web hosting space. Let's say it's:

/home/rob

and now you've upload your file.txt so you have:

/home/rob/file.txt

In your existing PHP file, create a variable that holds the path to file.txt. Let's say it's:

$path = "/home/rob/file.txt";

Now, when your PHP file is executed (either manually by you in a web browser or via cron job), it will check that variable and read from file.txt as you've normally been doing it and you're back in business.

If you Google, there are more than a lot of links for setting up cron jobs. It's very easy.

Be sure to use some sort of timestamp method to prevent the cron job from grabbing the same file.txt everyday and running it. You might want to try something like:

/home/rob/oct_28/file.txt

/home/rob/oct_28.txt

and for tomororw, just increase the date by one (if you are only executing the cron once every 24 hours).

rob_illinois

ok that is what I am after. So I make lets say (upload.php) then upload it to the webserver home dir (/home/rob/)"; Then the listings.txt file upload it to the same place and create a cron job to execute the php script file.? Right?

This is the upload.php script I would use:

<?php
//Check to see if the form was submited and the file was uploaded
if($POST['submit'] && $sql_file != 'none'){
//File Path and File Name
$fileName = listings.txt';
$path = "/home/rob/file.txt";
$path = $SERVER['DOCUMENT_ROOT']."/test/admin/".$fileName;

//copy the uploaded file to the server
$tempName = $HTTP_POST_FILES['sql_file']['tmp_name'];
move_uploaded_file($tempName,$path);

//Create MySQL Class Instance
$MySQL = new MySQL;

//Delete records in the database
$MySQL->query_mysql("DELETE FROM MK_LISTINGS");

//Open and Read File
$handle = fopen($path, "r");

while (!feof($handle)) {
//Read in each line of the file and put it into buffer
$buffer = fgets($handle, 4096);

//Create MySQL Insert Query
$result = "INSERT INTO MK_LISTINGS VALUES (\"\",$buffer)";

//Check to see if the buffer is not empty, if not insert the record
if($buffer != ''){
$MySQL->query_mysql($result);
}
}
fclose($handle);

//Delete the file on the server
@unlink($path);
}
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

rachel2004

I looked over your script quickly... You can probably consider dropping all of the HTML syntax since you would no longer need it if this script is not also doing the file upload (since you'd be doing that via FTP). You'll also want to get rid of the block under the part that says "copy uploaded file to the server" since your file has already been uploaded.

So, now just test it manually a few times so you feel confident. If it works, then do the Google search for a tutorial on setting up your cron job.

By the way, be sure that $path is referring to your file that you're calling listings.txt. In my previous example, I used the generic name file.txt.

rob_illinois

Ok will try it out.

On the execute part if I can not go through a webpage how do I execute it manualy? before doing cron.

Here is what I am going to try:

<?php
//Check to see if the form was submited and the file was uploaded
if($POST['submit'] && $sql_file != 'none'){
//File Path and File Name
$fileName = listings.txt';
$path = "/home/rob/listings.txt";
$path = $SERVER['DOCUMENT_ROOT']."/test/admin/".$fileName;

//Create MySQL Class Instance
$MySQL = new MySQL;

//Delete records in the database
$MySQL->query_mysql("DELETE FROM MK_LISTINGS");

//Open and Read File
$handle = fopen($path, "r");

while (!feof($handle)) {
//Read in each line of the file and put it into buffer
$buffer = fgets($handle, 4096);

//Create MySQL Insert Query
$result = "INSERT INTO MK_LISTINGS VALUES (\"\",$buffer)";

//Check to see if the buffer is not empty, if not insert the record
if($buffer != ''){
$MySQL->query_mysql($result);
}
}
fclose($handle);

//Delete the file on the server
@unlink($path);
}
?>
<?php
if($_POST['submit']){
if($sql_file != 'none')
echo '<hr width="50%"><p align="center">Database was successfully updated!</p><hr width="50%">';
else
echo '<hr width="50%"><p align="center">Error: There was no file uploaded!</p><hr width="50%">';
}

Thanks

rachel2004

Type the URL of the page in your browser as you normally would. What will happen is the the file (upload.php) will run, but since you will have taken out the HTML portions of your page, you won't see the HTML form as you had in the past. Be aware that each time you call the page from your browser, it will execute the file, so technially you'll be writing to your database each time.

Probably to help you while you're developing this, create two echo statements. One should appear at the top of the script that could say something like:

echo "Beginning.";

And then at the end, have something like:

echo "Finished.";

This will let you know at least that the PHP file executed completely. Otherwise, without the HTML in your file, you will see nothing in your browser.

rachel2004

Remove this from your code:

//Check to see if the form was submited and the file was uploaded
if($_POST['submit'] && $sql_file != 'none'){

And also remove the closing curly brace near the end so it should then look like this:

//Delete the file on the server
@unlink($path);
?>

rob_illinois

Ok done.

Ran it got this error:

Parse error: parse error in /home/bsp/public_html/files_2/admin/upload.php on line 7

Line 7 is: $path = $_SERVER['DOCUMENT_ROOT']."/files_2/admin/".$fileName;