DB connections

I am using this code to connect to a db

     @$conn = mysql_connect("localhost",$dbUserName, $dbPassword) or die("An error occured whilst connecting to the database.\n");

     @mysql_select_db($dbName) or die("Could not select database on host");

     //check to see if page is in database

     $sql="select id from pages where name = \"$name\";";
     $result = mysql_query($sql) or die("Query failed");

this is in the global scope of the script, i then have a function in the same script that also needs to runa sql query on the same connection, how do i do this, how do i make the connection global or accessable to the function.

thanks in advance kieran
kplonkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrivCommented:
Have you tried:

function function_name()
{
global $conn;
0
huweiqiCommented:

Hi,
Here is my way:

1) put your mysql_connect( or mysql_pconnect) and mysql_select_db in an include file, for example "db.inc".
   also you could put your function in the same or another include file, for example "functions.inc".

2) use include or require statement in files you want to get database connection and use your functions. for example:
#inlcude("db.inc");
#include("functions.inc");

my_function();

hope it helps
regards
huweiqi


0
dkjariwalaCommented:
Basically you don't need to worry about link identifier which is returned by the mysql_connect.

Why ?

Cause when you use function mysql_query and don't give link identifier it would AUTOMATICALLY identify current connection and execute query using that. So no need to use global and stuff, just go ahead in using the mysql_query function.

JD
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

andrivCommented:
JD is right, if no link is provided PHP will use the last opened link.
0
kplonkAuthor Commented:
Ok here is the main call to the db, the first one not in any function just in the script.

     @$conn = mysql_connect("localhost",$dbUserName, $dbPassword) or die("An error occured whilst connecting to the database.\n");

     @mysql_select_db($dbName) or die("Could not select database on host");

     //check to see if page is in database

     $sql="select id from pages where name = \"$name\";";
     $result = mysql_query($sql) or die("Query failed");

then i do some other stuff then i have this function that is called form the above script

function buildNavigation()
{
     $username = $GLOBALS["username"];
     
     //get user details      
    $sql="select id from user where user_name = \"$username\";";
    $result = mysql_query($sql) or die("Query failed");    
     
     return "User name " . $username;
}

here the query failes, why is this, i thought that if i provided no more conection details php would simply use the last obtained connection, i think this is what JD is saying??
0
andrivCommented:
First make sure it's the connection that is failing:

$result = mysql_query($sql) or die(mysql_error());

The mysql_error function will return a error message from the db if any.

If it's the connection try:

$result = mysql_query($sql,$GLOBALS["conn"]) or die(mysql_error());  
0
Steves2001Commented:
here database object that I created

<?
class DatabaseObj {

  var $dblink;
     var $dbname;
     var $lasterror;
     var $lastresult;
     
     function DatabaseObj($DBName, $DBHost, $DBUser, $DBPass) {

          $this->dbname = 'upgrader'; // Change this back to = $DBName after correcting source code
         
    $this->dblink = @mysql_connect("$DBHost", "$DBUser", "$DBPass")
     or die( "<p><b>Could not connect to database server: ($DBHost)</b></p>" );

    @mysql_select_db ($this->dbname , $this->dblink)
     or die (  "<p><b>Could not connect to database ($DBName)</b></p>" );


     }
     
     function SQLFetchAssoc() {
       return mysql_fetch_assoc($this->lastresult);
     }
     
     function SQLSelect ($sql){
       $this->lastresult = mysql_query($sql, $this->dblink);
       return $this->lastresult;
     }
     
     function SQLInsert($table, $values) {
       $fieldnames = implode(", ",array_keys($values));
          $fieldconts = implode(", ",array_values($values));

          $sql = "INSERT INTO $table ($fieldnames) VALUES ($fieldconts)";

          $result  = mysql_query($sql, $this->dblink)
     or $this->lasterror = mysql_error();

          return $result;
     }

     function SQLUpdate($table, $values, $criteria) {

          $sql = "UPDATE $table SET ";

    foreach ($values as $field => $value)
               $sql .= "$field = $value, ";
               
          $sql = substr ($sql, 0, -2); // strip last comma and space  

          $sql .= " WHERE $criteria";

          $result = mysql_query($sql, $this->dblink) or $this->lasterror = mysql_error();

          return $result;
     }

