help doing a mysql query

[jayreis]

Hello,

have a page where I am going to be doing multiple queries on different databases.

However I thought my below code would work but it does not seem to resurn any error message nore any result set.

Here is my common database connection file

<?php
//database username and pass
$dblogin = mysql_connect("localhost","sitename_abcd","tx35e2vy") or die("Error connecting: <br><br>".mysql_error());

//public NoizIvy database connection
$nidb = mysql_select_db('sitename_databasea', $dblogin) or die("Error getting db: <br><br>".mysql_error());

//Taskpad database connection
$tpdb = mysql_select_db('sitename_databaseb', $dblogin) or die("Error getting db: <br><br>".mysql_error());
?>

here is my query

<select name="acompanyid">
  <?php
   $myid ='1';
   $comanysql = mysql_query("SELECT Usercompany_connections.Companyid, 
                             Userscompanyinfo.Companyname
                             from Usercompany_connections, Userscompanyinfo
                             where Usercompany_connections.Profileid='$myid' AND Usercompany_connections.Companyid = Userscompanyinfo.Companyid", $nidb);
   while($comprow = mysql_fetch_array($comanysql)){
    extract($comprow);
     echo "<option value='$Companyname'>$Companyname</option>";
   }
  ?>
 </select>

[chrishea]

The easiest starting point is to echo your select statement, copy it and try it in PHPMyAdmin. You may find that it needs a change and you can experiment in PHPMyAdmin then make the appropriate changes in your program.

[jayreis]

wll the actual query itself works if I copy and past it directly into phpmyadnim which is why I feel its just that the page does not know what database to use for the query. However in my select query I have a comma and the $nidb which is my datatbase select value so I am not sure why that does not work?

[great2010]

The easiest starting point is to echo your select statement, copy it and try it in PHPMyAdmin. You may find that it needs a change and you can experiment in PHPMyAdmin then make the appropriate changes in your program.

That is good idear,I agreen it.

[Will Gresham]

The reason this does not work will be clear when you read the mysql_query page at php.net.

Here is what it says:

Description:
resource msql_query ( string $query [, resource $link_identifier ] )

And then goes on to say:

link_identifier
The mSQL connection. If not specified, the last link opened by msql_connect() is assumed. If no such link is found, the function will try to establish a link as if msql_connect() was called, and use it.

You are using the mysql_select_db value in the query, it is expecting the result from mysql_connect.

To simplify things, if you only make 1 connection to MySQL, then you can omit this parameter altogether.

[jayreis]

Ok so I am still having a prolem and looking for help.

I have a file that I use to make my two mysql connects to the two seperate databases

//first database connection
$nilogin = mysql_connect("localhost","xxxxx_dnziws1","tx35e2vy") or die("Error connecting: <br><br>".mysql_error());
$nidb = mysql_select_db("xxxxx_noizysysdb", $nilogin) or die("Error getting db: <br><br>".mysql_error());

//second database connection
$tplogin = mysql_connect("localhost","xxxxx_dnziws1","tx35e2vy") or die("Error connecting: <br><br>".mysql_error());
$tpdb = mysql_select_db("xxxxx_taskpad", $tplogin) or die("Error getting db: <br><br>".mysql_error());

I then have a page that has two seperate queries on the page that are quirying seperate databases. My issue is it seems that each query does not know what database to connect to. Even though I believe I am sepcificying what database to select in the query.

$myid ='201';
//query using first database connection
 $companysql = mysql_query("SELECT Usercompany_connections.Profileid, Usercompany_connections.Companyid, 
Userscompanyinfo.Companyname, Userscompanyinfo.Companyid 
from Usercompany_connections, Userscompanyinfo
 where Usercompany_connections.Profileid='$myid' AND
Usercompany_connections.Companyid = Userscompanyinfo.Companyid", $nidb);
while($companyrow = mysql_fetch_array($companysql)){
    $Companyid = $companyrow['Companyid'];
    $Companyname = $companyrow['Companyname'];

//query for using second database connection
$myprojects = mysql_query("SELECT * from Projects where CompanyID='$Companyid' AND 
		         Projectownedby='$myid' OR CompanyID='$Companyid' AND 
                           Privateproject='No'", $tpdb);
  $numprojects = mysql_num_rows($myprojects);
  echo "$numprojects";

The quries themselves work fine when I paste them directly into phpmyadamin so again my issue if I can not figure out why each query does not understand what database connection and table it is to be using? Suggestions

Make an order:

i) Establish a connection
ii) Execute the query
iii) close the connection

Again step (i). This is the easiest way to execute the queries.