PHP - Send A Web Table To An Excel Sheet Via Odbc And Sql

the second database found on the cloud

 

i try to get JSON data but how to insert and update  them to another online database with the same table

my php script to return json data 

<?php        

 include_once('db.php');

        $users = array();

        $users_data = $db -> prepare('SELECT id, username FROM  users');

        $users_data -> execute();

        while($fetched = $users_data->fetch()) {

            $users[$fetchedt['id']] = array (

                'id' => $fetched['id'],

                'username' => $fetched['name']

            );

        }

        echo json_encode($leaders);

 

 

 

 

i get

 

{"1":{"id":1,"username":"jeremia"},"2":{"id":2,"username":"Ernest"}}

 

Edited March 24 by mahenda

I have something that I cannot get to work.. The code I usually use uses INSERT and not UPDATE and has the user fill out all the fields. What I have now is a form where the user updates their PIN and email address. I have the update working. I just need to figure out how to get an email to the admin and to the end user based on where they are located  Here is what I have:

$dbhost 	= "localhost";
$dbname		= "jimkoons_freshstart";
$dbuser		= "jimkoons_start";
$dbpass		= "Koons123";

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

$sql = "Update Customers SET Email = :Email WHERE PIN = :PIN";                                      
$stmt = $conn->prepare($sql);              
$stmt->bindValue(':PIN', $_POST['PIN'], PDO::PARAM_STR);      
$stmt->bindValue(':Email', $_POST['Email'], PDO::PARAM_STR);


                header("Location: http://koonsfreshstart.com/thank-you");
              
$stmt->execute(); 

Here is the snippet I use to email based on location in previous applications

$STH->bindValue(':PIN', $_POST['PIN']); 
$STH->bindValue(':FirstName', $_POST['First']); 
$STH->bindValue(':LastName', $_POST['Last']);
$STH->bindValue(':Email', $_POST['Email']);


