Re: [PHP-DB] Building WHERE SQL clauses

[landavia]

how about type like this

SELECT * FROM chico c, harpo h WHERE c.operator = "Bill" OR c.operator = 
"Jessica"




Mike Sullivan wrote:
Hello all.  I'm using PHP to build a query for a database that consists of 
multiple tables, all with identical attribues.  A typical syntax try looks 
like this:  SELECT * FROM chico, harpo WHERE operator = "Bill" OR operator = 
"Jessica"


MySQL responds with this:  Couldn't execute query.Column 'operator' in where 
clause is ambiguous


I was hoping that since the tables are identical all I would need to do is 
list the attribute values not have to append them to the table names.  Is 
there any way to do this?  Perhaps with a setting in MySQL or a different 
syntax (JOIN, UNION, ...)?  If not are there available some canned code 
snippets that build these types of strings from values passed in the $_POST 
array.  Thanks for any insights on this. --- Mike 




  



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Building WHERE SQL clauses

[Bastien Koert]

On Mon, Sep 15, 2008 at 1:33 PM, Stephen Wellington <
[EMAIL PROTECTED]> wrote:

> You probably want something like this:
>
> SELECT * FROM chico as c, harpo as h WHERE c.operator = "Bill" OR
> c.operator =
> "Jessica" OR h.operator = "Bill" OR h.operator ="Jessica"
>
> However if those tables really are identical I would suggest having a
> good look at your
> database design to see if it can be normalised or something...
>
> Stephen Wellington
>
> On Mon, Sep 15, 2008 at 4:06 PM, Mike Sullivan <[EMAIL PROTECTED]> wrote:
> > Hello all.  I'm using PHP to build a query for a database that consists
> of
> > multiple tables, all with identical attribues.  A typical syntax try
> looks
> > like this:  SELECT * FROM chico, harpo WHERE operator = "Bill" OR
> operator =
> > "Jessica"
> >
> > MySQL responds with this:  Couldn't execute query.Column 'operator' in
> where
> > clause is ambiguous
> >
> > I was hoping that since the tables are identical all I would need to do
> is
> > list the attribute values not have to append them to the table names.  Is
> > there any way to do this?  Perhaps with a setting in MySQL or a different
> > syntax (JOIN, UNION, ...)?  If not are there available some canned code
> > snippets that build these types of strings from values passed in the
> $_POST
> > array.  Thanks for any insights on this. --- Mike
> >
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
> if the structures and fields are truly the same you can UNION the queries

select * from chico where operator in('Jessica','William')
union
select * from harpo where operator in('Jessica','William')

But as suggested, if they are truly similar, the db needs to be looked for
design

-- 

Bastien

Cat, the other other white meat

Re: [PHP-DB] Question about databases and foreign keys

[Philip Thompson]

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] myphpadmin mcrypt and libmcrypt

[Marc Fromm]

I'm finding some stuff, but since I cannot use yum with these rpms I don't know 
if I am getting all the dependencies correct and I am too new at this to know 
what all I need.

Example of libmcrypt
http://ftp.freshrpms.net/pub/freshrpms/redhat/testing/EL5/oracle/i386/


From: Yakir Magriso [mailto:[EMAIL PROTECTED]
Sent: Monday, September 15, 2008 1:45 PM
To: Marc Fromm
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] myphpadmin mcrypt and libmcrypt

Hi.

Did you try looking for a "php-mcrypt" RPM package?


Regards,
Yakir Magriso.
On Mon, Sep 15, 2008 at 11:30 PM, Marc Fromm <[EMAIL PROTECTED]> wrote:

I am configuring a Red Hat EL5 system.
I need to install the packages mcrypt and libmcrypt for the myPHPadmin tool to 
access the MySQL databases.
I recently learned that Red Hat does not provide packages at their repository 
to install third party packages like mcrypt and libmcrypt.

The mcrypt setup page at php.net states to go to my distros 
site to get the files, since Red Hat doesn't have them, how can I get the 
packages and use yum to install them so that all the dependencies are taken 
care of?

Thanks


Marc

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] myphpadmin mcrypt and libmcrypt

[Yakir Magriso]

Hi.

Did you try looking for a "php-mcrypt" RPM package?


Regards,
Yakir Magriso.

On Mon, Sep 15, 2008 at 11:30 PM, Marc Fromm <[EMAIL PROTECTED]> wrote:

>
> I am configuring a Red Hat EL5 system.
> I need to install the packages mcrypt and libmcrypt for the myPHPadmin tool
> to access the MySQL databases.
> I recently learned that Red Hat does not provide packages at their
> repository to install third party packages like mcrypt and libmcrypt.
>
> The mcrypt setup page at php.net states to go to my distros site to get
> the files, since Red Hat doesn't have them, how can I get the packages and
> use yum to install them so that all the dependencies are taken care of?
>
> Thanks
>
>
> Marc
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

[PHP-DB] myphpadmin mcrypt and libmcrypt

[Marc Fromm]

I am configuring a Red Hat EL5 system.
I need to install the packages mcrypt and libmcrypt for the myPHPadmin tool to 
access the MySQL databases.
I recently learned that Red Hat does not provide packages at their repository 
to install third party packages like mcrypt and libmcrypt.

The mcrypt setup page at php.net states to go to my distros site to get the 
files, since Red Hat doesn't have them, how can I get the packages and use yum 
to install them so that all the dependencies are taken care of?

Thanks


Marc

--
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

[Micah Gersten]

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

[PHP-DB] PgCon.US: West registration now open

[Joshua Drake]

Registration for the second annual PostgreSQL Conference: West is now
open!. You may register here:

http://www.postgresqlconference.org/west08/register

Once you have registered be sure to subscribe to the attendees list:

http://lists.postgresqlconference.org/mailman/listinfo/attendees

We have 20 talks and tutorials waiting to teach you everything
from the wonders of Python + PostgreSQL + SQLAlchemy to the finer
points of Catalyst, DBIx::Class and PostgreSQL. These talks are being
provided by core members of the respective projects! We also have talks
on upcoming PostgreSQL 8.4 features as well as forums on PgUS and how
PgUS and PgEU can work together! See the full list (and growing) of
talks and tutorials here: http://www.pgcon.us/west08/talks/ .

Thanks to our new and recurring sponsors:

Command Prompt, Inc. - http://www.commandprompt.com/
EnterpriseDB - http://www.enterprisedb.com/

Afilias - http://www.afilias.info
HP - http://www.hp.com/

Continuent - http://www.continuent.com/
Emma - http://www.myemma.com/
Endpoint - http://www.endpoint.com/
OTG - http://www.otg-nc.com
Truviso - http://www.truviso.com/

EFF - http://www.eff.org/
Google - http://www.google.com/

All proceeds for PostgreSQL Conference: West benefit PgUs
http://www.postgresql.us/ .

Sincerely,

Joshua D. Drake
-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
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

[Thodoris]

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] Building WHERE SQL clauses

[Stephen Wellington]

You probably want something like this:

SELECT * FROM chico as c, harpo as h WHERE c.operator = "Bill" OR c.operator =
"Jessica" OR h.operator = "Bill" OR h.operator ="Jessica"

However if those tables really are identical I would suggest having a
good look at your
database design to see if it can be normalised or something...

Stephen Wellington

On Mon, Sep 15, 2008 at 4:06 PM, Mike Sullivan <[EMAIL PROTECTED]> wrote:
> Hello all.  I'm using PHP to build a query for a database that consists of
> multiple tables, all with identical attribues.  A typical syntax try looks
> like this:  SELECT * FROM chico, harpo WHERE operator = "Bill" OR operator =
> "Jessica"
>
> MySQL responds with this:  Couldn't execute query.Column 'operator' in where
> clause is ambiguous
>
> I was hoping that since the tables are identical all I would need to do is
> list the attribute values not have to append them to the table names.  Is
> there any way to do this?  Perhaps with a setting in MySQL or a different
> syntax (JOIN, UNION, ...)?  If not are there available some canned code
> snippets that build these types of strings from values passed in the $_POST
> array.  Thanks for any insights on this. --- Mike
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>



-- 
Stephen Wellington
07956 042387
01865 28 ext 12438
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Building WHERE SQL clauses

[Mike Sullivan]

Hello all.  I'm using PHP to build a query for a database that consists of 
multiple tables, all with identical attribues.  A typical syntax try looks 
like this:  SELECT * FROM chico, harpo WHERE operator = "Bill" OR operator = 
"Jessica"

MySQL responds with this:  Couldn't execute query.Column 'operator' in where 
clause is ambiguous

I was hoping that since the tables are identical all I would need to do is 
list the attribute values not have to append them to the table names.  Is 
there any way to do this?  Perhaps with a setting in MySQL or a different 
syntax (JOIN, UNION, ...)?  If not are there available some canned code 
snippets that build these types of strings from values passed in the $_POST 
array.  Thanks for any insights on this. --- Mike 



-- 
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

[Philip Thompson]

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

[Jason Pruim]

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

[Micah Gersten]

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

[PHP-DB] Question about databases and foreign keys

[Jason Pruim]

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