Here's a twisty one I hope someone out there can help me with. There's probably an easy solution, but I tend to complicate things and work my way back from there🙂

Objective:

Connect to 10 databases different databases through MySQL ODBC 3.51
that have the same name, same table names, same field names etc. simultaneously and run one query.

Reason:

Aggregate reporting from 10 different companies created in simply accounting that cannot be consolidated.

Question:

Can I connect to all databases using a variable or function and use that variable or function name to run one single query on all the databases at once?

What I've Tried

<?php

//Connection Variables
$username = "Paul";
$password = "pau345j";
$port1 = "13540";
$port2 = "13541";

//ODBC Connections
$EGL = odbc_connect($port1,$username,$password);
$OCI = odbc_connect($port2,$username,$password);

$multi = array($EGL,$OCI);

//Query and result for Everlast
$sql="SELECT * FROM tcompany";
$rs=odbc_exec($multi,$sql);

while (odbc_fetch_row($rs))
{
$lId = odbc_result($rs, "lId");
$sCompName = odbc_result($rs, "sCompName");

echo "<table width='100%' cellpadding='0' cellspacing='0'>
<tr>
<td class='table_data_L'>$lId</td>
<td class='table_data_R'>$sCompName</td>
</tr>
<table>";
}

?>

And Finally, The error Messages

Warning: odbc_exec() expects parameter 1 to be resource, array given in C:\xampp\htdocs\array_sandbox.php on line 34

Warning: odbc_fetch_row() expects parameter 1 to be resource, null given in C:\xampp\htdocs\array_sandbox.php on line 38

    aManWithNoSocks,

    Please for future code posts place your code within [ php] and [ /php] bbcode tags (without spaces) this helps us to see the code as it should be (proper spacing, etc).

    From what I've read, odbc_exec() doesn't accept arrays as input to use in this fashion, it is expecting a resource object/id.

    You can take a few approaches, but it is dependent on the amount of data.

    I personally -- and have never done this, so someone please correct me if I am wrong.. would execute each connection and bring it into either an Array (with SMALL data) or into a CSV/XML File.

    If you have access to a SQL Server where you can dump data, I would create a temporary table, and bring in all of the data from the CSV/XML files, and if you need to segregate by company add another column for which company (or whatever defining item between each odbc connection it is) then work with the data in that manner.

    Your approach is highly dependent on the number of rows you are pulling from each database, and of course, if you have access to a SQL server in which to dump the data.

    That's my two cents.. best of luck.

      Thanks big.nerd, and everyone sorry about not using the [ php] and [ /php] as tags, it's my first post and didn't even think about it.

      I have a mysql server and will give it a shot. If it works I'll post what I did. here's the code I posted before using the [ php] and [ /php] tags.

      //Connection Variables
$username = "Paul";
$password = "pau345j";
$port1 = "13540";
$port2 = "13541";

//ODBC Connections
$EGL = odbc_connect($port1,$username,$password);
$OCI = odbc_connect($port2,$username,$password);

//Combine the Connections
$multi = array($EGL,$OCI);

//Run the Query
$sql="SELECT * FROM tcompany";
$rs=odbc_exec($multi,$sql);


//Show the Goods
while (odbc_fetch_row($rs))
{
$lId = odbc_result($rs, "lId");
$sCompName = odbc_result($rs, "sCompName");

echo "<table width='100%' cellpadding='0' cellspacing='0'>
<tr>
<td class='table_data_L'>$lId</td>
<td class='table_data_R'>$sCompName</td>
</tr>
<table>";
}

        big.nerd means [noparse]

        ...

        [/noparse] tags (that's what the "(without spaces)" meant). See this FAQ for details.
        I have fixed your post.

          Write a Reply...