Solved

PHP / MySql strange inability to select a database

Posted on 2013-01-30
9
258 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 83

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 83

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

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 110

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

679 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