mySQL Database Information

[SN3]

I have a .xls file with a couple of fields, and I need to put the information into a database and pull it into a web page. What is the easiest way to do this? I am concerned about getting the .xls file into a mySQL database, how will I do this?

I know I need to use the following code to pull the information to the php web page:

PHP Code:

 <?php
 // Make a MySQL Connection
 mysql_connect("localhost", "username", "password") or die(mysql_error());
 mysql_select_db("database") or die(mysql_error());

  // Retrieve all the data from the "example" table
 $result = mysql_query("SELECT * FROM example") 
 or die(mysql_error()); 
 
  // store the record of the "example" table into $row
 $row = mysql_fetch_array( $result );
 // Print out the contents of the entry 

  echo ".$row['1'];
 echo ".$row['2'];
 echo ".$row['3'];


  ?>

Right?

[insitedev]

I would save it as a CSV file, then use explode to separate into individual columns (hoping that you don't have a comma in the text you are trying to insert, if so, you need to choose another delimiter).

Here is some code to handle the insertion:

<?php

$fp = fopen("yourfile.csv", "r");
while (($data = fgetcsv($fp, 1000, ",")) !== FALSE) {
        mysql_query("INSERT INTO table VALUES ('" . $data[0] . "', '" . $data[1] . "', '" . $data[2] . "', '" . $data[3] . "')");  // put in the appropriate fields here.  $data[#] represents the column number in the csv file.
}
fclose ($fp);
?>

You will need to have a MySQL table setup with the columns you want to have. Let me know if you need help with this.

[StaticShock]

Full code including the entry form for the file here.

<?php


if (isset($_POST['submit'])) 
	{
		$server = 'SERVER NAME';
		$table = 'TABLE NAME';
		$db_name = 'DATABASE NAME';
		$user = 'USERNAME';
		$password = 'PASSWORD';
	
		$db = mysql_connect("$server","$user","$password") or die ("Could not connect to database!");
		$result = mysql_select_db($db_name) or die("Could not select database!");

			$fp = fopen($_FILES['userfile']['tmp_name'], 'r') or die('Could not open file!');
				while ($data = fgetcsv($fp, 1028)) 
					{
						$insert = "INSERT INTO $table (fieldname1, fieldname2, fieldname3) VALUES ('$data[0]', '$data[1]', '$data[2]')";
						$result_insert = mysql_query($insert, $db) or die("Could not insert query!");
					
						echo "<b>Row #".$num." was inserted!<br>"; 
						$num++;
					}
		
			fclose($fp);
			mysql_close;
		
else
	{
		echo "<form enctype=multipart/form-data method=post><input name=userfile type=file><input type=submit value=Submit></form>";
	}
?>

[SN3]

Thanks! I'll look into it.

[SN3]

Hmm...it didn't work (StaticShock). I kept getting an error on line 15, and I can't figure out why. Any ideas?

[SN3]

EDIT:

I had the database name wrong, but now I get an error:


Parse error: parse error, unexpected T_ELSE in FILE.php on line 27

[insitedev]

put () at the end of mysql_close.

[SN3]

Originally Posted by insitedev

put () at the end of mysql_close.

Now I get:


Parse error: parse error, unexpected T_ELSE in FILE NAME on line 30

[kwvarga]

put a } before the else

[SN3]

Thanks. The page works, but once I upload the file, the database does not change. Does anyone know why?

[SN3]

I got it working, but now I have a tricky problem.

I am using this to prevent against bots from getting emails:

PHP Code:

 <a onclick="zz_antileech('Email*emailservice.tld');return false" href="javascript://" class="namelistb">Email Me</a> 


I can't figure out how to get this to work using this code to get the email address:

PHP Code:


<?PHP

// Connecting, selecting database


$link = mysql_connect('localhost', 'Username', 'Password')
   or die('Could not connect: ' . mysql_error());


mysql_select_db('Database') or die('Could not select database');

// Performing SQL query
$query = 'SELECT * FROM TABLE';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());


// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {

       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);

?>

The script (bottom) pulls numerous email addresses from a database, in the format of name@email.tld.

I think that I would have to split the email, and then put it into the code, is that the only way?

I don't know if it can be done. Thanks.