Functions & SQL

[teyeheimans]

hello,

ive got a script whith a lot of functions. In every function i make a connection to a database.

I had made a function that connects to a database and send the information back..

It isnt working.. can you tell me why?

here's my code. (atleast, how it works. You can few the full code at http://www.teye.nl/data.txt)

Thanx

Teye

btw: i get this errors:

Warning: odbc_fetch_row(): 3 is not a valid ODBC result resource in c:\program files\apache group\apache\htdocs\helpdesk\data.php on line 69

Warning: odbc_fetch_row(): 10 is not a valid ODBC result resource in c:\program files\apache group\apache\htdocs\helpdesk\data.php on line 129

Warning: First argument to array_keys() should be an array in c:\program files\apache group\apache\htdocs\helpdesk\userinfo.php on line 29

<?

function ConnectMYSQL ($query) {
/ maak mysql connectie met de volgende gegevens (database, host, gebruiker, pass) /
$mysql_connect = mysql_pconnect("localhost", "", "");
$connect = mysql_select_db("personalia", $mysql_connect);

/* voer de query uit */ 
$sql = mysql_query($query) or die(mysql_error() . "\n" . $query); 

/* sluit de databaseconnectie */ 
mysql_close($mysql_connect); 

/* return de gegevens */ 
return $sql; 

}

function ListSecties() {
/ de query die uitgevoerd wordt /
$query = "SELECT * FROM afdeling ORDER BY afd_id";

/* voer de query uit */ 
$sql = ConnectMYSQL($query); 

/* stop de resultaten in een array */ 
$secties=array(); 
while($row = mysql_fetch_object($sql)) { 
    $secties[$row->afd_id] = $row->afd_naam; 
} 

/* geef de array terug */ 
return $secties; 

}
?>

[daarius]

in the:
function ConnectMYSQL ($query);

you are returning the result resource as:
return $sql;

which is fine for returning the sql resource. But when you do:

$sql = ConnectMYSQL($query);

your $sql is being "copied" by the sql resource being returned by the function. So the resource ID is being duplicated by a simple copy to the $sql. that is the reason you are getting "invalid resource" errors, because you are using the copy.

to solve this problem, do return by reference from your function.
i.e. change the function to:

function &ConnectMYSQL($query);

so when you do return $sql; in the function, this will return the "address" of the $sql and not the "value" of the $sql.

hope my explanation was helpfull.

regards,
Daarius...

[Anon]

Are you sure about that? I don't recall any problems passing result sets by value (I've written database abstraction libraries that do exactly that).

One thing that has me worried is that the error messages refer to ODBC resources not MySQL ones, but that may just be my ignorance wrt Widows/MySQL.

It would be a bit easier if we could tell which lines were 69, etc.

Another thing that worries me slightly is; the database is supposed to be persistent ("pconnect"). That being the case, it makes no sense to try and close it again. Maybe just using mysql_connect() will have a noticeable effect. Of course, mysql_close() probably isn't that necessary either way.

Instead, the connection should be made once at the start of the script (persistent or otherwise) and closed when no longer needed. It should certainly not be opened and closed for every single query (unless there really is going to be only one during the coure of the entire script).

[teyeheimans]

<?
// from line 55 to 72
/ haal de gegevens op /
$exec =& ConnectODBC($odbc_query);
$sql =& ConnectMYSQL($mysql_query);
$afd_sql =& ConnectMYSQL($mysql_afd_query);

/* stop de sectie-gegevens in een array */
$secties=array();
while($row = mysql_fetch_object($afd_sql)) {
    $secties[$row->afd_id] = $row->afd_naam;
}
/* als alle gegevens zijn opgevraagt, stop deze dan in een array */
if($str=="all") {
    while(odbc_fetch_row($exec)) {
        for($i = 1; $i <= 32; $i++) {
            $id = odbc_result($exec,1);
            $gegevens[$id][$i] = odbc_result($exec,$i);
        }
    }

// etc
?>

<?
// line 127
while(odbc_fetch_row($exec) ) {
$num = odbc_result($exec, 1);
$beroepen[$num] = odbc_result($exec, 2);
}
?>

It isnt working with references.

I now have this, but the same errors appear.

<?
function &ConnectODBC ($query) {
/ maak odbc connectie met de volgende gegevens (database, gebruiker, pass) /
$odbc_connect = odbc_pconnect("personalia", " ", " ",SQL_CUR_USE_ODBC);

/* voer de opgegeven query uit */
$exec = odbc_exec($odbc_connect, $query) or die (odbc_error() . "<br>\n" .  $query);

/* sluit de dbconnectie */
//odbc_close($odbc_connect);

/* return de gegevens */
return $exec;

}
/ haal de gegevens op /
$exec =& ConnectODBC($odbc_query);
// etc.
?>

[Anon]

Oh, so there're ODBC functions as well.

I can see this loop:

for($i = 1; $i <= 32; $i++) {
$id = odbc_result($exec,1);
$gegevens[$id][$i] = odbc_result($exec,$i);
}

That will choke if there are only two records returned in $exec. The first column of the first record will go in $id, and the $ith (1 as it happens) of the second record will go in $gegevens[$id][1]. Then the first column of the third record will go in $id - and if there is no third record you'll get the error message you describe.

If what you're trying to do is get all the fields of every record into $gegevens, You'd probably be better off:

while($exec_row=odbc_fetch_row($exec)){
$gegevens[$exec_row[0]]=$exec_row;
}

Maybe that should be $exec_array[1] - whichever one the ID fields turns out to be in, of course.

My guess is something similar is happening in the other cases.