&amp;quot;Data source name not found&amp;quot; trying to connect to DSN - using MySQL and PHP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I thought that I might need to and indeed I did try ... but there is no driver there for MySQL, and the only driver I see that mentions ODBC is "Microsoft ODBC for Oracle".
So what driver am I supposed to use? OR ... where do I get the appropriate driver and how do I "install" it so it shows up in the list of drivers?
I do have MySQL installed on my PC. As I mentioned, accessing the database directly works just fine.
Thanks.

SOLUTION

Umesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Yes, ushastry is right, you need to install the MySQL OCBC driver.

for lower version of MySQL, you may need this:
http://dev.mysql.com/downloads/connector/odbc/3.51.html#win32

ASKER

Thanks. I downloaded the 5.1 connector and installed it, then created the datasource through CP > Administrative Tools > Data Sources.
My odbc_connect now works: $connection = odbc_connect("connWidgets","root","mypassword");
So now instead of this (which I used for the direct connection):

$result = mysql_query("
SELECT menu_name, position
FROM subjects
", $connection);

I am trying this:

$result = odbc_execute("
SELECT menu_name, position
FROM subjects
");

And I'm getting this error:
Warning: odbc_execute(): supplied argument is not a valid ODBC result resource in C:\Inetpub\wwwroot\meatormotion\php_sandbox\db_DSN.php
So once I've set up the datasource and connected to it, how do I query the database? Thanks.

ASKER

Nevermind my last post. I figured out what I needed to do. See code snippet below. Still have one more piece that needs solving ... see next post.

$connection =  odbc_connect("connWidgets","","");	
	
$sql_statement = odbc_prepare($connection,"
	SELECT menu_name, position
	FROM subjects
	");
	
$result = odbc_execute($sql_statement);

ASKER

So now that I have $result:
Previously, when I got $result through a direct connection and used "mysql_query" to get it, I then used "mysql_fetch_array" to access the result.
What's the equivalent of "mysql_fetch_array" for ODBC? And where can I find these ODBC functions?
Thanks.

ASKER

Here's the code for how I did it before (with direct connect and mysql_ commands)

<?php
	//Use returned data
	while ($row = mysql_fetch_array($result)) {
	echo $row["menu_name"]." ".$row["position"]."<br />";
	}
?>

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks. I actually had already tried that (see code below). I get this error:
Warning: odbc_fetch_array(): supplied argument is not a valid ODBC result resource

<?php
	//Use returned data
	while ($row = odbc_fetch_array($result)) {
	echo $row["menu_name"]." ".$row["position"]."<br />";
	}
?>

you may add this ?

<?php
if (!function_exists('odbc_fetch_array')) {
    function odbc_fetch_array($result, $rownumber=null) {
        $array = array();
        if (!($cols = odbc_fetch_into($result, $result_array, $rownumber))) {
            return false;
        }
        for ($i = 1; $i <= $cols; $i++) {
            $array[odbc_field_name($result, $i)] = $result_array[$i - 1];
        }
        return $array;
    }
}
 
?>
 
please read more on the comments on that article and see if that helpful?

ASKER

Yes, I did read that comment. They are actually defining the function if it does not exist. In my case, I'm pretty sure the function already exists because the error is complaining about an argument not being valid. It's not saying that the function does not exist.
I did figure out, though, that the problem was the odbc_execute returns a boolean, not a resource. I should have used odbc_exec instead.
So now everything works, and I know where to find the odbc functions. Thanks!
Below is all of the code that I used to make the connection, execute a query, and use the result, just to have all the correct code in one place.

<?php
//Create a connection
$connection =  odbc_connect("connWidgets","","");
if (!$connection) {
     die("Database connection failed: " . mysql_error());
}
	
//Execute the query
$result = odbc_exec($connection, "
	SELECT menu_name, position
	FROM subjects
	");
 
//Use the returned data
while ($row = odbc_fetch_array($result)) {
	echo $row["menu_name"]." ".$row["position"]."<br />";
	}		
?>

hey, your codes seems working fine for me...