Bulk upload

[miniu]

Hi. I've made a website for assignors and referees. There is a possibility for coordinators to add games one by one. But I would like to make a connection with the mysql database, so that they can just prepare a file, and just upload this file on the webside, so that they can upload more than only one game.

 

I understand that they have to prepare the file like I would import it to mysql directly by myself

 

for example the first line would look like that:

 

INSERT INTO `games` VALUES (1, 'GARDEN STATE SOCCER LEAGUE', 'TEAM NAME');

 

But I understand that the people will not add the "INSERT INTO `games` VALUES" line, so is there any possibility that they can just prepare the files, by putting in the same order than in the database information that will need. Also some of the files are varchar, some are not. So to avoid to make them to much problems by insert text in '' or not can I somehow make it automatically?

 

thank you for help

[schilly]

probably best to have them fill out an excel sheet, export it to csv then upload it to you.

 

you can either parse it yourself when it's uploaded and call the INSERTs one by one or you can load the file directly into MySQL.

 

If you want to load the file directly into mysql you probably want to read this: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

[aeroswat]

Hi. I've made a website for assignors and referees. There is a possibility for coordinators to add games one by one. But I would like to make a connection with the mysql database, so that they can just prepare a file, and just upload this file on the webside, so that they can upload more than only one game.

 

I understand that they have to prepare the file like I would import it to mysql directly by myself

 

for example the first line would look like that:

 

INSERT INTO `games` VALUES (1, 'GARDEN STATE SOCCER LEAGUE', 'TEAM NAME');

 

But I understand that the people will not add the "INSERT INTO `games` VALUES" line, so is there any possibility that they can just prepare the files, by putting in the same order than in the database information that will need. Also some of the files are varchar, some are not. So to avoid to make them to much problems by insert text in '' or not can I somehow make it automatically?

 

thank you for help

 

Of course you can. Do the following steps

1) Read in a line from the file

2) Explode the line by the comma delimiter into an array

3) Create a query string with that first part that you want left out

4) Do error checking on the array for each individual input to ensure that it is what you want it to be (like checking to see if its a number)

5) Run the query

6) Rinse and repeat with the next line of text

 

Optional but advised: Make your error checking very specific and make a print out of each of the schedules that have been uploaded so the person inputting them knows what they just put in so they can do a quick check over to make sure there weren't problems.

[miniu]

Of course you can. Do the following steps

1) Read in a line from the file

2) Explode the line by the comma delimiter into an array

3) Create a query string with that first part that you want left out

4) Do error checking on the array for each individual input to ensure that it is what you want it to be (like checking to see if its a number)

5) Run the query

6) Rinse and repeat with the next line of text

 

Optional but advised: Make your error checking very specific and make a print out of each of the schedules that have been uploaded so the person inputting them knows what they just put in so they can do a quick check over to make sure there weren't problems.

 

thanx. I am just wondering (I don't want to complicate them the upload). The should just put information separate by commas and don't care about it if it is varchar or bigint. But the mysql cares about it. How to make it, that they fields will automatically convert into varchar or bigint.

[aeroswat]

Of course you can. Do the following steps

1) Read in a line from the file

2) Explode the line by the comma delimiter into an array

3) Create a query string with that first part that you want left out

4) Do error checking on the array for each individual input to ensure that it is what you want it to be (like checking to see if its a number)

5) Run the query

6) Rinse and repeat with the next line of text

 

Optional but advised: Make your error checking very specific and make a print out of each of the schedules that have been uploaded so the person inputting them knows what they just put in so they can do a quick check over to make sure there weren't problems.

 

thanx. I am just wondering (I don't want to complicate them the upload). The should just put information separate by commas and don't care about it if it is varchar or bigint. But the mysql cares about it. How to make it, that they fields will automatically convert into varchar or bigint.

 

Really you don't have to worry about the varchar fields. The bigints can be a problem tho. In that case you want to ensure that your input is an integer for one and that it meets the requirements of bigint's size. This is pretty easy. If you need help with that logic I can help out. The other person that gave advice in here does have a good idea though. Excel is an easy format for your coaches to use and will help with a lot of headaches in the future for you. If they can get it through their head to always leave in the blank spaces and all then you'll be fine but I deal with a lot of stupid people that really need to have all of their privileges/room for error taken away because they can't read  So it's really up to you which way you want to do it.

[miniu]

<?php
if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
{
$fileName = $_FILES['userfile']['name'];
$tmpName  = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];

$fp      = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

if(!get_magic_quotes_gpc())
{
    $fileName = addslashes($fileName);
}
include 'library/config.php';
include 'library/opendb.php';

$query = "INSERT INTO upload (name, size, type, content ) ".
"VALUES ('$fileName', '$fileSize', '$fileType', '$content')";

mysql_query($query) or die('Error, query failed'); 
include 'library/closedb.php';

echo "<br>File $fileName uploaded<br>";
} 
?>

 