try {  
// Retrieve the user's name and associated list items
    $sql = "SELECT *
            FROM Customers
            WHERE PIN = PIN";

    $stmt = $conn->prepare($sql); // Prepare the statement
    if($stmt->execute(array($_POST['PIN'])))
    {

        // Loop through the returned results
        while($row = $stmt->fetch()) {

            // Save the Store name
            $PIN = $row['PIN'];

            // Create an array of items
            $items[] = $row['PIN'];
        }

        $stmt->closeCursor(); // Free memory used in this query
        
    		$first = $_POST["first"];
                $last = $_POST["last"];
               $email = $_POST["email"];
   
$PIN = $_POST['PIN'];
        switch ($PIN)
        {
 case "East":
             	
      
      

$to = "mail@mail.com";
$subject = "Mail";

$message = "$first $last
Email Address: $email";
$user = "$email";
$usersubject = "Thank You for your submission";
$userheaders = "From: no-reply@mail.com\n";

$usermessage = "Thank you for submitting your information.  We will be contacting you soon with more information on a customized extended service agreement for your vehicle.  We look forward to working with you!";

mail($to,$subject,$message,$headers);

mail($user,$usersubject,$usermessage,$userheaders);
                header("Location: http://mail.com/second/");
                break;
 
 etc...

Any help on making this work would be appreciated.. It is showing any errors, but it is not redirecting or emailing any of the data.   Thanks for your help!    

Hello,
i am currently getting an Microsoft Excel formatted text file whose save type is .Txt from a URL.I used to open it and will change the save type  as excel file. Please suggest whether we can do this with php code.

currently my code is like this,
<? php
copy("http://www.faa.gov/airports/airport_safety/airportdata_5010/menu/emergencyplanexport.cfm?Region=&District=&State=&County=&City=LAS%20VEGAS&Use=&Certification=","./contactsexport.xls");
?>

where as the contactsexport.xls type is .Txt which i need it in .xls

Thanks in Advance.

Hi, I got this script which I would like to send a message to all email addresses in the database or send to approved members only. Once I got the first part of this script working I can get the rest to work when sending to approved members.

Here is the form which passes the message to the script.

Code: [Select]
<?php 

include_once("data/server.php");

$all = 'unchecked';
$approved = 'unchecked';


if (isset($_POST['Submit1'])) {

$selected_radio = $_POST['Mailto'];

if ($selected_radio == 'all') {
$all = 'checked';

}
else if ($selected_radio == 'approved') {
$approved = 'checked';
}
}

?>

<div id="pageNav">
    <div id="sectionLinks"> <a href="admin.php?cmd=database&username=admin">Database</a> <a href="admin.php?cmd=uploads&username=admin">Uploads</a> <a href="admin.php?cmd=editTemplate&username=admin">Templates </a> <a href="admin.php?cmd=email&username=admin">Email</a> <a href="admin.php?cmd=messaging&username=admin">Messaging</a> <a href="admin.php?cmd=protected&username=admin">Protected Directory</a> <a href="admin.php?cmd=filesanddir&username=admin">Files and Directories</a> <a href="admin.php?cmd=usertracking&username=admin">User Tracking</a> <a href="admin.php?cmd=advanced&username=admin">Advanced</a> <a href="admin.php?cmd=uploads&username=admin">Uploads</a> <a href="admin.php?cmd=subscriptions&username=admin">Subscriptions</a> <a href="admin.php?cmd=applications&username=admin">Applications</a> <a href="admin.php?cmd=searchForms&username=admin">Search Form Builder</a> <a href="admin.php?cmd=categories&username=admin">Categories</a> <a href="admin.php?cmd=phpBB&username=admin">phpBB</a> <a href="admin.php?cmd=mySQL&username=admin">MySQL</a></div>
  </div>
  <div id="content">
   
    <div class="page">


<form action=templates/admin/msgtest.php method=POST>
<table>
<tr>
<td>Your Message:</td>
<tr><td>&nbsp;</td><td>&nbsp;</td></tr>
<tr><td><TEXTAREA NAME="message" COLS=40 ROWS=6></TEXTAREA></td></tr>
</table>
<table>
<tr>
<td><input type = 'Radio' Name ='Mailto' value= 'yes'
<?PHP print $all; ?>
</td>
<td>Send Message to all members</td>
</tr>
<tr>
<td><input type = 'Radio' Name ='Mailto' value= 'yes'
<?PHP print $approved; ?>
</td>
<td>Send Message to Approved members only</td>
</tr>
<tr><td colspan=2><input type=submit name=submit value=Submit></td></tr>
</table>
</form>



</table>
    </div>
   
  </div> 
</div>



And here is the code I have got so far but it doesnt work

Code: [Select]
<?php

include_once("../../data/server.php");
include("../../data/mysql.php");

if (isset($_POST['Submit1'])) {

$selected_radio = $_POST['Mailto'];

if ($selected_radio == 'all') {

$mysqlPassword = (base64_decode($mysqlpword));

$con = mysql_connect("$localhost", "$mysqlusername", "$mysqlPassword") or die(mysql_error());
mysql_select_db("$dbname", $con) or die(mysql_error());

 $q2 = "select * from games_newsletter ";
$result = mysql_query("SELECT email FROM profiles");   
 
while ($email = mysql_fetch_assoc($result)) {

$to = $email;
$subject = "Test Message";
$body = "This is a test message";
 $headers = "From: payments@tropicsbay.co.uk\r\n" .
     "X-Mailer: php";
 if (mail($to, $subject, $body, $headers)) {
   
// Redirect back to manage page
header("Location: admin.php?cmd=messaging");


} else {

echo "Approval Message Failed";

}
}
mysql_close($con);
}
}

?>
As always, any help is much appreciated

Paul

I could really use some help, I'm a new programmer so I'm open to any advice or ideas, but we've created a VFP program and here's what I need...

I need to make an HTML page for people to input information about a contract and then upload files that are associated with that contract. This all needs to be done over the internet of course and I'm not sure if there's a software that would help with this or if ODBC is even the way to go? Right now I'm coming up awfully blank with ODBC...

Any suggestions?

I have "UPC" setup as a system dsn, type "CodeBaseOdbcStand", and I can connect to it from access.  However, I am not having success yet using php.  This is a connection to .dbf /.cdx files for data associated with our inventory program.


ERROR
Code: [Select]
PHP Warning:  odbc_connect() [<a href='function.odbc-connect'>function.odbc-connect</a>]: SQL error: [Simba][SimbaEngine ODBC Driver][DRM File Library]No such database., SQL state 08004 in SQLConnect in C:\Inetpub\wwwroot\test.php on line 6

CODE
Code: [Select]
<html>
<body>


<?php
$conn=odbc_connect('UPC','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM ARUPC";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<table><tr>";
echo "<th>UPC</th>";
echo "<th>ITEM</th></tr>";
while (odbc_fetch_row($rs))
  {
  $UPC=odbc_result($rs,"UPC");
  $ITEM=odbc_result($rs,"ITEM");
  echo "<tr><td>$UPC</td>";
  echo "<td>$ITEM</td></tr>";
  }
odbc_close($conn);
echo "</table>";

?>

</body>
</html>

Hi

Have an issue with a script that connects to multiple Access databases to extract data.

There is one master database and then numerous small databases (I take no responsibility for the design!). The master is opened and then the sub databases are looped around, opened process and closed in turn.

However after about 20 connections I get the error [ODBC Microsoft Access Driver] Too many client tasks for any new connections. It is related to the number of connections rather than the number of operations on databases (ie, if I comment out one of the pieces of SQL run on each database it makes no difference).

I am closing the connection and unsetting the variable that stores the connection. As such there shouldn't be an excess of connections open at any one time.

Any suggestions?

All the best

Keith

Hello, I'm new at PHP programming. I'm creating a simple application. I created a form where users can fill in some data. One of the fields is a username. This is a drop down list. This list is build up with data from SQL Server 2005 R2. I created the code in PHPDesigner7. The ODBC connection is working fine in PHPDesigner7. When I try to run it on the localhost I get no connection
The code I used :

            <?php
                $conn=odbc_connect('MyDataBase','','');
                echo "connectie ".$conn;
                if (!$conn)
                    {exit("Connection Failed: ". $conn);}
       
                $sql="SELECT Id, [Name] FROM Names";
                $rs=odbc_exec($conn,$sql);         
   
                $options="";

                echo "<br />";
                echo "User : ";               
                echo "<select name='QCT_name'>";
               
                while ($row=odbc_fetch_array($rs))
                {
                    $id= $row["Id"];
                    $Controleur = $row["Name"];
                    echo "<option value='".$row['Id']."'>".$row['Name']."</option>";
                }         
                echo "</select>";
                echo "<br />";
               
            ?>

Has anybody any clue what I'm doing wrong?

Hi   I am having problems running a while loop twice. The first loop runs fine but the 2nd one does not run. Can anyone please advise why this is? My code is below   Thanks in advance. Jonathan

  $sql = "SELECT id, url, time FROM fyi_links";
  $res = odbc_exec($con, $sql);
  

while ($row = odbc_fetch_array($res)) 
{
    print($row['id'].",".$row['url'].",".$row['time']."\n");
  }  

//Run loop again after some other code
while ($row = odbc_fetch_array($res)) 
​{
print($row['id'].",".$row['url'].",".$row['time']."\n");
} 

can anyone  give me an example of how to establish an ODBC connection with PHP. I have a MS ACCESS database i want to open and read from

thanks

I am using PHP with ODBC to connect to an existing MSSQL database to query for some names.  When I query a name that has an apostrophe in it, I get an error.

Example typing O'Malley as the name:

Quote

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'malley'., SQL state 37000 in SQLExecDirect



I have used addslashes to the string but get the same result:

Code: [Select]
$string = addslashes($_POST['string']);

$connect = odbc_connect("$mssql_name", "$mssql_user", "$mssql_pass");

$query_mssql = "SELECT pr.NameFirst, pr.NameLast, pr.NameMiddle, pr.Birthdate, p.Barcode, p.OrganizationID
FROM PatronRegistration pr
JOIN Patrons p ON (pr.PatronID = p.PatronID)
WHERE pr.NameLast = '$string'";

$result = odbc_exec($connect, $query_mssql);

Any ideas?

CAN ANYONE TELL ME WHAT i AM DOING WRONG. I WANT TO RETRIEVE THE VERY FIRST RECORD IN MY DATABASE WHEN THE CODE EXECUTE IT ONLY SHOWS THE LAST RECORD IN THE DATABASE

<?PHP
$thisMonth = date('M');
$thisDay = date('j');
$eventMonth = array();
$eventDay = array();
$eventTime = array();
$eventName = array();
$eventLocation = array();
$dbMonth="";
$dbDay="";

$i=0;

$conn = odbc_connect('eventsDB','','');

$sql= "SELECT month,day, time, event,location FROM Events";
$rs="$conn,$sql";
if (!$conn)
{
   exit("Connection Failed: " . $conn);
}
 $rs=odbc_exec($conn,$sql);

if(!$rs)
{
   exit("Error in SQL");
}
 echo "DATABASE OPEN";
 

while($i<3)
{
   $dbMonth= odbc_result($rs,"month");
   echo $eventMonth[$i]=odbc_result($rs,"month")."\n"; 
   
   
   if($dbMonth<>$thisMonth)
   {
      odbc_fetch_row($rs);   
   
   }
                echo $eventMonth[$i]=odbc_result($rs,"month")."\n"; 
               echo $eventDay[$i]=odbc_result($rs,"day")."\n";
               echo $eventTime[$i]=odbc_result($rs,"time")."\n";
               echo $eventDay[$i]=odbc_result($rs,"event")."\n";
               echo $eventLocation[$i]=odbc_result($rs,"location")."\n";
               $i++;
               odbc_fetch_row($rs);
            
                  
         
   

echo $i;
} //ends while loop
odbc_close($conn);
?>

I am having trouble transferring data from provideX ODBC to MySQL using Navicat. So, I am wanting to write a php script that will write all of the data to a text file, then do a MySQL "DATA LOAD INFILE".

I am VERY limited as far as the SQL functions that are available to me with the ODBC connection. The table does not have a special unique identifier other than the item number.

This table I am pulling from has a little over 100,000 items.  How can I get php to process the full 100,000 rows.  I thought about getting it to process 5,000 rows sorted by item #> break > pass the last item to the next page > then process the next batch of 5,000 sorted by item # where the item is greater than the item processed on the last page. ??

Jake

I have not tested this code yet. I just kind of threw it together.
Code: [Select]
<?php
set_time_limit(900);
ini_set('max_execution_time', '999');

$myFile = "item_master.txt";
unlink($myFile);

$fp = fopen("item_master.txt", "w"); 


require("..\inc/data.inc");

if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT ITEM_NUM, DESCRIPTION_1, DESCRIPTION_2, ITEM_CLASS, ALPHA_SORT, STANDARD_PACK, GL_TABLE, PRIMARY_VND_NUM, VENDOR_ITEM_NUM, ACTIVE, ITEM_PRICE_CLS FROM ic_inventry_mast";

$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
while (odbc_fetch_row($rs))
  {
$ITEM_NUM=trim(odbc_result($rs,"ITEM_NUM"));
$DESCRIPTION_1=trim(odbc_result($rs,"DESCRIPTION_1"));
$DESCRIPTION_2=trim(odbc_result($rs,"DESCRIPTION_2"));
$ITEM_CLASS=trim(odbc_result($rs,"ITEM_CLASS"));
$ALPHA_SORT=trim(odbc_result($rs,"ALPHA_SORT"));
$STANDARD_PACK=trim(odbc_result($rs,"STANDARD_PACK"));
$GL_TABLE=trim(odbc_result($rs,"GL_TABLE"));
$PRIMARY_VND_NUM=trim(odbc_result($rs,"PRIMARY_VND_NUM"));
$VENDOR_ITEM_NUM=trim(odbc_result($rs,"VENDOR_ITEM_NUM"));
$ACTIVE=trim(odbc_result($rs,"ACTIVE"));
$ITEM_PRICE_CLS=trim(odbc_result($rs,"ITEM_PRICE_CLS"));


$ITEM_NUM=str_replace('@','',$ITEM_NUM);
$DESCRIPTION_1=str_replace('@','',$DESCRIPTION_1);
$DESCRIPTION_2=str_replace('@','',$DESCRIPTION_2);
$ITEM_CLASS=str_replace('@','',$ITEM_CLASS);
$ALPHA_SORT=str_replace('@','',$ALPHA_SORT);
$STANDARD_PACK=str_replace('@','',$STANDARD_PACK);
$GL_TABLE=str_replace('@','',$GL_TABLE);
$PRIMARY_VND_NUM=str_replace('@','',$PRIMARY_VND_NUM);
$VENDOR_ITEM_NUM=str_replace('@','',$VENDOR_ITEM_NUM);
$ACTIVE=str_replace('@','',$ACTIVE);
$ITEM_PRICE_CLS=str_replace('@','',$ITEM_PRICE_CLS);


$row="$ITEM_NUM@$DESCRIPTION_1@$DESCRIPTION_2@$ITEM_CLASS@$ALPHA_SORT@$STANDARD_PACK@$GL_TABLE@$PRIMARY_VND_NUM@$VENDOR_ITEM_NUM@$ACTIVE@$ITEM_PRICE_CLS\r\n";

fwrite($fp, $row); 

  }
  
  
fclose($fp); 

?>

I'm able to use PHP-ODBC to read the tables in an MDB file, as well as the column names on the tables. But when I try to read data from a particular table, it fails (I don't get an error, instead the browser pops up a message asking if I want to save the file "mdbtest.php" which is 0 bytes in size)! Any idea what's wrong?

Code: [Select]
//THIS WORKS
$conn = odbc_connect( 'TestDB', '', '' );
if ( !$conn) exit("Connection Failed: " . $conn);

//STILL FINE HERE
$sql = 'SELECT * FROM Customers';

//INCLUDING THIS LINE OF CODE CAUSES THE PROBLEM
$rs = odbc_exec( $conn, $sql );

Hi Everyone,

This is Tamilmani Mohan , i have few doubts in SQL Stored Procedure.

I am able to run the SQL SP using odbc driver and also get the result set.

For Example: odbc_exec($connection,"exec get_Members");

I don't know how to run the input / output parameters SP's?

Example:-
If i run the below code by using SQL Management Visual Studio, I am able to insert to new record and get the member id and error code values.

declare @MemberId  int,@Error    int
     
EXEC INSERT_MEMBER 1,'Tamilmani','Mohan','Address1', 'Address2', 'Address3','Salem','Tamilnadu','India',9993234234 , @MemberId  OUTPUT ,  @Error    OUTPUT

SELECT @MemberId , @Error

I don't know how to run this type code in PHP ODBC ?

Kindly , please any one give me good suggestion for this problem ?

Thanks
Tamilmani Mohan