[PHP] PHP+MySQL question
Hi. I have a table called images with 4 columns - `image_id`, `item_name`, `image_url`, `image_views` (Where image_id is UNIQUE and AUTO-INCREMENT). Sometimes, there might be many items with the same name (but not with the same url). I want to make sure that each item name has at most 3 images (and therefore I need to delete the rest). The problem is that I want to keep the images with the most views. I've tried to look for efficient solutions either in MySQL or in PHP, but they are mostly very resource-intensive, Such as selecting all different names in PHP (using GROUP BY), then, for each name, doing DELETE FROM images WHERE item_name = 'ITEM-NAME-HERE' ORDER BY image_views ASC LIMIT (here some sub-query with count on how many rows have the name ITEM-NAME-HERE minus 3). I'd be glad if anyone could help me or point me to the right direction. Daniel. -- Use ROT26 for best security
Re: [PHP] PHP+MySQL question
On 11-02-16 11:37 AM, דניאל דנון wrote: Hi. I have a table called images with 4 columns - `image_id`, `item_name`, `image_url`, `image_views` (Where image_id is UNIQUE and AUTO-INCREMENT). Sometimes, there might be many items with the same name (but not with the same url). I want to make sure that each item name has at most 3 images (and therefore I need to delete the rest). The problem is that I want to keep the images with the most views. I've tried to look for efficient solutions either in MySQL or in PHP, but they are mostly very resource-intensive, Such as selecting all different names in PHP (using GROUP BY), then, for each name, doing DELETE FROM images WHERE item_name = 'ITEM-NAME-HERE' ORDER BY image_views ASC LIMIT (here some sub-query with count on how many rows have the name ITEM-NAME-HERE minus 3). I'd be glad if anyone could help me or point me to the right direction. I'd use a cron job to manage the purging process... and off the top of my head I'd probably go the following route: Get the list of images with more than 3 of the same name: SELECT item_name, SUM( 1 ) AS total FROM images HAVING total 3; Get the 3 best images for each image returned above: SELECT image_id FROM images WHERE image_name = '[[NAME]]' ORDER BY image_views DESC. (Make sure to quote your criteria properly in the above-- this is pseudo codish). Now delete the laggards using the ID we just retrieved: DELETE FROM images WHERE image_id NOT IN ([[ID_LIST]]). That should get you to a decent solution. Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP+MySQL question
On 11-02-16 11:55 AM, Robert Cummings wrote: On 11-02-16 11:37 AM, דניאל דנון wrote: Hi. I have a table called images with 4 columns - `image_id`, `item_name`, `image_url`, `image_views` (Where image_id is UNIQUE and AUTO-INCREMENT). Sometimes, there might be many items with the same name (but not with the same url). I want to make sure that each item name has at most 3 images (and therefore I need to delete the rest). The problem is that I want to keep the images with the most views. I've tried to look for efficient solutions either in MySQL or in PHP, but they are mostly very resource-intensive, Such as selecting all different names in PHP (using GROUP BY), then, for each name, doing DELETE FROM images WHERE item_name = 'ITEM-NAME-HERE' ORDER BY image_views ASC LIMIT (here some sub-query with count on how many rows have the name ITEM-NAME-HERE minus 3). I'd be glad if anyone could help me or point me to the right direction. I'd use a cron job to manage the purging process... and off the top of my head I'd probably go the following route: Get the list of images with more than 3 of the same name: SELECT item_name, SUM( 1 ) AS total FROM images HAVING total 3; Get the 3 best images for each image returned above: SELECT image_id FROM images WHERE image_name = '[[NAME]]' ORDER BY image_views DESC. Oops... that should have a LIMIT clause on it: SELECT image_id FROM images WHERE image_name = '[[NAME]]' ORDER BY image_views DESC LIMIT 3 Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] A mysql question...
hai all, I have two tables a and b as follows to implement a simple block list where users can block other users. Table A ++--+--+ | Name | phone |userid| ++--+--+ | Mr Sasi | 01225 708225 | 1 | | Miss Brown | 01225 899360 | 2 | | Mr Black | 01380 724040 | 3 | ++--+--+ Table B ++--+ | blockedbyid| blockedid | ++--+ | 1 | 2 | | 2 | 3 | | 1 | 3 | ++--+ blockedbyid is id of user who has blocked the user in blockedid. I need to join the two tables and fetch all records from table A such that the result has all users who are not blocked by a particular user [ie blockedbyid='XXX'].. Can you guys give the SQL query so that i can fetch the records as a recordset??? I dont want to fetch two different rowsets and compare it in php Correct me if my db structure is wrong. regards, Midhun Girish
Re: [PHP] A mysql question...
On 21 October 2010 14:13, Midhun Girish midhungir...@gmail.com wrote: | Name | phone |userid| ++--+--+ | Mr Sasi | 01225 708225 | 1 | | Miss Brown | 01225 899360 | 2 | | Mr Black | 01380 724040 | 3 | ++--+--+ Table B ++--+ | blockedbyid| blockedid | At first glance ... SELECT A.UserID, A.Name, A.Phone FROM A WHERE A.UserID NOT IN ( SELECT B.BlockedID FROM B WHERE B.BlockedByID = x ) The Subselect finds all the user ids blocked by x. The main select finds all users who are NOT in the subselects list. I'm pretty sure a subselect isn't as efficient as it could be though. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP-MYSQL Question
Hi guys,
Please can anyone tell me what I'm doing wrong with the code below? It keep
returning unsuccessful.
$result=mysql_query(CREATE TABLE table2(table2_id INT NOT NULL PRIMARY KEY
AUTO_INCREMENT,
table1_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
school VARCHAR(100) NOT NULL,
comment TEXT NOT NULL,
entrydate TIMESTAMP NOT NULL,
FOREIGN KEY(table1_id) REFERENCES table1(table1_id))
ENGINE = INNODB );
if($result){ printSuccessful;}
else {print Unsuccessful;}
Thanks in advance. Cheers.
Alugo Abdulazeez.
_
Drag n’ drop—Get easy photo sharing with Windows Live™ Photos.
http://www.microsoft.com/windows/windowslive/products/photos.aspx
Re: [PHP] PHP-MYSQL Question
This isn't PHP but mysql question.
You didn't mention that the table itslef is created or not. If not, then it
is probably a mysql error,
maybe your installation of mysql doesn't support INNODB.
SanTa
- Original Message -
From: abdulazeez alugo defati...@hotmail.com
To: php-general@lists.php.net
Sent: Tuesday, April 07, 2009 3:05 PM
Subject: [PHP] PHP-MYSQL Question
Hi guys,
Please can anyone tell me what I'm doing wrong with the code below? It keep
returning unsuccessful.
$result=mysql_query(CREATE TABLE table2(table2_id INT NOT NULL PRIMARY KEY
AUTO_INCREMENT,
table1_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
school VARCHAR(100) NOT NULL,
comment TEXT NOT NULL,
entrydate TIMESTAMP NOT NULL,
FOREIGN KEY(table1_id) REFERENCES table1(table1_id))
ENGINE = INNODB );
if($result){ printSuccessful;}
else {print Unsuccessful;}
Thanks in advance. Cheers.
Alugo Abdulazeez.
_
Drag n’ drop—Get easy photo sharing with Windows Live™ Photos.
http://www.microsoft.com/windows/windowslive/products/photos.aspx
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP-MYSQL Question
abdulazeez alugo wrote: Hi guys, Please can anyone tell me what I'm doing wrong with the code below? It keep returning unsuccessful. Why don't you print out mysql_error() ? It'll tell you right away. /Per -- Per Jessen, Zürich (20.1°C) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP-MYSQL Question
From: p...@computer.org Date: Tue, 7 Apr 2009 15:18:35 +0200 To: php-general@lists.php.net Subject: Re: [PHP] PHP-MYSQL Question abdulazeez alugo wrote: Hi guys, Please can anyone tell me what I'm doing wrong with the code below? It keep returning unsuccessful. Why don't you print out mysql_error() ? It'll tell you right away. /Per Thanks Per, I should have thought of that. Now I owe you a beer. Cheers. _ More than messages–check out the rest of the Windows Live™. http://www.microsoft.com/windows/windowslive/
Re: [PHP] A MySQL Question
On Mon, 2008-12-08 at 23:50 -0800, Yeti wrote:
?php
define('HUMAN_STUPIDITY', true);
function bigbang() {
while (HUMAN_STUPIDITY || !isset($debate_is_over)) { }
return true;
}
if (!isset($universe)) bigbang();
?
Who says the big bang is past?
I can't hear it.
Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
German Geek wrote: On Tue, Dec 9, 2008 at 2:46 PM, Micah Gersten [EMAIL PROTECTED] wrote: Robert Cummings wrote: On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote: Ashley Sheridan wrote: On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk any chance of writing the implementation of that bigbang() function? If nothing exists and a universe is created via a big bang... does it make a sound? Can we realistically call it a big bang if it doesn't make a sound? Couldn't we call it the big light show? But then again... if nothing exists and a universe is created via a big light show... does it matter? Can it be perceived? Is this just a proverbial pandrödinger's box? You can't implement the bigbang() function if you don't exist. Cheers, Rob. The function doesn't say who's doing the creating, it just checks for the existence of the universe. Lol, I agree, the function bigbang() doesn't need to be implemented (or it could be empty if it needs to be there for this line to work), because by definition, the universe must exist, if this statement is to exist. Although it would be interesting to see an implementation of a simulation of bigbang(). And, I would say there is a sound, even if no one is there to hear it, assuming it to have happened. Also if there were no sound, there would be no light show either, there would be nothing, which contradicts the assumption that the big bang was there (exists)... Guys, I think this is taking it a bit far... In space, no one can hear you scream... (or bang, for that matter) -- Peter Ford phone: 01580 89 Developer fax: 01580 893399 Justcroft International Ltd., Staplehurst, Kent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
As a matter of fact, in space you can't even scream. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Tue, Dec 9, 2008 at 10:49 PM, Yeti [EMAIL PROTECTED] wrote: As a matter of fact, in space you can't even scream. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php I don't know if there is a better or best solution to this, but an infinite loop for something that is finite, I don't know... I do like the stick whacking the drum part though. :) And yes, you wouldnt hear anything in space, hadnt thought about that, so you can argue that the big bang didnt make a noise because it wouldnt have been hearable because there was nothing to carry the sound. For that matter it wouldnt have been seeable either. Something being hearable or seeable is different from someone/thing hearing or seeing it though. My point: Not trying to make one, or any sense either, because im probably contradicting myself here as are all of you, no offence. However, I find the big bang theory more convincing than any 7 days creation theory or things like that (sorry to all the religious people out there), But then you can always ask what was before that and before that and so on. But no one ever asks who or what created god in the first place, if s/he/it exists. Was s/he/it always there? Well then one could argue that the universe was always there too and there was no creation or big bang, or was good there for infinity and after a few quadrillion years, s/he/it became so bored and decided to make a big firework or only spend 7 days in creating everything? 7 days is a horribly short time for such a task after an infinite time of boredome. Maybe earth was always there (although this seems unlikely too). But do we really know that? I mean, ive read it in a book and learned it at school, but maybe we're all wrong and its all totally different to what is expected. To me only one thing is clear: We will never know how it all began, because a beginning of time and everything seems illogical to me, because there must have been something before that. Infinity, although to most not graspable seems a more graspable concept to me than finity. Anybody agree or am i alone in this universe? Sorry to go terribly off topic here...
Re: [PHP] A MySQL Question
At 12:44 AM +1300 12/10/08, German Geek wrote: Anybody agree or am i alone in this universe? Of course you are alone. As for the existence and history of the Universe, the Earth, you, or God -- it's all a matter of perception; and what you believe to be true; and on what the foundations you accept for those truths. My beliefs will never be yours and vice versa. I don't see: $bigBang = explode(null); to be any more/less true than: $belief = function_exists($God); Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
Perhaps you couldn't hear the big bang in this universe, but what about in the universe that spawned it? :-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Tue, Dec 9, 2008 at 11:40 AM, [EMAIL PROTECTED] wrote: Perhaps you couldn't hear the big bang in this universe, but what about in the universe that spawned it? :-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php The omniverse or the underverse? -- Bastien Cat, the other other white meat
Re: [PHP] A MySQL Question
On Tue, 2008-12-09 at 16:40 +, [EMAIL PROTECTED] wrote: Perhaps you couldn't hear the big bang in this universe, but what about in the universe that spawned it? :-) The black hole wouldn't allow it to travel in that direction ;) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Tue, 2008-12-09 at 01:49 -0800, Yeti wrote: As a matter of fact, in space you can't even scream. Sure you can... I'm screaming right now... and I'm in space. A container within a container within a container within a container (ad infinitum) is still within the outermost container. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
Sure you can... I'm screaming right now... and I'm in space. A container within a container within a container within a container (ad infinitum) is still within the outermost container. I didn't hear you scream. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Sun, Dec 7, 2008 at 12:38 PM, Robert Cummings [EMAIL PROTECTED] wrote: On Sun, 2008-12-07 at 11:01 -0500, Eric Butera wrote: Sounds like someone thinks they're pretty clever. I'll never understand why interviewers want to ask really odd edge case questions instead of ones that really show practical knowledge. I know that I don't know the syntax to everything. What I do know is where to find it in seconds if I need it. There's better ways of weeding out resume fibbers. :) I've never actually used EXIST before, but maybe now that I've looked at it I'll find a use. Oh you'll find a use alright... on stupid esoteric interview questions :) Cheers, Rob. I use EXISTS quite a bit for regular queries. These queries are all roughly equivalent in what they return, but depending on the database engine and optimizer, they may have different execution plans that make one perform better than another: SELECT a.* FROMsometable AS a, someothertable AS b WHERE a.keyid = b.keyid SELECT a.* FROMsometable AS a INNER JOIN someothertable AS b ON a.keyid = b.keyid SELECT a.* FROMsometable AS a WHERE a.keyid IN ( SELECT b.keyid FROMsomeothertable AS b ) SELECT a.* FROMsometable AS a WHERE EXISTS ( SELECT * FROMsomeothertable AS b WHERE a.keyid = b.keyid ) Now, I realize that in a simple contrived example like this, it is pointless to quibble much about one over another. But in more complex queries, especially with nested subqueries, each may have benefits in different situations. Where I've seen EXISTS shine more is in the reverse case, where you want to find records in one table that have no match in another: SELECT a.* FROMsometable AS a LEFT OUTER JOIN someothertable AS b ON a.keyid = b.keyid WHERE b.keyid IS NULL SELECT a.* FROMsometable AS a WHERE a.keyid NOT IN ( SELECT b.keyid FROMsomeothertable AS b ) SELECT a.* FROMsometable AS a WHERE NOT EXISTS ( SELECT * FROMsomeothertable AS b WHERE a.keyid = b.keyid ) Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Mon, 2008-12-08 at 10:19 -0500, Andrew Ballard wrote: On Sun, Dec 7, 2008 at 12:38 PM, Robert Cummings [EMAIL PROTECTED] wrote: On Sun, 2008-12-07 at 11:01 -0500, Eric Butera wrote: Sounds like someone thinks they're pretty clever. I'll never understand why interviewers want to ask really odd edge case questions instead of ones that really show practical knowledge. I know that I don't know the syntax to everything. What I do know is where to find it in seconds if I need it. There's better ways of weeding out resume fibbers. :) I've never actually used EXIST before, but maybe now that I've looked at it I'll find a use. Oh you'll find a use alright... on stupid esoteric interview questions :) Cheers, Rob. I use EXISTS quite a bit for regular queries. These queries are all roughly equivalent in what they return, but depending on the database engine and optimizer, they may have different execution plans that make one perform better than another: SELECT a.* FROMsometable AS a, someothertable AS b WHERE a.keyid = b.keyid SELECT a.* FROMsometable AS a INNER JOIN someothertable AS b ON a.keyid = b.keyid SELECT a.* FROMsometable AS a WHERE a.keyid IN ( SELECT b.keyid FROMsomeothertable AS b ) SELECT a.* FROMsometable AS a WHERE EXISTS ( SELECT * FROMsomeothertable AS b WHERE a.keyid = b.keyid ) Now, I realize that in a simple contrived example like this, it is pointless to quibble much about one over another. But in more complex queries, especially with nested subqueries, each may have benefits in different situations. Where I've seen EXISTS shine more is in the reverse case, where you want to find records in one table that have no match in another: SELECT a.* FROMsometable AS a LEFT OUTER JOIN someothertable AS b ON a.keyid = b.keyid WHERE b.keyid IS NULL SELECT a.* FROMsometable AS a WHERE a.keyid NOT IN ( SELECT b.keyid FROMsomeothertable AS b ) SELECT a.* FROMsometable AS a WHERE NOT EXISTS ( SELECT * FROMsomeothertable AS b WHERE a.keyid = b.keyid ) A good example. I guess I've just never come across a need for this query. But still, not having come across a need does not indicate an inability to find such syntax when necessary. That said though, someone else mentioned earlier that maybe the job was teaching about SQL or databases, in which case I guess a more comprehensive need for the knowledge would be necessary beforehand :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
Ashley Sheridan wrote: On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk any chance of writing the implementation of that bigbang() function? give you a pound! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote: Ashley Sheridan wrote: On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk any chance of writing the implementation of that bigbang() function? If nothing exists and a universe is created via a big bang... does it make a sound? Can we realistically call it a big bang if it doesn't make a sound? Couldn't we call it the big light show? But then again... if nothing exists and a universe is created via a big light show... does it matter? Can it be perceived? Is this just a proverbial pandrödinger's box? You can't implement the bigbang() function if you don't exist. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
Robert Cummings wrote: On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote: Ashley Sheridan wrote: On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk any chance of writing the implementation of that bigbang() function? If nothing exists and a universe is created via a big bang... does it make a sound? Can we realistically call it a big bang if it doesn't make a sound? Couldn't we call it the big light show? But then again... if nothing exists and a universe is created via a big light show... does it matter? Can it be perceived? Is this just a proverbial pandrödinger's box? You can't implement the bigbang() function if you don't exist. Cheers, Rob. The function doesn't say who's doing the creating, it just checks for the existence of the universe. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Tue, Dec 9, 2008 at 2:46 PM, Micah Gersten [EMAIL PROTECTED] wrote: Robert Cummings wrote: On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote: Ashley Sheridan wrote: On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk any chance of writing the implementation of that bigbang() function? If nothing exists and a universe is created via a big bang... does it make a sound? Can we realistically call it a big bang if it doesn't make a sound? Couldn't we call it the big light show? But then again... if nothing exists and a universe is created via a big light show... does it matter? Can it be perceived? Is this just a proverbial pandrödinger's box? You can't implement the bigbang() function if you don't exist. Cheers, Rob. The function doesn't say who's doing the creating, it just checks for the existence of the universe. Lol, I agree, the function bigbang() doesn't need to be implemented (or it could be empty if it needs to be there for this line to work), because by definition, the universe must exist, if this statement is to exist. Although it would be interesting to see an implementation of a simulation of bigbang(). And, I would say there is a sound, even if no one is there to hear it, assuming it to have happened. Also if there were no sound, there would be no light show either, there would be nothing, which contradicts the assumption that the big bang was there (exists)... Guys, I think this is taking it a bit far...
Re: [PHP] A MySQL Question
On Mon, 2008-12-08 at 19:46 -0600, Micah Gersten wrote: Robert Cummings wrote: On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote: Ashley Sheridan wrote: On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk any chance of writing the implementation of that bigbang() function? If nothing exists and a universe is created via a big bang... does it make a sound? Can we realistically call it a big bang if it doesn't make a sound? Couldn't we call it the big light show? But then again... if nothing exists and a universe is created via a big light show... does it matter? Can it be perceived? Is this just a proverbial pandrödinger's box? You can't implement the bigbang() function if you don't exist. Cheers, Rob. The function doesn't say who's doing the creating, it just checks for the existence of the universe. How do you know? Are you... God? ;) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
Robert Cummings wrote: On Mon, 2008-12-08 at 19:46 -0600, Micah Gersten wrote: Robert Cummings wrote: On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote: Ashley Sheridan wrote: On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk any chance of writing the implementation of that bigbang() function? If nothing exists and a universe is created via a big bang... does it make a sound? Can we realistically call it a big bang if it doesn't make a sound? Couldn't we call it the big light show? But then again... if nothing exists and a universe is created via a big light show... does it matter? Can it be perceived? Is this just a proverbial pandrödinger's box? You can't implement the bigbang() function if you don't exist. Cheers, Rob. The function doesn't say who's doing the creating, it just checks for the existence of the universe. How do you know? Are you... God? ;) Cheers, Rob. Sorry, term foul-up. I meant the statement doesn't say who's doing the creating, it just checks for existence of the universe. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
German Geek wrote: On Tue, Dec 9, 2008 at 2:46 PM, Micah Gersten [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Robert Cummings wrote: On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote: Ashley Sheridan wrote: On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk any chance of writing the implementation of that bigbang() function? If nothing exists and a universe is created via a big bang... does it make a sound? Can we realistically call it a big bang if it doesn't make a sound? Couldn't we call it the big light show? But then again... if nothing exists and a universe is created via a big light show... does it matter? Can it be perceived? Is this just a proverbial pandrödinger's box? You can't implement the bigbang() function if you don't exist. Cheers, Rob. The function doesn't say who's doing the creating, it just checks for the existence of the universe. Lol, I agree, the function bigbang() doesn't need to be implemented (or it could be empty if it needs to be there for this line to work), because by definition, the universe must exist, if this statement is to exist. Who says this statement is run in this universe? Who says it's not for a simulator? Guys, I think this is taking it a bit far... You new here? ;) Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
Micah Gersten wrote: Robert Cummings wrote: On Mon, 2008-12-08 at 19:46 -0600, Micah Gersten wrote: Robert Cummings wrote: On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote: Ashley Sheridan wrote: On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED] wrote: Presumable, the EXISTS sub-query can be optimized sometimes to just stop processing the sub-query and kick things back out to the outer query. IN has to process them all and find them all. Don't forget the special case use as well: IF NOT EXISTS `universe` THEN bigbang() Ash www.ashleysheridan.co.uk any chance of writing the implementation of that bigbang() function? If nothing exists and a universe is created via a big bang... does it make a sound? Can we realistically call it a big bang if it doesn't make a sound? Couldn't we call it the big light show? But then again... if nothing exists and a universe is created via a big light show... does it matter? Can it be perceived? Is this just a proverbial pandrödinger's box? You can't implement the bigbang() function if you don't exist. Cheers, Rob. The function doesn't say who's doing the creating, it just checks for the existence of the universe. How do you know? Are you... God? ;) Cheers, Rob. Sorry, term foul-up. I meant the statement doesn't say who's doing the creating, it just checks for existence of the universe. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com jesus stop the debating and get making it, there's a pound on offer here. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Tue, Dec 9, 2008 at 3:51 PM, Micah Gersten [EMAIL PROTECTED] wrote:
German Geek wrote:
On Tue, Dec 9, 2008 at 2:46 PM, Micah Gersten [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Robert Cummings wrote:
On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote:
Ashley Sheridan wrote:
On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Presumable, the EXISTS sub-query can be optimized sometimes
to just stop processing the sub-query and kick things back out to
the outer query.
IN has to process them all and find them all.
Don't forget the special case use as well:
IF NOT EXISTS `universe` THEN bigbang()
Ash
www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk
any chance of writing the implementation of that bigbang()
function?
If nothing exists and a universe is created via a big bang...
does it
make a sound? Can we realistically call it a big bang if it
doesn't make
a sound? Couldn't we call it the big light show? But then
again... if
nothing exists and a universe is created via a big light show...
does it
matter? Can it be perceived? Is this just a proverbial
pandrödinger's
box? You can't implement the bigbang() function if you don't exist.
Cheers,
Rob.
The function doesn't say who's doing the creating, it just checks for
the existence of the universe.
Lol, I agree, the function bigbang() doesn't need to be implemented
(or it could be empty if it needs to be there for this line to work),
because by definition, the universe must exist, if this statement is
to exist.
Who says this statement is run in this universe? Who says it's not for
a simulator?
Guys, I think this is taking it a bit far...
You new here? ;)
Yep. I'm new here. :)
OK, to take this even further then...
How about a start of a high level function:
function bigbang() {
$elementsNecessaryForBang = God::createElements(); // have to get it from
somewhere, don't know how to get this just yet
$particleSimulator = new ParticleSimulator($elementsNecessaryForBang);
$particleSimulator-start();
// ...
return $universe;
}
Oh, I forgot. We're writing this in plain MySQL? Don't know how to even
start this... :) I guess this is a PHP List, so I guess it's safe to use
PHP, no? Sorry for stealing the thread...
Re: [PHP] A MySQL Question
On Tue, 2008-12-09 at 17:40 +1300, German Geek wrote:
On Tue, Dec 9, 2008 at 3:51 PM, Micah Gersten [EMAIL PROTECTED] wrote:
German Geek wrote:
On Tue, Dec 9, 2008 at 2:46 PM, Micah Gersten [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Robert Cummings wrote:
On Tue, 2008-12-09 at 00:16 +, Nathan Rixham wrote:
Ashley Sheridan wrote:
On Mon, 2008-12-08 at 23:23 +, [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Presumable, the EXISTS sub-query can be optimized sometimes
to just stop processing the sub-query and kick things back out to
the outer query.
IN has to process them all and find them all.
Don't forget the special case use as well:
IF NOT EXISTS `universe` THEN bigbang()
Ash
www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk
any chance of writing the implementation of that bigbang()
function?
If nothing exists and a universe is created via a big bang...
does it
make a sound? Can we realistically call it a big bang if it
doesn't make
a sound? Couldn't we call it the big light show? But then
again... if
nothing exists and a universe is created via a big light show...
does it
matter? Can it be perceived? Is this just a proverbial
pandrödinger's
box? You can't implement the bigbang() function if you don't exist.
Cheers,
Rob.
The function doesn't say who's doing the creating, it just checks for
the existence of the universe.
Lol, I agree, the function bigbang() doesn't need to be implemented
(or it could be empty if it needs to be there for this line to work),
because by definition, the universe must exist, if this statement is
to exist.
Who says this statement is run in this universe? Who says it's not for
a simulator?
Guys, I think this is taking it a bit far...
You new here? ;)
Yep. I'm new here. :)
OK, to take this even further then...
How about a start of a high level function:
function bigbang() {
$elementsNecessaryForBang = God::createElements(); // have to get it from
somewhere, don't know how to get this just yet
$particleSimulator = new ParticleSimulator($elementsNecessaryForBang);
$particleSimulator-start();
// ...
return $universe;
}
Oh, I forgot. We're writing this in plain MySQL? Don't know how to even
start this... :) I guess this is a PHP List, so I guess it's safe to use
PHP, no? Sorry for stealing the thread...
Wow! You really are new around here... the following is a better
solution:
?php
require_once( 'reality.php' );
$stick = new Stick();
$drum = new Drum();
$bigbang = $stick-whack( $drum );
for( ; ; )
{
$bigbang-expand();
}
?
Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
?php
define('HUMAN_STUPIDITY', true);
function bigbang() {
while (HUMAN_STUPIDITY || !isset($debate_is_over)) { }
return true;
}
if (!isset($universe)) bigbang();
?
Who says the big bang is past?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] A MySQL Question
Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? Now without running to the manuals, please be honest and tell me how many of you know off the top of your head what EXIST means? I would be curious to know. I answered the question correctly, (I'm one of those weird types who read manuals for fun) but I have never used EXIST in a query. Have any of you? And while we're on the subject of MySQL -- while we all know how to write it, how do you say it? I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. What say you? Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
I believe I've seen EXIST used when creating backups of tables to test for the existence of a table and DROP it if it exists (if you select the option to add such code in phpMyAdmin). So that would have been my answer (give or take more specific wording for an interview). As for pronounciation... I used to say S-Q-L for all such references and still use it when talking about the language most of the time, but some of my coworkers sequel has rubbed off on me, especially with MySQL. I used to cringe heartily at such wordification of acronyms that didn't actually spell a word, or something similar that at least contained some vowels, but it's kind of nice to reduce a mouthful a little bit. I've never heard squell, though. Not sure I like that very much. -TG - Original Message - From: tedd [EMAIL PROTECTED] To: PHP General Mailing List php-general@lists.php.net Date: Sun, 7 Dec 2008 10:03:26 -0500 Subject: [PHP] A MySQL Question Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? Now without running to the manuals, please be honest and tell me how many of you know off the top of your head what EXIST means? I would be curious to know. I answered the question correctly, (I'm one of those weird types who read manuals for fun) but I have never used EXIST in a query. Have any of you? And while we're on the subject of MySQL -- while we all know how to write it, how do you say it? I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. What say you? Cheers, tedd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Sun, Dec 7, 2008 at 10:03 AM, tedd [EMAIL PROTECTED] wrote: Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? Now without running to the manuals, please be honest and tell me how many of you know off the top of your head what EXIST means? I would be curious to know. I answered the question correctly, (I'm one of those weird types who read manuals for fun) but I have never used EXIST in a query. Have any of you? And while we're on the subject of MySQL -- while we all know how to write it, how do you say it? I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. What say you? Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Sounds like someone thinks they're pretty clever. I'll never understand why interviewers want to ask really odd edge case questions instead of ones that really show practical knowledge. I know that I don't know the syntax to everything. What I do know is where to find it in seconds if I need it. There's better ways of weeding out resume fibbers. :) I've never actually used EXIST before, but maybe now that I've looked at it I'll find a use. I'm more used to using joins, but this might be a little more readable in cases. On their site I saw once they said they call it My-S-Q-L, but the other way works too. I prefer My-SQL. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Sun, 2008-12-07 at 10:03 -0500, tedd wrote: Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? Not sure about EXIST, but definitely see EXISTS when creating table dumps. DROP TABLE IF EXISTS foo; I usually do dumps via command-line so to get the DROP TABLE line you add the --add-drop-table flag. Now without running to the manuals, please be honest and tell me how many of you know off the top of your head what EXIST means? I would be curious to know. I answered the question correctly, (I'm one of those weird types who read manuals for fun) but I have never used EXIST in a query. Have any of you? And while we're on the subject of MySQL -- while we all know how to write it, how do you say it? I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. What say you? I say as you do... My S. Q. L. Although, I recall hearing a long time ago that the My part was named after a person's name and is supposed to be pronounced as Me. People who say My Sequel are confusing the old database language SEQUEL with SQL. While SEQUEL was a precursor to SQL, SQL actually stands for Standard Query Language, and is not a shortening of SEQUEL. Wikipedia states that the official pronunciation is My S. Q. L. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Sun, 2008-12-07 at 11:01 -0500, Eric Butera wrote: Sounds like someone thinks they're pretty clever. I'll never understand why interviewers want to ask really odd edge case questions instead of ones that really show practical knowledge. I know that I don't know the syntax to everything. What I do know is where to find it in seconds if I need it. There's better ways of weeding out resume fibbers. :) I've never actually used EXIST before, but maybe now that I've looked at it I'll find a use. Oh you'll find a use alright... on stupid esoteric interview questions :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Sun, Dec 07, 2008 at 10:03:26AM -0500, tedd wrote: Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? Now without running to the manuals, please be honest and tell me how many of you know off the top of your head what EXIST means? I would be curious to know. I answered the question correctly, (I'm one of those weird types who read manuals for fun) but I have never used EXIST in a query. Have any of you? Really? What *does* it mean? It's not in the manual index. Perhaps you're confusing it with EXISTS. And while we're on the subject of MySQL -- while we all know how to write it, how do you say it? I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. My-S-Q-L. Few people realize that 'sequel' was the language used by Ingres for their database and it was different from S-Q-L. -- You are the only one who can use your ability. It is an awesome responsibility. -- Zig Zigler Rick Pasotto[EMAIL PROTECTED]http://www.niof.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Sun, 2008-12-07 at 10:03 -0500, tedd wrote: Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? Now without running to the manuals, please be honest and tell me how many of you know off the top of your head what EXIST means? I would be curious to know. I answered the question correctly, (I'm one of those weird types who read manuals for fun) but I have never used EXIST in a query. Have any of you? And while we're on the subject of MySQL -- while we all know how to write it, how do you say it? I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. What say you? Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com I've heard several pronunciations, 'sequel', 's.q.l', 'squeal' but I tend to always refer to it as My-S.Q.L, and likewise the M$ version is M.S-S.Q.L, despite M$ insisting it's just SQL running on a product that affectionately like to call simply SQL Server ;) Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Sun, 2008-12-07 at 12:38 -0500, Robert Cummings wrote: On Sun, 2008-12-07 at 11:01 -0500, Eric Butera wrote: Sounds like someone thinks they're pretty clever. I'll never understand why interviewers want to ask really odd edge case questions instead of ones that really show practical knowledge. I know that I don't know the syntax to everything. What I do know is where to find it in seconds if I need it. There's better ways of weeding out resume fibbers. :) I've never actually used EXIST before, but maybe now that I've looked at it I'll find a use. Oh you'll find a use alright... on stupid esoteric interview questions :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP I know. I had to take a similar test one time. I actually ended up pointing out the errors in the questions and the fact that one question featured twice with exactly the same wording! Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
This one time, at band camp, tedd [EMAIL PROTECTED] wrote: In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? I only ever use it in rollbacks to check if a table exists. Not sure if it has another purpose... I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. Only a barbarian would call it Sequel or anything other than My S. Q. L MY ESS KEW ELL Kevin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
Kevin Waterson wrote: Only a barbarian would call it Sequel or anything other than My S. Q. L MY ESS KEW ELL I have never heard that product SQL Server referred to as anything other than seequel server. Guess that proves your point :) Stephen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
tedd wrote: Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? There is no 'Exist' keyword in sql, it's 'EXISTS'. Everyone seems to know the drop table if exists syntax but that's not the right one they are after (and it's non-standard sql). There is an 'Exists' keyword for subselects. Instead of doing an IN query, you can do an 'exists' query - but it's use is very different. It returns true if any row in the subquery exists or false if none exist. The idea is instead of: select * from accounts where company_id in (select company_id from companies where name='X'); you do select * from company where company_id exists (select company_id from companies where name='X'); the subselect checks the company table for the id, if it exists, the outer query runs. It does not matter what company_id is returned from the subquery, just that at least 1 does return. I've never used this anywhere myself because I usually care what id's are returned from the subquery. (Yes, that's from the top of my head :P but here's doc's to help). http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html http://www.postgresql.org/docs/8.3/static/functions-subquery.html -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
EXIST? Yeah I certainly have seen it before in the result of a mysqldump, but from the top of my head, I probably wouldn't have known in exactly what context it is used. I've used MySQL for 5 years now and i think if you ask such a question, you don't know what you should be asking because the context of EXIST is hardly ever needed, and if, if you know where to look for it, that's more important than being able to reproduce it in from the top of your head. It's like asking: Do you know the syntax for ... where ... is a rarely used function in PHP or any other language. It's like requiring your employees to know every function of a language... On Mon, Dec 8, 2008 at 4:03 AM, tedd [EMAIL PROTECTED] wrote: Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? Now without running to the manuals, please be honest and tell me how many of you know off the top of your head what EXIST means? I would be curious to know. I answered the question correctly, (I'm one of those weird types who read manuals for fun) but I have never used EXIST in a query. Have any of you? And while we're on the subject of MySQL -- while we all know how to write it, how do you say it? I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. What say you? Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Tim-Hinnerk Heuer http://www.ihostnz.com -- Web Design, Hosting and free Linux Support
Re: [PHP] A MySQL Question
On Mon, 2008-12-08 at 12:33 +1300, German Geek wrote: EXIST? Yeah I certainly have seen it before in the result of a mysqldump, but from the top of my head, I probably wouldn't have known in exactly what context it is used. I've used MySQL for 5 years now and i think if you ask such a question, you don't know what you should be asking because the context of EXIST is hardly ever needed, and if, if you know where to look for it, that's more important than being able to reproduce it in from the top of your head. It's like asking: Do you know the syntax for ... where ... is a rarely used function in PHP or any other language. It's like requiring your employees to know every function of a language... On Mon, Dec 8, 2008 at 4:03 AM, tedd [EMAIL PROTECTED] wrote: Hi gang: I just interviewed for a job teaching at the local college (imagine me taking minds of mush and molding them to the world according to tedd -- frightening huh?) In any event, the interviewer asked me how long I've been using MySQL and I replied several years. After which she asked a single question, which was What does EXIST mean? Now without running to the manuals, please be honest and tell me how many of you know off the top of your head what EXIST means? I would be curious to know. I answered the question correctly, (I'm one of those weird types who read manuals for fun) but I have never used EXIST in a query. Have any of you? And while we're on the subject of MySQL -- while we all know how to write it, how do you say it? I've read that the common way is to say My Squell, or something like that. But I always sounded out each letter, such as My S-Q-L. The interviewer pronounced it the same as I, but I have heard others say it differently. What say you? Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Better questions really ought to be along the lines of how would you tackle this problem? and then accept a variety of answers back, or some debugging on erroneous code. Unfortunately, these tests all too often feature questions on those obscure functions that are rarely used. Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
German Geek wrote: EXIST? Yeah I certainly have seen it before in the result of a mysqldump, but from the top of my head, I probably wouldn't have known in exactly what context it is used. I've used MySQL for 5 years now and i think if you ask such a question, you don't know what you should be asking because the context of EXIST is hardly ever needed, and if, if you know where to look for it, that's more important than being able to reproduce it in from the top of your head. It's like asking: Do you know the syntax for ... where ... is a rarely used function in PHP or any other language. It's like requiring your employees to know every function of a language... You're making an assumption about the situation. What if it was for a dba job or teaching advanced sql? And 'exists' is not for mysqldump. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Mon, Dec 8, 2008 at 2:59 PM, Chris [EMAIL PROTECTED] wrote: German Geek wrote: EXIST? Yeah I certainly have seen it before in the result of a mysqldump, but from the top of my head, I probably wouldn't have known in exactly what context it is used. I've used MySQL for 5 years now and i think if you ask such a question, you don't know what you should be asking because the context of EXIST is hardly ever needed, and if, if you know where to look for it, that's more important than being able to reproduce it in from the top of your head. It's like asking: Do you know the syntax for ... where ... is a rarely used function in PHP or any other language. It's like requiring your employees to know every function of a language... You're making an assumption about the situation. What if it was for a dba job or teaching advanced sql? And 'exists' is not for mysqldump. DROP TABLE IF EXISTS `mytable`; I said, I've seen EXIST in a result of a mysqldump before, which is not wrong is it? Unless I'm hallucinating... Or is EXISTS something completely different? And even a DBA or teacher doesn't need to know every part of syntax in MySQL. It's more important that they know the concepts... Anyway, I think this is all off-topic in a PHP mailing list... -- Postgresql php tutorials http://www.designmagick.com/ -- Tim-Hinnerk Heuer http://www.ihostnz.com -- Web Design, Hosting and free Linux Support
Re: [PHP] A MySQL Question
And 'exists' is not for mysqldump. DROP TABLE IF EXISTS `mytable`; if exists can be used in lots of places other than drop table, like triggers, functions and i'm sure other things. I said, I've seen EXIST in a result of a mysqldump before, which is not wrong is it? Unless I'm hallucinating... Or is EXISTS something completely different? Yes it is completely different. http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Mon, Dec 8, 2008 at 3:24 PM, Chris [EMAIL PROTECTED] wrote: And 'exists' is not for mysqldump. DROP TABLE IF EXISTS `mytable`; if exists can be used in lots of places other than drop table, like triggers, functions and i'm sure other things. I said, I've seen EXIST in a result of a mysqldump before, which is not wrong is it? Unless I'm hallucinating... Or is EXISTS something completely different? Yes it is completely different. Sorry, I couldnt find EXIST there, only EXISTS. http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html -- Postgresql php tutorials http://www.designmagick.com/ -- Tim-Hinnerk Heuer http://www.ihostnz.com -- Web Design, Hosting and free Linux Support
Re: [PHP] A MySQL Question
German Geek wrote: On Mon, Dec 8, 2008 at 3:24 PM, Chris [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: And 'exists' is not for mysqldump. DROP TABLE IF EXISTS `mytable`; if exists can be used in lots of places other than drop table, like triggers, functions and i'm sure other things. I said, I've seen EXIST in a result of a mysqldump before, which is not wrong is it? Unless I'm hallucinating... Or is EXISTS something completely different? Yes it is completely different. Sorry, I couldnt find EXIST there, only EXISTS. If you're going to be that pedantic, exist isn't in mysqldump either :P -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Mon, Dec 8, 2008 at 5:06 PM, Chris [EMAIL PROTECTED] wrote: German Geek wrote: On Mon, Dec 8, 2008 at 3:24 PM, Chris [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] wrote: And 'exists' is not for mysqldump. DROP TABLE IF EXISTS `mytable`; if exists can be used in lots of places other than drop table, like triggers, functions and i'm sure other things. I said, I've seen EXIST in a result of a mysqldump before, which is not wrong is it? Unless I'm hallucinating... Or is EXISTS something completely different? Yes it is completely different. Sorry, I couldnt find EXIST there, only EXISTS. If you're going to be that pedantic, exist isn't in mysqldump either :P I know it's pedantic, but unfortunately computers are strictly pedantic and I wasn't sure why you said if exists can be used in lots of places other than drop table, like triggers, functions and i'm sure other things. I was just stating that I saw it in a dump and I never really used it, so assume(d), it's not very important because you can do the same thing with IN etc and other conditions, can't u? Anyway, this discussion is getting rediculous. Let's move on. Didn't mean to offend anyone here. Don't worry about answering the last question if you also think it's irrelevant... All good. :) -- Postgresql php tutorials http://www.designmagick.com/ -- Tim-Hinnerk Heuer http://www.ihostnz.com -- Web Design, Hosting and free Linux Support
Re: [PHP] A MySQL Question
Sorry, I couldnt find EXIST there, only EXISTS. If you're going to be that pedantic, exist isn't in mysqldump either :P I know it's pedantic, but unfortunately computers are strictly pedantic and I wasn't sure why you said if exists can be used in lots of places other than drop table, like triggers, functions and i'm sure other things. I was just stating that I saw it in a dump and I never really used it, so assume(d), it's not very important because you can do the same thing with IN etc and other conditions, can't u? They are completely different things you're mixing up here. IF EXISTS with DROP TABLE means if the table does not exist, do not give an error. Same for drop view, drop trigger. If the view/table/trigger/function does not exist, do not give an error. EXISTS in a select query is a subquery - same as using IN. Completely different. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Mon, Dec 8, 2008 at 5:50 PM, Chris [EMAIL PROTECTED] wrote: Sorry, I couldnt find EXIST there, only EXISTS. If you're going to be that pedantic, exist isn't in mysqldump either :P I know it's pedantic, but unfortunately computers are strictly pedantic and I wasn't sure why you said if exists can be used in lots of places other than drop table, like triggers, functions and i'm sure other things. I was just stating that I saw it in a dump and I never really used it, so assume(d), it's not very important because you can do the same thing with IN etc and other conditions, can't u? They are completely different things you're mixing up here. IF EXISTS with DROP TABLE means if the table does not exist, do not give an error. I do understand that it's a bit different here. Same for drop view, drop trigger. If the view/table/trigger/function does not exist, do not give an error. EXISTS in a select query is a subquery - same as using IN. Completely different. Right So, how are these different: SELECT * FROM t1 WHERE id EXISTS (SELECT id FROM t2) to SELECT * FROM t1 WHERE id IN (SELECT id FROM t2) ?? According to my understanding of the documentation, these would have the same result. Can't think of any sub query that could not have an equivalent statement with IN (NOT IN). -- Postgresql php tutorials http://www.designmagick.com/ -- Tim-Hinnerk Heuer http://www.ihostnz.com -- Web Design, Hosting and free Linux Support
Re: [PHP] A MySQL Question
Right So, how are these different: SELECT * FROM t1 WHERE id EXISTS (SELECT id FROM t2) If there are *any* results for the subselect, the exists returns true. It's the equivalent of: select * from t1 where id is true; ie select * from t1; If there are no results for the subselect, the exists returns false, ie: select * from t1 where false; which will return nothing. to SELECT * FROM t1 WHERE id IN (SELECT id FROM t2) this returns specific id's that match. According to my understanding of the documentation, these would have the same result. No, they aren't. create table t1(id int, name varchar(5)); insert into t1(id, name) values (1, 'one'); insert into t1(id, name) values (2, 'two'); insert into t1(id, name) values (3, 'three'); insert into t1(id, name) values (4, 'four'); insert into t1(id, name) values (5, 'five'); create table t2(id int, other_name varchar(5)); insert into t2(id, other_name) values (1, 'one'); insert into t2(id, other_name) values (2, 'two'); this returns everything from t1: SELECT * FROM t1 WHERE EXISTS (SELECT id FROM t2); this returns 2 rows that match: SELECT * FROM t1 WHERE id IN (SELECT id FROM t2); -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL Question
On Mon, Dec 8, 2008 at 7:06 PM, Chris [EMAIL PROTECTED] wrote: Right So, how are these different: SELECT * FROM t1 WHERE id EXISTS (SELECT id FROM t2) If there are *any* results for the subselect, the exists returns true. It's the equivalent of: select * from t1 where id is true; ie select * from t1; If there are no results for the subselect, the exists returns false, ie: select * from t1 where false; which will return nothing. to SELECT * FROM t1 WHERE id IN (SELECT id FROM t2) this returns specific id's that match. According to my understanding of the documentation, these would have the same result. No, they aren't. create table t1(id int, name varchar(5)); insert into t1(id, name) values (1, 'one'); insert into t1(id, name) values (2, 'two'); insert into t1(id, name) values (3, 'three'); insert into t1(id, name) values (4, 'four'); insert into t1(id, name) values (5, 'five'); create table t2(id int, other_name varchar(5)); insert into t2(id, other_name) values (1, 'one'); insert into t2(id, other_name) values (2, 'two'); this returns everything from t1: SELECT * FROM t1 WHERE EXISTS (SELECT id FROM t2); this returns 2 rows that match: SELECT * FROM t1 WHERE id IN (SELECT id FROM t2); Oh OK. Thanks for clearing that up. -- Postgresql php tutorials http://www.designmagick.com/ -- Tim-Hinnerk Heuer http://www.ihostnz.com -- Web Design, Hosting and free Linux Support
[PHP] PHP/mySQL question using ORDER BY with logic
Question about mySQL and PHP, when using the mySQL ORDER BY method... Basically I've got data coming from the database where a wine producer-name is a word like: Château Bahans Haut-Brion or La Chapelle de La Mission Haut-Brion or Le Clarence de Haut-Brion but I need to ORDER BY using a varient of the string: 1) If it begins with Château, don't include Chateau in the string to order by. 2) If it begins with La, don't order by La, unless the first word is Chateau, and then go ahead and order by La. Example sort: Notice how the producer as-in comes before the parenthesis, but the ORDER BY actually occurs after a re-ordering of the producer-string, using the above rules. Red: Château Bahans Haut-Brion (Bahans Haut-Brion, Château ) Red: La Chapelle de La Mission Haut-Brion (Chapelle de La Mission Haut-Brion, La ) Red: Le Clarence de Haut-Brion (Clarence de Haut-Brion, Le ) Red: Château Haut-Brion (Haut-Brion, Château ) Red: Château La Mission Haut-Brion (La Mission Haut-Brion, Château ) Red: Domaine de La Passion Haut Brion (La Passion Haut Brion, Domaine de ) Red: Château La Tour Haut-Brion (La Tour Haut-Brion, Château ) Red: Château Larrivet-Haut-Brion (Larrivet-Haut-Brion, Château ) Red: Château Les Carmes Haut-Brion (Les Carmes Haut-Brion, Château ) That logic between mySQL and PHP, I'm just not sure how to accomplish? I think it might involve a mySQL alias-technique but I could be wrong. Right now, my PHP call to generate the search is this: $query = 'SELECT * FROM wine WHERE MATCH(producer, varietal, appellation, designation, region, vineyard, subregion, country, vintage) AGAINST ( ' . $searchstring . ') ORDER BY producer LIMIT 0,100';
Re: [PHP] PHP/mySQL question using ORDER BY with logic
On Fri, 2008-10-24 at 00:18 -0400, Rob Gould wrote: Question about mySQL and PHP, when using the mySQL ORDER BY method... Basically I've got data coming from the database where a wine producer-name is a word like: Château Bahans Haut-Brion or La Chapelle de La Mission Haut-Brion or Le Clarence de Haut-Brion but I need to ORDER BY using a varient of the string: 1) If it begins with Château, don't include Chateau in the string to order by. 2) If it begins with La, don't order by La, unless the first word is Chateau, and then go ahead and order by La. Example sort: Notice how the producer as-in comes before the parenthesis, but the ORDER BY actually occurs after a re-ordering of the producer-string, using the above rules. Red: Château Bahans Haut-Brion (Bahans Haut-Brion, Château ) Red: La Chapelle de La Mission Haut-Brion (Chapelle de La Mission Haut-Brion, La ) Red: Le Clarence de Haut-Brion (Clarence de Haut-Brion, Le ) Red: Château Haut-Brion (Haut-Brion, Château ) Red: Château La Mission Haut-Brion (La Mission Haut-Brion, Château ) Red: Domaine de La Passion Haut Brion (La Passion Haut Brion, Domaine de ) Red: Château La Tour Haut-Brion (La Tour Haut-Brion, Château ) Red: Château Larrivet-Haut-Brion (Larrivet-Haut-Brion, Château ) Red: Château Les Carmes Haut-Brion (Les Carmes Haut-Brion, Château ) That logic between mySQL and PHP, I'm just not sure how to accomplish? I think it might involve a mySQL alias-technique but I could be wrong. Right now, my PHP call to generate the search is this: $query = 'SELECT * FROM wine WHERE MATCH(producer, varietal, appellation, designation, region, vineyard, subregion, country, vintage) AGAINST ( ' . $searchstring . ') ORDER BY producer LIMIT 0,100'; Maybe there's a good way to do it with the table as is... but I'm doubtful. I would create a second field that contains a pre-processed version of the name that performs stripping to achieve what you want. This could be done by a PHP script when the data is inserted into the database, or if not possible like that, then a cron job could run once in a while, check for entries with this field empty and generate it. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: mysql question #2
nihilism machine wrote:
Ok, I read the php.net info. so with this function though:
public function select_one($sql) {
$this-last_query = $sql;
$r = mysql_query($sql);
if (!$r) {
$this-last_error = mysql_error();
return false;
}
if (mysql_num_rows($r) != 1) {
return false;
}
$ret = mysql_result($r, 0);
mysql_free_result($r);
if ($this-auto_slashes) {
return stripslashes($ret);
} else {
return $ret;
}
}
how can i get the contents of a column in the returned row say for
something called Email as the column name. here is my code now:
HUH?
// Attempt to login a user
public function CheckValidUser($Email, $Password) {
$PasswordEncoded = $this-encode($Password);
$sql = SELECT * FROM CMS_Users WHERE Email='$Email' AND
Password='$PasswordEncoded';
$result = $this-DB-select_one($sql);
if ($result) {
// User info stored in Sessions
$_SESSION['Status'] = loggedIn;
$_SESSION['ID'] = $row['ID'];
$_SESSION['Email'] = $row['Email'];
$_SESSION['AdminLevel'] = $row['AdminLevel'];
$_SESSION['FirstName'] = $row['FirstName'];
$_SESSION['LastName'] = $row['LastName'];
return true;
} else {
return false;
}
}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP/mySQL question about groups
You are correct, but that is what I meant by all columns (all those in the query, and therefore subject to the distinct). At this point different database engines work differently, without an order by, some will use the primary sequence, some will scan table space (in other words, without an order by [or group by], some engines will give you a performance hit). It's always good practice to write your queries to help your DB engine find an index. Warren -Original Message- From: Andrew Ballard [mailto:[EMAIL PROTECTED] Sent: Thursday, February 07, 2008 7:16 AM To: PHP General list Subject: Re: [PHP] PHP/mySQL question about groups On Feb 7, 2008 1:20 AM, Warren Vail [EMAIL PROTECTED] wrote: I did some looking into performance issues many years ago at company that developed and marketed another database server, comparing the query plan to the actual code, and a query plan usually shows the processes that consume the major amount of time, disk I/O, index or table searches and such, but doesn't show time consumed comparing, discriminating, and totaling, mostly because they are negligible. On the other hand distinct depends on comparison of all columns and will have no help in reducing row counts unless accompanied by an order by clause, where as group by implys an orderby and can be faster if indexes are available for use in row ordering, and while the same totaling occurs, comparison is limited to the columns specified in the group by. Does DISTINCT really compare all columns? I would think it would only compare the columns explicitly included in the SELECT clause. The biggest impact on one or the other would be a well placed index, but for the most part they should be about the same. Warren Vail I have seen discussions where in GROUP BY can be faster than DISTINCT depending on whether the query uses things like correlated subqueries, but this is not applicable in the current case. At any rate, I don't want to stray the conversation any further away than I already have. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/mySQL question about groups
On Feb 7, 2008 1:20 AM, Warren Vail [EMAIL PROTECTED] wrote: I did some looking into performance issues many years ago at company that developed and marketed another database server, comparing the query plan to the actual code, and a query plan usually shows the processes that consume the major amount of time, disk I/O, index or table searches and such, but doesn't show time consumed comparing, discriminating, and totaling, mostly because they are negligible. On the other hand distinct depends on comparison of all columns and will have no help in reducing row counts unless accompanied by an order by clause, where as group by implys an orderby and can be faster if indexes are available for use in row ordering, and while the same totaling occurs, comparison is limited to the columns specified in the group by. Does DISTINCT really compare all columns? I would think it would only compare the columns explicitly included in the SELECT clause. The biggest impact on one or the other would be a well placed index, but for the most part they should be about the same. Warren Vail I have seen discussions where in GROUP BY can be faster than DISTINCT depending on whether the query uses things like correlated subqueries, but this is not applicable in the current case. At any rate, I don't want to stray the conversation any further away than I already have. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP/mySQL question about groups
Let's say I have a PHP-based wine application, and it's taking a set of mySQL data that looks like this: wineidname size 123 Silver Oak 750ML 123 Silver Oak 1.5L 123 Silver Oak 1.5L 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 1.5L If I do a: Select * from wine where name = 'Silver Oak' GROUP BY 'wineid' I'd get: Silver Oak However, what I'd REALLY like to return is: Silver Oak 750ML Silver Oak 1.5L I'd like the groupby to group by wineid, BUT ALSO separate the groups by 'size'. So there'd be a '750ML' group, and a '1.5L' group Can anyone tell me how I'd do that? I'm hoping I don't have to write a PHP script that loops through the results and separates things manually. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/mySQL question about groups
On Feb 6, 2008 8:46 PM, Rob Gould [EMAIL PROTECTED] wrote: Let's say I have a PHP-based wine application, and it's taking a set of mySQL data that looks like this: wineidname size 123 Silver Oak 750ML 123 Silver Oak 1.5L 123 Silver Oak 1.5L 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 1.5L i think you can just do group by wineid, size -nathan
RE: [PHP] PHP/mySQL question about groups
Select * from wine where name = 'Silver Oak' GROUP BY wineid,size bastien Date: Wed, 6 Feb 2008 17:46:52 -0800 From: [EMAIL PROTECTED] To: php-general@lists.php.net Subject: [PHP] PHP/mySQL question about groups Let's say I have a PHP-based wine application, and it's taking a set of mySQL data that looks like this: wineid name size 123 Silver Oak 750ML 123 Silver Oak 1.5L 123 Silver Oak 1.5L 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 1.5L If I do a: Select * from wine where name = 'Silver Oak' GROUP BY 'wineid' I'd get: Silver Oak However, what I'd REALLY like to return is: Silver Oak 750ML Silver Oak 1.5L I'd like the groupby to group by wineid, BUT ALSO separate the groups by 'size'. So there'd be a '750ML' group, and a '1.5L' group Can anyone tell me how I'd do that? I'm hoping I don't have to write a PHP script that loops through the results and separates things manually. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _
Re: [PHP] PHP/mySQL question about groups
On Feb 6, 2008 8:46 PM, Rob Gould [EMAIL PROTECTED] wrote: Let's say I have a PHP-based wine application, and it's taking a set of mySQL data that looks like this: wineidname size 123 Silver Oak 750ML 123 Silver Oak 1.5L 123 Silver Oak 1.5L 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 750ML 456 Liberty School 1.5L If I do a: Select * from wine where name = 'Silver Oak' GROUP BY 'wineid' I'd get: Silver Oak However, what I'd REALLY like to return is: Silver Oak 750ML Silver Oak 1.5L I'd like the groupby to group by wineid, BUT ALSO separate the groups by 'size'. So there'd be a '750ML' group, and a '1.5L' group Can anyone tell me how I'd do that? I'm hoping I don't have to write a PHP script that loops through the results and separates things manually. That's something MySQL will allow that IMO it should not. Being able to use SELECT * and GROUP BY at the same time can create confusion as it did here. The other suggestions would probably work, but a good rule of thumb is not to use any columns in the SELECT clause unless they are either included in the GROUP BY clause or else use one of the aggregate functions like COUNT, SUM, AVG, etc. (I'm not sure of the actual SQL standard on this point, but SQL Server insists on it.) SELECT name, size FROMwine GROUP BY name, size Of course, in this case you could just avoid all the confusion with this statement as well: SELECT DISTINCT name, size FROMwine Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/mySQL question about groups
On Feb 6, 2008 10:59 PM, Andrew Ballard [EMAIL PROTECTED] wrote: Of course, in this case you could just avoid all the confusion with this statement as well: SELECT DISTINCT name, size FROMwine im not sure why, but i think distinct is typically way slower than group by. -nathan
Re: [PHP] PHP/mySQL question about groups
On Feb 6, 2008 11:20 PM, Nathan Nobbe [EMAIL PROTECTED] wrote: On Feb 6, 2008 10:59 PM, Andrew Ballard [EMAIL PROTECTED] wrote: Of course, in this case you could just avoid all the confusion with this statement as well: SELECT DISTINCT name, size FROMwine im not sure why, but i think distinct is typically way slower than group by. -nathan I can't really say for MySQL, but in my experience I'd say it depends. It seems to me that for this case they should be about the same, as it's always been my thinking that GROUP BY did a DISTINCT implicitly. However, I don't really know the internals of any DB platform so I can't confirm that. I ran DESCRIBE on a couple different tables, and they both return the same plan. I don't see any notable difference in the queries either. However, I'll leave it to the OP to test and see if one is better for his question. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP/mySQL question about groups
I did some looking into performance issues many years ago at company that developed and marketed another database server, comparing the query plan to the actual code, and a query plan usually shows the processes that consume the major amount of time, disk I/O, index or table searches and such, but doesn't show time consumed comparing, discriminating, and totaling, mostly because they are negligible. On the other hand distinct depends on comparison of all columns and will have no help in reducing row counts unless accompanied by an order by clause, where as group by implys an orderby and can be faster if indexes are available for use in row ordering, and while the same totaling occurs, comparison is limited to the columns specified in the group by. The biggest impact on one or the other would be a well placed index, but for the most part they should be about the same. Warren Vail -Original Message- From: Andrew Ballard [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 06, 2008 8:41 PM To: PHP General list Subject: Re: [PHP] PHP/mySQL question about groups On Feb 6, 2008 11:20 PM, Nathan Nobbe [EMAIL PROTECTED] wrote: On Feb 6, 2008 10:59 PM, Andrew Ballard [EMAIL PROTECTED] wrote: Of course, in this case you could just avoid all the confusion with this statement as well: SELECT DISTINCT name, size FROMwine im not sure why, but i think distinct is typically way slower than group by. -nathan I can't really say for MySQL, but in my experience I'd say it depends. It seems to me that for this case they should be about the same, as it's always been my thinking that GROUP BY did a DISTINCT implicitly. However, I don't really know the internals of any DB platform so I can't confirm that. I ran DESCRIBE on a couple different tables, and they both return the same plan. I don't see any notable difference in the queries either. However, I'll leave it to the OP to test and see if one is better for his question. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: mysql question
Man-wai Chang wrote: I tried to post to mysql.general, but the message never appeared. So I am trying my luck here. How could I build an index for a table created using the CREATE TEMPORARY TABLE ... SELECT ... FROM ... syntax, using an account without the privilege to use ALTER TABLE? See the docs. CREATE INDEX syntax: http://dev.mysql.com/doc/refman/5.0/en/create-index.html About privileges: http://dev.mysql.com/doc/refman/5.0/en/grant.html You didn't send much info along, so I assume you're using some version of MySQL 5.0. HTH -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: mysql question
You didn't send much info along, so I assume you're using some version of MySQL 5.0. Thanks. I figured it out (doesn't need 5.0): CREATE TEMPORARY haha ( KEY (field1), KEY (field2) ) AS SELECT po_no, po_date, item_no FROM . WHERE Then I don't need the privilege for ALTER TABLE. -- .~. Might. Courage. Vision. Sincerity. http://www.linux-sxs.org / v \ Simplicity is Beauty! May the Force and Farce be with you! /( _ )\ (Fedora Core 4) Linux 2.6.17-1.2142_FC4 ^ ^ 17:33:02 up 165 days 1:17 1 user load average: 0.03 0.08 0.08 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: mysql question
CREATE TEMPORARY haha ( KEY (field1), KEY (field2) ) AS SELECT po_no, po_date, item_no FROM . WHERE Then I don't need the privilege for ALTER TABLE. I meant I could build index on a temp table without the privilege to use ALTER TABLE by making the index definitions as part of the CREATE TEMPORARY statement. -- .~. Might. Courage. Vision. Sincerity. http://www.linux-sxs.org / v \ Simplicity is Beauty! May the Force and Farce be with you! /( _ )\ (Fedora Core 4) Linux 2.6.17-1.2142_FC4 ^ ^ 17:40:01 up 165 days 1:24 1 user load average: 0.02 0.04 0.06 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] php - mysql question
Dave Goodchild wrote: Hi all. I am writing a web app with a mysql back end and there is every chance one of the tables may have to handle 56+ million records. I am no mysql expert but has anyone else here ever handled that volume of data, and if so, any suggestions or caveats? The tables will of course be correctly indexed and the database normalised. There's no reason why it can't but the mysql list will be better to ask (because they could tell you what to do.. eg how much memory, what disks you should look to get etc to get decent performance). Even with indexes and normalized data (which in some cases makes performance worse with all the joins you have to do) you'll need to tweak your server / settings to get something resembling reasonable performance. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] php - mysql question
Hi all. I am writing a web app with a mysql back end and there is every chance one of the tables may have to handle 56+ million records. I am no mysql expert but has anyone else here ever handled that volume of data, and if so, any suggestions or caveats? The tables will of course be correctly indexed and the database normalised. I know it's really a mysql question so apologies. -- http://www.web-buddha.co.uk
[PHP] PHP/MySQL question
I have a php form that pulls data from the database (hence the problems)
I need to do an OR search on three columns, and AND the rest, anyone
have a good way to do this? So far my searching on the MySQL lists have
been fruitless more then anything, and I figured we've probably come
across this ourselves at some point.
Here's the code I have so far:
$query = select * from honorclub;
if ($dead != || $unknown != || $name != || $county != ||
$year != || $countynow != || $state != )
{$query .= WHERE ;}
if ($dead == )
{$query .= `Deceased`='N' AND;}
if ($unknown == )
{$query .= `USPS_Unknown`='N' AND ;}
if ($name != )
{$query .= `Last_Name` like '$name%' AND ;}
if ($county != )
{$query .= `County` like '$county' AND ;}
if ($year != )
{$query .= `Year_Tapped` like '$year' AND ;}
if ($countynow != )
{$query .= `County_Now` like '$countynow' AND ;}
if ($state != )
{$query .= `State_Now` like '$state' AND ;}
$query = rtrim($query, AND);
$query .= order by $order_by;
What needs to be 'OR' is the $name section to be:
$query .= `Last_Name` like '%$name%' OR `First_Name` like '%$name%' OR
`Maiden_Name` like '%$name%';
Thanks,
Wolf
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP/MySQL question
[snip] I need to do an OR search on three columns, and AND the rest, anyone have a good way to do this? So far my searching on the MySQL lists have been fruitless more then anything, and I figured we've probably come across this ourselves at some point. [/snip] More of a MySQL question, but easily enough answered; Always group the OR with parenthese and the AND individually. Write the query and test with MySQL before placing into PHP code; SELECT * FROM `table` WHERE (`foo` = 'bar' OR `foo` = 'glorp' OR `sqirk` = 'glorp') AND `today` = CURDATE() AND `userID` = 'Marvin' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MySQL question
On Mon, June 5, 2006 10:32 am, Wolf wrote:
I have a php form that pulls data from the database (hence the
problems)
I need to do an OR search on three columns, and AND the rest, anyone
have a good way to do this? So far my searching on the MySQL lists
have
been fruitless more then anything, and I figured we've probably come
across this ourselves at some point.
Here's the code I have so far:
I'm confused just by the indenting (or lack thereof) but one standard
technique is to start off with a yeast such as:
$query = select * from honorclub ; //Fix * to be actual columns!
$query .= WHERE 1 ;
if ($dead != ...){
$query .= AND Deceased = 'N' ;
}
$query .= AND (First_name like '%$name%' or Last_name like '%$name%'
) ;
$query = select * from honorclub;
if ($dead != || $unknown != || $name != || $county != ||
$year != || $countynow != || $state != )
{$query .= WHERE ;}
if ($dead == )
{$query .= `Deceased`='N' AND;}
if ($unknown == )
{$query .= `USPS_Unknown`='N' AND ;}
if ($name != )
{$query .= `Last_Name` like '$name%' AND ;}
if ($county != )
{$query .= `County` like '$county' AND ;}
if ($year != )
{$query .= `Year_Tapped` like '$year' AND ;}
if ($countynow != )
{$query .= `County_Now` like '$countynow' AND ;}
if ($state != )
{$query .= `State_Now` like '$state' AND ;}
$query = rtrim($query, AND);
$query .= order by $order_by;
What needs to be 'OR' is the $name section to be:
$query .= `Last_Name` like '%$name%' OR `First_Name` like '%$name%'
OR
`Maiden_Name` like '%$name%';
Thanks,
Wolf
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MySQL question
Thanks guys, I knew it was a stupid Q when I sent it, but I had another one where I encapsulated them in () blow up on me, so I figured if I asked and it was the same answer then I was on the right track. And so far all the tests have shown positive. :) Wolf More of a MySQL question, but easily enough answered; Always group the OR with parenthese and the AND individually. Write the query and test with MySQL before placing into PHP code; SELECT * FROM `table` WHERE (`foo` = 'bar' OR `foo` = 'glorp' OR `sqirk` = 'glorp') AND `today` = CURDATE() AND `userID` = 'Marvin' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] php mySql question
Have you tried doing a search for the text php5 in the php.ini file that sits in your c:\windows folder? C. -Original Message- From: Ned Kotter [mailto:[EMAIL PROTECTED] Sent: 26 July 2005 04:03 To: php-general@lists.php.net Subject: [PHP] php mySql question I have installed php 5.0.4 on my windows 2000, IIS 6.0 server. PHP works but when I try to connect to MySQL I get the Fatal error: Call to undefined function mysql_connect(). I have uncommented the line in the php.ini file that says 'extension=php_mysql.dll'. I have path variables set for both c:\php and c:\php\ext. One very peculiar thing that I noticed when I ran phpinfo() is that it shows the extension_dir is set to c:\php5 even though in my php.ini file it is set to c:\php. I have a feeling that this is where the problem exists. Any advice would be appreciated. Thanks, NK __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] php mySql question
Yes, it is quite possible that you have more than one php.ini file. Check this and delete as appropriate. Shaw, Chris - Accenture [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] ... Have you tried doing a search for the text php5 in the php.ini file that sits in your c:\windows folder? C. -Original Message- From: Ned Kotter [mailto:[EMAIL PROTECTED] Sent: 26 July 2005 04:03 To: php-general@lists.php.net Subject: [PHP] php mySql question I have installed php 5.0.4 on my windows 2000, IIS 6.0 server. PHP works but when I try to connect to MySQL I get the Fatal error: Call to undefined function mysql_connect(). I have uncommented the line in the php.ini file that says 'extension=php_mysql.dll'. I have path variables set for both c:\php and c:\php\ext. One very peculiar thing that I noticed when I ran phpinfo() is that it shows the extension_dir is set to c:\php5 even though in my php.ini file it is set to c:\php. I have a feeling that this is where the problem exists. Any advice would be appreciated. Thanks, NK __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] php mySql question
Ned Kotter wrote: I have installed php 5.0.4 on my windows 2000, IIS 6.0 server. PHP works but when I try to connect to MySQL I get the Fatal error: Call to undefined function mysql_connect(). I have uncommented the line in the php.ini file that says 'extension=php_mysql.dll'. I have path variables set for both c:\php and c:\php\ext. One very peculiar thing that I noticed when I ran phpinfo() is that it shows the extension_dir is set to c:\php5 even though in my php.ini file it is set to c:\php. I have a feeling that this is where the problem exists. Any advice would be appreciated. Look at your phpinfo page and make sure it's reading the ini file that you think it is. -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] php mySql question
I have installed php 5.0.4 on my windows 2000, IIS 6.0 server. PHP works but when I try to connect to MySQL I get the Fatal error: Call to undefined function mysql_connect(). I have uncommented the line in the php.ini file that says 'extension=php_mysql.dll'. I have path variables set for both c:\php and c:\php\ext. One very peculiar thing that I noticed when I ran phpinfo() is that it shows the extension_dir is set to c:\php5 even though in my php.ini file it is set to c:\php. I have a feeling that this is where the problem exists. Any advice would be appreciated. Thanks, NK __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[PHP] Re: MySQL question
I would have at least expected it give me 'margaret atwood' before it gives me
'margaret' and then 'atwood'.
Sorry, MySQL question. Any takers? If I search for 'margaret atwood', I get results
in no real structured heirachy. Any thoughts?
SELECT ST,BT,AT FROM ccl_main WHERE MATCH (ST,BT,AT) AGAINST ('margaret atwood' IN
BOOLEAN MODE);h1Search font color=blueccl.ccl_main/font For: font
color=blueimargaret atwood/i/font - 275 record(s) found/h1
John
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: MySQL question
John Taylor-Johnston wrote:
Sorry, MySQL question. Any takers? If I search for 'margaret atwood', I
get results in no real structured heirachy. Any thoughts?
SELECT ST,BT,AT FROM ccl_main WHERE MATCH (ST,BT,AT) AGAINST ('margaret
atwood' IN BOOLEAN MODE);h1Search font
color=blueccl.ccl_main/font For: font color=blueimargaret
atwood/i/font - 275 record(s) found/h1
John
The results from IN BOOLEAN MODE are not sorted in order of relevance, from
what I can see in the docs. More info can be found at
http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html
Depending on what you are trying to achieve, BOOLAEN MODE may not be what
you want.
David
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] another mysql question, sorry
I am sorry to post another mysql question, but this one is design related. I need to create a relationship between two different tables in two different databases. I feel like I am commiting a sin doing so, but it makes sence at the application level, and to redesign everything would be disastrous. Any comments? Thanks Christian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] another mysql question, sorry
I am sorry to post another mysql question, but this one is design related. I need to create a relationship between two different tables in two different databases. I feel like I am commiting a sin doing so, but it makes sence at the application level, and to redesign everything would be disastrous. Any comments? Thanks I don't see any huge problem with this. I've done it several times in my user authentication code. I'm allowing (for some users) a single UID/PW pair for access to each of my sites but each of those sites has their own user info (Name, Address, etc) table. Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] another mysql question, sorry
Christian Calloway wrote: I am sorry to post another mysql question, but this one is design related. I need to create a relationship between two different tables in two different databases. I feel like I am commiting a sin doing so, but it makes sence at the application level, and to redesign everything would be disastrous. Any comments? Thanks I can see your point about it feeling like committing a sin, and I certainly wouldn't recommend doing it with out a good reason but if it seems to make sense for your application, why not? Mysql specifically provides the syntax to do a join between tables in different databases so they must think there is a good reason to do allow it. Even if it is an out of the ordinary case that calls for it. Chris W -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] A MySQL question
Ok, I want to delete only 1 row off of a database table... Example: I have a table with columns user and item... Lets say the table contains the following rows (user | item): 582-668243 | Toothbrush 582-668243 | Toothbrush 582-668243 | Toothbrush 582-668243 | Trash can 582-668243 | Trash can 582-668243 | Something else 582-668243 | Something else 582-668243 | Something else 582-668243 | Something else 720-387690 | Dog treats 720-387690 | Car 720-387690 | Car 720-387690 | Toothbrush 720-387690 | Toothbrush Ok, user 582-668243 is buying a lot, eh? LoL Anyway, how can I remove only 1 Toothbrush from user 582-668243? I just want the query... I've been trying to find it out for a few hours now... Thanks in advance! -- - Zavaboy [EMAIL PROTECTED] www.zavaboy.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL question
--- zavaboy [EMAIL PROTECTED] wrote: Lets say the table contains the following rows (user | item): 582-668243 | Toothbrush 582-668243 | Toothbrush 582-668243 | Toothbrush ... Anyway, how can I remove only 1 Toothbrush from user 582-668243? With the information you provided, you can't. You should never have multiple rows in the database that are non-unique, and this is one reason why. You need a primary key. I just want the query You should rather spend your time learning some database fundamentals rather than asking this list (not a database list, in fact) for spoon-fed answers. You will appreciate the knowledge more than the answer. Chris = Become a better Web developer with the HTTP Developer's Handbook http://httphandbook.org/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL question
zavaboy [EMAIL PROTECTED] wrote: Ok, I want to delete only 1 row off of a database table... Example: I have a table with columns user and item... Lets say the table contains the following rows (user | item): 582-668243 | Toothbrush 582-668243 | Toothbrush Note the 'LIMIT' part in the delete statment. http://www.mysql.com/doc/en/DELETE.html [...] Thanks in advance! -- - Zavaboy [EMAIL PROTECTED] www.zavaboy.com HTH Curt -- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A MySQL question
Ok, I added a primary key. I figured it out and I have it working... Thanks! Chris Shiflett [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] --- zavaboy [EMAIL PROTECTED] wrote: Lets say the table contains the following rows (user | item): 582-668243 | Toothbrush 582-668243 | Toothbrush 582-668243 | Toothbrush ... Anyway, how can I remove only 1 Toothbrush from user 582-668243? With the information you provided, you can't. You should never have multiple rows in the database that are non-unique, and this is one reason why. You need a primary key. I just want the query You should rather spend your time learning some database fundamentals rather than asking this list (not a database list, in fact) for spoon-fed answers. You will appreciate the knowledge more than the answer. Chris = Become a better Web developer with the HTTP Developer's Handbook http://httphandbook.org/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] A MySQL question
You have made a mistake in your database design. Every table should have a primary key that allows you to uniquely identify any single row in the table. You do not have a primary key, hence when you want to delete a single row from a series of duplicates, you can't do it. Unlike oracle, mysql has no rowid. -Original Message- From: zavaboy [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 2:13 PM To: [EMAIL PROTECTED] Subject: [PHP] A MySQL question Ok, I want to delete only 1 row off of a database table... Example: I have a table with columns user and item... Lets say the table contains the following rows (user | item): 582-668243 | Toothbrush 582-668243 | Toothbrush 582-668243 | Toothbrush 582-668243 | Trash can 582-668243 | Trash can 582-668243 | Something else 582-668243 | Something else 582-668243 | Something else 582-668243 | Something else 720-387690 | Dog treats 720-387690 | Car 720-387690 | Car 720-387690 | Toothbrush 720-387690 | Toothbrush Ok, user 582-668243 is buying a lot, eh? LoL Anyway, how can I remove only 1 Toothbrush from user 582-668243? I just want the query... I've been trying to find it out for a few hours now... Thanks in advance! -- - Zavaboy [EMAIL PROTECTED] www.zavaboy.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Part PHP part MySQL question
Hi, I have a hockey database with players names in it and I want to be able to update their stats one after the other. To be more clear - when the page is first entered I want the first players name to automatically appear showing his current stats (this will be in a form). Then you can update the record and write it back to the database, then the next player will show up with his stats showing, and the process continues until all the players have been done. Setting up the database and forms is no problem, but I'm not sure how I would keep count of what player has been done so the next one automatically appears (and if there should be a power failure orWindows crashes, or whatever else - I want the process to start at the last player being updated - I don't want to have to start again from player one). I'm not sure if this is more a PHP or MySQL question - or both - so I have sent it to both lists. Thanks Beauford -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Part PHP part MySQL question
I'd add a timestamp to the database to automatically track when a record was last modified. Then poll the database for the oldest one every time you call the form, loading it with his stats. Every time you update a player's data, his timestamp will become the newest, the form then gets the next oldest. Lather, rinse, repeat. kind regards, bill Beauford.2002 wrote: Hi, I have a hockey database with players names in it and I want to be able to update their stats one after the other. To be more clear - when the page is first entered I want the first players name to automatically appear showing his current stats (this will be in a form). Then you can update the record and write it back to the database, then the next player will show up with his stats showing, and the process continues until all the players have been done. Setting up the database and forms is no problem, but I'm not sure how I would keep count of what player has been done so the next one automatically appears (and if there should be a power failure orWindows crashes, or whatever else - I want the process to start at the last player being updated - I don't want to have to start again from player one). I'm not sure if this is more a PHP or MySQL question - or both - so I have sent it to both lists. Thanks Beauford -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Part PHP part MySQL question
I have a hockey database with players names in it and I want to be
able to
update their stats one after the other. To be more clear - when the
page
is
first entered I want the first players name to automatically appear
showing
his current stats (this will be in a form). Then you can update the
record
and write it back to the database, then the next player will show up
with
his stats showing, and the process continues until all the players
have
been
done. Setting up the database and forms is no problem, but I'm not
sure
how
I would keep count of what player has been done so the next one
automatically appears (and if there should be a power failure
orWindows
crashes, or whatever else - I want the process to start at the last
player
being updated - I don't want to have to start again from player one).
I'm
not sure if this is more a PHP or MySQL question - or both - so I have
sent
it to both lists.
Each player has a unique number, right? Probably an auto_increment
column? Use that to your advantage.
SELECT * FROM your_table WHERE id {$_COOKIE['id']} ORDER BY id ASC
LIMIT 1
When you load the first player, $_COOKIE['id'] isn't set, so you'll get
the player with the lowest ID number. Now you submit the form, process
the data, and save the ID of the player you just edited into a cookie
named 'id'. Now when you run the above query, you'll get the next
highest ID number based on the last ID number you saved in a cookie.
Keep track of the ID number in a cookie so that you'll always get the
next ID whenever you come to the site. Have a method where you can
reset the ID if you need to, also. Once the above query returns no
rows then there is no one left to edit and you'll want to reset it also.
---John W. Holmes...
PHP Architect - A monthly magazine for PHP Professionals. Get your copy
today. http://www.phparch.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] OT - mysql question...
Sorry for the OT post, quick mysql question... I've found a couple of cool utilites that will create basic forms from a mysql table (add/edit/delete/search), which look they could save some time coding. However, I can't connect with them because my server is setup as localhost. I How do you create a user/host configuration that will allow you to connect to mysql other than via localhost? Thanks in advance, Kelly
Re: [PHP] OT - mysql question...
http://www.mysql.com/doc/en/Adding_users.html http://www.mysql.com/documentation/lists.html Kelly Meeks wrote: Sorry for the OT post, quick mysql question... I've found a couple of cool utilites that will create basic forms from a mysql table (add/edit/delete/search), which look they could save some time coding. However, I can't connect with them because my server is setup as localhost. I How do you create a user/host configuration that will allow you to connect to mysql other than via localhost? Thanks in advance, Kelly -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] quick mysql question
where can i find info on setting up a mysql database on a remote server? i know how to set them up on a local machine, but i can't figure out how to get to the command line setting on a remote server... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] quick mysql question
Hi Doug, where can i find info on setting up a mysql database on a remote server? i know how to set them up on a local machine, but i can't figure out how to get to the command line setting on a remote server... me@mybox:~$ ssh my.remote.server Or am I missing something? Cheers Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] quick mysql question
jon - thanks for the reply. i tried this: prompt ssh server.net i got an open failed: no such file or directoy does that mean that ssh is not enabled on the server? i pinged the server and it said it was alive... Jon Haworth wrote: Hi Doug, where can i find info on setting up a mysql database on a remote server? i know how to set them up on a local machine, but i can't figure out how to get to the command line setting on a remote server... me@mybox:~$ ssh my.remote.server Or am I missing something? Cheers Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