like that?

[aeroswat]

<?php
if(isset($_POST['upload']) && $_FILES['userfile']['size'] > 0)
{
$fileName = $_FILES['userfile']['name'];
$tmpName  = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size'];
$fileType = $_FILES['userfile']['type'];

$fp      = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);

if(!get_magic_quotes_gpc())
{
    $fileName = addslashes($fileName);
}
include 'library/config.php';
include 'library/opendb.php';

$query = "INSERT INTO upload (name, size, type, content ) ".
"VALUES ('$fileName', '$fileSize', '$fileType', '$content')";

mysql_query($query) or die('Error, query failed'); 
include 'library/closedb.php';

echo "<br>File $fileName uploaded<br>";
} 
?>

 

like that?

 

Looks right but I don't see any kind of error checking in there. You might want to work on that Unless you want to test out that code before you go any further.

[miniu]

Yeah, but one more thing, to read the content of this file and insert the content to database I have to use addslashes() to escape the content. I am using addslashes() to upload one by one. This is how it is look like for a one by one game upload. How to prepare it for the file?

 

	if($_GET["id"])
{
	$currid = $_GET["id"];

	$changed='N';

	$telephone = addslashes($_POST["txttelephone"]);
	$htmanager = addslashes($_POST["txthtmanager"]);
	$ggid = addslashes($_POST["txtid"]);
	$div_id = addslashes($_POST["drpdivision"]);
	$league = addslashes($_POST["drpleague"]);
	$home_team_id= addslashes($_POST["drphometeam"]);
	$away_team_id= addslashes($_POST["drpawayteam"]);

	$gdate = addslashes($_POST["txtdate"]); 
	$kgdate=explode('-',$gdate);
	$gdate=$kgdate[2].'-'.$kgdate[0].'-'.$kgdate[1];

	$time = addslashes($_POST["drptime"]);
	$time2 = addslashes($_POST["drptime2"]);
	$ampm = addslashes($_POST["drpampm"]);
	$location_id= addslashes($_POST["drplocations"]);

	$gtime=$time.':'.$time2.' '.$ampm;

	$sql_check="select gid from assigned_games where gid=".$currid;
	$res_check=mysql_query($sql_check);
	$tot=mysql_num_rows($res_check);

	if($tot > 0)
	{
	//echo $div_id.$_POST['old_drpdivision']; exit;

	if($league!=$_POST['old_drpleague'])
	{
		$changed='Y';
		$sql_lea="select league_name from league where id=".$league;
		$res_lea=mysql_query($sql_lea);
		$row_lea=mysql_fetch_object($res_lea);
		$league_name=$row_lea->league_name;
		$lleague_name="<strong><i>".$row_lea->league_name."</i></strong>";
	}
	else
	{
		$sql_lea="select league_name from league where id=".$league;
		$res_lea=mysql_query($sql_lea);
		$row_lea=mysql_fetch_object($res_lea);
		$league_name=$row_lea->league_name;
		$lleague_name=$row_lea->league_name;
	}

	if($div_id!=$_POST['old_drpdivision'])
	{
		$changed='Y';
		$sql_div="select dname from divisions where id=".$div_id;
		$res_div=mysql_query($sql_div);
		$row_div=mysql_fetch_object($res_div);
		$dname=$row_div->dname;
		$ddname="<strong><i>".$row_div->dname."</i></strong>";
	}
	else
	{
		$sql_div="select dname from divisions where id=".$div_id;
		$res_div=mysql_query($sql_div);
		$row_div=mysql_fetch_object($res_div);
		$dname=$row_div->dname;
		$ddname=$row_div->dname;

	}


	if($home_team_id!=$_POST['old_drphometeam'])
	{
		$changed='Y';
		$sql_div="select tname from teams where id=".$home_team_id;
		$res_div=mysql_query($sql_div);
		$row_div=mysql_fetch_object($res_div);
		$htname=$row_div->tname;
		$hhtname="<strong><i>".$row_div->tname."</i></strong>";
	}
	else
	{
		$sql_div="select tname from teams where id=".$home_team_id;
		$res_div=mysql_query($sql_div);
		$row_div=mysql_fetch_object($res_div);
		$htname=$row_div->tname;
		$hhtname=$row_div->tname;
	}


	if($away_team_id!=$_POST['old_drpawayteam'])
	{
		$changed='Y';
		$sql_div="select tname from teams where id=".$away_team_id;
		$res_div=mysql_query($sql_div);
		$row_div=mysql_fetch_object($res_div);
		$atname=$row_div->tname;
		$aatname="<strong><i>".$row_div->tname."</i></strong>";
	}
	else
	{
		$sql_div="select tname from teams where id=".$away_team_id;
		$res_div=mysql_query($sql_div);
		$row_div=mysql_fetch_object($res_div);
		$atname=$row_div->tname;
		$aatname=$row_div->tname;

	}


	if($location_id!=$_POST['old_drplocations'])
	{
		$changed='Y';
		$sql_div="select lname from locations where id=".$location_id;
		$res_div=mysql_query($sql_div);
		$row_div=mysql_fetch_object($res_div);
		$locname=$row_div->lname;
		$llocname="<strong><i>".$row_div->lname."</i></strong>";
	}
	else
	{
		$sql_div="select lname from locations where id=".$location_id;
		$res_div=mysql_query($sql_div);
		$row_div=mysql_fetch_object($res_div);
		$locname=$row_div->lname;
		$llocname=$row_div->lname;

	}


	if($telephone!=$_POST['old_txttelephone'])
	{
		$changed='Y';
		$telephone=$telephone;
		$ttelephone="<strong><i>".$telephone."</i></strong>";
	}
	else
	{
		$telephone=$telephone;
		$ttelephone=$telephone;
	}

	if($htmanager!=$_POST['old_txthtmanager'])
	{
		$changed='Y';
		$htmanager=$htmanager;
		$hhtmanager="<strong><i>".$htmanager."</i></strong>";
	}
	else
	{
		$htmanager=$htmanager;
		$hhtmanager=$htmanager;
	}

	if($_POST["txtdate"]!=$_POST['old_txtdate'])
	{
		$changed='Y';
		$gdate=$gdate;
		$ggdate="<strong><i>".$gdate."</i></strong>";
	}
	else
		$ggdate=$gdate;

	//echo $time.'test1'.'<br>';
	//echo $_POST['old_drptime'].'test2'.'<br>';


	if(($time!=$_POST['old_drptime']) || ($time2!=$_POST['old_drptime2']) || ($ampm!=$_POST['old_drpampm']) )
	{
		$changed='Y';
		$gtime=$gtime;
		$ggtime="<strong><i>".$gtime."</i></strong>";
	}
	else
		$ggtime=$gtime;

	}

	$sel="select gid from games where gid='".$ggid."' and id!=".$currid;
	$res=mysql_query($sel);
	if(mysql_num_rows($res) > 0)
	{
		$msg=1;

	}
	else
	{
		if($tot > 0 && $changed=='Y')
		{

		$sql_users="select main_ref_id,ast_ref_id1,ast_ref_id2,assigner_id from assigned_games where gid=".$currid;
		$res_users=mysql_query($sql_users);
		$row_users=mysql_fetch_object($res_users);

		if($row_users->main_ref_id!=0)
		{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD><title><?=Site_Title; ?></title>
<script language="javascript">
function kyb_check()
{
if(document.getElementById("drpleague").value=="")
{
	alert("Please Select League");
	document.getElementById("drpleague").focus();
	return false;
}

if(document.getElementById("drpdivision").value=="")
{
	alert("Please Select Division");
	document.getElementById("drpdivision").focus();
	return false;
}
if(document.getElementById("drphometeam").value=="")
{
	alert("Please Select Home Team");
	document.getElementById("drphometeam").focus();
	return false;
}
if(document.getElementById("drpawayteam").value=="")
{
	alert("Please Select Away Team");
	document.getElementById("drpawayteam").focus();
	return false;
}
if(document.getElementById("drphometeam").value==document.getElementById("drpawayteam").value)
{
	alert("Home Team and Away Team can not be Same.");
	document.getElementById("drpawayteam").focus();
	return false;

}
if(document.getElementById("txthtmanager").value=="")
{
	alert("Please Enter Home Team Manager");
	document.getElementById("txthtmanager").focus();
	return false;
}
if(document.getElementById("txttelephone").value=="")
{
	alert("Please Enter Telephone");
	document.getElementById("txttelephone").focus();
	return false;
}
if(document.getElementById("txtdate").value=="")
{
	alert("Please Select Game Date");
	document.getElementById("txtdate").focus();
	return false;
}
if(document.getElementById("drptime").value=="")
{
	alert("Please Select Hours");
	document.getElementById("drptime").focus();
	return false;
}
if(document.getElementById("drptime2").value=="")
{
	alert("Please Select Minutes");
	document.getElementById("drptime2").focus();
	return false;
}
if(document.getElementById("drplocations").value=="")
{
	alert("Please Select Locations");
	document.getElementById("drplocations").focus();
	return false;
}

}
function showteams(val)
{
window.location.href="add_game.php?divid=" + val + "&id=" + document.getElementById('id').value + "&league_id=" + document.getElementById('drpleague').value;

}
function show_manager(val,divid)
{
window.location.href="add_game.php?divid=" + divid + "&id=" + document.getElementById('id').value + "&tid=" + val + "&league_id=" + document.getElementById('drpleague').value;
}
function show_divisions(val)
{
window.location.href='add_game.php?league_id=' + val + '&htmanager=' + document.getElementById('txthtmanager').value + '&telephone=' + document.getElementById('txttelephone').value + '&id=' + document.getElementById('id').value;
}

</script>
<script src="calendar.js"></script>