Search the Community

Howdy, I'm trying to get a result from my database in the proper order. CREATE TABLE IF NOT EXISTS `clc_crz_regions` ( `region_id` tinyint(2) NOT NULL, `parent_id` tinyint(2) NOT NULL DEFAULT '0', `regAbbreviation` varchar(4) NOT NULL DEFAULT '', `reg_name` varchar(40) NOT NULL DEFAULT '', ) INSERT INTO `clc_crz_regions` (`region_id`, `parent_id`, `regAbbreviation`, `reg_name`) VALUES (4, 0, 'soam', 'South America'), (1, 0, 'cari', 'Caribbean'), (20, 1, 'eaca', 'Eastern Caribbean'), (44, 1, 'soca', 'Southern Caribbean'), (21, 1, 'weca', 'Western Caribbean'), (41, 4, 'amri', 'Amazon River'); CREATE TABLE IF NOT EXISTS `clc_crz_region_groups` ( `parent_id` tinyint(2) NOT NULL, `child_id` tinyint(2)NOT NULL ) INSERT INTO `clc_crz_region_groups` (`parent_id`, `child_id`) VALUES (1, 1), (1, 20), (1, 21), (1, 44), (2, 2), (3, 3), (3, 40), (3, 57), (4, 4), I'd like it to sort by 'reg_name' for the parents, with the children under the parents also sorted by name. Caribbean Eastern Caribbean Southern Caribbean Western Caribbean South America Amazon River Cape Horn I can get the results, just can't get them to order the way I want to. SELECT r1.region_id, r1.reg_name, r1.reg_entry_name, r1.parent_id FROM $wpdb->crz_regions r1 JOIN $wpdb->crz_region_groups r2 ON r1.region_id = r2.child_id I just can't get it sorted correctly. I'd like to do it in SQL if possible. Thank you

Hello All, I've added search to my CMS and one column of data in particular is a date column. My whileLoop spits out data I want however it sorts the date field ASC but I need is DESC. I've tried adding the ORDER BY in several different ways however I keep getting an error... what would be the best approach to get it to sort the way I want using my current SELECT statement $search_sql = " SELECT * FROM tracking INNER JOIN sender ON tracking_sender_id = sender_id INNER JOIN method ON tracking_method_id = method_id WHERE (tracking_recepient LIKE '%" . $_POST['search'] . "%')"; the above code works fine but the output sorts ASC, can seem to add the ORDER BY in an appropriate manner to get it DESC. Any advice ? Thank you in advance

Here is the query: $query = 'SELECT * FROM a_playerRank WHERE year="2015" and position="1" and grouping<4 ORDER BY grouping DESC,rankPos,region,nameLast'; I have three groups (grouping). The first group is based on their ranking 1-10 or 1-15. (working fine) The second group is supposed to be alphabetical based on last name. (not working) The third group is supposed to be based on region, then alphabetically by their last name, and I have a header for each region. (working fine) I attached an image of grouping two and three. Group two (top group in the picture) isn't alphabetical. I know why the query isn't producing what I want, but I can't figure out the query I need to get my list to show as I described it. It shows the second group ordered by region then last name, just without headers. When I flip region and nameLast in the order by, the second group works great, but the third group basically produces a header for each kid (it looks like a mess). I assume there is a more complex query that will produce what I want.

I have a pretty beginner's question, but cant seem to get this right with what i try. I had the same problem with using LIKE and OR with multiple fields and queries. essentially i just want to ORDER BY using multiple fields i have in the DB. my query is something like this $query = "SELECT * FROM content WHERE rating > 7 ORDER BY `votes` DESC, `release_date` DESC "; now i understand with LIKE and OR you must separate arguments with (), but with ORDER BY i am not using any AND / OR so i though a comma should be sufficient. Yet, i can never seem to get this work properly. any suggestions to get both fields to ORDER BY ? thanks guys

