Solved

PHP / MySql strange inability to select a database

Posted on 2013-01-30
9
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
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.

636 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