sql into php variable

[maydhyam]

How could I put the following sql statement into a php variable?

load data local infile ".$filename." INTO table testtsttbills FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'(account,service,date,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber)

when I tried to putting it in the variable...it's showing not working because it recognizes the double quote in ENCLOSED BY '"' as an open double quote and not as what it is enclosed by...

any help?

[somedude3488]

like this:

$sql = "LOAD DATA LOCAL INFILE ".$filename." INTO TABLE testtsttbills FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'(account,service,date,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber)";

you have to escape the "

[maydhyam]

I did that, and it doesn't show any errors now...thank you...but my code still doesn't work...may I display the code?

[somedude3488]

yeah, post your code. something else may be wrong.

[maydhyam]

Here are my 2 php files...

billUpload.php

.
.
.
 <?php
 include('./uploadBill.php');
 ?>
  <tr>
    <td colspan="6">
	<form id="form1" name="form1" method="post" action="" enctype="multipart/form-data">
				<table>					
				  <tr>
					  <td>
					      <p align="left"><span class="style4 style25 style28">Bill Uploader </span></p>
					      <p align="left"><span class="style21 style29">The Bill Uploader works in 2 steps.</span></p>					  </td>
				  </tr>
   				  <tr><td><p>&nbsp;</p></td></tr>
				  <tr>
				  	<td>
				      <table width="950" border="0" align="center" cellpadding="1" cellspacing="0" >
						<tr>
						  <td><p>&nbsp;</p></td>
						  <td colspan="4" bgcolor="#006600">
						  	<p align="left" class="style32">
						  <span class="style21  style33">Step 1: Browse for file</span></p>						  </td>
						  <td><p>&nbsp;</p></td>
						</tr>
						<tr>
						  <td><p>&nbsp;</p></td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td width="125" bgcolor="#006600" scope="row"><div align="left" class="style13 style29 style31">Browse</div></td>
						  <td width="356" bgcolor="#006600">
						  	<div align="left">
								<strong>
									<input id="file" type="file" name="file" size="30" />
								</strong>
							</div>						  
						  </td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td><p>&nbsp;</p></td>
						</tr>
						<tr width="950">
						  <td><p>&nbsp;</p></td>
						  <td colspan="4" bgcolor="#006600">
						  	<p align="left"><strong>
							  <span class="style21 style30">Step 2: Upload the file selected</span></strong></p>						  						  </td>
						  <td><p>&nbsp;</p></td>
						</tr>
						<tr>
						  <td><p>&nbsp;</p></td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td bgcolor="#006600">
						  <div align="left">
						  	<strong>
					      		<input name="btnSubmit" type="submit" id="btnSubmit" value="Upload">
						  	</strong>
						  </div>
  						  </td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td><p>&nbsp;</p></td>
						</tr>
					  </table>
					  <?
					  	upload();
					  ?>
  				    </td>
				</tr>
			</table>
		</form>	
	</td>
  </tr>
.
.
.

I am not sure if this is correct...but the code below is where I would like the file that was selected in the code above to be read, and have the data inserted into the MySQL database.
When I try to upload a file, nothing happens...as well as, where <? upload(); ?> is located, the code after it doesn't display... (It's like it's blocked)

uploadBill.php

<?php
include "./connect.php";

function upload()
{
	global $conn;
	
	if(isset($_POST['btnSubmit']))
	   {
		 $filename=$_FILES['file']['tmp_name'];
		   $import = "load data local infile ".$filename." INTO table testtsttbills FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'(account,service,date,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber)";		   
		   
		   mysql_query($import,$conn) or die(mysql_error());

		 print "Import done";
	 
	  }
	  else
	  {
	 	die(mysql_error());
	  } 
}
?>

[maydhyam]

Hello....do you all understand what I have done/am trying to do?

[somedude3488]

you are trying to import the data in an uploaded file into the database.

i have never see it done like that. i usually open the file with php get its contents then process it into the database. or use phpmyadmin.

to be honest i have no idea how to solve your problem the way you are doing it.

maybe you should upload the file into a local directory instead of trying to read it from the temp one.

[maydhyam]

Errr....ok.....
I created the code hoping that it'll work, but after you have explained it to me, I realized that I did crap...
I do not know php much, but I would look at what you said, and work on it from there...
I'll keep you posted...
Thank You

[maydhyam]

Hello,

I have tried to redo my uploadBill.php file...and I do not know what else to do now...
I'll post the code, and I'll explain what I tried to do...

