Re: [PHP-DB] Question about databases and foreign keys
On Sep 15, 2008, at 2:12 PM, Micah Gersten wrote: You'll actually want to have the User Id in the clocking table, not the other way around. User Id is the foreign key because it has a many to one relationship with the time logging. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Yes, I agree with that. That was just a quick example that I whipped up (and obviously didn't think enough about). ;) So, follow Micah's advice, people. =D ~Philip Philip Thompson wrote: I'll throw this out there though. When dealing with foreign keys and multiple tables, remember to index appropriately. For example: Table `users`: user_id int(10) primary key -- other fields here -- clock_id int(10) Table `clocking`: clock_id int(10) primary key clock_in int(10) clock_out int(10) In table `clocking`, clock_id is already indexed because it's primary. Be sure to index clock_id in `users` so that when you join on the two, you'll have optimal speed! ALTER TABLE `users` ADD INDEX (`clock_id`); And an example query... SELECT `u`.`user_id`, `c`.`clock_in`, `c`.`clock_out` FROM `users` `u` INNER JOIN `clocking` `c` ON `u`.`clock_id` = `c`.`clock_id` WHERE (`u`.`user_id` = '$user_id' AND `c`.`clock_in` > '' AND `c`.`clock_out` < ''); Something along those lines. I always find it useful to have unique names throughout the database, so if you reference a name, you know where it originated. Because of this, I can just look at `users` and determine that `clock_id` is a foreign key. Hope that helps a little! ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php "innerHTML is a string. The DOM is not a string, it's a hierarchal object structure. Shoving a string into an object is impure and similar to wrapping a spaghetti noodle around an orange and calling it lunch." -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about databases and foreign keys
You'll actually want to have the User Id in the clocking table, not the other way around. User Id is the foreign key because it has a many to one relationship with the time logging. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Philip Thompson wrote: > > > I'll throw this out there though. When dealing with foreign keys and > multiple tables, remember to index appropriately. For example: > > Table `users`: > user_id int(10) primary key > -- other fields here -- > clock_id int(10) > > Table `clocking`: > clock_id int(10) primary key > clock_in int(10) > clock_out int(10) > > In table `clocking`, clock_id is already indexed because it's primary. > Be sure to index clock_id in `users` so that when you join on the two, > you'll have optimal speed! > > ALTER TABLE `users` ADD INDEX (`clock_id`); > > And an example query... > > SELECT `u`.`user_id`, `c`.`clock_in`, `c`.`clock_out` FROM `users` `u` > INNER JOIN `clocking` `c` ON `u`.`clock_id` = `c`.`clock_id` WHERE > (`u`.`user_id` = '$user_id' AND `c`.`clock_in` > '' AND > `c`.`clock_out` < ''); > > Something along those lines. I always find it useful to have unique > names throughout the database, so if you reference a name, you know > where it originated. Because of this, I can just look at `users` and > determine that `clock_id` is a foreign key. > > Hope that helps a little! > > ~Philip > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about databases and foreign keys
On Sep 15, 2008, at 10:59 AM, Micah Gersten wrote: Use 2 tables. You never know what the app might grow into and you should do it right the first time. That's what I was thinking too... Just wanted to hear it from someone else... NOW I get to learn about foreign keys and how to update things with them! So if anyone knows of a good resource I'd appreciate it... Until then, I'm off to search the web and figure this stuff out! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 11287 James St Holland, MI 49424 www.raoset.com [EMAIL PROTECTED] This for mysql only but it can give some info you may find useful. This is a very enlightening article for starters in order to see the sql part. http://articles.techrepublic.com.com/5100-10878_11-6035435.html The mysql manual could also give you an idea on that. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about databases and foreign keys
On Sep 15, 2008, at 10:03 AM, Jason Pruim wrote: On Sep 15, 2008, at 10:59 AM, Micah Gersten wrote: Use 2 tables. You never know what the app might grow into and you should do it right the first time. That's what I was thinking too... Just wanted to hear it from someone else... NOW I get to learn about foreign keys and how to update things with them! So if anyone knows of a good resource I'd appreciate it... Until then, I'm off to search the web and figure this stuff out! I'm sure Google will probably be your best resource for what you need to find out. ;) I'll throw this out there though. When dealing with foreign keys and multiple tables, remember to index appropriately. For example: Table `users`: user_id int(10) primary key -- other fields here -- clock_id int(10) Table `clocking`: clock_id int(10) primary key clock_in int(10) clock_out int(10) In table `clocking`, clock_id is already indexed because it's primary. Be sure to index clock_id in `users` so that when you join on the two, you'll have optimal speed! ALTER TABLE `users` ADD INDEX (`clock_id`); And an example query... SELECT `u`.`user_id`, `c`.`clock_in`, `c`.`clock_out` FROM `users` `u` INNER JOIN `clocking` `c` ON `u`.`clock_id` = `c`.`clock_id` WHERE (`u`.`user_id` = '$user_id' AND `c`.`clock_in` > '' AND `c`.`clock_out` < ''); Something along those lines. I always find it useful to have unique names throughout the database, so if you reference a name, you know where it originated. Because of this, I can just look at `users` and determine that `clock_id` is a foreign key. Hope that helps a little! ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about databases and foreign keys
On Sep 15, 2008, at 10:59 AM, Micah Gersten wrote: Use 2 tables. You never know what the app might grow into and you should do it right the first time. That's what I was thinking too... Just wanted to hear it from someone else... NOW I get to learn about foreign keys and how to update things with them! So if anyone knows of a good resource I'd appreciate it... Until then, I'm off to search the web and figure this stuff out! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 11287 James St Holland, MI 49424 www.raoset.com [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about databases and foreign keys
Use 2 tables. You never know what the app might grow into and you should do it right the first time. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Jason Pruim wrote: > Hi everyone, > > I just wanted to make sure that I am not making something more > complicated then it has to be. > > I am working on a time clock application to use at my company, and so > far, I have a login table, and with a foreign key that links to the > time table. The thinking being, that when someone logs in, they get > assigned there employee number, and then it adds a new record for that > employee ID. > > All I will be doing with the info, is grabbing the total amount of > time they were clocked in for each day, and then totaling for the week. > > I'm planning on using timestamps to store the actual time, hopefully > making the math part easier :) > > Do I need the foreign key and the 2 tables? Or should I just add the > column's into the database since it will be for a small company? > > Ideas? Suggestions? > > Thanks! > > > -- > > Jason Pruim > Raoset Inc. > Technology Manager > MQC Specialist > 11287 James St > Holland, MI 49424 > www.raoset.com > [EMAIL PROTECTED] > > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about access rights in php/mysql
On Aug 26, 2008, at 8:32 AM, Dan Shirah wrote:
Hi Everyone,
I am attempting to wrap my head around an issue and wanted to see if
I was thinking right.
I am attempting to setup a pURL site, one where they go to something
like: example.com/purl.php?purl=jason1234 and the site says "Welcome
Jason". I have that part of it working, and it's pulling the info
from the database just fine, what I'm wondering about is locking it
down a little bit more so that they can't just edit the info in the
main page, but they have to specifically hit a button to edit the
info.
Is it better to have 2 connections to the database with 2 separate
logins? One for the initial load which just has select privileges
and a second for when they hit edit which then gives them update
privileges?
Or, should i just do 1 login with select, and update privileges?
Any ideas or suggestions?
Thanks!
Jason,
I would assume you have some kind of login page, correct? Or can
anyone type in the direct URL and access someone else's page?
If they have already logged in, I would use some simple javascript
to enable/disable the editing of form objects. By setting all of the
form objects to "disabled" they will appear grayed out but the text
is still legible. Then when they click on your "Edit" button just
have a Javascript function set them all to enabled. You could do
something similar to show/hide div's.
Or, you could bypass all the javascript and just add an "edit" flag
to your page? So once they click on the Edit button/link it would
update a variable and you could do something like:
if ($edit =="Y") {
}
Add in the option to save only if Edit has been selected?
As for your database logins, I don't see a problem with doing it
either way. I personally prefer to use a single login, but doing a
include() for a second login is no big deal either.
Hi Dan,
Thanks for the info! the page will not actually require a login, it is
a new direct mail technique where they get a piece of mail with the
pURL on it, and then go there to "follow up" on the web.
I think though that I will go with the 1 login, with a simple variable
like to described.
Thanks Dan!
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
11287 James St
Holland, MI 49424
www.raoset.com
[EMAIL PROTECTED]
Re: [PHP-DB] Question about access rights in php/mysql
>
> Hi Everyone,
>
> I am attempting to wrap my head around an issue and wanted to see if I was
> thinking right.
>
> I am attempting to setup a pURL site, one where they go to something like:
> example.com/purl.php?purl=jason1234 and the site says "Welcome Jason". I
> have that part of it working, and it's pulling the info from the database
> just fine, what I'm wondering about is locking it down a little bit more so
> that they can't just edit the info in the main page, but they have to
> specifically hit a button to edit the info.
>
> Is it better to have 2 connections to the database with 2 separate logins?
> One for the initial load which just has select privileges and a second for
> when they hit edit which then gives them update privileges?
>
> Or, should i just do 1 login with select, and update privileges?
>
> Any ideas or suggestions?
>
> Thanks!
Jason,
I would assume you have some kind of login page, correct? Or can anyone type
in the direct URL and access someone else's page?
If they have already logged in, I would use some simple javascript to
enable/disable the editing of form objects. By setting all of the form
objects to "disabled" they will appear grayed out but the text is still
legible. Then when they click on your "Edit" button just have a Javascript
function set them all to enabled. You could do something similar to
show/hide div's.
Or, you could bypass all the javascript and just add an "edit" flag to your
page? So once they click on the Edit button/link it would update a variable
and you could do something like:
if ($edit =="Y") {
}
Add in the option to save only if Edit has been selected?
As for your database logins, I don't see a problem with doing it either way.
I personally prefer to use a single login, but doing a include() for a
second login is no big deal either.
RE: [PHP-DB] Question about database design
Nope, you got it
Bastien
> CC: php-db@lists.php.net> From: [EMAIL
PROTECTED]> Subject: Re: [PHP-DB] Question about database design> Date: Wed, 24
Oct 2007 15:18:05 -0400> To: [EMAIL PROTECTED]>> I think I understand what you
are saying here... On the main table just list the persons name and then on a
second table use a 1 to many relationship on a foreign key to link all the
addresses into the name? Or did I miss the mark? :)> On Oct 24, 2007, at 9:30
AM, Bastien Koert wrote:> I would approach this by having a main people table
(with a unique id of course) and then create a second addresses table which
uses the people Id key as the foreign key to this table...then you can have
multiple (more than two) addresses for those users, you could add a season in
the addresses to be able to pull the correct one based on date> bastien>> To:
php-db@lists.php.net>> From: [EMAIL PROTECTED]>> Date: Wed, 24 Oct 2007
09:06:29 -0400>> Subject: [PHP-DB] Question about database design>>>> I
sent this to the MySQL list but didn't receive any>> response, My apologies if
you have already received this.>>>>>> Hi Everyone,>>>> So having learned my
lesson with the last application, I am trying to>> plan out the addition of a
feature to my database application.>> Basically, some of my customers go south
for the winter ("Snow>> Birds") what I would like to do is have away of storing
both their>> addresses in the database, and have it so that the people>>
administering the list can choose between wether they are up north or>> down
south without having to erase the old address.>>>> For that I was thinking
creating a second table "SnowBirds" and list>> their southern addresses in
there and then when the list admin clicks>> on the edit button for their name,
it would also be able to pull up a>> list of the the addresses stored and
associated with that person.>>>> I'm also considering adding a date range for
the addresses so that if>> they know they'll be south from November to March it
will check the>> date and switch between the record accordingly BEFORE
exporting to>> excel.>>>> Now... I haven't really asked a question yet but gave
some background>> into what I want to do. So... Here's the question, does
anyone>> have any advice on the best way to do it? Am I right in thinking
that>> a second table is required? Would it be called a Relational database?>>
Or have I missed the terminology?>>>> Any help would be greatly
appreciated!>>>> Thanks for looking!>>>> ohhh... and in case it makes a
difference it's MySQL 5.* and I'll be>> writing the stuff to access that
database with php 5.>>>> -->>>> Jason Pruim>> Raoset Inc.>> Technology
Manager>> MQC Specialist>> 3251 132nd ave>> Holland, MI, 49424>>
www.raoset.com>> [EMAIL PROTECTED]>>>>> >
Express yourself with free Messenger emoticons. Get them today!> --> Jason
Pruim> Raoset Inc.> Technology Manager> MQC Specialist> 3251 132nd ave>
Holland, MI, 49424> www.raoset.com> [EMAIL PROTECTED]
_
R U Ready for Windows Live Messenger Beta 8.5? Try it today!
http://entertainment.sympatico.msn.ca/WindowsLiveMessenger
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about database design
A second address table is definitely the way to go (the '*' signifies the primary key): People Table *user_id first_name last_name etc Address Table (compound primary key) = *user_id (fk to People Table) *address_id *obs_no (you can skip this if you don't want to keep an address history) active_ind (is the row currently active or "deleted"?) effective_date expiry_date address_line_1 address_line_2 city etc So say a user lives in the north from Mar to Sept and in the South from Oct to Feb, your two rows would look like this: Row 1 = *john_doe (I prefer natural keys to surrogate) *north *1 Y 2007-03-01 2007-10-01 blah blah Row 2 = *john_doe *south *1 Y 2007-10-01 2007-03-01 blah blah If you want to keep a history of past addresses, just add a new row with an obs_no of 2 and set the active_ind to 'N' for the old row. All your queries will have to contain a where clause (active_ind = 'Y') to keep the old rows from showing up. I hope this helps. Tony On 10/24/07 7:30 AM, "Bastien Koert" <[EMAIL PROTECTED]> wrote: > > I would approach this by having a main people table (with a unique id of > course) and then create a second addresses table which uses the people Id key > as the foreign key to this table...then you can have multiple (more than two) > addresses for those users, you could add a season in the addresses to be able > to pull the correct one based on date -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about database design
I think I understand what you are saying here... On the main table
just list the persons name and then on a second table use a 1 to many
relationship on a foreign key to link all the addresses into the
name? Or did I miss the mark? :)
On Oct 24, 2007, at 9:30 AM, Bastien Koert wrote:
I would approach this by having a main people table (with a unique
id of course) and then create a second addresses table which uses
the people Id key as the foreign key to this table...then you can
have multiple (more than two) addresses for those users, you could
add a season in the addresses to be able to pull the correct one
based on date
bastien
> To: php-db@lists.php.net
> From: [EMAIL PROTECTED]
> Date: Wed, 24 Oct 2007 09:06:29 -0400
> Subject: [PHP-DB] Question about database design
>
> I sent this to the MySQL list but didn't receive any
> response, My apologies if you have already received this.
>
>
> Hi Everyone,
>
> So having learned my lesson with the last application, I am
trying to
> plan out the addition of a feature to my database application.
> Basically, some of my customers go south for the winter ("Snow
> Birds") what I would like to do is have away of storing both their
> addresses in the database, and have it so that the people
> administering the list can choose between wether they are up
north or
> down south without having to erase the old address.
>
> For that I was thinking creating a second table "SnowBirds" and list
> their southern addresses in there and then when the list admin
clicks
> on the edit button for their name, it would also be able to pull
up a
> list of the the addresses stored and associated with that person.
>
> I'm also considering adding a date range for the addresses so
that if
> they know they'll be south from November to March it will check the
> date and switch between the record accordingly BEFORE exporting to
> excel.
>
> Now... I haven't really asked a question yet but gave some
background
> into what I want to do. So... Here's the question, does anyone
> have any advice on the best way to do it? Am I right in thinking
that
> a second table is required? Would it be called a Relational
database?
> Or have I missed the terminology?
>
> Any help would be greatly appreciated!
>
> Thanks for looking!
>
> ohhh... and in case it makes a difference it's MySQL 5.* and I'll be
> writing the stuff to access that database with php 5.
>
> --
>
> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 3251 132nd ave
> Holland, MI, 49424
> www.raoset.com
> [EMAIL PROTECTED]
>
>
Express yourself with free Messenger emoticons. Get them today!
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
Re: [PHP-DB] Question about database design
Hi Tim,
Right now the customer I have has about 1,000 records which I know
isn't alot for MySQL to handle, but if people like the application we
could end up with 15 to 20 all having around 1,000 or more records
which would add up more on the database. Although right now I plan to
have a database per customer, easier to keep the info separate. I'm
just beginning with MySQL and PHP (Although I know and understand PHP
more then MySQL)
So just to double check I understand what you are saying, you would
set up a database with 2 tables, on the first one would be something
like name, address, city, state, zip, Primary Key. And then on the
second table it would have Name, address, city, state, zip, foreign
key? and the foreign key someone links the 2 records?
Like I said, I'm still learning MySQL :) Know of any good articles
that describe it for a beginner?
On Oct 24, 2007, at 9:28 AM, Tim McGeary wrote:
Hi Jason,
There are a couple ways you could do this. Yes, this would make it
a relational database.
If you go with a second table, you'll want to be sure to include
the UID from the main address table in the "SnowBirds" table so
that they are linked. The UID in the second table would actually
be a foreign key that links it to the main address table. It could
also act as the primary key of the "SnowBirds" table since it will
be unique. The other importance for this relationship of the UID
is that if you delete a person and their address from the main
table, you'll probably want to delete their seasonal address, too.
You may also want to have a binary column in your main address
table to indicate to your PHP script whether or not to look for
another address in the SnowBirds tables. I would do this so that
you aren't wasting an SQL query if it isn't needed. May not be a
big deal if you have limited tables, but as list of queries
increase, there is no need for extra queries.
You could add a start date and end date column the "SnowBirds"
database. Sounds like for your purposes that is a good idea.
If you I missed anything or you have more specific questions, feel
free to email me on or off list.
Cheers,
Tim
Tim McGeary '99, '06G
Senior Systems Specialist
Lehigh University
610-758-4998
[EMAIL PROTECTED]
Jason Pruim wrote:
I sent this to the MySQL list but didn't receive any
response, My apologies if you have already received this.
Hi Everyone,
So having learned my lesson with the last application, I am trying to
plan out the addition of a feature to my database application.
Basically, some of my customers go south for the winter ("Snow
Birds") what I would like to do is have away of storing both their
addresses in the database, and have it so that the people
administering the list can choose between wether they are up north or
down south without having to erase the old address.
For that I was thinking creating a second table "SnowBirds" and list
their southern addresses in there and then when the list admin clicks
on the edit button for their name, it would also be able to pull up a
list of the the addresses stored and associated with that person.
I'm also considering adding a date range for the addresses so that if
they know they'll be south from November to March it will check the
date and switch between the record accordingly BEFORE exporting to
excel.
Now... I haven't really asked a question yet but gave some background
into what I want to do. So... Here's the question, does anyone
have any advice on the best way to do it? Am I right in thinking that
a second table is required? Would it be called a Relational database?
Or have I missed the terminology?
Any help would be greatly appreciated!
Thanks for looking!
ohhh... and in case it makes a difference it's MySQL 5.* and I'll be
writing the stuff to access that database with php 5.
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Question about database design
I would approach this by having a main people table (with a unique id of
course) and then create a second addresses table which uses the people Id key
as the foreign key to this table...then you can have multiple (more than two)
addresses for those users, you could add a season in the addresses to be able
to pull the correct one based on date
bastien> To: php-db@lists.php.net> From: [EMAIL PROTECTED]> Date: Wed, 24 Oct
2007 09:06:29 -0400> Subject: [PHP-DB] Question about database design> >
I sent this to the MySQL list but didn't receive any > response, My
apologies if you have already received this.> > > Hi Everyone,> > So having
learned my lesson with the last application, I am trying to> plan out the
addition of a feature to my database application.> Basically, some of my
customers go south for the winter ("Snow> Birds") what I would like to do is
have away of storing both their> addresses in the database, and have it so that
the people> administering the list can choose between wether they are up north
or> down south without having to erase the old address.> > For that I was
thinking creating a second table "SnowBirds" and list> their southern addresses
in there and then when the list admin clicks> on the edit button for their
name, it would also be able to pull up a> list of the the addresses stored and
associated with that person.> > I'm also considering adding a date range for
the addresses so that if> they know they'll be south from November to March it
will check the> date and switch between the record accordingly BEFORE exporting
to> excel.> > Now... I haven't really asked a question yet but gave some
background> into what I want to do. So... Here's the question, does anyone>
have any advice on the best way to do it? Am I right in thinking that> a second
table is required? Would it be called a Relational database?> Or have I missed
the terminology?> > Any help would be greatly appreciated!> > Thanks for
looking!> > ohhh... and in case it makes a difference it's MySQL 5.* and I'll
be> writing the stuff to access that database with php 5.> > --> > Jason Pruim>
Raoset Inc.> Technology Manager> MQC Specialist> 3251 132nd ave> Holland, MI,
49424> www.raoset.com> [EMAIL PROTECTED]> >
_
Express yourself with free Messenger emoticons. Get them today!
http://www.freemessengeremoticons.ca/?icid=EMENCA122
Re: [PHP-DB] Question about database design
Hi Jason,
There are a couple ways you could do this. Yes, this would make it a
relational database.
If you go with a second table, you'll want to be sure to include the UID
from the main address table in the "SnowBirds" table so that they are
linked. The UID in the second table would actually be a foreign key
that links it to the main address table. It could also act as the
primary key of the "SnowBirds" table since it will be unique. The other
importance for this relationship of the UID is that if you delete a
person and their address from the main table, you'll probably want to
delete their seasonal address, too.
You may also want to have a binary column in your main address table to
indicate to your PHP script whether or not to look for another address
in the SnowBirds tables. I would do this so that you aren't wasting an
SQL query if it isn't needed. May not be a big deal if you have limited
tables, but as list of queries increase, there is no need for extra queries.
You could add a start date and end date column the "SnowBirds" database.
Sounds like for your purposes that is a good idea.
If you I missed anything or you have more specific questions, feel free
to email me on or off list.
Cheers,
Tim
Tim McGeary '99, '06G
Senior Systems Specialist
Lehigh University
610-758-4998
[EMAIL PROTECTED]
Jason Pruim wrote:
I sent this to the MySQL list but didn't receive any response,
My apologies if you have already received this.
Hi Everyone,
So having learned my lesson with the last application, I am trying to
plan out the addition of a feature to my database application.
Basically, some of my customers go south for the winter ("Snow
Birds") what I would like to do is have away of storing both their
addresses in the database, and have it so that the people
administering the list can choose between wether they are up north or
down south without having to erase the old address.
For that I was thinking creating a second table "SnowBirds" and list
their southern addresses in there and then when the list admin clicks
on the edit button for their name, it would also be able to pull up a
list of the the addresses stored and associated with that person.
I'm also considering adding a date range for the addresses so that if
they know they'll be south from November to March it will check the
date and switch between the record accordingly BEFORE exporting to
excel.
Now... I haven't really asked a question yet but gave some background
into what I want to do. So... Here's the question, does anyone
have any advice on the best way to do it? Am I right in thinking that
a second table is required? Would it be called a Relational database?
Or have I missed the terminology?
Any help would be greatly appreciated!
Thanks for looking!
ohhh... and in case it makes a difference it's MySQL 5.* and I'll be
writing the stuff to access that database with php 5.
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about Classes
Andrew Darby wrote:
Hello, all. I have a sort of theoretical/sort of dumb question about
PHP classes, which I haven't really worked with and don't entirely
understand the purpose of, so here goes:
Say i want to handle the add or update or delete of an item to a MySQL
db, and instead of having three functions (function addItem, function
updateItem, function deleteItem), it seems I could:
a) have one function (modifyItem) with some sort of attribute saying
which type it is, and then if/else through to the appropriate type of
operation based on this flag, i.e.,
modifyItem($array_of_data, 'update') chooses the update switch
b) have a modifyItem class, with the three possible functions inside
c) stick with my three original functions
Using any of these methods, how would you construct a query to actually
run? Do you need to pass in the fieldnames and the data? eg:
$data = array('newstitle' => $title, 'newscontent' => $data);
How about which table to place this data in?
It would get complicated I think..
I construct my queries elsewhere then hand it to my db class for
processing. It has some basic functions:
- Query (runs pg_query/mysql_query)
- Fetch (runs pg_fetch_assoc/mysql_fetch_assoc)
- Quote (runs pg_escape_string/mysql_escape_string)
and so on.
Is b) the sort of thing you might use a class for, or do I
misunderstand? If not, what would be some "classic" uses of classes
in our php/mysql world?
OOP is good for API's.
http://www.designmagick.com/article/18/PHP/Introduction-to-Object-Oriented-Programming
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Question about COUNT(*)
Thanks Rory, This query solves the problem: SELECT client.client_name, IFNULL( COUNT( sales.sale_id ) , 0 ) AS total FROM CLIENT LEFT JOIN sales ON client.client_id = sales.client_id GROUP BY client.client_name ORDER BY total DESC Also thanks to Nandar and Prabhu, but MySQL does not recognize '*=' operator. You guys is very helpful, thanks again :) Erick Wellem You could also try something like this: SELECT client.name, IFNULL(COUNT(sales.sale_id), 0) AS total FROM client LEFT JOIN sales ON client.client_id = sales.client_id GROUP BY client.name ORDER BY total DESC The left join forces every record in the left table of the join (client) to be present in the result set whether or not there are matching entries in the right table (sales). The IFNULL takes care fo your count problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about COUNT(*)
[EMAIL PROTECTED] wrote: > Hi, > > Let's say that I have 2 tables: client and sales > --- > | client_id | client_name | > --- > | 1 | John| > | 2 | Mark| > | 3 | Luke| > | 4 | Matthew | > --- > > - > | sale_id | client_id | sale_item | > - > | 1 | 1 | Book| > | 2 | 1 | Pencil | > | 3 | 1 | Pen | > | 4 | 2 | Ruler | > | 5 | 2 | Bag | > | 6 | 3 | Hat | > - > > How can I have total purchased items for each of the client like this table > below since COUNT(*) is only for non-NULL values? You could also try something like this: SELECT client.name, IFNULL(COUNT(sales.sale_id), 0) AS total FROM client LEFT JOIN sales ON client.client_id = sales.client_id GROUP BY client.name ORDER BY total DESC The left join forces every record in the left table of the join (client) to be present in the result set whether or not there are matching entries in the right table (sales). The IFNULL takes care fo your count problem. HTH Rory -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about COUNT(*)
try this SELECT client_name, COUNT(sale.client_id) as total FROM client, sale WHERE client.client_id *= sale.client_id GROUP BY client_name ORDER BY total DESC nandar - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Wednesday, July 06, 2005 1:11 PM Subject: [PHP-DB] Question about COUNT(*) > Hi, > > Let's say that I have 2 tables: client and sales > --- > | client_id | client_name | > --- > | 1 | John| > | 2 | Mark| > | 3 | Luke| > | 4 | Matthew | > --- > > - > | sale_id | client_id | sale_item | > - > | 1 | 1 | Book| > | 2 | 1 | Pencil | > | 3 | 1 | Pen | > | 4 | 2 | Ruler | > | 5 | 2 | Bag | > | 6 | 3 | Hat | > - > > How can I have total purchased items for each of the client like this table > below since COUNT(*) is only for non-NULL values? > --- > | client_name | total | > --- > | John| 3 | > | Mark| 2 | > | Luke| 1 | > | Matthew | 0 | > --- > > I've tried: SELECT client.name, COUNT(*) total > FROM client, sales > WHERE client.client_id = sales.client_id > GROUP BY client.client_name > ORDER BY total DESC > > and it returns: > --- > | client_name | total | > --- > | John| 3 | > | Mark| 2 | > | Luke| 1 | > --- > > How can I have Matthew who does't purchase anything on the list? > Your help is much appreciated. Thanks. > > > Erick Wellem > > -- > 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] Question about undices for inversed LIKE statesments.
The index should still work normally. >>Hello. >>I have a question about behaviour of indices in queries with inversed LIKE >>in MySQL. I mean something like this: >> >>"select foo from bar where '$some_string' LIKE some_field" >> >>where in the `some_field` I have a strings like 'symbols%' :) >> >>Do you have some ideas? >> >>WBR, Wicked -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] question about '
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] says...
> Hi there,
>
> The problem is the data is already in the DB, it was mass-added with the '
> already there, what I need is a way to retrieve the ' that is already in the
> DB.
>
> Thanks
>
> Chris
>
> > yes, by adding stripslashes it will somewhat escape those quotes ('),
> > but when displaying the results there will not be any need to do so.
> > unless it is your wish to actually store the backslash into the db.
> >
> >
> > Jerry wrote:
> >
> > >I think you should use addslashes to store the text in the database and
> > >then stripslashes when displaying the text. I think that should work,
> > >but I'm new to PHP so I might be wrong.
> > >
> > >Jerry
> > >
> > >-Original Message-
> > >From: Chris Payne [mailto:[EMAIL PROTECTED]
> > >Sent: Saturday, July 12, 2003 2:11 PM
> > >To: php
> > >Subject: [PHP-DB] question about '
> > >
> > >
> > >Hi there everyone,
> > >
> > >I am having alittle problem. I am calling some text that has a ' in it
> > >in the DB, but when it is being displayed on the screen the ' becomes a
> > >? any ideas? I'm displaying just with a and everything
> > >works great except that. I just read about addslashes and stripslashes
> > >etc . but not sure if that's relevant to this being that the data in
> > >the DB has a single ' already there.
> > >
> > >Anyway help would be appreciated :-)
Is it possible that this is one of the Microsoft Word 'smart quote' things
and you are displaying it in other than IE? You may need to track down the
ascii value for it/them and run a conversion on your data prior to
display.
I have found this happens where people copy/paste data from MS Word to
input forms.
--
Quod subigo farinam
$email =~ s/oz$/au/o;
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] question about '
Hi there,
The problem is the data is already in the DB, it was mass-added with the '
already there, what I need is a way to retrieve the ' that is already in the
DB.
Thanks
Chris
> yes, by adding stripslashes it will somewhat escape those quotes ('),
> but when displaying the results there will not be any need to do so.
> unless it is your wish to actually store the backslash into the db.
>
>
> Jerry wrote:
>
> >I think you should use addslashes to store the text in the database and
> >then stripslashes when displaying the text. I think that should work,
> >but I'm new to PHP so I might be wrong.
> >
> >Jerry
> >
> >-Original Message-
> >From: Chris Payne [mailto:[EMAIL PROTECTED]
> >Sent: Saturday, July 12, 2003 2:11 PM
> >To: php
> >Subject: [PHP-DB] question about '
> >
> >
> >Hi there everyone,
> >
> >I am having alittle problem. I am calling some text that has a ' in it
> >in the DB, but when it is being displayed on the screen the ' becomes a
> >? any ideas? I'm displaying just with a and everything
> >works great except that. I just read about addslashes and stripslashes
> >etc . but not sure if that's relevant to this being that the data in
> >the DB has a single ' already there.
> >
> >Anyway help would be appreciated :-)
> >
> >Regards
> >
> >Chris
> >
> >
> >
> >
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] question about '
yes, by adding stripslashes it will somewhat escape those quotes ('),
but when displaying the results there will not be any need to do so.
unless it is your wish to actually store the backslash into the db.
Jerry wrote:
I think you should use addslashes to store the text in the database and
then stripslashes when displaying the text. I think that should work,
but I'm new to PHP so I might be wrong.
Jerry
-Original Message-
From: Chris Payne [mailto:[EMAIL PROTECTED]
Sent: Saturday, July 12, 2003 2:11 PM
To: php
Subject: [PHP-DB] question about '
Hi there everyone,
I am having alittle problem. I am calling some text that has a ' in it
in the DB, but when it is being displayed on the screen the ' becomes a
? any ideas? I'm displaying just with a and everything
works great except that. I just read about addslashes and stripslashes
etc . but not sure if that's relevant to this being that the data in
the DB has a single ' already there.
Anyway help would be appreciated :-)
Regards
Chris
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] question about '
I think you should use addslashes to store the text in the database and then stripslashes when displaying the text. I think that should work, but I'm new to PHP so I might be wrong. Jerry -Original Message- From: Chris Payne [mailto:[EMAIL PROTECTED] Sent: Saturday, July 12, 2003 2:11 PM To: php Subject: [PHP-DB] question about ' Hi there everyone, I am having alittle problem. I am calling some text that has a ' in it in the DB, but when it is being displayed on the screen the ' becomes a ? any ideas? I'm displaying just with a and everything works great except that. I just read about addslashes and stripslashes etc . but not sure if that's relevant to this being that the data in the DB has a single ' already there. Anyway help would be appreciated :-) Regards Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about replacing large textfields
http://www.hotscripts.com/PHP/ One example: http://www.hotscripts.com/Detailed/9026.html Good luck, Doug On Fri, 14 Feb 2003 16:09:34 +0100, Ingen, Wart van wrote: >Hi there, > >I'm looking for a way to let people without any knowledge of coding replace >large fields of text and images on their website. >Is there anyone who knows some way to do this with MySQL and PHP ? Maybe >someone knows where I can find an online >tutorial about this? > >Thanks a bunch > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] question about access
If your client's friend wants to do learning/development, let him load PHPTriad, FoxServ, or one of the other trinity setups onto his computer. It is truly remarkable that you/your company would even consider such a request for longer than it takes to say: Never in a million years. Also, if I were running security at your site, I would closely monitor that client's activities on the system assuming that's how you are set up. That is a loose cannon out there. Doug On Mon, 10 Feb 2003 10:29:18 -0600, Terry Romine wrote: >I'd like to get some opinions from the list. > >We run php/mysql on our linux servers located behind a firewall. Many >of our clients have scripts that access their databases via php running >on the hosting server, and the general access is set up as: > > $hostname = "localhost"; > $database = "clientsDB"; > $username = "client"; > $password = ""; > > etc.. > >One of our clients has a friend who wants to do some php/mysql and has >asked for access to the database. We gave them the information above, >and he complains that "localhost" is insufficient. We think that if he >is requesting "servername.domain.net:accessPort" that that gives him >access through the firewall. Instead, he should upload his scripts >using ftp and use localhost, as all our other clients do. > >What is the general consensus? > >If giving an outsider this kind of access just asking for trouble? > >Terry -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about Load Data to mysql through php
On Tuesday 30 April 2002 16:56, Jack wrote: > Dear Jason > Thx for the reply, but do you know where i should copy that file to in > serverside? > is it got to be the same directory of mysql? It doesn't matter where as long as it is readable by the process running PHP (ie the process running your webserver). /tmp is usually writeable and readable by anyone so may try that first. However if you're on a shared-server and are worried about other's reading your data then you may copy into the root directory of your webspace. -- Jason Wong -> Gremlins Associates -> www.gremlins.com.hk Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * /* Satire is tragedy plus time. -- Lenny Bruce */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about Load Data to mysql through php
Dear Jason Thx for the reply, but do you know where i should copy that file to in serverside? is it got to be the same directory of mysql? Thx a lot! Jack [EMAIL PROTECTED] - Original Message - From: "Jason Wong" <[EMAIL PROTECTED]> Newsgroups: php.db To: <[EMAIL PROTECTED]> Sent: Tuesday, April 30, 2002 2:45 PM Subject: Re: [PHP-DB] Question about Load Data to mysql through php > On Tuesday 30 April 2002 14:33, Jack wrote: > > Dear all > > i'm a newbie in mysql, but there is one question about LOAD Data command in > > mysql, let say if i had a CSV file in client side and i want to load this > > file's data into particular table , so do i need to copy this CSV file into > > Web server side where the Mysql is or i will be able to load it from the > > client side? > > Yes, you would need a copy of CSV file on the server first. There are many > ways to get it there: > > 1) ftp > 2) scp > 3) write a simple upload page in php > etc. > > > Why i asking this, is i had a page that will load the csv file's data into > > mysql's table using php script, but when i execute this page from client > > side, it doesn't load the data into mysql, and there is no message saying > > anythin(as i don't know how to show the error message from mysql at php > > script!!). > > Someone has previously posted the required code in response to your earlier > post. > > -- > Jason Wong -> Gremlins Associates -> www.gremlins.com.hk > Open Source Software Systems Integrators > * Web Design & Hosting * Internet & Intranet Applications Development * > > > /* > Cohen's Law: > There is no bottom to worse. > */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about Load Data to mysql through php
On Tuesday 30 April 2002 14:33, Jack wrote: > Dear all > i'm a newbie in mysql, but there is one question about LOAD Data command in > mysql, let say if i had a CSV file in client side and i want to load this > file's data into particular table , so do i need to copy this CSV file into > Web server side where the Mysql is or i will be able to load it from the > client side? Yes, you would need a copy of CSV file on the server first. There are many ways to get it there: 1) ftp 2) scp 3) write a simple upload page in php etc. > Why i asking this, is i had a page that will load the csv file's data into > mysql's table using php script, but when i execute this page from client > side, it doesn't load the data into mysql, and there is no message saying > anythin(as i don't know how to show the error message from mysql at php > script!!). Someone has previously posted the required code in response to your earlier post. -- Jason Wong -> Gremlins Associates -> www.gremlins.com.hk Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * /* Cohen's Law: There is no bottom to worse. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Question about PHP Arrays syntax
> Hello, folks, and I apologize in advance if I break any rules here. This > is my first post on this list and I couldn't find any archive to review > existing posts for the answer to this question. http://www.php.net/mailing-lists.php Thanks for the URL. I feel stupid for having missed it, cause of course that is the page I used to sign up Maybe it was the late hour. > a) How would you change the following the automatically find the tables and > run through the iteration. Using mysql running the query: SHOW tables; Would return the names of the tables in the currently selected database. Okay, so I swapped $queryOfTableNames = "SELECT Name FROM $tableNamesTable"; for $queryOfTableNames = "SHOW tables"; Perfect. Worked like a charm. THANKS! >...the RTFM thing, ... I just haven't found the > answer yet. Does that mean you haven't RTFM so you haven't found the answer yet? No. Actually I have been doing a huge bit of reading. To clarify the RTFM thing, I have been absorbing a lot of info, but I just wasn't able to parse all the info well enough yet to recognize the answer when I saw it. > b) When I grab the values of the table names using my prepared table > tableNames, I couldn't figure out how to get the TEXT value of that record. > When I put in a debug statement, all I got was a value of "Array", rather > than a name of a table. It helps to RTFM. In any case the name of the function mysql_fetch_array() kind of gives the game away :) Yeah. I kind of slapped my forehead since I noticed the name AFTER the word "ARRAY appeared in my debug statements. Nice of PHP to output the type of an unsupported object in its functions. $value_of_field_0 = $line[0]; The perfect fit. Thanks. Remember the manual is your friend. If you haven't already done so, download a copy (it's available in various formats) and make use of it! Thanks Jason for your help! _ Vania Smrkovski Internet/Interactivity Consultant 865 450 9952 [EMAIL PROTECTED] http://mywebpages.comcast.net/smrkovski -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about PHP Arrays syntax
From: "Vania Smrkovski" <[EMAIL PROTECTED]>
> host with PHP and MySQL support.
>
> a) How would you change the following the automatically find the tables
and
> run through the iteration.
You should rtfm as php/mysql makes a lot of the torturous
VB/MS/Access methods very simple. All the functions below
are straight from the book.
");
print ("List of all databases/tables/fields on server");
$dbResult = mysql_list_dbs($link);
while($this_db = mysql_fetch_row($dbResult)) {
print(" ".$this_db[0]."");
$tblResult = mysql_list_tables ("$this_db[0]");
$i = 0;
while ($i < mysql_num_rows ($tblResult)) {
$this_tbl[$i] = mysql_tablename ($tblResult, $i);
echo " ".$this_tbl[$i] .
"";
$fld_Result = mysql_list_fields("$this_db[0]", "$this_tbl[$i]",
$link);
$nflds = mysql_num_fields($fld_Result);
echo " (".$nflds.")";
print("\n");
for($j = 0; $j < $nflds; $j++) {
print("
" . mysql_field_name($fld_Result, $j) .
"
" . mysql_field_type($fld_Result, $j) . "
" . mysql_field_len($fld_Result, $j) . "
" . mysql_field_flags($fld_Result, $j) . "
\n");
}
print("\n");
$i++;
}
}
mysql_close ($link);
?>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about PHP Arrays syntax
On Monday 22 April 2002 12:54, Vania Smrkovski wrote: > Hello, folks, and I apologize in advance if I break any rules here. This > is my first post on this list and I couldn't find any archive to review > existing posts for the answer to this question. http://www.php.net/mailing-lists.php > a) How would you change the following the automatically find the tables and > run through the iteration. Using mysql running the query: SHOW tables; Would return the names of the tables in the currently selected database. > I'm new at PHP, and I learn by doing, so > forgive me for not doing the RTFM thing, but I just haven't found the > answer yet. Does that mean you haven't RTFM so you haven't found the answer yet? > So instead I created a table called tableNames with one field, > which I personally populated with the names of all tables. > > How should I have done it so that wouldn't be necessary. See above. > b) When I grab the values of the table names using my prepared table > tableNames, I couldn't figure out how to get the TEXT value of that record. > When I put in a debug statement, all I got was a value of "Array", rather > than a name of a table. It helps to RTFM. In any case the name of the function mysql_fetch_array() kind of gives the game away :) > You'll see below that I hacked a work-around by > using a "foreach()" statement, which works flawlessly. That is common practice when dealing with arrays. > How can I grab a specific field of an array without using a foreach()? > while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) If you know the names of the fields, then you can access them directly by: $value_of_field = $line['name_of_field']; If you had used: while ($line = mysql_fetch_array($result, MYSQL_NUM)) Then you would have to reference the fields using: $value_of_field_0 = $line[0]; Remember the manual is your friend. If you haven't already done so, download a copy (it's available in various formats) and make use of it! -- Jason Wong -> Gremlins Associates -> www.gremlins.com.hk Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * /* I don't know half of you half as well as I should like; and I like less than half of you half as well as you deserve. -- J. R. R. Tolkien */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about setting up multiple relations
on 2/28/02 2:21 AM, Monty at [EMAIL PROTECTED] appended the following bits to my mbox: > I have an Image table in a MySQL database that will be used to store the > location of all images used on my site. Some images will relate to an > Article table (article.id) and some images may relate to a Member table > (member.id). > > As it's possible that there may be an article.id that is the same as a > member.id, I am considering adding two fields to the Image table (article_id > and member_id) one of which will be left blank for each record, depending on > the image's relation. For example, if "mypic.jpg" is related to article.id > 288 in the Article table, then I would insert 288 into the article_id field > in the Image table but leave the member_id field blank. Personally, I'd use translation or many to many relationship tables. You'd have five tables total: images, articles, articles_images, members, members_images The schema for the articles_images table would be: record_id (primary key sequence/auto-increment) article_id image_id If the RDBMS supports it, make a unique key on the combo of (article_id, image_id). If you're in an article, you can then get the images by doing a: SELECT col1,col2,col3 FROM articles_images AS ai LEFT JOIN images AS i ON ai.image_id=i.image_id WHERE article_id="CURRENT_ARTICLE_ID" This allows you to have one source of images and allows you to have multiple images used in an article or multiple articles using the same image. You could then also setup a members_images in the same way. Hope that helps. Paul -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about advanced SQL
Hi, I assumed that you was used MySQL for database. With MySQL you can used this SQL to find different row select tblcd.* from tblcd left join tblorder on tblcd.cdid=tblorder.cdid where tblorder.cdid is null sommai At 12:37 28/2/2002 +1000, Adam Royle wrote: >Hi. > >I need some guidance for writing an effective query (instead of processing >through PHP). > >I lets says I have two tables, tblCDs and tblOrders. > >tblCD > >cdID| cdTitle | cdArtist >-- >1 | Great Hits | Bon Jovi >2 | Forever Young| The Youngsters >3 | Now and Then | Beach Boys >4 | Cheesy Name | Two Tones > > >tblOrders > >orderID | cdID_FK | ordererID >-- >1 | 1| 442 >2 | 3| 233 >3 | 1| 233 > > >Now, I want to select all the records from tblCD where the cdID does not >appear in any row of tblOrders.cdID_FK > >This means that it selects all the CDs that have not been ordered. > >The results of the query should be > > >cdID| cdTitle | cdArtist >-- >2 | Forever Young| The Youngsters >4 | Cheesy Name | Two Tones > > >I know how I can do this in PHP (two queries - put all cdID_FK in an array, >and on displaying the other query, check if it is in the array, and display >if not) but there HAS to be an easier way. > >Adam. > > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php -- Please be informed that all e-mail which are addressing to thaithanakit.co.th will need to be changed to BTsecurities.com by March 1, 2002 Thank you. :-) -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Question about advanced SQL
select * from tblCd where cdId not in (select distinct cdId from tblOrders); On Thu, Feb 28, 2002 at 12:37:49PM +1000, Adam Royle wrote: > Hi. > > I need some guidance for writing an effective query (instead of processing > through PHP). > > I lets says I have two tables, tblCDs and tblOrders. > > tblCD > > cdID | cdTitle | cdArtist > -- > 1 | Great Hits | Bon Jovi > 2 | Forever Young| The Youngsters > 3 | Now and Then | Beach Boys > 4 | Cheesy Name | Two Tones > > > tblOrders > > orderID | cdID_FK | ordererID > -- > 1 | 1| 442 > 2 | 3| 233 > 3 | 1| 233 > > > Now, I want to select all the records from tblCD where the cdID does not > appear in any row of tblOrders.cdID_FK > > This means that it selects all the CDs that have not been ordered. > > The results of the query should be > > > cdID | cdTitle | cdArtist > -- > 2 | Forever Young| The Youngsters > 4 | Cheesy Name | Two Tones > > > I know how I can do this in PHP (two queries - put all cdID_FK in an array, > and on displaying the other query, check if it is in the array, and display > if not) but there HAS to be an easier way. > > Adam. > > > > -- > 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] Question about advanced SQL
SELECT tblCD.cdID, tblCD.cdTitle, tblCD.cdArtist FROM tblCD, tblOrders WHERE tblOrders.cdID = tblCD.cdID AND tblCD.cdID NOT IN ( SELECT cdID FROM tblOrders ) The support for this sort of "nested query" will very between RDBMSs but the statement above is most generic. Databases like postgres will not only support this but they will also have proprietary ways of achiveing the same thing more efficiently. Some databases also support LEFT JOIN, OUTER JOIN directives but support and implementation will vary. I havn't tested the above query but it should work :-) Although it probably isn't the most efficient. [TK] > -Original Message- > From: Adam Royle [mailto:[EMAIL PROTECTED]] > Sent: Thursday, 28 February 2002 1:38 PM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Question about advanced SQL > > > Hi. > > I need some guidance for writing an effective query (instead > of processing through PHP). > > I lets says I have two tables, tblCDs and tblOrders. > > tblCD > > cdID | cdTitle | cdArtist > -- > 1 | Great Hits | Bon Jovi > 2 | Forever Young| The Youngsters > 3 | Now and Then | Beach Boys > 4 | Cheesy Name | Two Tones > > > tblOrders > > orderID | cdID_FK | ordererID > -- > 1 | 1| 442 > 2 | 3| 233 > 3 | 1| 233 > > > Now, I want to select all the records from tblCD where the > cdID does not appear in any row of tblOrders.cdID_FK > > This means that it selects all the CDs that have not been ordered. > > The results of the query should be > > > cdID | cdTitle | cdArtist > -- > 2 | Forever Young| The Youngsters > 4 | Cheesy Name | Two Tones > > > I know how I can do this in PHP (two queries - put all > cdID_FK in an array, and on displaying the other query, check > if it is in the array, and display if not) but there HAS to > be an easier way. > > Adam. > > > > -- > 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] Question about strlen .. I think
You could split the string on the slashes with explode('/',$string) and
then manipulate the array elements you get back.
Good luck,
Ben
-Original Message-
From: Dennis Kaandorp [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 15, 2001 9:21 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Question about strlen .. I think
Hello,
On my site users can submit ftp's.
Is there a way to replace the spaces between the paths?
This is what I mean:
/uploads//by/ /dennis/
must become
/uploads/<4sp>/by/<3sp>dennis/
Thnx,
Dennis
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] question about OCIBindByName(add some comment)
On Tue, May 22, 2001 at 09:41:25AM +0900, Michael Cheung wrote: > > On Mon, 21 May 2001 16:39:05 +0200 > "Thies C. Arntzen" <[EMAIL PROTECTED]> wrote: > > > On Mon, May 21, 2001 at 11:35:36PM +0900, Michael Cheung wrote: > > > Hi; > > > Linux 2.2.18 + oracle 8.1.7i + php-4.0.5 + oci8 interface. > > > > > > I use the following lines to get a result from database; > > > But It give me result 0, it is incorrect; > > > > > > $sql="Select count(*) From member where email=:email"; > > > $sth=OCIParse($dbh,$sql); > > > OCIBindByName($sth,"email",$email,64); > > > // with or without ':' is same, and use reference or not is also same here. > > > OCIExecute($sth); > > > OCIFetchInto($sth,$data); > > > > have you set $email in your script (before OCIExecute)? > > I have set the $email before OCIParse(). > > Is there any problem about the varchar2 data type in bind operation? it works for me - could you send me a _short_ but complete testcase that reproduces your problem? tc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] question about OCIBindByName(add some comment)
On Mon, May 21, 2001 at 11:35:36PM +0900, Michael Cheung wrote: > Hi; > Linux 2.2.18 + oracle 8.1.7i + php-4.0.5 + oci8 interface. > > I use the following lines to get a result from database; > But It give me result 0, it is incorrect; > > $sql="Select count(*) From member where email=:email"; > $sth=OCIParse($dbh,$sql); > OCIBindByName($sth,"email",$email,64); > // with or without ':' is same, and use reference or not is also same here. > OCIExecute($sth); > OCIFetchInto($sth,$data); have you set $email in your script (before OCIExecute)? tc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Question about using php with Microsoft SQL server
> Hello, I'm trying to use the mssql_connect function to connect to the > Microsoft SQL server with php4.0.3pl1 for window, but the return is "Fatal > error: Call to undefined function: mssql_connect()". And another mssql > function are also with the same result, but I have try use the mysql and > odbc functions to connection with database is work, so could you help me > tell me what the reason and/or how to slove this problem. Waiting for your > reply, thanks you. > ms-SQL support is not abilitated by default in PHP-Win32. First, make sure you have a mssql.dll (or something similar, name may change reflecting the version of MS-Sql for which the dll is built) in the directory where PHP extensions are saved. If you find it, then you have to go to php.ini and uncomment the relative row in the "extensions" section. You may have to set the row "extensions path" too. If your PHP-win32 distro does not comprehend the required dll, try the one built by the folks at www.php4win.de that's quite complete. HTH, bye /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
