PHP - Order By Question

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?

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

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!

Lets say I have this query:

$sql = mysql_query("SELECT * FROM Member ORDER BY posts DESC LIMIT 10");
while ($get = mysql_fetch_array($sql)){

$row .= $get['id'];
$posts .= $get['posts'];

echo "$row - $posts<br />";

}

Lets say I have two of rows, with posts fields of 84 and 803. When it displays it, it grabs the 84 row first, when I want the bigger number 803 first. Is there a way to fix this?

I have quite a long .php page but I've attached the end of it. I was wondering if I can change the order it is displayed. Basically $j is a year. And currently it goes from 1894 until 2019. I don't think anything is currently telling it to sort either way, it just by default shows that way. But I want it to show 2019 first and go down to 1894. I had this written for me by someone who I can no longer get hold of.

	$total_record=count($session_player);
	$i=0;
	$palyerslink="";
	foreach($session_player as $var=>$val)
	{
		$i++;
		if($i==$total_record)
			$palyerslink.="<a href='/history/?pid=$var' title='view this in more detail'>$val</a>";
		else
			$palyerslink.="<a href='/history/?pid=$var' title='view this in more detail'>$val</a>, ";
		
	}
	if(!empty($session_player))
	{
		$tr++;
		$records.="<tr class=row>
		            <td class=border-right><a href='/history/browse?season=$j'>$j-$value</a></td>
		            <td class=border-right>$max_goal_season</td>
		            <td>$palyerslink</td>
		            </tr>";
	}
}
$records.="</table></div>";
echo $records;
?>

 

This is the end of the page where the records are then displayed using the echo.

I'm not sure if more code is needed though, I could post it in full if needed

Edited May 29, 2020 by alxne

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?

Hi, I've been trying to re-order an array and I can't seem to even do it let alone neatly. I've ran out of time and would appreciate any help!


$array = array(
  '0' => array(
    'filepath' => 'files/image_site1.png',
    'title' => 'Website',
  ),
  '1' => array(
    'filepath' => 'files/image_id.png',
    'title' => 'Identitity',
  ),
  '2' => array(
    'filepath' => 'files/image_site2.png',
    'title' => 'Website',
  )
)

I'm looking to re-order the arrays within the parent array depending on whether the 'filepath's contain a sub string of '_id' or '_site'.

I've been using:
$type = '_site';
foreach ($array as $item){
  if (preg_match("/$type/i", $item['filename'])){
    //add this $item to the top of array, somehow...
  }
}

So with $type set to '_site', I'd like to re-arrange the array so both arrays containing _site1.png and _site2.png appear at the top of the parent array. I.e:
$array('0' = array(...), '2' => array(...), '1' => array(...));

I hope I've explained it well enough.

Thanks,

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?

Currently I have where the User can search for records, then the records are displayed on a page. I then have a drop down box to where the user can Order by 2 different categories to make it easier for the user to see things but for some reason it is not working. Here is my code.

Oh and the error I get is mysql_num_rows(): supplied argument is not a valid MySQL result resource in

<?php
$qry_str="SELECT * FROM timeslip WHERE 1 ";  

if($_POST['Initials']) 
{ 
   $Initials=$_POST['Initials'];  
   $qry_str.="and Initials='$Initials' "; 
} 

if($_POST['Identifier']) 
{ 
   $Identifier=$_POST['Identifier'];  
   $qry_str.="and Identifier LIKE '%$Identifier%' "; 
} 

if($_POST['Type']) 
{ 
   $Type=$_POST['Type'];  
   $qry_str.="and Type LIKE '%$Type%' "; 
} 
if($_POST['Terms']) 
{ 
   $Terms=$_POST['Terms'];  
   $qry_str.="and Terms LIKE '%$Terms%' "; 
} 
if($_POST['Memo']) 
{ 
   $Memo=$_POST['Memo'];  
   $qry_str.="and Memo LIKE '%$Memo%' "; 
}
if($_POST['date1']) 
{  
   $date1=$_POST['date1'];
   $date2=$_POST['date2'];
   $start=date('Y-m-d', strtotime($date1));
   $end=date('Y-m-d', strtotime($date2));
   $qry_str.="and Date >= '$start' and Date <= '$end' "; 
}
if($_POST['order1'] && $_POST['order2'])
{
$order1=$_POST['order1'];
$order2=$_POST['order2'];
$qry=$_POST['qry'];
$qry_str="$qry ORDER BY $order1 ASC, $order2 ASC ";
}
if($_POST['order1'])
{
$order1=$_POST['order1'];
$qry=$_POST['qry'];
$qry_str="$qry ORDER BY $order1 ASC";
}
if($_POST['order2'])
{
$order2=$_POST['order2'];
$qry=$_POST['qry'];
$qry_str="$qry ORDER BY $order2' ASC ";
}
$result=mysql_query($qry_str); 
$count=mysql_num_rows($result); 
if ($count>0){

?>

<form action="" method="post">
<input type="hidden" value="<?=$qry_str?>" name="qry"/>
Order Results By
<select name="order1" >
<OPTION value="<?=$order1?>"><?=$order1?></OPTION>
<OPTION value="Identifier">Identifier</OPTION>
                <OPTION value="Type">Type</OPTION>
             <OPTION value="Terms">Terms</OPTION>
                <OPTION value="Date">Date</OPTION>
             <OPTION value="Cost">Cost</OPTION>
              
</select> 
Then
<select name="order2" >
<OPTION value="<?=$order2?>"><?=$order2?></OPTION>
<OPTION value="Identifier">Identifier</OPTION>
                <OPTION value="Type">Type</OPTION>
             <OPTION value="Terms">Terms</OPTION>
                <OPTION value="Date">Date</OPTION>
             <OPTION value="Cost">Cost</OPTION>
              
</select> 
<input type="submit" name="submit">
</form>

Is it possible to use the PDO with a statement that has ORDER BY tablename.

The program works fine but when I added the ORDER BY clause it outputted an error.

Is it maybe because I set the fetch mode to PDO::FETCH_OBJ? Or is that irrelevant?

This is the error that outputs:
Code: [Select]
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY task_position' at line 1' in /tasks.php(38): PDO->query('SELECT * FROM t...') #1 tasks.php(57): tasks->grab_tasks('2') #2 {main} thrown in tasks.php on line 38
It normally worked without the ORDER BY.

Here is my MYSQL call:

$grabber = $this->pdo->query('SELECT * FROM tasks WHERE user_id = ' . $userid . "ORDER BY 'task_position'");
$grabber->setFetchMode(PDO::FETCH_OBJ);



 

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

hi, i have a field for price that gets returned.

is there a way to do ORDER BY 'category' so that i can have tally at the bottom of each category that adds all the prices together?

I'm pulling a list from a DB and inserting it in a drop down menu. Each item on the list has a numerical id associated called marketid in the DB. It works, but it orders the list alphabetically. I need it to order by marketid which is the column name. What code do I need to add? Here is what I have now....

<option value="<?=$market['marketid']?>"><?=$market['name']?></option>

Thanks in advance all!!

Tony