PHP - Odbc Connections Not Closing

On my new site on average every page that loads opens  9 MySql connections and runs 14 MySql Queries, And as I use Ajax to dynamically update the pages, so this repeats every 60 seconds per page.

Is this too many connections?
I am not having any problems at the moment but the site is getting more busy, and I don't want the site to start running slow, or the MySql server to crash.

Any comment would be much appreciated.

   

Hi,

I would just like to know what limitations apply for opening X amount of connections to different websites with Curl? (specifically, I'm using Curl multi and have my own way of regulating how many connections occur at a given time).

I ask this because it seems I can open 100 connections and get the info from the websites quite quickly. If I were to open say 1000 would that crash my router or what would happen?

Thanks in advance!

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);
?>

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 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); 

?>

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");
} 

OK, so I dont think this is a code issue as such, but I am completely stumped as to where the issue could be.

I have a page with some standard include() functions at the top. When I access the page from home, everything operates as it should, when I access it from work (on multiple machines), I get the standard "unable to open stream" and "unable to access" warnings for the include statements. I have tried hard refreshing and deleting cache (not that it should make a shred of difference) etc, but the error is still there after a week or so.

Does anyone know what could be causing this issue?

Hi, I am quite new to using php classes so it maybe something simple here, however I am trying to establish to connection to two different databases using one PHP class; The problem is that this will work for one but when I create a new connection they will both fail.   

<?php
putenv("TZ=Europe/London");
 
// start database connection
$funky_db = new Database('xxxx', 'xxxx', 'xxxx', 'xxxx');
 
// start database connection
$funky_cc = new Database('xxxx', 'xxxx', 'xxxx', 'xxxx');
 
class Database {
 
    private $host;
    private $user;
    private $pass;
    private $name;
    private $link;
    private $error;
    private $errno;
    private $query;
 
    function __construct($host, $user, $pass, $name = "", $conn = 1) {
        $this -> host = $host;
        $this -> user = $user;
        $this -> pass = $pass;
        if (!empty($name)) $this -> name = $name;      
        if ($conn == 1) $this -> connect();
    }
 
    function __destruct() {
        @mysql_close($this->link);
    }
 
    public function connect() {
        if ($this -> link = mysql_connect($this -> host, $this -> user, $this -> pass, TRUE)) {
            if (!empty($this -> name)) {
                if (!mysql_select_db($this -> name)) $this -> exception("Could not connect to the database!");
            }
        } else {
            $this -> exception("Could not create database connection!");
        }
    }
 
    public function close() {
        @mysql_close($this->link);
    }
 
    public function query($sql) {
        if ($this->query = @mysql_query($sql)) {
            return $this->query;
        } else {
            $this->exception("Could not query database!".$this->name);
            return false;
        }
    }
 
    public function num_rows($qid) {
        if (empty($qid)) {         
            $this->exception("Could not get number of rows because no query id was supplied!");
            return false;
        } else {
            return mysql_num_rows($qid);
        }
    }
 
    public function fetch_array($qid) {
        if (empty($qid)) {
            $this->exception("Could not fetch array because no query id was supplied!");
            return false;
        } else {
            $data = mysql_fetch_array($qid);
        }
        return $data;
    }
 
    public function fetch_array_assoc($qid) {
        if (empty($qid)) {
            $this->exception("Could not fetch array assoc because no query id was supplied!");
            return false;
        } else {
            $data = mysql_fetch_array($qid, MYSQL_ASSOC);
        }
        return $data;
    }
 
public function fetch_object($qid) {
        if (empty($qid)) {
            $this->exception("Could not fetch object assoc because no query id was supplied!");
            return false;
        } else {
            $data = mysql_fetch_object($qid);
        }
        return $data;
    }
 
    public function fetch_all_array($sql, $assoc = true) {
        $data = array();
        if ($qid = $this->query($sql)) {
            if ($assoc) {
                while ($row = $this->fetch_array_assoc($qid)) {
                    $data[] = $row;
                }
            } else {
                while ($row = $this->fetch_array($qid)) {
                    $data[] = $row;
                }
            }
        } else {
            return false;
        }
        return $data;
    }
 
    public function last_id() {
        if ($id = mysql_insert_id()) {
            return $id;
        } else {
            return false;
        }
    }
 
    private function exception($message) {
        if ($this->link) {
            $this->error = mysql_error($this->link);
            $this->errno = mysql_errno($this->link);
        } else {
            $this->error = mysql_error();
            $this->errno = mysql_errno();
        }
        if (PHP_SAPI !== 'cli') {
        ?>
 
            <div class="alert-bad">
                <div>
                    Database Error
                </div>
                <div>
                    Message: <?php echo $message; ?>
                </div>
                <?php if (strlen($this->error) > 0): ?>
                    <div>
                        <?php echo $this->error; ?>
                    </div>
                <?php endif; ?>
                <div>
                    Script: <?php echo @$_SERVER['REQUEST_URI']; ?>
                </div>
                <?php if (strlen(@$_SERVER['HTTP_REFERER']) > 0): ?>
                    <div>
                        <?php echo @$_SERVER['HTTP_REFERER']; ?>
                    </div>
                <?php endif; ?>
            </div>
        <?php
        } else {
                    echo "MYSQL ERROR: " . ((isset($this->error) && !empty($this->error)) ? $this->error:'') . "\n";
        };
    }
 
}
?>