Here is my Display page where the user is allowed to select the desired file to be uploaded into the database.

billUpload.php

<?php
 include('uploadBill.php');
 ?>
 <tr>
  <td colspan="6">
   <form id="form" name="form" method="post" action="" enctype="multipart/form-data">
    <table>			          
     <tr>
      <td>
       <p align="left"><span class="style4 style25 style28"><a href="billUpload.php">Bill Uploader </a></span></p>
       <p align="left"><span class="style21 style29">The Bill Uploader works in 2 steps.</span></p>				  
      </td>
     </tr>
     <tr><td><p>&nbsp;</p></td></tr>
     <tr>
      <td>
       <table width="950" border="0" align="center" cellpadding="1" cellspacing="0" >
        <tr>
         <td><p>&nbsp;</p></td>
         <td colspan="4" bgcolor="#006600">
          <p align="left" class="style32"><span class="style21  style33">Step 1: Browse for file</span></p>						  
         </td>
         <td><p>&nbsp;</p></td>
        </tr>
        <tr>
         <td><p>&nbsp;</p></td>
         <td bgcolor="#006600"><p>&nbsp;</p></td>
         <td width="125" bgcolor="#006600" scope="row"><div align="left" class="style13 style29 style31">Browse</div></td>
         <td width="356" bgcolor="#006600">	<div align="left"><strong>	
          <input id="file" type="file" name="file" size="30" />
         </strong></div></td>
         <td bgcolor="#006600"><p>&nbsp;</p></td>
         <td><p>&nbsp;</p></td>
        </tr>
        <tr width="950">
         <td><p>&nbsp;</p></td>
         <td colspan="4" bgcolor="#006600">
          <p align="left"><strong><span class="style21 style30">Step 2: Upload the file selected</span></strong></p>					
         </td>
         <td><p>&nbsp;</p></td>
        </tr>
        <tr>						     
         <td><p>&nbsp;</p></td>
         <td bgcolor="#006600"><p>&nbsp;</p></td>
         <td bgcolor="#006600"><p>&nbsp;</p></td>
         <td bgcolor="#006600"><div align="left"><strong>
          <input name="btnSubmit" type="submit" id="btnSubmit" value="Upload" />
         </strong></div></td>
         <td bgcolor="#006600"><p>&nbsp;</p></td>
         <td><p>&nbsp;</p></td>
        </tr>
       </table>
      <? validateFile(); ?>
     </td>
    </tr>
   </table>
  </form>	
 </td>
</tr>

:'( Here is the part that gives me the trouble...

I called the function validateFile(); in the above code...in this function, I am putting the file name chosen from the user's search into a variable $fileName , I am also trying to know the file type that was entered.

The following piece of code was commented off because when I took off the comment /* */, my page has no display...here I am trying to open the file chosen, and (just for now at least) display the data from within the file...but....*sigh*....

/*else
     {
        $openFile=fopen($fileName, "r") or exit ("Unable to open file: "$fileName);
 
        while(!feof($fileName))
        {
	echo fgets($fileName). "<br />";		 
        }
        fclose($fileName);
     }*/

I tried to test my if's...

1. I clicked the upload button without selecting any file, and the error meassage displayed is correct

if(!$fileName)
     {
       echo '<center><br />				
	<font face="verdana" size="2" color="red"><b>You did not select a file to upload</b></font></center>
	</td>
	</tr>
	</table>
	</form>		
	</td>
	</tr>	
	  <tr>
	<td colspan="6" bgcolor="#339900">&nbsp;</td>
	  </tr>
	  <tr>
	<td colspan="6"><div align="center"><span class="style27">&copy;ITT Department 1.</span></div></td>
	  </tr>								</table>
	</body>';		
     }

2. I clicked the upload button selecting any file that is not a csv or text file, and the error meassage displayed is incorrect

elseif($fileType != 'text/csv')
     {
       echo '<center><br />				
	<font face="verdana" size="2" color="red"><b>You did not select a .csv file to upload</b></font></center>
	</td>
	</tr>
	</table>
	</form>		
	</td>
	</tr>	
	  <tr>
	<td colspan="6" bgcolor="#339900">&nbsp;</td>
	  </tr>
	  <tr>
	<td colspan="6"><div align="center"><span class="style27">&copy;ITT Department 2.</span></div></td>
	  </tr>								</table>
	</body>';
	die();			
     }

