Re: [PHP] MySQL Query Help
On Sun, 21 Nov 2010 11:19:04 -0700 "Ben Miller" wrote: > > To help clarify - the 3 tables look something like the following (tableName > => column,column,column...): > > Products => product_id,product_name,product_description... (key = > product_id) > Criteria => criteria_id,criteria_title,criteria_text,... (key = > criteria_id) > Criteria_values => product_id,criteria_id,criteria_value,... (key = > product_id & criteria_id) > > The user selects up to X product_id's to compare, stored in > $selected_products. > > I then need to get each criteria_title and criteria_text from > table(criteria) where there is a matching criteria_id in > table(criteria_values) for each/all $selected_products, also returning the > criteria_value for each $selected_products, ultimately ending up with an > array or object that looks something like: > > (Assuming the user selected Product A (product_id=1), Product B > (product_id=2) and Product C (product_id=3) > > criteria => Array ( > [$criteria_id] => Array ( > [title] => query_row[criteria_title] > [text] => query_row[criteria_text] > [values] => Array ( > [1] => Product A's value for this criteria > [2] => Product B's value for this criteria > [3] => Product C's value for this criteria > ) > ) > [$criteria_id] => Array ( > . > ) > ) > > Again, displaying only/all criteria where there is a matching value for > each/all $selected_products > > Thanks again, > Ben > > You should probably select all relevant rows for each product, without checking that a given criteria has a matching value for each product. Use php to filter out the criteria which do not apply to all products. -- Simcha Younger -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help
SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT
JOIN criteria c ON cv.key=c.key WHERE c.value IS NOT NULL
Hard to answer without more detail, but I am guessing the answer will be
something like the above. Your question makes it hard to understand whether
c or cv is joined to p. So swap em around if I misunderstood.
iPhone 4. It rocks!
On Nov 21, 2010, at 1:37 AM, Simcha Younger wrote:
> On Sat, 20 Nov 2010 13:54:29 -0700
> "Ben Miller" wrote:
>
>> Hi,
>>
>> I'm building a website for a client in which I need to compare their
>> products, side-by-side, but only include criteria for which all
>> selected products have a value for that criteria.
>>
>> In my database (MySQL), I have a tables named "products","criteria"
>> and "criteria_values"
>>
>> If I have something like
>>
>> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) //
>> All products selected for comparison by the user
>>
>> I need to get only rows from "criteria" where there is a row in
>> "criteria_values" matching "criteria.criteria_id" for each
>> $selected_product
>> - in other words, if any of the $selected_product does not have a row
>> in "criteria_values" that matches "criteria.criteria_id", that
>> criteria would not be returned. I hope that makes sense.
>
> It would be a lot easier to think about this if you could provide the
table structure or create table statements.
>
> If I understood correctly, you have products which reference a criteria ID
which has no matching value. If this is the problem you have a to first take
care of the integrity of your data, as this should never happen.
>
To help clarify - the 3 tables look something like the following (tableName
=> column,column,column...):
Products => product_id,product_name,product_description... (key =
product_id)
Criteria => criteria_id,criteria_title,criteria_text,... (key =
criteria_id)
Criteria_values => product_id,criteria_id,criteria_value,... (key =
product_id & criteria_id)
The user selects up to X product_id's to compare, stored in
$selected_products.
I then need to get each criteria_title and criteria_text from
table(criteria) where there is a matching criteria_id in
table(criteria_values) for each/all $selected_products, also returning the
criteria_value for each $selected_products, ultimately ending up with an
array or object that looks something like:
(Assuming the user selected Product A (product_id=1), Product B
(product_id=2) and Product C (product_id=3)
criteria => Array (
[$criteria_id] => Array (
[title] => query_row[criteria_title]
[text] => query_row[criteria_text]
[values] => Array (
[1] => Product A's value for this criteria
[2] => Product B's value for this criteria
[3] => Product C's value for this criteria
)
)
[$criteria_id] => Array (
.
)
)
Again, displaying only/all criteria where there is a matching value for
each/all $selected_products
Thanks again,
Ben
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT JOIN
criteria c ON cv.key=c.key WHERE c.value IS NOT NULL
Hard to answer without more detail, but I am guessing the answer will be
something like the above. Your question makes it hard to understand whether c
or cv is joined to p. So swap em around if I misunderstood.
iPhone 4. It rocks!
On Nov 21, 2010, at 1:37 AM, Simcha Younger wrote:
> On Sat, 20 Nov 2010 13:54:29 -0700
> "Ben Miller" wrote:
>
>> Hi,
>>
>> I'm building a website for a client in which I need to compare their
>> products, side-by-side, but only include criteria for which all selected
>> products have a value for that criteria.
>>
>> In my database (MySQL), I have a tables named "products","criteria" and
>> "criteria_values"
>>
>> If I have something like
>>
>> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
>> products selected for comparison by the user
>>
>> I need to get only rows from "criteria" where there is a row in
>> "criteria_values" matching "criteria.criteria_id" for each $selected_product
>> - in other words, if any of the $selected_product does not have a row in
>> "criteria_values" that matches "criteria.criteria_id", that criteria would
>> not be returned. I hope that makes sense.
>
> It would be a lot easier to think about this if you could provide the table
> structure or create table statements.
>
> If I understood correctly, you have products which reference a criteria ID
> which has no matching value. If this is the problem you have a to first take
> care of the integrity of your data, as this should never happen.
>
>
> --
> Simcha Younger
>
> --
> 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] MySQL Query Help
On Sat, 20 Nov 2010 13:54:29 -0700
"Ben Miller" wrote:
> Hi,
>
> I'm building a website for a client in which I need to compare their
> products, side-by-side, but only include criteria for which all selected
> products have a value for that criteria.
>
> In my database (MySQL), I have a tables named "products","criteria" and
> "criteria_values"
>
> If I have something like
>
> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
> products selected for comparison by the user
>
> I need to get only rows from "criteria" where there is a row in
> "criteria_values" matching "criteria.criteria_id" for each $selected_product
> - in other words, if any of the $selected_product does not have a row in
> "criteria_values" that matches "criteria.criteria_id", that criteria would
> not be returned. I hope that makes sense.
It would be a lot easier to think about this if you could provide the table
structure or create table statements.
If I understood correctly, you have products which reference a criteria ID
which has no matching value. If this is the problem you have a to first take
care of the integrity of your data, as this should never happen.
--
Simcha Younger
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help
Since we are just tossing out development environments.
We moved to Aptana in conjunction with TortoiseSVN for a team environment
development timelines dropped.
Personally I do not feel any gui editor makes you a better programmer, maybe
you understand the fundamentals a little less.
Not that anything so far has been an answer to your question.
Developing mysql statements that not only require minimum resources but that
are highly effective. Take a lot of trial and error.
I feel there is no better tool in my mind to test query concepts than
NaviCat.
Not only is the product very user friendly but as a senior developer it
gives me more insight into the impact the query has on my servers.
Always think longevity of the product you are producing.
Imagine one day you have 650,000 products how will the query impact the
service you have written.
I feel as a certified MySQL DBA you should understand that what works today
may NOT be the best choice in query statements for the future.
Always analyze your query statements for query length and system resources
requirements.
Depending on the structure of your database/tables/fields your query may be
achieved many different ways.
I might suggest you try an extended select statement.
"SELECT product from sometable WHERE product='$array1' AND product=(SELECT
product_name from sometable where other matching critera)";
By extending or what some may call concating the statement the return is
more effective.
Richard L. Buskirk
-Original Message-
From: Ben Miller [mailto:biprel...@gmail.com]
Sent: Saturday, November 20, 2010 3:54 PM
To: 'php-general'
Subject: [PHP] MySQL Query Help
Hi,
I'm building a website for a client in which I need to compare their
products, side-by-side, but only include criteria for which all selected
products have a value for that criteria.
In my database (MySQL), I have a tables named "products","criteria" and
"criteria_values"
If I have something like
$selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
products selected for comparison by the user
I need to get only rows from "criteria" where there is a row in
"criteria_values" matching "criteria.criteria_id" for each $selected_product
- in other words, if any of the $selected_product does not have a row in
"criteria_values" that matches "criteria.criteria_id", that criteria would
not be returned. I hope that makes sense.
I've played around with a few join queries, but none have given the desired
results. Best I've been able to come up with so far is to query "criteria"
for each DISTINCT(criteria_id) and then run through each $selected_product
to make sure each has a criteria_value with a matching criteria_id,
eliminating any criteria where the number of criteria_values <
count($selected_product), but this seems pretty inefficient.
Thanks in advance for any help.
Ben Miller
--
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] MySQL Query Help
I'm going to jump in and throw in my 2 cents...
Have you used dreamweaver?
I would suggest Dreamweaver to any new programmer beginning php/mysql.
It helped me out tremendously in the beginning. I'm not an advanced programmer
with hand coding classes yet, but I can get any job completed for clients with
dreamweaver. Custom content management systems, image galleries from mysql
etc...
Give it a try, It lets you add the the prewritten code and then you can switch
to code view and see whats its doing.
RD
On Nov 20, 2010, at 3:54 PM, Ben Miller wrote:
> Hi,
>
> I'm building a website for a client in which I need to compare their
> products, side-by-side, but only include criteria for which all selected
> products have a value for that criteria.
>
> In my database (MySQL), I have a tables named "products","criteria" and
> "criteria_values"
>
> If I have something like
>
> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
> products selected for comparison by the user
>
> I need to get only rows from "criteria" where there is a row in
> "criteria_values" matching "criteria.criteria_id" for each $selected_product
> - in other words, if any of the $selected_product does not have a row in
> "criteria_values" that matches "criteria.criteria_id", that criteria would
> not be returned. I hope that makes sense.
>
> I've played around with a few join queries, but none have given the desired
> results. Best I've been able to come up with so far is to query "criteria"
> for each DISTINCT(criteria_id) and then run through each $selected_product
> to make sure each has a criteria_value with a matching criteria_id,
> eliminating any criteria where the number of criteria_values <
> count($selected_product), but this seems pretty inefficient.
>
> Thanks in advance for any help.
>
> Ben Miller
>
>
> --
> 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] MySql Query Help: COUNT()
I'm trying to get the total number of a certain records from a database,
but the result is always '1'. Please advise!
=MySql Table =
=activitiy =
id | employee_id | project_id | date
1 | 45 | 60 | 2003-09-09
2 | 34 | 10 | 2003-09-10
3 | 45 | 45 | 2003-09-10
4 | 23 | 30 | 2003-04-11
Now, I'm trying to get the following info:
- Total Number of employees in the datbase
- Total number of projects
-total number of projects per employee
I currently have
$_sql = "select count(*) as TotalRecords, count(employee_id) as
TotalEmployees, count(project_id) as TotalProjects GROUP BY employee_id,
project_id
$_qry = mysql_query($_sql) or die('...blah...');
$_res = mysql_fetch_object($_qry);
//output
echo $_res->TotalRecords; //prints nothing
echo $_res->TotalEmployees; // returns 1
echo $_res->TotalProjects; //also return 1
Your query doesn't even have a "FROM Table" in it. Hopefully that's just
a cut and paste error, as you should see your "die" message because of
that error.
You're not going to get the results you're after with a single query
(unless you have PHP do some of the counting). Look at using a couple
different queries to get your count.
One solution may be:
SELECT employee_id, COUNT(*) AS NumProjects FROM activity GROUP BY
employee_id;
The number of rows returned will be the number of employees. Each row
will list the employee_id and how many times it appeared in the table.
If project_id is not repeated for a given employee_id, then this will be
your project count. As you loop throught the results, keep a running
total of the NumProjects column to get the total projects.
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help!!!!
Chris Kay wrote: >>The query does not error out it just does not give any records, and I > > Know > > What part of "The query does not error out" do you not understand. > > Why are there so many people willing to say what is wrong with a code but when it >comes to > A solution that go silent. > > I find that the ones most often to find flaws in someones code, are the ones who >never provide > Answers. I've been on this list for a few months (IIRC), but I don't post much. That's because I never provide Answers (being a newbie, and therefore not knowing them). I'm on the list because I want to learn about PHP, and I've found it easier in the years I've been online to subscribe to mailing lists or (once upon a time) USENET newsgroups and just read the various solutions to peoples' problems. "Where's all this going?", you impatiently ask, perhaps stamping your foot. Well, pause for a second, gentle reader, for you are about to be shocked to your very core. Well, maybe not. Here goes anyway: I've learnt a lot about PHP simply from reading the answers provided by the more intelligent, knowledgeable and helpful members of this list. Guess who's helped the most? A young (?) man by the unusual name of "John Holmes". Before you chew people out, Mr Kay, about being perpetually unhelpful, you may like to lurk on a list for a while and see if you're actually within a stones throw of the truth, or really just spouting off out of your own sense of hurt pride. HTH, HAND! -- Mark Gallagher http://cyberfuddle.com/infinitebabble/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help!!!!
> On Fri, 14 Jun 2002, Chris Kay wrote: > >> The query does not error out it just does not give any records, and I > >> Know > > > > What part of "The query does not error out" do you not understand. > > > > Why are there so many people willing to say what is wrong with a code > > but when it comes to A solution that go silent. > > > > I find that the ones most often to find flaws in someones code, are the > > ones who never provide Answers. > > Wow, you really know how to sweet-talk the people you're coming to for > help. Nice technique. > No sh_t. How about ignoring the other point I made that 'string' <= '20021010' will never be true! 'column1, column2' is a string and has nothing to do with your columns in the table. Dude, you're coming here for help and people might be nice enough to offer any kind of answer they want. If it's not to your liking, restate your question and ask again or ignore them. If you can't do that, then go away and keep learning on your own, we don't have to help you. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help!!!!
> The query does not error out it just does not give any records, and I
Know
What part of "The query does not error out" do you not understand.
Why are there so many people willing to say what is wrong with a code but when it
comes to
A solution that go silent.
I find that the ones most often to find flaws in someones code, are the ones who never
provide
Answers.
---
Chris Kay
Technical Support - Techex Communications
Website: www.techex.com.au Email: [EMAIL PROTECTED]
Telephone: 1300 88 111 2 - Fax: (02) 9970 5788
Address: Suite 13, 5 Vuko Place, Warriewood, NSW 2102
Platinum Channel Partner of the Year - Request DSL - Broadband for Business
---
> -Original Message-
> From: John Holmes [mailto:[EMAIL PROTECTED]]
> Sent: Friday, 14 June 2002 1:26 PM
> To: Chris Kay; 'PHP General List'
> Subject: RE: [PHP] MySQL Query Help
>
>
> Man, where do I start. There could be so many things wrong.
> First of all, this is a PHP list, not MySQL. Second, use
> MySQL_error() after you issue a query to see if an error was
> returned http://www.php.net/mysql_error. Third, in this line:
>
> 'detail_start_date_y,detail_start_date_m,detail_start_date_d,d
> etail_star
> t_time_h,detail_start_time_m' <= '$ttwo'
>
> You are comparing the literal string of 'detail... ' is less
> than or equal to a string like '200203041234' or something.
> That's going to fail.
>
> You need to go back to your SQL books...
>
> ---John Holmes...
>
> > -Original Message-
> > From: Chris Kay [mailto:[EMAIL PROTECTED]]
> > Sent: Thursday, June 13, 2002 7:33 PM
> > To: PHP General List
> > Subject: [PHP] MySQL Query Help
> >
> >
> > I have a rather longer query which I would like to get all records
> past
> > todays date.
> > Here is my query
> >
> > $ttwo = date("YmdGi");
> >
> > $dbq = select("select detail.*, type.type_name, status.status_name,
> > staff.staff_name, source.source_long, source.source_short
> from detail,
> > type, status, staff, source where type.type_id =
> detail.detail_type &&
> > status.status_id = detail.detail_status &&
> > staff.staff_id = detail.detail_staff && source.source_short =
> > detail.detail_source &&
> >
> 'detail_start_date_y,detail_start_date_m,detail_start_date_d,d
> etail_star
> t_
> > time_h,detail_start_time_m' <= '$ttwo' order by
> detail.detail_start_date_m
> > DESC, detail.detail_start_date_d DESC");
> >
> > The query works fine before I try to get all records in the
> furure as
> > shown below
> >
> >
> 'detail_start_date_y,detail_start_date_m,detail_start_date_d,d
> etail_star
> t_
> > time_h,detail_start_time_m' <= '$ttwo'
> >
> > The query does not error out it just does not give any
> records, and I
> know
> > there are 4 records
> >
> > Detail_start_date_y = 4 digit year
> > Detail_start_date_m = 2 digit month
> > Deatil_start_date_d = 2 digit day
> > Detail_start_time_h = 24 hour time
> >
> > Can anyone see what I am doing wrong?
> >
> > Thanks in advance.
> >
> >
> --
> --
> --
> > -
> > Chris Kay
> > Technical Support - Techex Communications
> > Website: www.techex.com.au Email: [EMAIL PROTECTED]
> > Telephone: 1300 88 111 2 - Fax: (02) 9970 5788
> > Address: Suite 13, 5 Vuko Place, Warriewood, NSW 2102
> Platinum Channel
> > Partner of the Year - Request DSL - Broadband for Business
> >
> --
> --
> --
> > -
> >
> > --
> > 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] MySQL Query Help!!!!
Man, where do I start. There could be so many things wrong. First of
all, this is a PHP list, not MySQL. Second, use MySQL_error() after you
issue a query to see if an error was returned
http://www.php.net/mysql_error. Third, in this line:
'detail_start_date_y,detail_start_date_m,detail_start_date_d,detail_star
t_time_h,detail_start_time_m' <= '$ttwo'
You are comparing the literal string of 'detail... ' is less than or
equal to a string like '200203041234' or something. That's going to
fail.
You need to go back to your SQL books...
---John Holmes...
> -Original Message-
> From: Chris Kay [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, June 13, 2002 7:33 PM
> To: PHP General List
> Subject: [PHP] MySQL Query Help
>
>
> I have a rather longer query which I would like to get all records
past
> todays date.
> Here is my query
>
> $ttwo = date("YmdGi");
>
> $dbq = select("select detail.*, type.type_name, status.status_name,
> staff.staff_name, source.source_long,
> source.source_short from detail, type, status, staff,
> source where type.type_id = detail.detail_type && status.status_id =
> detail.detail_status &&
> staff.staff_id = detail.detail_staff && source.source_short =
> detail.detail_source &&
>
'detail_start_date_y,detail_start_date_m,detail_start_date_d,detail_star
t_
> time_h,detail_start_time_m' <= '$ttwo' order by
detail.detail_start_date_m
> DESC, detail.detail_start_date_d DESC");
>
> The query works fine before I try to get all records in the furure as
> shown below
>
>
'detail_start_date_y,detail_start_date_m,detail_start_date_d,detail_star
t_
> time_h,detail_start_time_m' <= '$ttwo'
>
> The query does not error out it just does not give any records, and I
know
> there are 4 records
>
> Detail_start_date_y = 4 digit year
> Detail_start_date_m = 2 digit month
> Deatil_start_date_d = 2 digit day
> Detail_start_time_h = 24 hour time
>
> Can anyone see what I am doing wrong?
>
> Thanks in advance.
>
>
--
> -
> Chris Kay
> Technical Support - Techex Communications
> Website: www.techex.com.au Email: [EMAIL PROTECTED]
> Telephone: 1300 88 111 2 - Fax: (02) 9970 5788
> Address: Suite 13, 5 Vuko Place, Warriewood, NSW 2102
> Platinum Channel Partner of the Year - Request DSL - Broadband for
> Business
>
--
> -
>
> --
> 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] MySQL Query Help!!!!
ps... from a PHP perspective, you may find troubleshooting things like this
easier by using formatting like this:
$sql = "
select
detail.*,
type.type_name,
status.status_name,
staff.staff_name,
source.source_long,
source.source_short from detail,
type, status, staff,
source
where
type.type_id = detail.detail_type &&
status.status_id = detail.detail_status &&
staff.staff_id = detail.detail_staff &&
source.source_short = > detail.detail_source &&
'detail_start_date_y,detail_start_date_m,detail_start_date_d,detail_start_ti
me_h,detail_start_time_m' <= '$ttwo'
order by
detail.detail_start_date_m DESC,
detail.detail_start_date_d DESC
";
$dbq = select($sql);
> -Original Message-
> From: Chris Kay [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, June 13, 2002 4:33 PM
> To: PHP General List
> Subject: [PHP] MySQL Query Help
>
>
>
> I have a rather longer query which I would like to get all
> records past todays date.
> Here is my query
>
> $ttwo = date("YmdGi");
>
> $dbq = select("select detail.*, type.type_name,
> status.status_name, staff.staff_name, source.source_long,
> source.source_short from detail, type, status, staff,
> source where type.type_id = detail.detail_type &&
> status.status_id = detail.detail_status &&
> staff.staff_id = detail.detail_staff && source.source_short =
> detail.detail_source &&
> 'detail_start_date_y,detail_start_date_m,detail_start_date_d,d
> etail_start_time_h,detail_start_time_m' <= '$ttwo' order by
> detail.detail_start_date_m DESC, detail.detail_start_date_d DESC");
>
> The query works fine before I try to get all records in the
> furure as shown below
>
> 'detail_start_date_y,detail_start_date_m,detail_start_date_d,d
> etail_start_time_h,detail_start_time_m' <= '$ttwo'
>
> The query does not error out it just does not give any
> records, and I know there are 4 records
>
> Detail_start_date_y = 4 digit year
> Detail_start_date_m = 2 digit month
> Deatil_start_date_d = 2 digit day
> Detail_start_time_h = 24 hour time
>
> Can anyone see what I am doing wrong?
>
> Thanks in advance.
>
> --
> -
> Chris Kay
> Technical Support - Techex Communications
> Website: www.techex.com.au Email: [EMAIL PROTECTED]
> Telephone: 1300 88 111 2 - Fax: (02) 9970 5788
> Address: Suite 13, 5 Vuko Place, Warriewood, NSW 2102
> Platinum Channel Partner of the Year - Request DSL -
> Broadband for Business
> --
> -
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
This message is intended for the sole use of the individual and entity to
whom it is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you are
not the intended addressee, nor authorized to receive for the intended
addressee, you are hereby notified that you may not use, copy, disclose or
distribute to anyone the message or any information contained in the
message. If you have received this message in error, please immediately
advise the sender by reply email and delete the message. Thank you very
much.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help!!!!
This is a MySQL question and best directed to the MySQL mailing lists
available at:
http://www.mysql.com/documentation/lists.html
> -Original Message-
> From: Chris Kay [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, June 13, 2002 4:33 PM
> To: PHP General List
> Subject: [PHP] MySQL Query Help
>
>
>
> I have a rather longer query which I would like to get all
> records past todays date.
> Here is my query
>
> $ttwo = date("YmdGi");
>
> $dbq = select("select detail.*, type.type_name,
> status.status_name, staff.staff_name, source.source_long,
> source.source_short from detail, type, status, staff,
> source where type.type_id = detail.detail_type &&
> status.status_id = detail.detail_status &&
> staff.staff_id = detail.detail_staff && source.source_short =
> detail.detail_source &&
> 'detail_start_date_y,detail_start_date_m,detail_start_date_d,d
> etail_start_time_h,detail_start_time_m' <= '$ttwo' order by
> detail.detail_start_date_m DESC, detail.detail_start_date_d DESC");
>
> The query works fine before I try to get all records in the
> furure as shown below
>
> 'detail_start_date_y,detail_start_date_m,detail_start_date_d,d
> etail_start_time_h,detail_start_time_m' <= '$ttwo'
>
> The query does not error out it just does not give any
> records, and I know there are 4 records
>
> Detail_start_date_y = 4 digit year
> Detail_start_date_m = 2 digit month
> Deatil_start_date_d = 2 digit day
> Detail_start_time_h = 24 hour time
>
> Can anyone see what I am doing wrong?
>
> Thanks in advance.
>
> --
> -
> Chris Kay
> Technical Support - Techex Communications
> Website: www.techex.com.au Email: [EMAIL PROTECTED]
> Telephone: 1300 88 111 2 - Fax: (02) 9970 5788
> Address: Suite 13, 5 Vuko Place, Warriewood, NSW 2102
> Platinum Channel Partner of the Year - Request DSL -
> Broadband for Business
> --
> -
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
This message is intended for the sole use of the individual and entity to
whom it is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you are
not the intended addressee, nor authorized to receive for the intended
addressee, you are hereby notified that you may not use, copy, disclose or
distribute to anyone the message or any information contained in the
message. If you have received this message in error, please immediately
advise the sender by reply email and delete the message. Thank you very
much.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Query Help needed
[snip] "select cust_fnn, cust_name, agroup_access.group_access_cust from cust, agroup_access where agroup_access.group_access_group='$id' && cust.cust_fnn!=agroup_access.group_access_cust order by cust.cust_name" [/snip] try this (note syntactical differences); "select cust_fnn, cust_name, agroup_access.group_access_cust from cust, agroup_access where agroup_access.group_access_group = '$id' and cust.cust_fnn <> agroup_access.group_access_cust order by cust.cust_name" HTH! Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query help
On Mon, Sep 10, 2001 at 03:59:36PM -0500, Sheridan Saint-Michel wrote: > Well, I played with this a little more and it seems to be acting oddly when > you first > call this select unless you set the variable first. So if the below doesn't > work try > actually doing this > > $query="set @count=NULL; select > tableName.*,if(@count,@count:=@count+1,@count:=1) as inc, from tableName"; > > (I also realized that simply having @count as the test value should work as > it should default to NULL) > > If I don't set it, it just returns 1 on every row. Anyone know why it is > doing this? > (I am Running MySQL 3.23.36) Thank you very much for your help. I tried this (well, from the command line, but I'm sure from within PHP it'll work just fine, too) and it does just what I want! I looked in my MySQL book and I found the information on the if() operator/function. However, there was no mention in the text about using variables with the "@" prefix... Oops, here it is in the online manual that installed with mysql 3.23.36... I should have looked there first. Thanks for your help! -Michael -- No, my friend, the way to have good and safe government, is not to trust it all to one, but to divide it among the many, distributing to every one exactly the functions he is competent to. It is by dividing and subdividing these republics from the national one down through all its subordinations, until it ends in the administration of every man's farm by himself; by placing under every one what his own eye may superintend, that all will be done for the best. -- Thomas Jefferson, to Joseph Cabell, 1816 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL query help
Well, I played with this a little more and it seems to be acting oddly when you first call this select unless you set the variable first. So if the below doesn't work try actually doing this $query="set @count=NULL; select tableName.*,if(@count,@count:=@count+1,@count:=1) as inc, from tableName"; (I also realized that simply having @count as the test value should work as it should default to NULL) If I don't set it, it just returns 1 on every row. Anyone know why it is doing this? (I am Running MySQL 3.23.36) Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com - Original Message - From: "Sheridan Saint-Michel" <[EMAIL PROTECTED]> To: "Michael George" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, September 10, 2001 3:29 PM Subject: Re: [PHP] MySQL query help > See if this does what you are shooting for: > > select tableName.*,if(@count>=1,@count:=@count+1,@count:=1) as inc, from > tableName; > > Keep in mind that @count will keep it's value until the thread is closed, so > if for some reason you have > to do the above twice in one thread throw in a statement like: > > set @count=Null; > > between the selects =P > > Sheridan Saint-Michel > Website Administrator > FoxJet, an ITW Company > www.foxjet.com > > > - Original Message - > From: "Michael George" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, September 10, 2001 2:20 PM > Subject: [PHP] MySQL query help > > > > I'm trying to make a query that will number it's own output rows. e.g. > when > > listing all the entries in a table that are related to a specific invoice, > > there will be a column with a monotonically increasing integer value (1-x > > where x is the number of matching entries). > > > > I know I can easily do this with PHP after the query is generated, but I > have > > a generic function to create a pulldown menu selection that will work > easily > > if I can just get that counter column in there... > > > > I've tried: > > > > select tableName.*, 1 as inc, sum( inc ) from tableName; > > > > But sum() will only accept a table column, not a display column... I have > the > > Widenius MySQL book, but I haven't found anything useful in there. > > > > If anyone has an idea, I'd appreciate it... > > > > -Michael > > > > -- > > No, my friend, the way to have good and safe government, is not to trust > it > > all to one, but to divide it among the many, distributing to every one > exactly > > the functions he is competent to. It is by dividing and subdividing these > > republics from the national one down through all its subordinations, until > it > > ends in the administration of every man's farm by himself; by placing > under > > every one what his own eye may superintend, that all will be done for the > > best. > > -- Thomas Jefferson, to Joseph Cabell, 1816 > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL query help
See if this does what you are shooting for: select tableName.*,if(@count>=1,@count:=@count+1,@count:=1) as inc, from tableName; Keep in mind that @count will keep it's value until the thread is closed, so if for some reason you have to do the above twice in one thread throw in a statement like: set @count=Null; between the selects =P Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com - Original Message - From: "Michael George" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 10, 2001 2:20 PM Subject: [PHP] MySQL query help > I'm trying to make a query that will number it's own output rows. e.g. when > listing all the entries in a table that are related to a specific invoice, > there will be a column with a monotonically increasing integer value (1-x > where x is the number of matching entries). > > I know I can easily do this with PHP after the query is generated, but I have > a generic function to create a pulldown menu selection that will work easily > if I can just get that counter column in there... > > I've tried: > > select tableName.*, 1 as inc, sum( inc ) from tableName; > > But sum() will only accept a table column, not a display column... I have the > Widenius MySQL book, but I haven't found anything useful in there. > > If anyone has an idea, I'd appreciate it... > > -Michael > > -- > No, my friend, the way to have good and safe government, is not to trust it > all to one, but to divide it among the many, distributing to every one exactly > the functions he is competent to. It is by dividing and subdividing these > republics from the national one down through all its subordinations, until it > ends in the administration of every man's farm by himself; by placing under > every one what his own eye may superintend, that all will be done for the > best. > -- Thomas Jefferson, to Joseph Cabell, 1816 > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
