Solved

PHP / MySql strange inability to select a database

Posted on 2013-01-30
9
252 Views
Last Modified: 2013-01-31
In one of my pages, I need to connect to a database and read which other databases to connect to.

this has been working in production, but then I made a tweak in development ... and now the development one has started doing crazy stuff.  specifically, it blanks out the string variable that holds the database name to which to connect after the connection attempt and does not select the DB.

				if ($resultDB != null){ //skip empty results caused by bad setup.
					$rowDB = $resultDB->fetch_object();
					
						//return $rowDB->DB; -- returns right answer
						$sDBName = $rowDB->DB;
						//return $sDBName; -- returns right answer
						
						
					$sNote = "Attempt to connect to $sDBName";
					$connData = new mysqli($db['host'],$db['user'],$db['passwd'],$sDBName);
					if($connData->connect_error) return $connData->connect_error; 
						//return $sDBName; -- returns right answer
					$sNote .= " Attempt to select $sDBName";
						//return $sDBName; -- returns right answer
					if (!$connData->select_db($sDBName)){
						return "$sNote - ERROR " .$connData->error ." Failed to select " .$sDBName;
					}
				
					$sql = "Select Sum(Goal) as Goal From " . $sDBName.".wt_collectors Where Active=1; ";
						//return $sql;
						//$firephp->info($sql,'Querying '. $rowDB->DB);
					if ($resultData = $connData->query($sql)){// $connData->query($sql);
					//$rowData = $resultData->fetch_object();
							//return $rowData;
					//$val += db_SPres($sql);// $rowData->Goal;
					
						$resultData->close();
					}else return $connData->error;
					
					$connData->close();
					
				}

Open in new window


Any of those return statements that I've marked with " -- returns right answer" gives me the right DB name.

But if I let it run as written above, I get

"Attempt to connect to  Attempt to select  - ERROR No database selected Failed to select "

Just as though $sDBName never did have a value in it.

My problems started when I added a different function to this file.  I have since commented out that function entirely, but this is happening.

Can anyone tell what on earth is wrong with this thing?
0
Comment
Question by:Daniel Wilson
  • 5
  • 3
9 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 38837413
You appear to be trying to work with two different connections.  They have to have different names, you can't use '$connData' for both of them and have it work.  The statements where you say it is 'right' don't have anything to do with MySQL.

On line 19, you do not need to put the database name in there for MySQL in PHP.  In fact, I thought I read somewhere that you shouldn't do that because the MySQL driver takes care of that if it is needed.  Try removing  " . $sDBName.". from line 19.
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 38837671
I am using 2 connections but the one from which $rowDB loads is $connDB.

I dont' get to line 19.  I'm returning the error message from line 16.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 38837719
Oh, I just noticed.  In 'new mysqli()', the database to be used is part of the set up.  Trying to select it again is an error.  Comment out all of this:
if (!$connData->select_db($sDBName)){
  return "$sNote - ERROR " .$connData->error ." Failed to select " .$sDBName;
  }

Open in new window

From http://www.php.net/manual/en/mysqli.select-db.php
This function should only be used to change the default database for the connection. You can select the default database with 4th parameter in mysqli_connect().
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 38837850
OK, it's back how I started -- just selecting the DB in the new mysqli() call.

if ($resultDB != null){ //skip empty results caused by bad setup.
					$rowDB = $resultDB->fetch_object();
					
						//return $rowDB->DB;
						$sDBName = $rowDB->DB;
						//return $sDBName;
						
						
					$sNote = "Attempt to connect to $sDBName";
					$connData = new mysqli($db['host'],$db['user'],$db['passwd'],$sDBName);
					if($connData->connect_error) return $connData->connect_error; 
						//return $sDBName;
					$sNote .= " Attempt to select $sDBName";
						//return $sDBName;
					/*if (!$connData->select_db($sDBName)){
						return "$sNote - ERROR " .$connData->error ." Failed to select " .$sDBName;
					}*/
				
					//$sql = "Select Sum(Goal) as Goal From " . $sDBName.".wt_collectors Where Active=1; ";
					$sql = "Select Sum(Goal) as Goal From wt_collectors Where Active=1; ";
						//return $sql;
						//$firephp->info($sql,'Querying '. $rowDB->DB);
					if ($resultData = $connData->query($sql)){// $connData->query($sql);
					//$rowData = $resultData->fetch_object();
							//return $rowData;
					//$val += db_SPres($sql);// $rowData->Goal;
					
						$resultData->close();
					}else return $connData->error;
					
					$connData->close();
					
				}

Open in new window


And I get the error:
No database selected
from the line ... else return $connData->error;

This was why I tried all those methods to select the database.  Any one of them should have worked.  None have.

I think I said this was a strange problem ...
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 300 total points
ID: 38837957
It is not a strange problem, you just have bad data somewhere.  This works using data on my systems:
<?php 
$sDBName = "nads";
$db['host'] = "10.202.46.41";
$db['user'] = "eecontact";
$db['passwd'] = "eazy4U";
//return $sDBName;

$sNote = "Attempt to connect to $sDBName";
echo $sNote."<br>";
$connData = new mysqli($db['host'],$db['user'],$db['passwd'],$sDBName);
if($connData->connect_error) return $connData->connect_error; 
//return $sDBName;
//$sNote .= " Attempt to select $sDBName";
//$sql = "Select Sum(Goal) as Goal From " . $sDBName.".wt_collectors Where Active=1; ";
$sql = "SELECT * FROM pdotest WHERE Pid='5'";
//return $sql;
if ($resultData = $connData->query($sql)) {
$row = mysqli_fetch_assoc($resultData);
echo $row['value1']."<br>";
}
$connData->close();

Open in new window

0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 38839446
After this instruction...

$connData = new mysqli($db['host'],$db['user'],$db['passwd'],$sDBName);

... add this: var_dump($connData);

You can learn a lot from var_dump()!
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 38839579
object(mysqli)#10 (0) {}object(mysqli)#9 (0) {}Table

Man, that looks pretty empty to me.

Dave, the idea that it's actually a matter of bad data seems to make sense, but I can't figure out what data are bad.
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 38839639
This is reminding me of string assignment problems in C/ C++.  In those languages, I should do a strcpy or similar function rather than just assigning the pointer with an =.

I don't think I've ever had that issue in PHP ... and I don't see a strcpy function in the documentation.  But is that my problem?
0
 
LVL 32

Author Closing Comment

by:Daniel Wilson
ID: 38839834
Dave, you were right, bad data.

Ray, thanks for var_dump.  It helped me identify the bad data.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

706 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

23 Experts available now in Live!

Get 1:1 Help Now