I'm not sure why it doesn't go to the proper error message:(

uploadBill.php

<?php

function validateFile()
{		
  $fileName = $_POST['file']; 
	
  $btnSubmit = $_POST['btnSubmit']; 
	
  $fileType = $_FILES['file']['type'];	
		
  if(isset($btnSubmit))
  {		
     if(!$fileName)
     {
       echo '<center><br />				
	<font face="verdana" size="2" color="red"><b>You did not select a file to upload</b></font></center>
	</td>
	</tr>
	</table>
	</form>		
	</td>
	</tr>	
	  <tr>
	<td colspan="6" bgcolor="#339900">&nbsp;</td>
	  </tr>
	  <tr>
	<td colspan="6"><div align="center"><span class="style27">&copy;ITT Department 1.</span></div></td>
	  </tr>								</table>
	</body>';		
     }		
     elseif($fileType != 'text/csv')
     {
       echo '<center><br />				
	<font face="verdana" size="2" color="red"><b>You did not select a .csv file to upload</b></font></center>
	</td>
	</tr>
	</table>
	</form>		
	</td>
	</tr>	
	  <tr>
	<td colspan="6" bgcolor="#339900">&nbsp;</td>
	  </tr>
	  <tr>
	<td colspan="6"><div align="center"><span class="style27">&copy;ITT Department 2.</span></div></td>
	  </tr>								</table>
	</body>';
	die();			
     }
     /*else
     {
        $openFile=fopen($fileName, "r") or exit ("Unable to open file: "$fileName);
 
        while(!feof($fileName))
        {
	echo fgets($fileName). "<br />";		 
        }
        fclose($fileName);
     }*/		
  }		
}
?>

[R0bb0b]

two things:
First: $_FILES comes from the browser which is not reliable.

I would take the content of the file and then validate it against an open source php sql validator.

if that comes back false then reply back to the user that the syntax is not correct.

Second: I would echo all of the variables back to you including the filetype so you can see what you are dealing with here.

[maydhyam]

Hi R0bb0b,
I am not too sure I follow what you're saying...

[R0bb0b]

see the following line at
http://blog.mikeseth.com/index.php?/archives/2-How-not-to-get-hacked-uploaded-files-in-PHP.html
"File's MIME type (derived from the Content-Type header) is an appealing factor, but you should not do any checks on it. It can be faked by the uploader, so you can't trust its contents."

This is $_FILES and it can be faked.
I always go by the extention:
$ext = substr(trim($filenamestring), -3);
this will give you the extension of the file.

You also need to consider the content of the file, even if it is sql, results can be unpredictable if you don't validate it somehow. I've never needed to look for an open source sql validator, so I'm not exactly sure if one exists, but I would assume that someone has already made one. If you don't find one you can use the function file() and require from your user that each sql command is one one line, or something of that sort. And then validate it on your own. I'm just saying that nobody is putting anything in my database if I don't know what it is first.

[R0bb0b]

When I say echo all of the variables, I mean rather than writing ifs and elses when I writing PHP, I usually start simple first.

Upon uploading the document I would do a print_r($_FILES);

This will show you everything that is in the array including the filetype, and that is where I would go from where you are now and build my php around that.

[maydhyam]

The content in the file is as follows:

123456789876,111-1234,1/11/2007,7:58:46,XX,COUNTRY NAME,98765432123,4:00:00,0,3.2,3.2,P0

I have a piece of sql code which can do the upload through the MySQL Query Browser Application. Here it is:

load data local infile ".$filename." INTO table testtsttbills FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'(account,service,date,time,timebandtype,destination,callednumber,mins,airtimecharge,tollcharge,amt,privatenumber)";

But I want to do the uploads through a webpage (so I can access it from anywhere I login to)

[R0bb0b]

If you know the contents of the file is valid and there is no risk then this works fine up until the mysql_query part as that will depend on your query and table structure.

<?
ini_set("display_errors", "1");
error_reporting (E_ALL);

if(count($_FILES) > 0)
{
        $ext = "";
	$ext = substr(trim($_FILES["file"]["name"]), -4);
	$allowedext = array(".txt", ".csv", ".sql");
	
	if(in_array($ext, $allowedext))
	{
		$filename = $_FILES['file']['tmp_name'];
		$fh = fopen($_FILES['file']['tmp_name'], 'r');
		$handle = fopen($filename, "r");
		$contents = fread($handle, filesize($filename));
		fclose($handle);
		
		echo $contents;
                //db conn stuff
                //mysql_query($contents);
	}
	else
	{
		echo "invalid format";
	}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form action="<? echo $_SERVER['REQUEST_URI']; ?>" enctype="multipart/form-data" method="post">
	<input type="file" name="file" /><input type="submit" value="Submit" name="btnSubmit" />
</form>
</body>
</html>

[maydhyam]

If you know the contents of the file is valid and there is no risk then this works fine up until the mysql_query part as that will depend on your query and table structure.

//All this takes place in 1 file?

    if(in_array($ext, $allowedext))
    {
        $filename = $_FILES['file']['tmp_name'];
        $fh = fopen($_FILES['file']['tmp_name'], 'r');
        $handle = fopen($filename, "r");
        $contents = fread($handle, filesize($filename));       
                                fclose($handle);
        
        echo $contents;
                //db conn stuff
                //mysql_query($contents);

    }

//So after the file is read, and closed, I upload it's data?

[R0bb0b]

Yes. The form and script is on the same file. You should be able to just copy and past it to a file and watch it work. Right now it just echos the contents of the file to the page and all you need to do is plug that variable into a db query.

[maydhyam]

Hi R0bb0b,

I copied and pasted the code like you said and it works...it simply echoes the data...I want to encorporate it into my form and then I would enter the mysql part...Thanks for everything thus far...

I'll keep you posted...

[maydhyam]

Hi there,

When the file is echoed, the data is displayed in a paragraph....when I read 10 lines of data, how can i get to output each line read into a new line?

[maydhyam]

Ok, So I finally got it to work, I am able to select a csv file, and upload it to the database...I am curious though, I have a piece of javascript on the page with the php upload script, and now that my php upload script works, my javascript is not working....it's actually a date and time piece of code...how can i fix that?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style28 {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 18px;
	font-weight: bold;
	color: #006600;
}
.style29 {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 14px;
	font-weight: bold;
}
-->
</style>


<style type="text/css">
<!--
.style27 {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 10px;
	font-weight: bold;
}
body {
	background-color: #CCFFCC;
}
.style30 {
	color: yellow;
	font-size: 14px;
	font-family: Verdana, Arial, Helvetica, sans-serif;
}
.style31 {
	color: #CCFFCC;
	font-weight: bold;
}
.style32 {color: yellow; font-weight: bold; }
.style33 {font-size: 14px; font-family: Verdana, Arial, Helvetica, sans-serif;}
-->
</style>
</head>

<body onload="goforit()">
<table width="200" border="0" align="center">
  <tr>
    <th colspan="6" scope="col"><img src="images/apBanner2.png" width="962" height="225" /></th>
  </tr>
  <tr>
    <td width="155"><img src="images/btnT.png" width="150" height="33" /></td>
    <td width="155"><img src="images/btnA.png" width="150" height="33" /></td>
    <td width="155"><img src="images/btnI.png" width="150" height="33" /></td>
    <td width="155"><img src="images/btnS.png" width="150" height="33" /></td>
    <td width="327" colspan="2">
		<div align="center">
		


  <script>
			/*
			Live Date Script- 
			© Dynamic Drive (www.dynamicdrive.com)
			For full source code, installation instructions, 100's more DHTML scripts, and Terms Of Use,
			visit http://www.dynamicdrive.com
			*/
			
			var dayarray=new Array("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
			var montharray=new Array("January","February","March","April","May","June","July","August","September","October","November","December")
			
			function getthedate()
			{
				var mydate=new Date()
				var year=mydate.getYear()
				if (year < 1000)
					year+=1900
				var day=mydate.getDay()
				var month=mydate.getMonth()
				var daym=mydate.getDate()
				if (daym<10)
					daym="0"+daym
				var hours=mydate.getHours()
				var minutes=mydate.getMinutes()
				var seconds=mydate.getSeconds()
				var dn="AM"
				if (hours>=12)
					dn="PM"
				if (hours>12)
				{
					hours=hours-12
				}
				if (hours==0)
					hours=12
				if (minutes<=9)
					minutes="0"+minutes
				if (seconds<=9)
					seconds="0"+seconds
				//change font size here
				var cdate="<small><font color='006600' face='Verdana'><b>"+dayarray[day]+" "+montharray[month]+" "+daym+", "+year+"    "+hours+":"+minutes+":"+seconds+" "+dn
				+"</b></font></small>"
				if (document.all)
				document.all.clock.innerHTML=cdate
				else if (document.getElementById)
				document.getElementById("clock").innerHTML=cdate
				else
				document.write(cdate)
			}
			if (!document.all&&!document.getElementById)
				getthedate()
			function goforit()
			{
				if (document.all||document.getElementById)
					setInterval("getthedate()",1000)
			}			
			</script>
	    <span id="clock"></span> 




       
		</div>		</td>
  </tr>
  <tr>
    <td colspan="6">&nbsp;</td>
  </tr>
  <tr>
    <td colspan="6">
		  	<form action="<? echo $_SERVER['REQUEST_URI']; ?>" enctype="multipart/form-data" method="post">
				<table>					
				  <tr>
					  <td>
					      <p align="left"><span class="style4 style25 style28The Uploader </span></p>
					      <p align="left"><span class="style21 style29">The Uploader works in 2 steps.</span></p>					  </td>
				  </tr>
   				  <tr><td><p>&nbsp;</p></td></tr>
				  <tr>
				  	<td>
				      <table width="950" border="0" align="center" cellpadding="1" cellspacing="0" >
						<tr>
						  <td><p>&nbsp;</p></td>
						  <td colspan="4" bgcolor="#006600">
						  	<p align="left" class="style32">
						  <span class="style21  style33">Step 1: Browse for file</span></p>						  </td>
						  <td><p>&nbsp;</p></td>
						</tr>
						<tr>
						  <td><p>&nbsp;</p></td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td width="125" bgcolor="#006600" scope="row"><div align="left" class="style13 style29 style31">Browse</div></td>
						  <td width="356" bgcolor="#006600">
						  	<div align="left">
								<strong>
									<input type="file" name="file" />
								</strong>							</div>						  </td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td><p>&nbsp;</p></td>
						</tr>
						<tr width="950">
						  <td><p>&nbsp;</p></td>
						  <td colspan="4" bgcolor="#006600">
						  	<p align="left"><strong>
							  <span class="style21 style30">Step 2: Upload the file selected</span></strong></p>						  						  </td>
						  <td><p>&nbsp;</p></td>
						</tr>
						<tr>
						  <td><p>&nbsp;</p></td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td bgcolor="#006600">
						  <div align="left">
						  	<strong>
					      		<input type="submit" value="Upload" name="btnSubmit" />
						  	</strong>						  </div>  						  </td>
						  <td bgcolor="#006600"><p>&nbsp;</p></td>
						  <td><p>&nbsp;</p></td>
						</tr>
					  </table>  				    </td>
				</tr>
			</table>
		</form>	</td>
  </tr>
  <tr>
  	<td colspan="6" >
		
	  <div align="left" class="style29">
		<?
			include ("./connect.php");
			global $conn;
			ini_set("display_errors", "1");
			error_reporting (E_ALL);
			
			if(count($_FILES) > 0)
			{
					$ext = "";
				$ext = substr(trim($_FILES["file"]["name"]), -4);
				$allowedext = array(".txt", ".csv", ".sql");
				
				if(in_array($ext, $allowedext))
				{
					$filename = $_FILES['file']['tmp_name'];
					//$fh = fopen($_FILES['file']['tmp_name'], 'r');
					$handle = fopen($filename, "r");
					
					while (($data = fgetcsv($handle, 1000, ',', '"')) !== FALSE)
					{
						$query = "INSERT INTO testtsttbills VALUES ('". implode("','", $data)."')";
						$query = @mysql_query($query,$conn);
					}		
					//$contents = fread($handle, filesize($filename));
					fclose($handle);		
					//echo $contents;
					echo "<div align='center'><font color='red'>The file was uploaded.</font></div>";
				}
				else
				{
					echo "<div align='center'><font color='red'>You are trying to upload an invalid file format. Please try again.</font></div>";
				}
			}
		?>
    </div></td>
  </tr>
  <tr><td colspan="6">&nbsp;</td></tr>
  <tr>
    <td colspan="6" bgcolor="#339900">&nbsp;</td>
  </tr>
  <tr>
    <td colspan="6"><div align="center"><span class="style27">&copy;ITT Department</span></div></td>
  </tr>
</table>
</body>
</html>

[R0bb0b]

I tried it myself and the javascript works fine for me in IE and FF. Your talking about the date and time up in the top right I assume, yeah it works for me just fine.

[maydhyam]

hmmm...i don't understand why it not displaying here...let me try to load the page again....

[maydhyam]

Ok, I closed and re-opened IE, and I opened the page once more...it works now...hmmm...I guess I needed to have done that before...cool....
Thank you
:)

[maydhyam]

:)

[R0bb0b]

Browser cache, blah.

No Problem.