Oh the pain

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

Oh the pain

Post by Deddog »

Is there anything that jumps out about the below php querry? I've update to mysql and php 4 so subquerries should be allowed.

Is it just crap or has my God forsaken me :oops:

//-------------------------------------------------------------------------------------------------------------
//
ConnectMeetingRoom();
$connection = mysql_connect("172.29.34.104", "intranet", "intranet");
$sqlAMOff = "SELECT rooms.* FROM rooms WHERE rooms.Available = 'Yes' and rooms.RoomsId LIKE
(SELECT contactdetails.Office FROM contactdetails LEFT JOIN absent ON contactdetails.Contact_Id=absent.User_Id and
(absent.Start_Date >='$TempDate' and absent.End_Date <='$TempDate')
WHERE absent.User_Id IS NULL,$connection)";
$resultAMOff = mysql_query($sqlAMOff);
//
//---------------------------------------------------------------------------------------------------------------
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

Try this.

And you don't hava a database selected.

Code: Select all

$connection = mysql_connect("172.29.34.104", "intranet", "intranet"); 
$dbp = mysql_select_db("database_you_use");
$sqlAMOff = "SELECT rooms.* FROM rooms WHERE rooms.Available = 'Yes' and rooms.RoomsId LIKE 
(SELECT contactdetails.Office FROM contactdetails LEFT JOIN absent ON contactdetails.Contact_Id=absent.User_Id and 
(absent.Start_Date >='$TempDate' and absent.End_Date <='$TempDate') 
WHERE absent.User_Id IS NULL)"; 
if(!$resultAMOff = mysql_query($sqlAMOff))&#123;
  echo mysql_error();
  echo $sqlAMOff;
&#125;
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

ah ha

Post by Deddog »

Well spotted! Doh!

There in lies my problem

ConnectMeetingRoom(); is a function that deal with connecting to one database.

Unfortunatly the second select statment is based in another databse. So i in fact have to open a connection to two seperate database's. Hence my attempt at the $connection variable. I just forgot about the Db select bit.

Can i connect to two DB in the same statement?
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

Yes this can be done.

use: database.table.cell
You can refer to a table as tbl_name (within the current database), or as dbname.tbl_name to explicitly specify a database. You can refer to a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a SELECT statement unless the reference would be ambiguous. See section 6.1.2 Database, Table, Index, Column, and Alias Names, for examples of ambiguity that require the more explicit column reference forms.
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

Post by Deddog »

oooooo.

Tested the two select statement indivdualy and they work. put them together and i get the below error meassge which relates to :

if(mysql_num_rows($resultAMOff)>=1):
while ($rowOff = mysql_fetch_array($resultAMOff)) {
//
//
"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\sites\intranet\htdocs\new03\site\Meeting_Centre\ListRooms.php on line 131"
//
//

So it looks like the subquerry is at fault, any ideas?

Is there a command that bring up the version of mysql and php?

pretty sure they are version4. have I missed some sort of configuration?

New attempt at code below.
//
$connection = mysql_connect("172.29.34.104", "intranet", "intranet");
$sqlAMOff = "SELECT rooms.* FROM MeetingRoom.rooms WHERE rooms.Available = 'Yes' and rooms.RoomsId LIKE
(SELECT contactDetails.Office FROM contacts.contactDetails LEFT JOIN contacts.absent ON contactDetails.Contact_Id=absent.User_Id and (absent.Start_Date >='$TempDate' and absent.End_Date <='$TempDate')
WHERE absent.User_Id IS NULL)";
$resultAMOff = mysql_query($sqlAMOff,$connection);
//---------------------------------------------------------------------------------------------------------------
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

Can you change
$resultAMOff = mysql_query($sqlAMOff);
to
if(!$resultAMOff = mysql_query($sqlAMOff)){
echo mysql_error();
echo $sqlAMOff;
exit;
}

This wil give the error that mysql gives.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Add some error reporting to see what MySQL says about the error:

Code: Select all

$resultAMOff = mysql_query($sqlAMOff,$connection) or die(mysql_error().'<p>'.$sqlAMOff.'</p>');
To find out your versions of PHP and MySQL just run a script with:

Code: Select all

<?php phpinfo(); ?>
in it and see what it says for both of them.

Mac
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

wierd

Post by Deddog »

I have just down loaded and installed Mysql 4.1 beta as this supports "Subselect". I now get the below message.

Subselect returns more than 1 record

SELECT rooms.* FROM MeetingRoom.rooms WHERE rooms.Available = 'Yes' and rooms.RoomsId LIKE (SELECT contactDetails.Office FROM contacts.contactDetails LEFT JOIN contacts.absent ON contactDetails.Contact_Id=absent.User_Id and (absent.Start_Date >='2003-07-04' and absent.End_Date <='2003-07-04') WHERE absent.User_Id IS NULL)
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

and

Post by Deddog »

and the phpinfo tell me that i'm runiing php 4.2.3

and that I have a mysql api client version 3.23.39 ?????

Doesn't seem to mention what version of Mysql is running
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Re: and

Post by twigletmac »

Deddog wrote:and the phpinfo tell me that i'm runiing php 4.2.3

and that I have a mysql api client version 3.23.39 ?????

Doesn't seem to mention what version of Mysql is running
My bad, I thought it did give you MySQL version information - try running this SELECT for that info:

Code: Select all

SELECT VERSION()
Deddog wrote:I now get the below message.

Subselect returns more than 1 record
If the subselect is returning more than one record then you can't compare rooms.RoomsID to it using LIKE - perhaps you need to use IN()?
http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

Mac
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

Ahh the proud farther

Post by Deddog »

twigletmac , InTeR

Thank you both, very much. A little bit of tweaking the second select and changing LIKE for IN did the job.

Below is the finished baby.

//
$connection = mysql_connect("172.29.34.104", "intranet", "intranet");
//
$sqlAMOff = "SELECT rooms.* FROM MeetingRoom.rooms WHERE rooms.Available = 'Yes' and rooms.RoomsId IN
(SELECT contactdetails.Office FROM contacts.contactdetails LEFT JOIN contacts.absent ON contactdetails.Contact_Id = absent.User_id
and contactdetails.Office IS NOT NULL
and (absent.Start_Date >='$TempDate' and absent.End_Date <='$TempDate')
WHERE absent.User_id IS NOT NULL)";
//
$resultAMOff = mysql_query($sqlAMOff,$connection) or die(mysql_error().'<p>'.$sqlAMOff.'</p>');
//
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

You'r welcome.
Post Reply