|
PHP - Select According To Particular Order
I teach 6 different class groups, named "A" to "F", respectively.
Similar TutorialsHi ho, I am trying to assemble a table so that it shows article in order by id descending. The current one i am using is working but not displaying as i hoped, i need them aligned on the left and right, so id=1 align left, id=2 align right etc. Here's my current code Code: [Select] <table style='table-layout:fixed' align="center" cellpadding="2" cellspacing="0" colspan="" class="Main"><br> <? $select_paper=mysql_query("SELECT * FROM paper ORDER BY id DESC"); while($the=mysql_fetch_object($select_paper)){ ?> <tr> <td class="subtableheader" width="25%"><?php echo "$the->title"; ?></td> </tr> <tr> <td class="profilerow" width="100%"><?php echo "$the->news"; ?></td> </tr> <tr> <td class="subtableheader" width="25%"><?php echo "Article By $the->by - $the->date"; ?></td> </tr> Hello.. I'm a somewhat beginner in php, but i decided to create my own basic forums system with a content managing system. Right now I'm trying to figure out how to make a thread link be at the top of the list for the forum display page, when someone posts a response or creates a new thread. HELP MUCH APPRECIATED How can i select the last 3 rows in a table and order it by ASC. When i use DESC it displays the results in the wrong order. $query24 = mysql_query("SELECT * FROM notifications WHERE to_id='$session' AND state='1' ORDER BY id DESC LIMIT 3 "); I have 2 tables I have settup called Users_Messages and Users_Message_Replies inside each of these tables I have a row called DateSent, I'm trying to select from both of these tables and only display the latest Sent item by ID and order them all by date. I can get it to display the items correctly using the below code, but I can't get them to order correctly by the latest date in both of the Tables. Code: [Select] $page_query = mysql_query(" SELECT MessageID, DateSent FROM Users_Messages WHERE ToID = '$user_ID' UNION SELECT MessageID, DateSent FROM Users_Messages WHERE FromID = '$user_ID' ORDER BY DateSent DESC "); while ($replycheck = mysql_fetch_assoc($page_query)){ $message_idmainnew = $replycheck['MessageID']; $date = $replycheck['DateSent']; $sql2 = "SELECT MainMessageID FROM Users_Message_Replies WHERE MainMessageID = '$message_idmainnew '"; $sql_result2 = mysql_query($sql2); $replycheck2 = mysql_fetch_assoc($sql_result2); $newreplyID = $replycheck2['MainMessageID']; $sql4 = "SELECT MessageID FROM Users_Messages WHERE MessageID= '$message_idmainnew'"; $sql_result4 = mysql_query($sql4); $messagecheck2 = mysql_fetch_assoc($sql_result4); $newmessageID = $messagecheck2['MessageID']; if ($newreplyID == NULL){ $sql2 = "SELECT * FROM Users_Messages WHERE MessageID= '$newmessageID' ORDER BY DateSent ASC"; $sql_result2 = mysql_query($sql2); $message_row = mysql_fetch_assoc($sql_result2); $message_01 = $reply_row['Message']; $date = $reply_row['DateSent']; $date1 = strtotime($date); $datemain = date('F j, Y, g:i a', $date1); }else{ $sql2 = "SELECT * FROM Users_Message_Replies WHERE MessageID= ' $newreplyID ' ORDER BY DateSent DESC"; $sql_result2 = mysql_query($sql2); $reply_row = mysql_fetch_assoc($sql_result2); $message_01 = $reply_row['Message']; $date = $reply_row['DateSent']; $date1 = strtotime($date); $datemain = date('F j, Y, g:i a', $date1); } { Is there an easier way to do this? And how would I get the dates to line up, with a Join? Thanks. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=321906.0 Hey guys, I am having trouble figuring this out. I currently have a database that automatically sorts itself by the id of the data. But, I am wanting the user to be able to change the sort order via a select box. I already have the select box programmed with the options, but I am not sure how to go about coding the options to change the sort order of the displayed data. What would the best way to go about programming this be? Thank you very much ahead of time!!
Hi everyone. I'm very new into self learning programming. Presently I'm trying to develop a simple basic Robot that would only Place a Market Order every seconds and it will cancel the Order every followed seconds. Using the following library: It would place Trade Order at a ( Price = ex.com api * Binance api Aggregate Trades Price) I have already wrote the api to call for xe.com exchange rate with php
<?php
$auth = base64_encode("username:password");
$context = stream_context_create([
"http" => [
"header" => "Authorization: Basic $auth"
]
]);
$homepage = file_get_contents("https://xecdapi.xe.com/v1/convert_from?to=NGN&amount=1.195", false, $context );
$json = json_decode($homepage, TRUE);
foreach ($json as $k=>$to){
echo $k; // etc
};
?>
And also for the Binance Aggregate Price in JavaScript
<script>
var burl = "https://api3.binance.com";
var query = '/api/v3/aggTrades';
query += '?symbol=BTCUSDT';
var url = burl + query;
var ourRequest = new XMLHttpRequest();
ourRequest.open('GET',url,true);
ourRequest.onload = function(){
console.log(ourRequest.responseText);
}
ourRequest.send();
</script>
My problem is how to handle these two api responds and also the functions to use them to place a trade and cancel it. I've got. hirealimo.com.au/code1.php this works as i want it: Quote SELECT * FROM price INNER JOIN vehicle USING (vehicleID) WHERE vehicle.passengers >= 1 AND price.townID = 1 AND price.eventID = 1 but apparelty selecting * is not a good thing???? but if I do this: Quote SELECT priceID, price FROM price INNER JOIN vehicle....etc it works but i lose the info from the vehicle table. but how do i make this work: Quote SELECT priceID, price, type, description, passengers FROM price INNER JOIN vehicle....etc so that i am specifiying which colums from which tables to query?? thanks I have 2 queries that I want to join together to make one row
Dear All, I wish to have 2 drop down boxes, Country Select Box and Locality Select Box. The locality select box will be affected by the value chosen in the country select box. All is working fine except that the locality select box is not being populated. I know that the problem is in the sql statement WHERE country_id='$co' because i am having an error that $co is an undefined variable. All the rest works fine because i have replaced the $co variable directly with a number (say 98) for a particular country id and it worked fine. In what way can i define this variable $co so that it is accepted by my sql statement? Thank you for your help in advance. MySQL Tables indicated below: CREATE TABLE countries( country_id INT(3) UNSIGNED NOT NULL AUTO_INCREMENT, country_name VARCHAR(30) NOT NULL, PRIMARY KEY(country_id), UNIQUE KEY(country_name), INDEX(country_id), INDEX(country_name)) ENGINE=MyISAM; CREATE TABLE localities( locality_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, country_id INT(3) UNSIGNED NOT NULL, locality_name VARCHAR(50), PRIMARY KEY (locality_id), INDEX (country_id), INDEX (locality_name)) ENGINE=MyISAM; Extract PHP script included below: // connect to database require_once(MYSQL); if(isset($_POST['submitted'])) { // trim the incoming data /* this line runs every element in $_POST through the trim() function, and assigns the returned result to the new $trimmed array */ $trimmed=array_map('trim',$_POST); // clean the data $co=mysqli_real_escape_string($dbc,$trimmed['country']); $lc=mysqli_real_escape_string($dbc,$trimmed['locality']); } ?> <form action="form.php" method="post"> <p>Country <select name="country"> <option>Select Country</option> <?php $q="SELECT country_id, country_name FROM countries"; $r=mysqli_query($dbc,$q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc)); while($row=mysqli_fetch_array($r)) { $country_id=$row[0]; $country_name=$row[1]; echo '<option value="' . $country_id . '"'; if(isset($trimmed['country']) && ($trimmed['country']==$country_id)) echo 'selected="selected"'; echo '>' . $country_name . '</option>\n'; } ?> </select> </p> <p>Locality <select name="locality"> <option>Select Locality</option> <?php $ql="SELECT locality_id, country_id, locality_name FROM localities WHERE country_id='$co' ORDER BY locality_name"; $rl=mysqli_query($dbc,$ql) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc)); while($row=mysqli_fetch_array($rl)) { $locality_id=$row[0]; $country_id=$row[1]; $locality_name=$row[2]; echo '<option value="' . $locality_id . '"'; if(isset($trimmed['locality']) && ($trimmed['locality']==$locality_id)) echo 'selected="selected"'; echo '>' . $locality_name . '</option>\n'; } // close database connection mysqli_close($dbc); ?> </select> </p> <p><input type="submit" name="submit" value="Submit" /></p> <input type="hidden" name="submitted" value="TRUE" /> </form> Hello, I have classes in a database with no set UNIX date, just the day like Wednesday and in two other columns the start and end dates. I want to be able to order by the day first and then by end_time but php orders the day column by spelling and not the day it holds in chronological order. Is there anyway to change the query to order the day column as a date? See the query below? Code: [Select] $query = "SELECT * FROM zumba_timetable WHERE end_time>'$current_time' ORDER BY day, end_time ASC LIMIT 0,1"; Hi there, just registered and in need of help, this looks a good place to start! I'm a php beginner so please bear with me I have a mysql database which holds 3 pieces of info id: match: 1: I'm trying to sort the results using ORDER BY match ASC but for some reason it's just not for having it... Can't work out if it's an error with my php code or my table. I can order by "ID" no problem but I when I try ordering alphabetically by "match" it won't. my code is: Code: [Select] mysql_connect("***.***.***") or die(mysql_error()); mysql_select_db("database") or die(mysql_error()); $query = "SELECT * FROM employees WHERE flag = 'tv' ORDER BY match ASC"; $result = mysql_query($query) or die ("Query failed"); $numrows = (mysql_num_rows ($result)); // loop to create rows if($numrows >0){ echo "<table width = 100% border = '0' cellspacing = '2' cellpadding = '0' >"; // loop to create columns $position = 1; while ($row = mysql_fetch_array($result)){ if($position == 1){echo "<tr>";} echo " <td align = 'center'><a href=\"http://www.website.eu/sport-stream/1/{$row['id']}.html\" target=_blank>{$row['match']} <br> <img src=\"{$row['8']}\" width=\"100\" height=\"70\" /> </a> </td> "; if($position == 4){echo "</tr> "; $position = 1;}else{ $position++;} }//while $end = ""; if($position != 1){ for($z=(4-$position); $z>0 ; $z--){ $end .= "<td></td>"; } $end .= "</tr>"; } echo $end."</table> "; }//if And here's a cap of my database: any help for a php n00b would be greatly appreciated! Currently i have a query which at the end has this Code: [Select] order by (value/counter) desc"); which works fine, however i was wondering, if in the case that 2 rows have the same, is it possible to set a second value to order by? I need to make 0, which displays as POA appear at the ned of the list not at the begining, I currently just use order by price asc and 0 is at the beginning, i need to make 0 at the end I got this code from a previous thread: Code: [Select] mysql_query("SET @rows = 0;"); $res = mysql_query("SELECT @rows:=@rows+1 AS view_rank,COUNT(id) AS views, credit_members_id FROM vtp_tracking GROUP BY credit_members_id ORDER BY views DESC"); $n = array(1 => 'st', 2 => 'nd', 3 => 'rd'); while($row = mysql_fetch_row($res)) { if ( $row[2] != $members_id ) continue; if ( substr($row[0], -1) < 4 ) { $row[0] .= $n[$row[0]]; } else { $row[0] .= 'th'; } echo ' You are in ' . $row[0] . ' place with ' . number_format($row[1]) . ' views.'; break; } Everything seems ok except it orders by the "credit_members_id" and not "views" as entered. Can someone explain why, and how to fix this? Hey everybody, i'm building a website for the softball league i play in.... so far the website is pretty good... with all the stats and profiles.... i'm having a little problem with the ORDER of the leaders in the categories i have to calculate the result... for example... avg. is the result of the total hits divided by the total number of at bats... both numbers are coming from the database... the problem comes when i have to order the leaders from first to last... this is my php: Code: [Select] $sql = "select profile_id, SUM(total_hits) AS hitstotal, SUM(ab) AS abtotal from batters WHERE year = '2010' GROUP BY profile_id"; $cons = mysql_query($sql); while ($result = mysql_fetch_array($cons)) { $sumaab = $resultad['abtotal']; $sumahits = $resultad['hitstotal']; $p_id = $resultad['profile_id']; $prom = round(($sumahits."") / ($sumaab.""),3); that gives me all the batting averages for the players. and here is my question: how can i order that $prom result from first to last? By not using the order by function in SQL. Like I have $match_1 and $segment_1. They are two seperate tables so how would I order them like the ORDER BY in sql. Is there a way to do that? SELECT field1 FROM table1 ORDER BY field2, field3 However I need to add something in so it also orders by a value that is returned from a calculation: SELECT field1 FROM table1 ORDER BY field2, (if field 4=="" and field 5==1 then order these records in order), field3 So they are ordered by field2, then the calculation, then field3 I am attempting to order the results of a query using the get value but they wont order. here is the form: echo "<form name=\"order_form\" action=\"{$site_root}/index.php\" method=\"GET\">"; echo "<input type=\"hidden\" value=\"{$forum_id}\" name=\"forum\" />"; echo "<select name=\"order_by\" style=\"margin-left:5px;\">"; echo "<option value=\"asc\" name=\"order_option\" />Order By: Ascending</option>"; echo "<option value=\"desc\" name=\"order_option\" />Order By: Descending</option>"; echo "</select>"; echo "<select name=\"sort_by\" style=\"margin-left:5px;\">"; echo "<option value=\"topic_name\" name=\"sort_option\" />Sort By: Topic Name</option>"; echo "<option value=\"topic_poster\" name=\"sort_option\" />Sort By: Topic Author</option>"; echo "<option value=\"topic_time_posted\" name=\"sort_option\" />Sort By: Time Posted</option>"; echo "<option value=\"topic_views\" name=\"sort_option\" />Sort By: Topic Views</option>"; echo "<option value=\"topic_replies\" name=\"sort_option\" />Sort By: Topic Replies</option>"; echo "<option value=\"topic_last_poster\" name=\"sort_option\" />Sort By: Last Poster</option>"; echo "<option value=\"topic_last_post_time\" name=\"sort_option\" />Sort By: Last Post Time</option>"; echo "</select>"; echo "<input type=\"submit\" value=\"Order\" />"; echo "</form>"; here is the query: $topic_info_query = $db->query("SELECT f.forum_id, f.forum_name, m.user_id, m.user_username, m.user_group, t.thread_topic_id, t.topic_name, t.topic_poster, t.topic_time_posted, t.topic_views, t.topic_replies, t.topic_last_poster, t.topic_last_post_time, t.topic_locked, t.topic_sticky, t.topic_edited, t.topic_last_poster_id, t.topic_last_poster_group, t.topic_icon FROM ".DB_PREFIX."topics as t LEFT JOIN ".DB_PREFIX."members as m ON t.topic_poster = m.user_username LEFT JOIN ".DB_PREFIX."forums as f ON t.forum_id = f.forum_id WHERE t.forum_id = '$forum_id' '".$sort_by . ' ' . $order_by."'") and here is where $sort_by and $order_by are defined: if (isset($_GET['order_by'])) { $order_by = mysql_real_escape_string($_GET['order_by']); $order_by = strtoupper($order_by); } if (isset($_GET['sort_by'])) { $sort_by = 'ORDER BY t.'.mysql_real_escape_string($_GET['sort_by']); } when i echo '".$sort_by . ' ' . $order_by."' which is how it appears in the query i get: t.topic_last_post_time DESC which is exactly right. But the results are not being sorted. The echoed variables change as does the url but no sorting happens. any ideas? |
|||||||