Jump to content

odbc_exec/connection strings no longer working

flamingo

By flamingo
in PHP Coding Help

Recommended Posts

flamingo Newbie

flamingo

Posted
Posted

hi

i have 3 PHP scripts that WERE running rather nicely until recently, we have a non secure Access database that we pick deliberate information from and then sort it (the first PHP is the opening page with drop down boxes pulled from the database and the search options, the second script does the actual search based on those selections, and the third outputs results in some kind of order on the final page)

Though i didn't write the entire script, i have worked on it and added to it and it was working until last month, someone else (a student intern we had) initially developed this method of searching through the database for us and though we know it's not the best way, it did work and right now nobody has the skillset to change it to anything else (i'm the webmistress and do some advanced coding and can hack my way around, but not just do this from the start, there doesn't seem to be much information on what we really need to do)......i asked for help from easycgi's (our host for this site) tech support and they rewrote the connection string (you could clearly see that it wasn't getting past the initial drop down box and therefore not connecting), i also went through the database because at one point several years ago, we had a similar issue and it was a blank field in the database.....

the page(s) start at:

http://www.rifamilytofamily.net/test/fronttest1.php

the drop down boxes and the rest of the page is now there (but no info being sent to the drop down box), i am using the PHP Designer and i only got an error for the odbc_execute and odbc_fetch_array commands that the connection string was invalid, we are non profit and i don't do enough of this to 'see it' though

 

i got it further than what i had last week, so any help greatly appreciated, i think i just need another pair of 'eyes' to look at it....