     function SQLDelete($table, $field, $values) {
          $range = implode(", ",array_values($values));

          $sql = "DELETE FROM $table WHERE $field IN ($range)"; echo $sql;
          $result  = mysql_query($sql, $this->dblink)
     or $this->lasterror = mysql_error();
          if ($result)
                return mysql_affected_rows($this->dblink);
          else
                return $result;
     }
}
?>

use as below

$db = new DatabaseObj('faults', 'localhost', '', '');

if ( $result = $db->SQLSelect("SELECT uid, user, email FROM users"))
{$row = $db->SQLFetchAssoc();}

if you wish to use in a function either pass $db by reference or use global $db



0
kplonkAuthor Commented:
Ok so now using $result = mysql_query($sql) or die(mysql_error()); thanks andriv, i can see that the problem is that no db is selected, as you can see this is not the case as i have selected on earler in the mane script. so what to do.  So i tried adding this line.      mysql_select_db("kplonk") or die(mysql_error());     but it failes with the error
"Access denied for user: '@localhost' to database 'kplonk'"
0
andrivCommented:
Try this:

function buildNavigation()
{
    global $name,$conn,$dbname;
   
    //get user details      
   $sql="select id from user where user_name = \"$username\";";
   $result = mysql_db_query("$dbname,$sql,$conn) or die("Query failed");    
   
    return "User name " . $username;
}


The mysql_db_query will allow you to select the database in the query.  The global will make the variables global.

Also, what version of PHP do you have?
0
dkjariwalaCommented:
Exactly.

So your problem is not about being your connection variable global. But your connection itself is not going through.

Seems like you have permission issue here. Check with MySQL admin about your permissions.

JD
0
kplonkAuthor Commented:
4, still having problems, this is what the function looks like

function buildNavigation()
{
     //get user details
        global $conn,$dbName,$username;
     
       $sql="select id from user where user_name = \"$username\";";
       $result = mysql_db_query($dbName,$sql,$conn) or die("Query failed");    
     
     return "User name " . $username;
}

and the error is now "Warning: Supplied argument is not a valid MySQL-Link resource in /home/sites/site56/web/test/index.php on line 105
"

any ideas
0
andrivCommented:
See what happens if you reconnect to the server:

function buildNavigation()
{
    //get user details
       global $dbName,$username;

     @$conn = mysql_connect("localhost",$dbUserName, $dbPassword) or die("An error occured whilst connecting
to the database.\n");

      $sql="select id from user where user_name = \"$username\";";
      $result = mysql_db_query($dbName,$sql,$conn) or die("Query failed");    
   
    return "User name " . $username;
}
0
andrivCommented:
One more thing, if you are not using version 3 remove the '@' symble from the $conn, this can be causing the problem.
0
kplonkAuthor Commented:
Odd thing the code now looks like this,

function buildNavigation()
{
     //get user details
        global $user_access_level,$username;
   
     $title_sql="select title,id from nav order by orderby;";
    $title_result = mysql_query($title_sql) or die("Query failed");

and works dont know what changed, appart form removing the reconect, that said i had already tried that must have miss typed or somthing.. my new proble is hwo to give the points to??
0
andrivCommented:
That is up to you, you can give it to who ever you feel gave you the most useful information or if you feel none of the comments helped you then you can request that the question be deleted in the 'Community Support' topic area
www.experts-exchange.com/commspt/. Also, if you feel more than one comment helped you accomplish you goal you can go to the link above and request the points be split between to different experts.

In any case I am glad you were able to get it working.

Some suggestions that may have helped you are:

From             Suggestion
====            ===========
Steves2001      Use of object script he created
dkjariwala      MySQL Permissions
huweiqi         Use include file example he provided
andriv          use of global versus GLOBAL[]
andriv          use of mysql_error() to get mysql error message
andriv          remove @ symbol
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kplonkAuthor Commented:
Ok thanks well as all in this thread helped me with this problem i feel i will split the points equaly, thanks for the input all of you it reall helped me out.
0
andrivCommented:
Glad we can help
0
kplonkAuthor Commented:
Ok have been told to add questions form the rest of you to collect some points have a look
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.