[PHP-DB] how to get records from one table that are not in another table
hi all, can anyone pls help me out to make the query like.. if two tables have some comman records then how to find records from one table that are not in another table. eg... i have one table room type master (room_type_m) fields : room_type_id, room_type_name and other details and another table hotel room type relation (hotel_rtype_r) fields : hotel_id and room_type_id and some other fields this table will contain multiple room_type_ids for one hotel_id i want to print those room_type_id (from room_type_m) that are not present in relation table ie .(hotel_rtype_r) regards, Smita _ It's all happening @ F1. Feel the thrill! http://server1.msn.co.in/sp03/formula2003/index.asp Race along right here! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Session
I have just recently entered the world of PHP and MySQL. I'm used to code in ASP with Access databases and since I am not very familiar with PHP, I have run into a problem, which is probably quite easy to solve: Connecting to the database requires a couple lines of code, which I would rather not have to write on every single page, which needs the database connection. ($link = mysql_connect(host, username, password) or die(Could not connect : . mysql_error()); mysql_select_db(dbname) or die(Could not select database.);) In ASP this is easy to solve, since I would just create a global.asa and on the Application_OnStart procedure, I would create the global database connection and then reference to that variable on each of the individual pages. Now, in PHP I do not seem to be able to create a global.asa or anything similar. It does not even seem to have an Application object, which I can refer to on any page. Can anyone tell me how I can connect to the MySQL database from some kind of a global page, being activated no matter which page the user starts on and where I only need to write the different SQL statements in the individual pages. (In ASP I could do the following on the individual page: Set RS = Application(conn).Execute(SELECT * FROM database) Thank you. Morten -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Session
Hi
welcome to php :)
in general you can use an include at the beginning of each page
?php
include('database_conn.php' ) ;
$query = 'select * from table'
$result = ($query, $link)
?
-Original Message-
From: Morten Twellmann [mailto:[EMAIL PROTECTED]
Sent: 27 July 2003 13:01
To: [EMAIL PROTECTED]
Subject: [PHP-DB] MySQL Session
I have just recently entered the world of PHP and MySQL. I'm used to code in
ASP with Access databases and since I am not very familiar with PHP, I have
run into a problem, which is probably quite easy to solve:
Connecting to the database requires a couple lines of code, which I would
rather not have to write on every single page, which needs the database
connection.
($link = mysql_connect(host, username, password)
or die(Could not connect : . mysql_error());
mysql_select_db(dbname) or die(Could not select database.);)
In ASP this is easy to solve, since I would just create a global.asa and
on the Application_OnStart procedure, I would create the global database
connection and then reference to that variable on each of the individual
pages.
Now, in PHP I do not seem to be able to create a global.asa or anything
similar. It does not even seem to have an Application object, which I can
refer to on any page.
Can anyone tell me how I can connect to the MySQL database from some kind of
a global page, being activated no matter which page the user starts on and
where I only need to write the different SQL statements in the individual
pages.
(In ASP I could do the following on the individual page:
Set RS = Application(conn).Execute(SELECT * FROM database)
Thank you.
Morten
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Session
Here's how I do it using functional PHP programming (you may prefer using OO
PHP which I don't bother with) :
I include a generic functions file in each page. You could take it further,
opening and closing the database connection for every query, but this way is
more efficient if more than open query is run against the database on each
page.
create functions.php which contains the following:
-
?php
$result;
$link;
function open_db()
{
global $link;
$link = mysql_connect(localhost, user_name, password) or die(Could
not connect);
mysql_select_db(database_name) or die(pCould not select
database./p);
}
function do_query($query)
{
global $result;
$result = mysql_query($query)
or die(pQuery failed./p
.pMySQL said .mysql_error()./p
.pquery=.$query./p);
}
function close_db()
{
global $result;
global $link;
/* Free resultset */
mysql_free_result($result);
/* Closing connection */
mysql_close($link);
}
?
Then use functions.php in each web page you code
-
?php
include_once(functions.php);
open_db();
do_query(SELECT name FROM FISH; );
$num_rows = mysql_num_rows($result);
if( $num_rows == 0 ) {
// handle no rows returned here
}
while ( $row = mysql_fetch_array($result) ) {
// do stuff here. like using $row[name];
}
// run more queries if you like here
close_db();
?
- Original Message -
From: Morten Twellmann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, July 27, 2003 1:01 PM
Subject: [PHP-DB] MySQL Session
I have just recently entered the world of PHP and MySQL. I'm used to code
in
ASP with Access databases and since I am not very familiar with PHP, I
have
run into a problem, which is probably quite easy to solve:
Connecting to the database requires a couple lines of code, which I would
rather not have to write on every single page, which needs the database
connection.
($link = mysql_connect(host, username, password)
or die(Could not connect : . mysql_error());
mysql_select_db(dbname) or die(Could not select database.);)
In ASP this is easy to solve, since I would just create a global.asa and
on the Application_OnStart procedure, I would create the global database
connection and then reference to that variable on each of the individual
pages.
Now, in PHP I do not seem to be able to create a global.asa or anything
similar. It does not even seem to have an Application object, which I
can
refer to on any page.
Can anyone tell me how I can connect to the MySQL database from some kind
of
a global page, being activated no matter which page the user starts on and
where I only need to write the different SQL statements in the individual
pages.
(In ASP I could do the following on the individual page:
Set RS = Application(conn).Execute(SELECT * FROM database)
Thank you.
Morten
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how to get records from one table that are not in anothertable
Smita Manohar wrote: can anyone pls help me out to make the query like.. if two tables have some comman records then how to find records from one table that are not in another table. eg... i have one table room type master (room_type_m) fields : room_type_id, room_type_name and other details and another table hotel room type relation (hotel_rtype_r) fields : hotel_id and room_type_id and some other fields this table will contain multiple room_type_ids for one hotel_id i want to print those room_type_id (from room_type_m) that are not present in relation table ie .(hotel_rtype_r) You need a LEFT JOIN SELECT r.room_type_id FROM room_type_m r LEFT JOIN hotel_rtype_r h ON r.room_type_id = h.room_type_id WHERE h.room_type_id IS NULL I'd suggest you pick up an SQL book or do some more reading if you don't understand that. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] how to get records from one table that are not in another table
I'd suggest you pick up an SQL book or do some more reading if you don't understand that. -- ---John Holmes... John, Do you have a good book recommendation for MySQL.. I'm looking for more knowledge on improving my queries, etc. LEFT JOINS were completely unfamiliar (still are) until you helped me with my 3 query into 1 problem I had last week. Any thoughts? Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A magazine for PHP Professionals - www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how to get records from one table that are not in anothertable
Aaron Wolski wrote: Do you have a good book recommendation for MySQL.. I'm looking for more knowledge on improving my queries, etc. LEFT JOINS were completely unfamiliar (still are) until you helped me with my 3 query into 1 problem I had last week. I've heard that MySQL, Second Edition by Paul Dubois is one of the best books. I just got a copy of Joe Celko's SQL for Smarties: Advanced SQL Programming and it looks to be a great book also. Thank you to all of you who have bought me a book. I really need the GRE study guide if anyone is willing... heh... :) -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how to get records from one table that are not in another table
thanks John, i was knowing that LEFT JOIN would work, but i was not knowing exact syntax where to put IS NULL. anyway, thanks ! still problem is not sloved :-( as u suggested following query worked fine SELECT A.room_type_id, room_type_desc FROM room_type_m A LEFT JOIN hotel_room_type_d B ON A.room_type_id = B.room_type_id WHERE B.room_type_id IS NULL but there is one more criteria, hotel_id from table hotel_room_type_d should be equal to some value i modified it like this... SELECT A.room_type_id, room_type_desc FROM room_type_m A LEFT JOIN hotel_room_type_d B ON A.room_type_id = B.room_type_id WHERE B.room_type_id IS NULL AND B.hotel_id = '$hotel_id' is it necessary to select hotel id also, ie. SELECT A.room_type_id, B.hotel_id FROM room_type_m A LEFT JOIN hotel_room_type_d B. there might be some syntax mistake... can u pls correct it?? it would be great help for me. but one more question ... follwoing query didnt work... SELECT A.room_type_id FROM room_type_m A WHERE A.room_type_id NOT IN(select B.room_type_id from hotel_room_type_d B) im not sure about syntax... Smita From: John W. Holmes [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Smita Manohar [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [PHP-DB] how to get records from one table that are not in another table Date: Sun, 27 Jul 2003 09:44:04 -0400 Smita Manohar wrote: can anyone pls help me out to make the query like.. if two tables have some comman records then how to find records from one table that are not in another table. eg... i have one table room type master (room_type_m) fields : room_type_id, room_type_name and other details and another table hotel room type relation (hotel_rtype_r) fields : hotel_id and room_type_id and some other fields this table will contain multiple room_type_ids for one hotel_id i want to print those room_type_id (from room_type_m) that are not present in relation table ie .(hotel_rtype_r) You need a LEFT JOIN SELECT r.room_type_id FROM room_type_m r LEFT JOIN hotel_rtype_r h ON r.room_type_id = h.room_type_id WHERE h.room_type_id IS NULL I'd suggest you pick up an SQL book or do some more reading if you don't understand that. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A magazine for PHP Professionals www.phparch.com _ They are beautiful. They are in danger. http://server1.msn.co.in/Slideshow/BeautyoftheBeast/index.asp Our four-legged friends. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how to get records from one table that are not in anothertable
Smita Manohar wrote: but there is one more criteria, hotel_id from table hotel_room_type_d should be equal to some value i modified it like this... SELECT A.room_type_id, room_type_desc FROM room_type_m A LEFT JOIN hotel_room_type_d B ON A.room_type_id = B.room_type_id WHERE B.room_type_id IS NULL AND B.hotel_id = '$hotel_id' is it necessary to select hotel id also, ie. SELECT A.room_type_id, B.hotel_id FROM room_type_m A LEFT JOIN hotel_room_type_d B. No, you don't have to select that column. Only if you need it. but one more question ... follwoing query didnt work... SELECT A.room_type_id FROM room_type_m A WHERE A.room_type_id NOT IN(select B.room_type_id from hotel_room_type_d B) im not sure about syntax... This would work if the DB system you're using supported subqueries. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] need help with repeated regions
Plz could some one help me how to create an horizontal repeated region in a php page using a mysql database for showing thumbnail without using a expensive plugin such like MXlooper for dreamweaver MX. plz sent me help over mail [EMAIL PROTECTED] thx -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Your E-Book
The Revolutionary Book Your Boss Doesn't Want You to Read ! Does an extra $500 to $1000 a week sound exciting? HINT: It's not what you think Dear Friend: Everybody is looking to make more money online, but the biggest problem is that VERY few things actually work. We have an exclusive system for making money from home and it only requires a few hours a week of your free time. Best is. You work once, get paid forever .. A recent revolutionary discovery has allowed many average people the ability to create another source of income in their life. For a limited time only, you can receive a FREE copy of your own personal 'hot off the presses' e-book, How to Achieve Unlimited Freedom: The Confidential Discovery of the Century by Dr. Tim Jaeger valued at $19.95 !! Click Below to receive it absolutely FREE ! Click Here NOTE - This is NOT SPAM - This message is sent in compliance of the new e-mail bill: SECTION 301. Per Section 301, paragraph (a)(2)(C) of S. 1618. This message is NOT Spam as long as you are provided with a way to remove your name from this mailing list. All further transmissions to you from me may be stopped at no cost to you by e-mailing [EMAIL PROTECTED] from your e-mail address with the word REMOVE in the subject line. This e-mail must be from or contain the address you wish to have removed.
Re: [PHP-DB] how to get records from one table that are not in another table
heyy... i put second condition (after AND) in the wrong place.. it should be after LEFT JOIN ON not after WHERE... following is the correct query... SELECT A.room_type_id, room_type_desc FROM room_type_m A LEFT JOIN hotel_room_type_d B ON A.room_type_id = B.room_type_id AND B.hotel_id = '$hotel_id' WHERE B.room_type_id IS NULL previously i was doing SELECT A.room_type_id, room_type_desc FROM room_type_m A LEFT JOIN hotel_room_type_d B ON A.room_type_id = B.room_type_id WHERE B.room_type_id IS NULL AND B.hotel_id = '$hotel_id' with this query it was only printing messege as,query executed successfully in phpmyadmin but not printing the result. anyway, now my problem is solved.. thanks ! - smita _ Cool new emoticons. Lots of colour! http://server1.msn.co.in/sp03/messengerpromo/index.asp On MSN Messenger V6.0 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