hey guys, i have a MySQL query that has multiple LIKE wildcard statement and multiple ORDER BY statements. "SELECT * FROM `content` WHERE `release_date` LIKE '%2013-06%' OR `release_date` LIKE '%2013-05%' OR `release_date` LIKE '%2013-04%' AND `release_country` = 'USA' AND `is_active_member` = 1 ORDER BY `votes' DESC, `release_date` DESC LIMIT 0 , 50" as you can see i have 3 wildcard's 5 total conditionals 2 ORDER BY fields The ISSUE: my wildcard statements work just fine and filters what i need, everything after ( release_country, is_active_member) dont seem to filter, also my ORDER BY only passes the first conditional. and release_date is not sorted. i have done plenty of research to see if there are other formats to this( which there is ) but they also don't seem to work. The data and field names are correct, and when i place the other fields first, they come out correctly. but i cant get this exact query to work. any suggestions as why this is happening ? thanks guys

Hello, I have a table called profiles. I have 3 colums, id,property,value The property has the items name,rating,birthday and the column value holds the value of each property. Now i want to order my results by the property rating and value Desc. I'm stuck here how i can do this ? Can anyone helps me with that ?

Hi, I need to sort a query 1st based on 'lb' column, then 'oz' column so that the results show - in DESC order from the 'paired' grouping of highest to lowest 'lb', 'oz' results. Here'st the full requests showing all the weights from userid 3: mysql> select lb,oz from tfish where userid = 3; +----+----+ | lb | oz | +----+----+ | 7 | 11 | | 11 | 15 | | 6 | 15 | | 14 | 11 | | 7 | 14 | | 7 | 16 | +----+----+ 6 rows in set Objective: I want to sort on the highest weights and show them, descending, which should list in this order: 14, 11 7, 16 7, 14 7, 11 ===== This is what I get on my query when attempting to order on highest 'lb' and in DESC with limit of 4: Note: giving you more queries as well so you see the tabled info unfiltered: mysql> select lb,oz from tfish where userid = 3 order by lb desc; +----+----+ | lb | oz | +----+----+ | 14 | 11 | | 11 | 15 | | 7 | 11 | | 7 | 14 | | 7 | 16 | | 6 | 15 | +----+----+ 6 rows in set mysql> select lb,oz from tfish where userid = 3 order by lb desc limit 4; +----+----+ | lb | oz | +----+----+ | 14 | 11 | | 11 | 15 | | 7 | 11 | | 7 | 14 | +----+----+ 4 rows in set mysql> Notice in the last query, 7, 16 is missing and 7,11 listed 1st (7,16 should be listed as the 1st '7lb' reference b/c of 'oz' being 16, followed by 7lb 14 which then excludes 7lb 11oz from the query results. Ok, (below) I'm providing the full table so you see everything - any help appreciated! (thx!) mysql> select id,userid,lb,oz from tfish; +----+--------+----+----+ | id | userid | lb | oz | +----+--------+----+----+ | 1 | 3 | 7 | 11 | | 2 | 3 | 11 | 15 | | 3 | 1 | 5 | 11 | | 4 | 3 | 6 | 15 | | 5 | 3 | 14 | 11 | | 6 | 3 | 7 | 14 | | 7 | 1 | 5 | 4 | | 8 | 3 | 7 | 16 | +----+--------+----+----+ 8 rows in set

Hello, I am doing a kind of financial calculator which gives you results about the best alternatives to choose from. I do a sql query to the table having the banks data. After that i create a bunch of variables in php and echo the results. These variables are math operations consisting in sql results variables and user input variables. I know how to order sql results, but the problem is that my criteria of sorting is from the php variables created later (which consist of math oparations between sql and user input variables). I have tried sorting it by sql rows....but the dynamic php variables created later are sometimes negatively related to the sql data....so the result does not always get the same. I have an idea like putting the dynamic php variables in an array/arrays and than sorting them according to one of the php variables. But i have no idea how it can be done. Here is a partial code, just to give you an idea of what i mean. <form method="post" action="calc2.php" /> <input name="ss" class="highlight"/> <input class="highlight" name="nn" size="5" /> </form> <?php if (isset($_POST['ss']) && $_POST['nn']>5) { $target = $_POST['ss']; $result = mysql_query("SELECT * FROM list1 WHERE minimum <= {$target} ORDER BY i_rate DESC LIMIT {$number_result}"); if (mysql_num_rows($result) > 0){ while($row = mysql_fetch_array($result)) { $s = $target-$support+$pu+$pv; // according to this variable i want to sort the results, and $pu, $pv are created from user input, which i don't have it here, $traget is sql output. $s is a mix. } } } ?> PLS HEEELP, i have searched everywhere for this and cennot find nothing about exactly what i want.