Importing Excel data in MySql via PHP

Hi Guys,

So i decided to venture into PHP but then I came to a halt when i wanted to import excel data into Mysql. I finally got a way around this and thought i should share with with anyone who has the same problem. so here it is.....

If you want to import an Excel file into MySQL database, you can do it easily using PHP code. First, of all  you will need to download some prerequisites library:

PHPExcelReader – http://sourceforge.net/projects/phpexcelreader/
Spreadsheet_Excel_Writer - http://pear.php.net/package/Spreadsheet_Excel_Writer

Once you have downloaded both files, please upload them to your web server.  To work around a small bug in PHPExcelReader, copy oleread.inc file from the Excel directory into a new path: Spreadsheet/Excel/Reader/OLERead.php

The PHPExcelReader code will expect OLERead.php to be in that specific location. Once that is complete, you’re ready to use the PHPExcelReader class. I made an example Excel spreadsheet like this:

<?php
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('yourExceldataSheet.xls');

$conn = mysql_connect("dataBaseHostName","DataBaseUserName","DataBasePassword");
mysql_select_db("DataBaseName",$conn);

for ($x=2; $x<=count($data->sheets[0]["cells"]); $x++) {
    $name = $data->sheets[0]["cells"][$x][1];
    $extension = $data->sheets[0]["cells"][$x][2];
    $email = $data->sheets[0]["cells"][$x][3];
    $sql = "INSERT INTO mytable (name,extension,email) 
        VALUES ('$name',$extension,'$email')";
    echo $sql."\n";
    mysql_query($sql);
}
?>

If you are getting an error like this–      
 Deprecated: Assigning the return value of new by reference is deprecated in                  E:\wamp\www\ARE\application\Excel\reader.php on line 262

 then look for this code
$this->_ole =& new OLERead(); and remove the & in front =, 
 new code $this->_ole = new OLERead();

Cena Files For Divorce $ his Wife Hires Linda Hogan's Attorney = Cena's Doomed!!

According to an article in The Miami Herald, John Cena has filed for divorce from his wife of three years, Liz. The two are said to have been high school sweethearts and the article says that Cena's petition for divorce came out of left field. The article says that, in Cena's words, the marriage has been "irrevocably broken". As of right now, I haven't read any details as to what's caused the break up. Thus far, there've been no allegations of adultery or abuse by either Cena or his wife, but things are still very early.

Liz Cena has aquired the services of attorney Raymond Rafool, who gained notoriety during his representation of Linda Hogan in her divorce. In that case, Rafool was able to get Linda Hogan 70% of Hulk Hogan's assets, which included a couple of homes, some expensive rides, and more than $7 million in cash. The fact that Liz Cena has gotten this guy is an indicator that she may very well try to bleed John Cena dry.

Rafool commented to the paper: "Although it is indeed unfortunate that John Cena decided to divorce his high school sweetheart Liz Cena; particularly, after they have come so far in their lives and in his career together, Liz will and really has no choice but to pursue all of her rights and entitlements.

He went on to further say: "Although Mr. Cena pushes a prenuptial agreement and that the parties have only been married for approximately 3 years, Liz Cena has always supported John Cena, even when no one else did, and stood behind him and pushed him forward to achieve their goals for the last 14 years. Sadly, divorce is not the way Liz thought her perfect love story would end."

The fact that Cena has a prenup is good for him. There's no way that his wife isn't going to at least something, probably a damn good something by any reasonable person's standards. While there's no such thing as an iron clad prenup, it does offer legit protection, depending on the circumstances. If Cena fooled around or has been fooling around, that's only going to hurt him as it did for Hogan. If she messed around or did something that violates the terms of the prenup, then that makes it easier on Cena.

In the coming weeks and months, it'll be kinda interesting to see what allegations get tossed around, and we all know they're coming. A source is said to have told the paper that the divorce would "dwarf the Hogan divorce in nastiness." Cena better enjoy those flashy cars he likes to collect while he can because I have a feeling that he's gonna lose a whole bunch of 'em.

Jack-Hammer

http://forums.wrestlezone.com/showthread.php?p=3896078#post3896078

Code sample demonstrating how to Select/Insert/Delete/Update records in SQL Server Compact database file(*.sdf) in VB.NET.

Imports System.Data.SqlServerCe