Hi everybody,

I am facing "Too many connections" problem on my site.
in fact the server is moved from one location to,another and after the movement this problem arise...

Pls help.

Thanks

Hey,

I would there be a way i could create a form in which upon entering a domain name/URL it will tell you how many connections there are to the site and how many (if any) MySQL queries are running?

If I add this line to a wordpress footer it displays the number of sql queries.

Code: [Select]
&lt;?php echo get_num_queries(); ?&gt; queries in &lt;?php timer_stop(1); ?&gt; seconds.

Any help is greatly appreciated.

Thanks.

Hello,

I'm trying to update my database with current statistics from my gameservers, but running the loop of socket connections to all of the servers on page load takes too long. For every server in the array it takes roughly 4 seconds to complete each one.. with a massive list of say 42 it takes awhile. However, I'm running from shared hosting and was wondering if that was the reason each server takes so long to query. I was thinking about running a cron job every 1 minute to update the information so it's still relativity new and current. Will the 1 minute cron job affect anything from the shared hosting? I'm using 1and1 web-hosting on a 1&1 Business Package.

Thanks

At the moment I am writing an application that supports plugins. Every plugin implements an interface so that the main application can control the plugin with the interface methods. There could be plugins that need a database connection and others that do not. What I am doing at the moment is loading every plugin with a __construct($databaseHandler). For plugins that don't need a database connection this is a little overhead.

I could also use a Singleton or something else "staticish" so that the plugin can get the database instance whenever it wants, but then the plugin can only be used for my application where this specific Singleton is present and maybe I would like to use the plugin for other purposes later in another environment without changing it.

Do you have any better ideas or how do you normally manage your database connections?

Hello guys, I hope you can help.

I can't get my page to switch between two database connections properly
It seems to ignore the second connection.

I'm trying to go through each group in database one and find out how many members they have registered in database two by checking the field "tsName".

This is a valid query for database one.
$fetchUsers = mysql_query("SELECT * FROM $db_corps_table ORDER BY cName;", $con1);
This is a valid query for database two.
$fetchUsers = mysql_query("SELECT * FROM 'users' WHERE tsName LIKE $cTicker%;", $con2);
However despite me specifying which database link to use it tries to use every query on database one ($con1). So I can never query database two ($con2) because it just breaks with the result below. :S

$fetchUsers = mysql_query("SELECT * FROM $db_corps_table ORDER BY cName;", $con1); gives
Quote

AREA 43 [A-43] (11):
result
result
result
(there are 3 groups in database one)

But:
$fetchUsers = mysql_query("SELECT * FROM 'users' WHERE tsName LIKE $cTicker%;", $con2);  gives
Quote

AREA 43 [A-43] (11):

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sever/public_html/mav/tools/ts_corp_registrations.php on line 51


<?php
// ===== Connect to database one (group) =====
$con1 = mysql_connect($db_host,$db_corps_user,$db_corps_pass);
if (!$con1) {
die("Could not connect:" . mysql_error());
}
$db_select1 = mysql_select_db($db_corps_db, $con1);
if (!$db_select1) {
die ("Could not select database:" . mysql_error());
}
// ===== Connect to database two (members) =====
$con2 = mysql_connect($db_host,$db_ts_user,$db_ts_pass, true);
if (!$con2) {
die("Could not connect:" . mysql_error());
}
$db_select2 = mysql_select_db($db_ts_db, $con2);
if (!$db_select2) {
die ("Could not select database:" . mysql_error());
}

$fetchCorps = mysql_query("SELECT * FROM $db_corps_table ORDER BY cName;", $con1);
while ($row = mysql_fetch_array($fetchCorps)) {
// For each of the results, gather details from database one, then see how many members we can find in database two 
$cName = $row["cName"];
$cTicker = mysql_real_escape_string($row["cTicker"],$con1);
$cMembers = $row["cMembers"];

echo $cName." [".$cTicker."] (".$cMembers."):<br />";
// ========================== HERE BE PROBLEMS ==========================
//$fetchUsers = mysql_query("SELECT * FROM $db_corps_table ORDER BY cName;", $con1);
$fetchUsers = mysql_query("SELECT * FROM 'users' WHERE tsName LIKE $cTicker%;", $con2);
// ===================================================================
while ($rowTS = mysql_fetch_array($fetchUsers)) {
echo "result<br />";
}
}
// all done, close DB connections
mysql_close($con2);
mysql_close($con1);
?>

Hi,

Just a quick query.

I call my db connection and configuration files on every script.
I also have just made a functions script where i'm storing some functions
except when i call the function, my script fails because of a missing database connection.

But on the page that fails, i call the configuration files including the db connection at the start of the script.
 
But when i get to where i include the function they are all on the same script so should it fail??

The page did work fine when the function was inbedded in the script. But as i'm using the function on a few pages, i thought it would be better practise and more efficent to just include the function on each page i need, rather than just copy and paste all the php in the required pages.

Or do i have to declare the db connection inside the function page as well???

Thanks