the OLD script which no longer seems to work (and yes, it's worked for YEARS) is at http://www.rifamilytofamily.net/front.php, it used a different method of connecting (@odbc_connect) than the current $db_conn = new COM ("ADODB.Connection"); that easycgi said i needed to use....

the code is not long so please email if you are willing to look at it and thank you again.

flamingo Newbie

flamingo

Posted
  • Author
Posted

as i've said, (a variation on these) scripts had worked for years, they (the host) asked that we change our connection string to what is now here, they also uploaded a test script that does fetch the info, but not to the extent that we need it formatted.....

 

here is the first script, front.php, this one sets up the page with the drop down lists that come from the database itself (i am only including relevant parts of the script, not the entire page/html associated with it):

<form method='post' action='runquery.php'>

<dl>

<dt><label>

<input type='radio' name='searchtype' value='allmembers' checked/>

<span class="style9"> All Members</span></label></dt>

 

<dt class="style9"><label>

<input type='radio' name='searchtype' value='restricted'/>

Restrict search by:</label></dt>

 

<dd class="style9"><label><input type='checkbox' name='byage'/>Age</label>

<label>Start Age</label><input type='text' name='StartAge' size=3/>

<label>End Age</label><input type='text' name='EndAge' size=3/></dd>

 

<dd class="style9"><label><input type='checkbox' name='disability'/>Disability:</label>

<select name='disab'><br/>

<?php

$db_conn = new COM("ADODB.Connection");

$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("F2F.mdb").";";

$db_conn->open($connstr);

$query= $db_conn->execute("SELECT DISTINCT DisabilityCategory FROM Members_FamilyMemberDisabilityCategory ORDER BY DisabilityCategory");

$disab_query = odbc_exec($db_conn,$query);

 

while($disabid = odbc_fetch_array($disab_query)){

echo "<option value='".$disabid['DisabilityCategory']."'>".$disabid['DisabilityCategory']."</option>";

}

$db_conn->Close();

?>

</select><br/>

<dd class="style9"><label><input type='checkbox' name='bylanguage'/>Language:</label>

<select name='lang'><br/>

 

<?php

$db_conn = new COM("ADODB.Connection");

$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("F2F.mdb").";";

$db_conn->open($connstr);

$query= $db_conn->execute("SELECT DISTINCT LanguageSpoken FROM Members_LanguagesSpoken ORDER BY LanguageSpoken");

$lang_query = odbc_exec($db_conn,$query);

 

while($langid = odbc_fetch_array($lang_query)){

echo "<option value='".$langid['LanguageSpoken']."'>".$langid['LanguageSpoken']."</option>";

}

$db_conn->Close();

?>

</select><br/>

<dd class="style9"><label><input type='checkbox' name='bytopic'/>Topic of interest:</label>

<select name='topic'><br/>

 

<?php

$db_conn = new COM("ADODB.Connection");

$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("F2F.mdb").";";

$db_conn->open($connstr);

$query= "SELECT DISTINCT Topic FROM Memberexperience_all ORDER BY Topic";

$topic_query = odbc_exec($db_conn,$query);

 

while($topicid = odbc_fetch_array($topic_query)){

echo "<option value='".$topicid['Topic']."'>".$topicid['Topic']."</option>";

}

$db_conn->Close();

?>

</select><br/>

</dl>

 

<dl class="style3">

<dt class="style9">Sort by:</dt>

<dd class="style9"><label><input type='radio' name='sortby' value='LastName' checked/>Last Name</label></dd>

<dd class="style9"><label><input type='radio' name='sortby' value='City'/>City</label>

</dd>

</dl>

<span class="style3">

<input type='submit' value='Get List'/>

</span>

</form>

 

the above script gets an error (using PHPdesigner) on the odbc_exec and odbc_fetch_array lines, saying the supplied argument is not valid ODBC-Link resource

 

this is the runquery.php which actually fetches the info, there are NO error codes with this according to logs:

 

<?php

 

require("data.php");

 

import_request_variables("p","p_");

 

$runquery = 0;

$query = "SELECT * FROM Members_contactinfo".

" WHERE Inactive=false";

 

if(!isset($p_searchtype)){

  $p_searchtype="Undef";

}

 

switch($p_searchtype){

case "allmembers":

$runquery = 1;

break;

case "restricted":

if( isset($p_byage)){

if(is_numeric($p_StartAge) && is_numeric($p_EndAge) ){

$query.=

" AND Network_ID IN".

" (SELECT Network_ID FROM Members_Age".

" WHERE Expr1 >=".$p_StartAge.

" AND Expr1 <=".$p_EndAge.")";

$runquery = 1;

} else {

$runquery = 0;

break;

}

}

 

 

if(isset($p_disability)){

  if($p_disab==""){

  $runquery=0;

break;

} else{

$query.=

" AND Network_ID IN".

" (SELECT Network_ID FROM Members_FamilyMemberDisabilityCategory".

" WHERE DisabilityCategory LIKE '".$p_disab."%')";

$runquery =1;

}

}

 

 

 

if(isset($p_bylanguage)){

if($p_lang==""){

$runquery=0;

 

} else{

$query.=

" AND Network_ID IN".

" (SELECT Network_ID FROM Members_LanguagesSpoken".

" WHERE LanguageSpoken LIKE '".$p_lang."%')";

$runquery = 1;

}

}

if(isset($p_bytopic)){

if($p_topic == ""){

$runquery = 0;

} else {

$query.=

" AND Network_ID IN".

" (SELECT Network_ID FROM Memberexperience_all".

" WHERE Topic LIKE '".$p_topic."%')";

$runquery = 1;

}

}

break;

default:

$runquery=0;

}

 

switch($p_sortby){

case "LastName":

$query.=" ORDER BY Last_Name";

break;

case "City":

$query.=" ORDER BY City";

break;

}

 

if($runquery){

runquery($query);

} else {

echo "You have entered something that either does not exist on our database or is incorrect.  Please click".

    " <a href='front.php'>here</a>",

    " to try again.";

}

 

?>

 

and last is the data.php which outputs/formats to the final page the results of what people have selected to search, there are also no error codes (according to PHPdesigner) with this:

 

<?php

$db_conn = new COM("ADODB.Connection");

$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("F2F.mdb").";";

$db_conn->open($connstr);

function runquery($passedQuery = "")

{

 

    $query = $passedQuery;

 

    $output = "";

 

    $contact_info_query = odbc_exec($db_conn, $query);

 

    while ($contact_info = odbc_fetch_array($contact_info_query)) {

        // Print Name and Address

        $output .= "<strong>Name:</strong> " . $contact_info["First_Name"] . " " . $contact_info["Last_Name"] .

            "<br/>\n";

        //echo $contact_info["Address"]."<br/>\n";

        $output .= "<strong>City, State, Zip:</strong> " . $contact_info["City"] . ", " .

            $contact_info["State"] . " " . $contact_info["Zip_Code"] . "<br/>\n";

        $output .= "<br/>\n";

 

 

        // Phone Number, if it's public

        if ($contact_info["Pub_Phone"]) {

            $output .= "<strong>Phone:</strong> " . $contact_info["Pub_Phonenumber"] .

                "<br/>\n";

        }

        //contact preference

        if ($contact_info["ContactPreference"]) {

            $output .= "<strong>Contact Preference:</strong> " . $contact_info["ContactPreference"] .

                "<br/>\n";

        }

        // Email Address, if it's public

        if ($contact_info["Pub_Email"]) {

            $output .= "<strong>Email:</strong> " . "<a href='mailto:" . $contact_info["Pub_address"] .

                "'>" . $contact_info["Pub_address"] . "</a><br/>\n";

 

            $output .= "<br/>\n";

        }

        //participation

        if ($contact_info["ParticipationList"]) {

            $output .= "<strong>Participates in:</strong> " . $contact_info["ParticipationList"] .

                "<br/>\n";

        }

 

 

        // Family Members

        $output .= "<table>\n" . "<th colspan='3' align='left'>" .

            "Family Member(s) with Special Needs" . "</th>\n";

        $query = "SELECT * FROM Members_Age WHERE Network_ID=" . $contact_info["Network_ID"];

        $memberdata_query = odbc_exec($db_conn, $query);

        while ($memberdata = odbc_fetch_array($memberdata_query)) {

            $output .= "<tr><td width='100px'>Age: " . floor($memberdata["Expr1"]) .

                "</td><td width='200px'>Gender: " . $memberdata["Gender"] .

                "</td><td width='400px'>Special Need: " . $memberdata["SpecialNeed"] .

                "</td></tr><br/>\n";

        }

        $output .= "</table>\n" . "<br/>\n" . "<ul>\n" .

            "<br/><strong>Language(s) Spoken:</strong><br/>\n";

        $query = "SELECT * FROM Members_LanguagesSpoken WHERE Network_ID=" . $contact_info["Network_ID"];

        $lang_query = odbc_exec($db_conn, $query);

        while ($language = odbc_fetch_array($lang_query)) {

            $output .= "<li>" . $language["LanguageSpoken"] . "</li>\n";

        }

        $output .= "</ul>\n" . "<ul>\n" . "<strong>Experience:</strong><br/>\n";

        $query = "SELECT * FROM Memberexperience_all WHERE Network_ID=" . $contact_info["Network_ID"];

        $exp_query = odbc_exec($db_conn, $query);

        while ($experience = odbc_fetch_array($exp_query)) {

            $output .= "<li>" . $experience["Experience"] . "</li>\n";

        }

        $output .= "</ul>\n" . "<br/>\n" . "<hr/>\n";

    }

 

    if ($output == "") {

        echo "No Values Returned<br/>";

    } else {

        echo $output;

    }

}

$db_conn->Close();

?>

 

i honestly think this is a matter of a word/syntax at this point...and you (all) must know that after awhile, you really need someone else to look at it, so thank you (all) again....

trq Prolific Member

trq

Posted
Posted

The problem is your using com to open a connection, then trying to use odbc_exec to execute your queries. Its all pretty messed up actually. For instance, this....

 

<?php
   $db_conn = new COM("ADODB.Connection");
$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("F2F.mdb").";";
$db_conn->open($connstr);
   $query= $db_conn->execute("SELECT DISTINCT DisabilityCategory FROM Members_FamilyMemberDisabilityCategory ORDER BY DisabilityCategory");
   $disab_query = odbc_exec($db_conn,$query);

   while($disabid = odbc_fetch_array($disab_query)){
      echo "<option value='".$disabid['DisabilityCategory']."'>".$disabid['DisabilityCategory']."</option>";
   }
   $db_conn->Close();
?>

 

Should simply be.....

 

<?php
  
  $db_conn = new COM("ADODB.Connection");
  $connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("F2F.mdb").";";
  $db_conn->open($connstr);
  $query = "SELECT DISTINCT DisabilityCategory FROM Members_FamilyMemberDisabilityCategory ORDER BY DisabilityCategory";
  $result = $db_conn->execute($query);

  while(!$result->eof){
    echo "<option value='".$result['DisabilityCategory']."'>".$result['DisabilityCategory']."</option>";
    $result->movenext;
  }

   $db_conn->Close();

?>

 

Or simular. Its been a long while since Ive used com or its ADODB.Connection object.

flamingo Newbie

flamingo

Posted
  • Author
Posted

you are correct, the hosting company tech support are the ones who changed it to com()!!! and that's where i've thought the conflict to be all along....this is what we USED to use that suddenly stopped working:

 

<?php

 

define("DSN", "uapdata");

define("USERNAME", "");

define("PASSWORD", "");

 

?>

 

<form method='post' action='runquery.php'>

<dl>

<dt><label>

<input type='radio' name='searchtype' value='allmembers' checked/>

<span class="style9"> All Members</span></label></dt>

 

<dt class="style9"><label>

<input type='radio' name='searchtype' value='restricted'/>

Restrict search by:</label></dt>

 

<dd class="style9"><label><input type='checkbox' name='byage'/>Age</label>

<label>Start Age</label><input type='text' name='StartAge' size=3/>

<label>End Age</label><input type='text' name='EndAge' size=3/></dd>

 

<dd class="style9"><label><input type='checkbox' name='disability'/>Disability:</label>

<select name='disab'><br/>

<?php

$link = @odbc_connect(DSN, USERNAME, PASSWORD) or exit();

$query= "SELECT DISTINCT DisabilityCategory FROM Members_FamilyMemberDisabilityCategory ORDER BY DisabilityCategory";

$disab_query = odbc_exec($link,$query);

 

while($disabid = odbc_fetch_array($disab_query)){

echo "<option value='".$disabid['DisabilityCategory']."'>".$disabid['DisabilityCategory']."</option>";

}

?>

 

so should i take your lead from above and just continue down with that way of doing things OR do you see a glaring error in the above? thanks again for the replies!

flamingo Newbie

flamingo

Posted
  • Author
Posted

i rewrote the opening page code using your example to test and it works here at home on my computer (using phpDesigner to debug, and so far just the front page, not the overall search later on), yet when i upload it to the server at easycgi.com, it just crashes (takes forever then finally i have to shut down the browser ctl-alt-del)

 

any other suggestions? thanks again, i honestly think this wasn't anything we/i did with this script as it did run for years, i think the hosting company changed something in the way you have to connect/configure databases and just won't own up to it....

flamingo Newbie

flamingo

Posted
  • Author
Posted

i have the front page working here at home via phpDesigner, but still will not work when i upload it.....it just takes forever and eventually internet explorer crashes...snippet of code for a section of that opening page is (based on your suggestion):

 

<dd class="style9"><label><input type='checkbox' name='disability'/>Disability:</label>

<select name='disab'><br/>

<?php

 

  $db_conn = new COM("ADODB.Connection");

  $connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("F2F.mdb").";";

  $db_conn->open($connstr);

  $query = "SELECT DISTINCT DisabilityCategory FROM Members_FamilyMemberDisabilityCategory ORDER BY DisabilityCategory";

  $result = $db_conn->execute($query);

 

  while(!$result->eof){

    echo "<option value='".$result['DisabilityCategory']."'>".$result['DisabilityCategory']."</option>";

    $result->movenext;

  }

 

  $db_conn->Close();

 

?>

</select><br/>

 

the 2nd script i think is fine, it doesn't have to connect as it's called as a function when you click the submit button.....the third script is looking a bit buggy though (though i haven't been able to get that far since i can't get it running online live), just attaching a part of that as well (lotsa if statements here):

 

<?php

$db_conn = new COM("ADODB.Connection");

$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./F2F.mdb").";";

$db_conn->open($connstr);

function runquery($passedQuery = "")

{

$result = $db_conn->execute($query);

 

$query = $passedQuery;

 

    echo "";

 

    while (!$result->eof) {

        // Print Name and Address

        echo "<strong>Name:</strong> " .$result["First_Name"] . " " .$result["Last_Name"] .

            "<br/>\n";

        //echo $contact_info["Address"]."<br/>\n";

        echo "<strong>City, State, Zip:</strong> " .$result["City"] . ", " .

            $result["State"] . " " . $result["Zip_Code"] . "<br/>\n";

        echo "<br/>\n";

 

 

        // Phone Number, if it's public

        if ($result["Pub_Phone"]) {

            echo "<strong>Phone:</strong> " . $result["Pub_Phonenumber"] .

                "<br/>\n";

        }

        //contact preference

        if ($result["ContactPreference"]) {

            echo "<strong>Contact Preference:</strong> " . $result["ContactPreference"] .

                "<br/>\n";

 

        if ($result == "") {

        echo "No Values Returned<br/>";

        } else {

        echo $result;

        }

}

$db_conn->Close();

?>

 

thanks again for all/any help, really appreciate it (my head hurts!!)

flamingo Newbie

flamingo

Posted
  • Author
Posted

i have the first page all set, it was a matter of syntax and a parenthesis, which is why i wanted someone else to look at it (i finally found it myself)

but the query script isn't quite right in the connection area, they admitted they changed their connections, but didn't offer much help on what i need to do to fix mine...so here is the OLD query connection code:

 

<?php

define("DSN", "uapdata");

define("USERNAME", "");

define("PASSWORD", "");

 

 

function runquery($passedQuery=""){

 

  $link = @odbc_connect(DSN, USERNAME, PASSWORD) or exit();

 

  $query= $passedQuery;

 

  $output = "";

 

  $contact_info_query = odbc_exec($link,$query);

 

  while($contact_info = odbc_fetch_array($contact_info_query)){

    // Print Name and Address

    $output.= "<strong>Name:</strong> ".$contact_info["First_Name"]." ".$contact_info["Last_Name"]."<br/>\n";

    $output.= "<strong>City, State, Zip:</strong> ".$contact_info["City"].

      ", ".$contact_info["State"].

      " ".$contact_info["Zip_Code"]."<br/>\n";

    $output.= "<br/>\n";

 

and here is what i have so far (with NO error messages, but NOT working):

 

<?php

$db_conn = new COM("ADODB.Connection");

$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./F2F.mdb").";";

$db_conn->open($connstr);

function runquery($passedQuery = "")

{

$result = $db_conn->execute($query);

 

$query = $passedQuery;

 

    echo "";

 

    while (!$result->eof) {

        // Print Name and Address

        echo "<strong>Name:</strong> " .$result["First_Name"] . " " .$result["Last_Name"] .

            "<br/>\n";

        echo "<strong>City, State, Zip:</strong> " .$result["City"] . ", " .

            $result["State"] . " " . $result["Zip_Code"] . "<br/>\n";

        echo "<br/>\n";

 

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.