Public Class Form1

    ' Shared variables
    Dim con As SqlCeConnection = New SqlCeConnection("Data Source=C:\Northwind.sdf")
    Dim cmd As SqlCeCommand
    Dim myDA As SqlCeDataAdapter
    Dim myDataSet As DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ShowData()
    End Sub

    'Binding database table to DataGridView
    Public Sub ShowData()
        cmd = New SqlCeCommand("Select * FROM Users", con)
        If con.State = ConnectionState.Closed Then con.Open()
        myDA = New SqlCeDataAdapter(cmd)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "MyTable")
        DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
    End Sub

    ' Retrieve/Select records  
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        cmd = New SqlCeCommand("SELECT * FROM Users Where name='Martin'", con)
        If con.State = ConnectionState.Closed Then con.Open()
        Dim sdr As SqlCeDataReader = cmd.ExecuteReader()
        While sdr.Read = True
            MessageBox.Show(sdr.Item("name") & " " & sdr.Item("phone"))
        End While
        sdr.Close()
    End Sub

    ' Insert record  
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        cmd = New SqlCeCommand("Insert Into Users(name, phone) Values('phenry', ‘88866677’)", con)
        If con.State = ConnectionState.Closed Then con.Open()
        cmd.ExecuteNonQuery()
        ShowData() 'Rebinding to DataGridView and view result
    End Sub

    ' Update record
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        cmd = New SqlCeCommand("Update Users Set phone=’34’ Where name='Martin'", con)
        If con.State = ConnectionState.Closed Then con.Open()
        cmd.ExecuteNonQuery()
        ShowData() 'Rebinding to DataGridView and view result
    End Sub

    'Delete record
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        cmd = New SqlCeCommand("Delete * From Users Where name='Martin'", con)
        If con.State = ConnectionState.Closed Then con.Open()
        cmd.ExecuteNonQuery()
        ShowData() 'Rebinding to DataGridView and view result
    End Sub

    ' Dispose Database Connection object  
    Private Sub Form4_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
        con.Close()
        con = Nothing
    End Sub

End Class



Remember:
You can't import a namespace if you haven't referenced an assembly that contains at least one member of that namespace. Go to the References page of the project properties and reference the SQL Server CE assembly first. Then you can import the SQL Server CE namespace.

SQL server Compact 3.5 deployment with VS2010

Private file–based deployment refers to the process of including the required SQL Server Compact 3.5 DLLs as files in the project (as opposed to a reference to DLLs already on the target computer). If you include the necessary DLLs with the application, the requirement to install SQL Server Compact 3.5 is removed. Therefore, the administrative credentials are no longer needed.

You can use ClickOnce deployment technology for private file–based deployment. If you do, you must remember to clear the SQL Server Compact 3.5 prerequisite so that the Setup program does not install it.

To deploy a SQL Server Compact 3.5 database by using private file–based deployment

1. To open the Project Designer, in Solution Explorer/Database Explorer, double-click My Project if you are working on a Visual Basic project (or Properties if you are working on a C# project).
2. Click the Publish tab.
3. Click Prerequisites and then clear the check box for SQL Server Compact 3.5.
4. Close the Project Designer.
5. Go to the directory that contains the SQL Server Compact 3.5 DLLs. These are located in C:\Program Files\Microsoft SQL Server Compact Edition\v3.5.
6. Select the seven SQL Server Compact 3.5 DLLs and copy them:
   
   • sqlceca35.dll
   • sqlcecompact35.dll
   • sqlceer35EN.dll
   • sqlceme35.dll
   • sqlceoledb35.dll
   • sqlceqp35.dll
   • sqlcese35.dll
7. Paste the DLLs into the project in Solution Explorer/Database Explorer.
8. Select all seven DLLs in Solution Explorer/Database Explorer and open the Properties window.
9. Set the Copy to Output Directory property to Copy if newer.
   
   This will replace any earlier DLLs in an existing application with the newer ones if the application is updated.
10. Click the Show All Files button in Solution Explorer/Database Explorer.
11. Expand the References node.
12. Select System.Data.SqlServerCe.
13. Set the Copy Local property to True.
    
    Because your development computer has the SqlServerCe DLLs in the global assembly cache, you must configure the application to use the DLLs in the output directory.
14. Right-click the project in Solution Explorer/Database Explorer and select Publish to open the Publish Wizard.
15. Complete the wizard to publish the application.

The application is ready to be installed. Go to the location you published to, and install the application to verify.

Source: http://msdn.microsoft.com/en-us/library/aa983326.aspx

Welcome All

Welcome All and feel free to share any tips that you might be having with everyone!!!!