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 sim...@syounger.com wrote: On Sat, 20 Nov 2010 13:54:29 -0700 Ben Miller biprel...@gmail.com 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 sim...@syounger.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
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 sim...@syounger.com wrote: On Sat, 20 Nov 2010 13:54:29 -0700 Ben Miller biprel...@gmail.com 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
[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
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
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
On Sat, 20 Nov 2010 13:54:29 -0700 Ben Miller biprel...@gmail.com 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 sim...@syounger.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySql Query Help: COUNT()
Hello everyone!
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
//end of code
Any suggestions? Did I miss something?
Please advise.
Thanks in advance.
_john
--
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
[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_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,detail_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
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!!!!
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!!!!
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!!!!
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!!!!
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
[PHP] Mysql Query Help needed
I have a query
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
The 2 tables are as follows
agroup_access
agroup_access_idagroup_access_group
agroup_access_cust
1 9
1610005847
2 9
0265478986
3 9
1610010254
Cust
cst_id cust_name cust_fnn(ect)
1 xxx 1610005847
2 x 0265478986
And so on 200+ records
What I am trying to do is pull all records from cust where cust_fnn !=
agroup_access_cust agroup_access_group=9..
What I am getting is all cust_fnn records but 3 of each and only 2 in
the case of it matching the
agroup_access_cust..
Can anyone see what I may have dome wrong..
I am trying to display them in a drop down box, below is the function
for the drop down box and the line to call the function..
combo5(fnn,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,);
function combo5($name, $query, $sel) {
$dbq=mysql_query($query);
$html=select name=\$name\ class=\longdd\\n;
while( $data = mysql_fetch_array($dbq) ) {
if($data[cust_fnn]==$sel)
$html.=option value=$data[cust_fnn]
selected$data[cust_name] : $data[cust_fnn]/option\n;
else
$html.=option
value=$data[cust_fnn]$data[cust_name]: $data[cust_fnn]/option\n;
}
$html.=/select\n;
return $html;
}
Hope someone can help...
---
Chris Kay, Eleet Internet Services
[EMAIL PROTECTED]
---
--
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]
[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]
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]
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]
