Solved

DB connections

Posted on 2002-04-04
18
358 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:kplonk
  • 8
  • 6
  • 2
  • +2
18 Comments
 
LVL 5

Expert Comment

by:andriv
ID: 6919497
Have you tried:

function function_name()
{
global $conn;
0
 

Expert Comment

by:huweiqi
ID: 6920040

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
 
LVL 5

Expert Comment

by:dkjariwala
ID: 6920177
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
 
LVL 5

Expert Comment

by:andriv
ID: 6920963
JD is right, if no link is provided PHP will use the last opened link.
0
 

Author Comment

by:kplonk
ID: 6921425
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
 
LVL 5

Expert Comment

by:andriv
ID: 6921444
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
 
LVL 1

Expert Comment

by:Steves2001
ID: 6921492
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
 

Author Comment

by:kplonk
ID: 6921544
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
 
LVL 5

Expert Comment

by:andriv
ID: 6921580
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Expert Comment

by:dkjariwala
ID: 6921593
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
 

Author Comment

by:kplonk
ID: 6921632
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
 
LVL 5

Expert Comment

by:andriv
ID: 6921671
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
 
LVL 5

Expert Comment

by:andriv
ID: 6921687
One more thing, if you are not using version 3 remove the '@' symble from the $conn, this can be causing the problem.
0
 

Author Comment

by:kplonk
ID: 6926421
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
 
LVL 5

Accepted Solution

by:
andriv earned 25 total points
ID: 6926460
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
 

Author Comment

by:kplonk
ID: 6926468
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
 
LVL 5

Expert Comment

by:andriv
ID: 6926481
Glad we can help
0
 

Author Comment

by:kplonk
ID: 6928831
Ok have been told to add questions form the rest of you to collect some points have a look
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now