Re: [PHP-DB] ORDER BY DESC
Chris, Try this: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html On 20 June 2011 20:56, Chris Stinemetz chrisstinem...@gmail.com wrote: I have the bellow sql query. What is the correct way to add ORDER BY posts.post_date DESC I would like to add it to the end of my query, but I am just getting a blank page or my added query doesn't work echo: The posts could not be displayed, please try again later. The query is: //fetch the posts from the database $posts_sql = SELECT posts.post_store, posts.post_content, posts.post_date, posts.post_by, users.user_id, users.user_name FROM posts LEFT JOIN users ON posts.post_by = users.user_id WHERE posts.post_store = . mysql_real_escape_string($_GET['id']); Thank you in advance for your help. Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Best regards, Gavin C
Re: [PHP-DB] ORDER BY DESC
Try.. $posts_sql = SELECT posts.post_store, posts.post_content, posts.post_date, posts.post_by, users.user_id, users.user_name FROM posts LEFT JOIN users ON osts.post_by = users.user_id WHERE posts.post_store = .mysql_real_escape_string($_GET['id']). ORDER BY posts.post_date DESC; Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ORDER BY DESC
On Mon, Jun 20, 2011 at 3:49 PM, Karl DeSaulniers k...@designdrumm.com wrote: Try.. $posts_sql = SELECT posts.post_store, posts.post_content, posts.post_date, posts.post_by, users.user_id, users.user_name FROM posts LEFT JOIN users ON osts.post_by = users.user_id WHERE posts.post_store = .mysql_real_escape_string($_GET['id']). ORDER BY posts.post_date DESC; Got it working thanks everyone who responded. Working query: $posts_sql = SELECT posts.post_store, posts.post_content, posts.post_date, posts.post_by, users.user_id, users.user_name FROM posts LEFT JOIN users ON posts.post_by = users.user_id WHERE posts.post_store = . mysql_real_escape_string($_GET['id']) . ORDER BY posts.post_date DESC ; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ORDER BY relevance DESC query
Hey friend. The problem is here: ( SELECT `reference`, `organization` FROM `ministry_profiles`... In a subselect, you must select just one column. You are selecting two reference and organization. If you really need both, so you must do two subselects: one for reference and another for organization. Cheers! Adriano Laranjeira. São Paulo - Brazil. On Tue, 12 Apr 2011 04:00:15 -0400 Ron Piggott ron.pigg...@actsministries.org wrote: I am programming the search feature for a directory. I am trying to make the query display the results in order of relevance (Greatest to least). I thought I had to perform the query similar to a column being selected in order to sort the results by relevance. What changes do I need to make to my query for it to work correctly? The mySQL query is giving me the error message: #1241 - Operand should contain 1 column(s) The FULLTEXT index contains: organization address_line_1 address_line_2 city province_state postal_zip_code country telephone toll_free fax email website Presently the mySQL query is: SELECT `reference`, `organization` , ( SELECT `reference`, `organization` FROM `ministry_profiles` WHERE MATCH( `ministry_profiles`.`organization`, `ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`, `ministry_profiles`.`city`, `ministry_profiles`.`province_state`, `ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`, `ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`, `ministry_profiles`.`fax`, `ministry_profiles`.`email`, `ministry_profiles`.`website` ) AGAINST ('$search') AND `live` = 1 ) AS relevance FROM `ministry_profiles` WHERE MATCH( `ministry_profiles`.`organization`, `ministry_profiles`.`address_line_1`, `ministry_profiles`.`address_line_2`, `ministry_profiles`.`city`, `ministry_profiles`.`province_state`, `ministry_profiles`.`postal_zip_code`, `ministry_profiles`.`country`, `ministry_profiles`.`telephone`, `ministry_profiles`.`toll_free`, `ministry_profiles`.`fax`, `ministry_profiles`.`email`, `ministry_profiles`.`website` ) AGAINST ('$search') AND `live` = 1 ORDER BY relevance DESC -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Order By [blank]
Depending on your MySQL version you could use a subquery by combining the two queries you mentioned, for a fairly straight-forward query. http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html http://mysqld.active-venture.com/Subqueries.html You could read about optimizing subqueries for optimum queries. -Original Message- From: Kevin Murphy [mailto:[EMAIL PROTECTED] Sent: Thursday, December 21, 2006 10:49 PM To: php-db@lists.php.net Subject: [PHP-DB] Order By [blank] I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
Unfortunately, I'm on 4.0.x so sub-queries are out. And yeah, I should get my host to upgrade but we both work for the government so that isn't happening. ;-) Any other thoughts. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 On Dec 21, 2006, at 9:30 AM, Naintara wrote: Depending on your MySQL version you could use a subquery by combining the two queries you mentioned, for a fairly straight-forward query. http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html http://mysqld.active-venture.com/Subqueries.html You could read about optimizing subqueries for optimum queries. -Original Message- From: Kevin Murphy [mailto:[EMAIL PROTECTED] Sent: Thursday, December 21, 2006 10:49 PM To: php-db@lists.php.net Subject: [PHP-DB] Order By [blank] I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
What about a union? Does mySql 4.0.x support it? select * from blank where tchar_10 != '' union all select * from blank where tchar_10 = '' - Dave On 12/21/06, Naintara [EMAIL PROTECTED] wrote: Depending on your MySQL version you could use a subquery by combining the two queries you mentioned, for a fairly straight-forward query. http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html http://mysqld.active-venture.com/Subqueries.html You could read about optimizing subqueries for optimum queries. -Original Message- From: Kevin Murphy [mailto:[EMAIL PROTECTED] Sent: Thursday, December 21, 2006 10:49 PM To: php-db@lists.php.net Subject: [PHP-DB] Order By [blank] I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
This is a little weird looking, but should do the job. Remember that items in your 'order by' can be manipulated conditionally. In this case, I'm looking for NULL as well as '' (empty) and changing it to something that should come after all your normal alphabetical values, but it doesn't change what appears in your results. This only affects the sorting: select * from sometable order by if(ifnull(somecolumn, '') = '', '', somecolumn) Hope that helps. -TG = = = Original message = = = I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
This is a little weird looking, but should do the job. Remember that items in your 'order by' can be manipulated conditionally. In this case, I'm looking for NULL as well as '' (empty) and changing it to something that should come after all your normal alphabetical values, but it doesn't change what appears in your results. This only affects the sorting: select * from sometable order by if(ifnull(somecolumn, '') = '', '', somecolumn) Hope that helps. -TG = = = Original message = = = I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
In case the blank is a null or is really a blank: select * from blank where tchar_10 is not null and tchar_10 != '' union all select * from blank where tchar_10 is null or tchar_10 = '' - Dave On 12/21/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This is a little weird looking, but should do the job. Remember that items in your 'order by' can be manipulated conditionally. In this case, I'm looking for NULL as well as '' (empty) and changing it to something that should come after all your normal alphabetical values, but it doesn't change what appears in your results. This only affects the sorting: select * from sometable order by if(ifnull(somecolumn, '') = '', '', somecolumn) Hope that helps. -TG = = = Original message = = = I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
You shouldn't have to do that. the IFNULL() handles all that. If the item is null, it returns an emptry string ''. If it's blank/empty, it returns an empty string. This is just used for the comparison = ''. This determines if it's empty or null and if so, returns 'ZZ', if not, it returns the unaltered value. And again, this returned value is only used for the sorting. The values you get from select * will be unaltered. Is this more efficient than doing two SELECTs and a UNION? I have no idea. But I like to keep things as clean as possible and in my reading and experience, letting the server handle an IFNULL() function should be quicker than doing four value checks (is null, is not null, = '' and != ''), collecting the values of two SELECTS then checking to see if it's able to UNION them together. Also, less code/typing typically means less chance of typos. BTW: In my example, realistically you could probably shorten the morphed value to ZZ or ZZZ unless you think you'll values in your database that will start with and get bumped lower on the sorting. -TG = = = Original message = = = In case the blank is a null or is really a blank: select * from blank where tchar_10 is not null and tchar_10 != '' union all select * from blank where tchar_10 is null or tchar_10 = '' - Dave On 12/21/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This is a little weird looking, but should do the job. Remember that items in your 'order by' can be manipulated conditionally. In this case, I'm looking for NULL as well as '' (empty) and changing it to something that should come after all your normal alphabetical values, but it doesn't change what appears in your results. This only affects the sorting: select * from sometable order by if(ifnull(somecolumn, '') = '', '', somecolumn) Hope that helps. -TG = = = Original message = = = I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
I haven't tried the union method the query i have is actually quite a bit more complicated than just a simple select * from a single table, so while it may work, it might take a while to write it if I am reading all this right. But yes, the ifnull() method works just fine. Thanks for your help. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 On Dec 21, 2006, at 10:12 AM, [EMAIL PROTECTED] tg- [EMAIL PROTECTED] wrote: You shouldn't have to do that. the IFNULL() handles all that. If the item is null, it returns an emptry string ''. If it's blank/ empty, it returns an empty string. This is just used for the comparison = ''. This determines if it's empty or null and if so, returns 'ZZ', if not, it returns the unaltered value. And again, this returned value is only used for the sorting. The values you get from select * will be unaltered. Is this more efficient than doing two SELECTs and a UNION? I have no idea. But I like to keep things as clean as possible and in my reading and experience, letting the server handle an IFNULL() function should be quicker than doing four value checks (is null, is not null, = '' and != ''), collecting the values of two SELECTS then checking to see if it's able to UNION them together. Also, less code/typing typically means less chance of typos. BTW: In my example, realistically you could probably shorten the morphed value to ZZ or ZZZ unless you think you'll values in your database that will start with and get bumped lower on the sorting. -TG = = = Original message = = = In case the blank is a null or is really a blank: select * from blank where tchar_10 is not null and tchar_10 != '' union all select * from blank where tchar_10 is null or tchar_10 = '' - Dave On 12/21/06, [EMAIL PROTECTED] tg- [EMAIL PROTECTED] wrote: This is a little weird looking, but should do the job. Remember that items in your 'order by' can be manipulated conditionally. In this case, I'm looking for NULL as well as '' (empty) and changing it to something that should come after all your normal alphabetical values, but it doesn't change what appears in your results. This only affects the sorting: select * from sometable order by if(ifnull(somecolumn, '') = '', '', somecolumn) Hope that helps. -TG = = = Original message = = = I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ORDER BY
Ron Piggott (PHP) wrote: Is it possible to do an ORDER BY column_a column_b ASC ? IE Can you specify two columns to cause the output to be displayed in a specific order? Ron Yes (a quick search would have told you this too). It's best to specify the order for each one: order by column_a asc, column_b asc; or order by column_a asc, column_b desc; for example. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] ORDER BY
Yes! Just separate your columns with commas! Check out the proper sintax!! -Original Message- From: Ron Piggott (PHP) [mailto:[EMAIL PROTECTED] Sent: Miércoles, 18 de Octubre de 2006 06:58 p.m. To: PHP DB Subject: [PHP-DB] ORDER BY Is it possible to do an ORDER BY column_a column_b ASC ? IE Can you specify two columns to cause the output to be displayed in a specific order? Ron Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta dirigido; contiene informacion estrictamente confidencial y legalmente protegida, cuya divulgacion es sancionada por la ley. Si el lector de este mensaje no es a quien esta dirigido, ni se trata del empleado o agente responsable de esta informacion, se le notifica por medio del presente, que su reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio este comunicado por error, favor de notificarlo inmediatamente al remitente y destruir el mensaje. Todas las opiniones contenidas en este mail son propias del autor del mensaje y no necesariamente coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos. This message is for the sole use of the person or entity to whom it is being sent. Therefore, it contains strictly confidential and legally protected material whose disclosure is subject to penalty by law. If the person reading this message is not the one to whom it is being sent and/or is not an employee or the responsible agent for this information, this person is herein notified that any unauthorized dissemination, distribution or copying of the materials included in this facsimile is strictly prohibited. If you received this document by mistake please notify immediately to the subscriber and destroy the message. Any opinions contained in this e-mail are those of the author of the message and do not necessarily coincide with those of Radiomovil Dipsa, S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries companies. No part of this message or attachments may be used or reproduced in any manner whatsoever. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] 'Order by' question
[EMAIL PROTECTED] wrote: Hi, I have a MySQL varchar column which I wish to display. Most of the entries are numbers eg 1, 2, 3 etc etc. But some are numbers and letters eg 34A, 34B, 35A. When I display the column I am able to sort by number using the order by column_name+0 command, as in: $result = mysql_query(SELECT id, house, pen, date_format(start,'%M %D, %Y') as start, date_format(end,'%M %D, %Y') as end, details FROM animalhouse WHERE house='old' ORDER BY pen+0) or die (mysql_error()); However, it doesn't sort the letters properly, so I get 34B, 34A, 34C etc etc. Is there a simple way for me to make it sort by number first and then by letter? Not in your query. Try resorting it using a natural sort in PHP. The A, B, and C should be in another column, anyhow. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] ORDER BY Query
Shaun wrote: I have a table in my database called Users. This table has (among others) two columns called Location and Name. Location will be either 1, 2, 3, or 4. How can I produce a query that groups the result into Location and then for each Location order the User by Name? Learn SQL, please. SELECT * FROM Table ORDER BY Location, Name -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] order by an average in another table (basically a top 10 list)
Just a shot in the dark.. it may be wrong, but try it out.. SELECT avg(rating) AS avgrating FROM ratings WHERE parent_id = '$id' ORDER BY avgrating DESC LIMIT 10 try that out.. should work. metin At 04:06 PM 6/18/2003 -0400, chris wrote: I've done a google search, but most of the results are for the part I've already figured out. People that have the top 10 sorting usually want money for their system (one wanted an absurd $500). I'm working on a database application where users vote on how much they like a particular picture. All of the picture information is in one table, and all of the votes are within another table. The picture table's auto incrementing id is referenced in the ratings table. Displaying the actual average rating of a particular photograph is easy enough: SELECT avg(rating) FROM ratings WHERE parent_id = '$id' But how the heck would one select the top $n entries, ordered by the average of the photo's ratings? Ideally, I'd like the query to work in both MySQL and PostgreSQL. -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order DB through url toggle
Let's see: ? $dir = ((isset ($_GET['dir'])) ? (int) $_GET['dir'] : 1); if (!$dir) $direction = 'desc'; $dir = ($dir ? 0 : 1); ? tda href=default2.php?orderBy=?=$colname?dir=?= $dir?Last Name/a/td This is the general idea. $dir will toggle between 1 and 0. There are more compact ways of writing this--but this one should be clearer. Marco - php|architect -- The Monthly Magazine For PHP Professionals Come visit us on the web at http://www.phparch.com! On Fri, 2002-11-08 at 10:04, Paul Ihrig wrote: Hello All! i am new to the list to PHP.. So i have a recordset that displays my contacts in a table. the headers of the columns look like this. tda href=default2.php?orderBy=priLastNameLast Name/a/td so when a user clicks on it It will Re-Sort the rs by priLastName Asc. what i want to do is make it a toggle between table name ASC or DESC.. any ideas? thanks. -paul #$orderBy = 'priFirstName'; $orderBy = 'priLastName'; if (isset($HTTP_GET_VARS['orderBy'])) { $orderBy = $HTTP_GET_VARS['orderBy']; } #?orderBy=priFirstName $maxRows_rsAll = 5;; $pageNum_rsAll = 0; if (isset($HTTP_GET_VARS['pageNum_rsAll'])) { $pageNum_rsAll = $HTTP_GET_VARS['pageNum_rsAll']; } $startRow_rsAll = $pageNum_rsAll * $maxRows_rsAll; mysql_select_db($database_myFirstSql, $myFirstSql); $query_rsAll = SELECT * FROM userinfo ORDER BY $orderBy ASC; $query_limit_rsAll = sprintf(%s LIMIT %d, %d, $query_rsAll, $startRow_rsAll, $maxRows_rsAll); $rsAll = mysql_query($query_limit_rsAll, $myFirstSql) or die(mysql_error()); $row_rsAll = mysql_fetch_assoc($rsAll); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Order DB through url toggle
well i tried that.. but it out puts the right number. but it is not taking that var to switch ACS or DESC... http://localhost/php/default2.php?orderBy=priLastNamedir=0 http://localhost/php/default2.php?orderBy=priLastNamedir=1 /* here we set up out order by clause */ $orderBy = 'priLastName'; if (isset($HTTP_GET_VARS['orderBy'])) { $orderBy = $HTTP_GET_VARS['orderBy']; } /* and the desc asc */ if $HTTP_GET_VARS['dir'] = 0 { $diri = 'DESC'; } else { $diri = 'ASC'; } mysql_select_db($database_myFirstSql, $myFirstSql); $query_rsAll = SELECT * FROM userinfo ORDER BY $orderBy $diri; $query_limit_rsAll = sprintf(%s LIMIT %d, %d, $query_rsAll, $startRow_rsAll, $maxRows_rsAll); $rsAll = mysql_query($query_limit_rsAll, $myFirstSql) or die(mysql_error()); $row_rsAll = mysql_fetch_assoc($rsAll); Then ? $dir = ((isset ($_GET['dir'])) ? (int) $_GET['dir'] : 1); if (!$dir) $direction = 'desc'; $dir = ($dir ? 0 : 1); ? tda href=default2.php?orderBy=priLastNamedir=?= $dir?Last Name/a/td tda href=default2.php?orderBy=priPhonePhone/a/td tda href=default2.php?orderBy=priEmailEmail/a/td tda href=default2.php?orderBy=priStreetStreet/a/td tda href=default2.php?orderBy=priCityCity/a/td -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order DB through url toggle
On Saturday 09 November 2002 00:26, Paul Ihrig wrote: well i tried that.. but it out puts the right number. but it is not taking that var to switch ACS or DESC... http://localhost/php/default2.php?orderBy=priLastNamedir=0 http://localhost/php/default2.php?orderBy=priLastNamedir=1 /* here we set up out order by clause */ $orderBy = 'priLastName'; if (isset($HTTP_GET_VARS['orderBy'])) { $orderBy = $HTTP_GET_VARS['orderBy']; } /* and the desc asc */ if $HTTP_GET_VARS['dir'] = 0 { Try: if $HTTP_GET_VARS['dir'] == 0 { -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Thank God a million billion times you live in Texas. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Order DB through url toggle
I think you might be missing a = sign in your if statement. Marco - php|architect -- The Monthly Magazine For PHP Professionals Come visit us on the web at http://www.phparch.com! On Fri, 2002-11-08 at 11:26, Paul Ihrig wrote: well i tried that.. but it out puts the right number. but it is not taking that var to switch ACS or DESC... http://localhost/php/default2.php?orderBy=priLastNamedir=0 http://localhost/php/default2.php?orderBy=priLastNamedir=1 /* here we set up out order by clause */ $orderBy = 'priLastName'; if (isset($HTTP_GET_VARS['orderBy'])) { $orderBy = $HTTP_GET_VARS['orderBy']; } /* and the desc asc */ if $HTTP_GET_VARS['dir'] = 0 { $diri = 'DESC'; } else { $diri = 'ASC'; } mysql_select_db($database_myFirstSql, $myFirstSql); $query_rsAll = SELECT * FROM userinfo ORDER BY $orderBy $diri; $query_limit_rsAll = sprintf(%s LIMIT %d, %d, $query_rsAll, $startRow_rsAll, $maxRows_rsAll); $rsAll = mysql_query($query_limit_rsAll, $myFirstSql) or die(mysql_error()); $row_rsAll = mysql_fetch_assoc($rsAll); Then ? $dir = ((isset ($_GET['dir'])) ? (int) $_GET['dir'] : 1); if (!$dir) $direction = 'desc'; $dir = ($dir ? 0 : 1); ? tda href=default2.php?orderBy=priLastNamedir=?= $dir?Last Name/a/td tda href=default2.php?orderBy=priPhonePhone/a/td tda href=default2.php?orderBy=priEmailEmail/a/td tda href=default2.php?orderBy=priStreetStreet/a/td tda href=default2.php?orderBy=priCityCity/a/td -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Order DB through url toggle
Thanks Guys.. This seemed to do the trick /* and the desc asc */ $dir = ((isset ($_GET['dir'])) ? (int) $_GET['dir'] : 1); if (!$dir) $dir = ($dir ? 0 : 1); if ($HTTP_GET_VARS['dir'] == 0) { $diri = 'DESC'; } if ($HTTP_GET_VARS['dir'] == 1) { $diri = 'ASC'; } mysql_select_db($database_myFirstSql, $myFirstSql); $query_rsAll = SELECT * FROM userinfo ORDER BY $orderBy $diri; -paul -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] order by problem
Thank you all for the Tip ! It works fine now !! :) Gus - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 10:32 AM Subject: Re: [PHP-DB] order by problem On Wed, 20 Mar 2002 [EMAIL PROTECTED] wrote: You have your ctrlnumber field defined as varchar. If you define ctrlnumber as number, you could also use SELECT max(ctrlnumber) FROM table WHERE id=14; Cheers Urosh Hello All, Im having a 'problem' that I dont know how to resolve. The problem is: I have a table that I want to get the last number to increment. To do this I want to sort the filed 'ctrlnumber'. Using this querys I get: mysql SELECT ctrlnumber FROM table WHERE id=14; ++ | ctrlnumber | ++ | 7 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 8 | | 9 | | 10 | | 11 | ++ 11 rows in set (0.00 sec) mysql SELECT ctrlnumber FROM table WHERE id=14 order by ctrlnumber; ++ | ctrlnumber | ++ | 1 | | 10 | | 11 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | ++ 11 rows in set (0.01 sec) When I try to sort, it doesnt return to me in the order that I need (1 2 3 4 5 6 7 8 9 10 11). Is there a way to do this or Ill have to include 01 02 03 04 05 to all my recoreds ? Thanks in advance, Gus -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] order by problem
Make CtrlNumber a numeric value instead of a string. =C= * * Cal Evans * Techno-Mage * http://www.calevans.com * - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 20, 2002 7:21 AM Subject: [PHP-DB] order by problem Hello All, I´m having a 'problem' that I dont know how to resolve. The problem is: I have a table that I want to get the last number to increment. To do this I want to sort the filed 'ctrlnumber'. Using this query´s I get: mysql SELECT ctrlnumber FROM table WHERE id=14; ++ | ctrlnumber | ++ | 7 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 8 | | 9 | | 10 | | 11 | ++ 11 rows in set (0.00 sec) mysql SELECT ctrlnumber FROM table WHERE id=14 order by ctrlnumber; ++ | ctrlnumber | ++ | 1 | | 10 | | 11 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | ++ 11 rows in set (0.01 sec) When I try to sort, it doesn´t return to me in the order that I need (1 2 3 4 5 6 7 8 9 10 11). Is there a way to do this or I´ll have to include 01 02 03 04 05 to all my recoreds ? Thank´s in advance, Gus -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] order by problem
On Wed, 20 Mar 2002 [EMAIL PROTECTED] wrote: You have your ctrlnumber field defined as varchar. If you define ctrlnumber as number, you could also use SELECT max(ctrlnumber) FROM table WHERE id=14; Cheers Urosh Hello All, Im having a 'problem' that I dont know how to resolve. The problem is: I have a table that I want to get the last number to increment. To do this I want to sort the filed 'ctrlnumber'. Using this querys I get: mysql SELECT ctrlnumber FROM table WHERE id=14; ++ | ctrlnumber | ++ | 7 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 8 | | 9 | | 10 | | 11 | ++ 11 rows in set (0.00 sec) mysql SELECT ctrlnumber FROM table WHERE id=14 order by ctrlnumber; ++ | ctrlnumber | ++ | 1 | | 10 | | 11 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | ++ 11 rows in set (0.01 sec) When I try to sort, it doesnt return to me in the order that I need (1 2 3 4 5 6 7 8 9 10 11). Is there a way to do this or Ill have to include 01 02 03 04 05 to all my recoreds ? Thanks in advance, Gus -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: RE: [PHP-DB] Order By for search results ???
Maybe I'm slow...I'm trying to decipher what you're trying to do. Show us your table layout Show us your search criteria and code Show us a dump of your table so we can see what the data look like ahhh give the chap a break, this is 4th (or so) posting. he thinks we're all wonderful! Let's not mess that up for him: You had it when you said: ..how do I ORDER BY and then.. SELECT * FROM your_table WHERE foo LIKE '%bar%' ORDER BY some_field LIMIT some_limit * You could set some_field in your code or have it as a variable set from the web-page: Click table headings to sort by that field etc etc * You could also have some_limit set as a number from a select menu again on the web page of search results. Good luck! Russ PS Where is this search engine? can I have a look??? go on - you know it makes sense On Thu, 18 Oct 2001 12:46:25 -0500 Rick Emery [EMAIL PROTECTED] wrote: Maybe I'm slow... I'm trying to decipher what you're trying to do. Show us your table layout Show us your search criteria and code Show us a dump of your table so we can see what the data look like -Original Message- From: Chris Payne [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 12:08 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Order By for search results ??? Hi there everyone, I am happy, I have my search engine working almost perfectly (Touch wood :-) but there is one problem. When I search - say for Backpacking in Australia it doesn't bring up the results in that order as I have to order by country, city or whatever. What I want to know is, how do I ORDER BY and then my search results so that I get the nearest matches at the top of the search? Thank you all for your help, this list is the best place to be for PHP :-) Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] #---# Believe nothing - consider everything Russ Michell Anglia Polytechnic University Webteam Room 1C 'The Eastings' East Road, Cambridge e: [EMAIL PROTECTED] w: www.apu.ac.uk/webteam www.theruss.com #---# -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] order by question
- Original Message - From: Andrius Jakutis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 12, 2001 12:12 PM Subject: [PHP-DB] order by question Ok, here is the question: I write to order by date Order by date. So, it output's something like this: 2001 08 01 2001 08 02 2001 08 03 What if I want to order by date, but not from the older to newest, but from newest to older ? The same is with numbers - priority I use order by priority. It lists: 1 2 3 4 But how to list like this: 4 3 2 1 Thanks Use ORDER BY Date ASC or ORDER BY Date DESC for ascending and descending. regards -- Jason Wong Gremlins Associates www.gremlins.com.hk Tel: +852-2573-5033 Fax: +852-2573-5851 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] order by question
Hi Andrius, try order by date desc Steve At 9:12 PM -0700 11/9/01, Andrius Jakutis wrote: Ok, here is the question: I write to order by date Order by date. So, it output's something like this: 2001 08 01 2001 08 02 2001 08 03 What if I want to order by date, but not from the older to newest, but from newest to older ? The same is with numbers - priority I use order by priority. It lists: 1 2 3 4 But how to list like this: 4 3 2 1 Thanks -- --- Minds are like parachutes, they work best when open Support free speech; visit http://www.efa.org.au/ Heads Together Systems Pty Ltd http://www.hts.com.au Email: [EMAIL PROTECTED] Tel: 612 9982 6767 Fax: 612 9981 3081 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] order by date
you can use order by date DESC for descending or order by date for ascending nice to see a PHP-Database question in here ;-) Dave -Original Message- From: Scott Chapman [mailto:[EMAIL PROTECTED]] Sent: 09 August 2001 10:50 To: [EMAIL PROTECTED] Subject: [PHP-DB] order by date Hi, I am querying a mysql database and am returning data ordered by date, the trouble is it returns the data from least recent to most recent, I want it to return the data the other way round. Is there a way of having the mysql database doping this re-ordering for me rather than coding it myself? thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] order by date
Hi Scott, RTFM. Anyway: '... ORDER BY Date DESC' will do the job for you. Greetinx, Mike Michael Rudel - Web-Development, Systemadministration - Besuchen Sie uns am 20. und 21. August 2001 auf der online-marketing-düsseldorf in Halle 1 Stand E 16 ___ Suchtreffer AG Bleicherstraße 20 D-78467 Konstanz Germany fon: +49-(0)7531-89207-17 fax: +49-(0)7531-89207-13 e-mail: mailto:[EMAIL PROTECTED] internet: http://www.suchtreffer.de ___ -Original Message- From: Scott Chapman [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 09, 2001 11:50 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] order by date Hi, I am querying a mysql database and am returning data ordered by date, the trouble is it returns the data from least recent to most recent, I want it to return the data the other way round. Is there a way of having the mysql database doping this re-ordering for me rather than coding it myself? thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Order by unix timestamp
ORDER BY unixtime DESC (it default orders by asc, not desc) Greetinx, Mike Michael Rudel - Web-Development, Systemadministration - Besuchen Sie uns am 20. und 21. August 2001 auf der online-marketing-düsseldorf in Halle 1 Stand E 16 ___ Suchtreffer AG Bleicherstraße 20 D-78467 Konstanz Germany fon: +49-(0)7531-89207-17 fax: +49-(0)7531-89207-13 e-mail: mailto:[EMAIL PROTECTED] internet: http://www.suchtreffer.de ___ -Original Message- From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 5:09 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Order by unix timestamp Hello, i have a table where unix timestamps are inserted when adding a record. If i read out the table data, i want to order it by unixtime ( the name of the filed where the timestamp is ) He orders it by unixtime, but beginning with the oldest record. i thougt i could fix it by using ORDER by !unixtime . Also does'nt work How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? -- mfg, andy - In Memoriam - Jaques www.amdclan.de/jaques -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Order by unix timestamp
-Original Message- From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]] Sent: 12 July 2001 16:09 To: [EMAIL PROTECTED] Subject: [PHP-DB] Order by unix timestamp How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? order by unixtime desc Steve -- mfg, andy - In Memoriam - Jaques www.amdclan.de/jaques -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Order by unix timestamp
Vielen Dank Michael Rudel [EMAIL PROTECTED] schrieb im Newsbeitrag 02bb01c10ae5$38128190$[EMAIL PROTECTED]">news:02bb01c10ae5$38128190$[EMAIL PROTECTED]... ORDER BY unixtime DESC (it default orders by asc, not desc) Greetinx, Mike Michael Rudel - Web-Development, Systemadministration - Besuchen Sie uns am 20. und 21. August 2001 auf der online-marketing-düsseldorf in Halle 1 Stand E 16 ___ Suchtreffer AG Bleicherstraße 20 D-78467 Konstanz Germany fon: +49-(0)7531-89207-17 fax: +49-(0)7531-89207-13 e-mail: mailto:[EMAIL PROTECTED] internet: http://www.suchtreffer.de ___ -Original Message- From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 5:09 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Order by unix timestamp Hello, i have a table where unix timestamps are inserted when adding a record. If i read out the table data, i want to order it by unixtime ( the name of the filed where the timestamp is ) He orders it by unixtime, but beginning with the oldest record. i thougt i could fix it by using ORDER by !unixtime . Also does'nt work How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? -- mfg, andy - In Memoriam - Jaques www.amdclan.de/jaques -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Order by unix timestamp
Thank you Steve Brett [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... -Original Message- From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]] Sent: 12 July 2001 16:09 To: [EMAIL PROTECTED] Subject: [PHP-DB] Order by unix timestamp How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? order by unixtime desc Steve -- mfg, andy - In Memoriam - Jaques www.amdclan.de/jaques -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Order by unix timestamp
try the RSORT (reserve sort) function??? Andreas Iwanowski wrote: Hello, i have a table where unix timestamps are inserted when adding a record. If i read out the table data, i want to order it by unixtime ( the name of the filed where the timestamp is ) He orders it by unixtime, but beginning with the oldest record. i thougt i could fix it by using ORDER by !unixtime . Also does'nt work How can i fix the problem that the records are ordered by unixtime, beginning with the newest record ? -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]