Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP / MySql strange inability to select a database

Posted on 2013-01-30
9
Medium Priority
?
261 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 84

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 84

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 84

Accepted Solution

by:
Dave Baldwin earned 1200 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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.
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…
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…

705 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