PHP - Order Rows Of Union Query

Hello, my name is Casper.

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 ");

Script:

<?php

    $tqs_admin_flag = "SELECT * FROM `flags`";
    $tqr_admin_flag = mysqli_query($dbc, $tqs_admin_flag) or die(mysqli_error($dbc));

while($row_admin_flag = mysqli_fetch_array($tqr_admin_flag)){

        //print_r($row_admin_flag);

        $tqs_admin_flag_thread = "SELECT * FROM `thread` WHERE `id` = '" . $row_admin_flag['thread_id'] . "' ORDER BY `date_created` DESC";
        $tqr_admin_flag_thread = mysqli_query($dbc, $tqs_admin_flag_thread) or die(mysqli_error($dbc));

    while($row = mysqli_fetch_assoc($tqr_admin_flag_thread)){

        include($_SERVER['DOCUMENT_ROOT'] . "/gallerysite/admin_flag_thread.php");


    }
}

?>

This in comparison works:

$tqs_admin_flag_thread = "SELECT * FROM `thread` ORDER BY `date_created` DESC";

I would appreciate suggestions for which reasons the above script (the way it is) may not work.   With the above script the querying and printing on screen does happen, though the "ordering by DESC" does not happen.
Edited by glassfish, 24 October 2014 - 11:38 AM.

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?

I have a basic query where I am retrieving project records. I basically want to show the records from newest to oldest, but I also want to show the Featured projects first. Each Featured project is marked by "1" in the mysql table column.  If it's not featured, then it's marked "0".

$find_records = $db->prepare("SELECT * FROM projects ORDER BY project_id DESC, featured DESC LIMIT 10");
$find_records->execute();
$result_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_records) > 0) {
  foreach($result_records as $row) {
    
  }
}

The issue with above query is that the ORDER BY works only for the first component(project_id) and ignores the second component(featured). Do you see what's wrong with it?

I am trying to use the following query with sorting:
$query = mysql_query("SELECT * FROM Table WHERE Date LIKE '$PeriodSelected' ORDER BY Date DESC"); 
$PeriodSelected is basically supposed to get me all results from month selected and it works fine. Now I am trying to add sorting to this query and although I get no errors it does not matter if I use ASC or DESC my results are sorted in the same way. Not sure what I am doing wrong.

Hi

I'm not at all good at PHP and haven't really played with it for some years, so bare with me if code examples are old

I'm looking for a simple solution to count rows (MySQL), without using multiple queries.

The below code is quite simple, counting rows where 'Department' is 'Sales':

$sql ="SELECT * FROM DB WHERE Department ='Sales'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

But what If I need to count the rows for the different departments?:

$sql ="SELECT * FROM DB WHERE Department ='Sales'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

$sql ="SELECT * FROM DB WHERE Department ='Support'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

$sql ="SELECT * FROM DB WHERE Department ='Online'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

There must be a better / simpler way to this with a single query?

Hope for someone to show me the light

 

PS. Once this is resolved, I'll probably need further help, to accomplish my final result.

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=312690.0

I am loading notifications from a database table called "notifications" and I am having a little trouble getting them to order in the correct way.

my query I'm using right now:

Code: [Select]
$query = mysql_query("SELECT B.* FROM (SELECT A.* FROM notifications A WHERE A.user_id='$session' AND A.from_id!='$session' ORDER BY A.id ASC ) AS B ORDER BY B.state ASC LIMIT 7");

this works well as far as showing the unread notifications on top, then the read notifications below, however it's not ordering the two sets by ID (which the id auto increments so the higher id number is the newest) from newest on top to the oldest on bottom, still keeping them separated by the unread on top, read on bottom (column name is state for showing whether they're read or not).
The order the notifications are displaying by their ID is:

3
5
2
4
1

when it should be:

5
3
2
4
1

Hi Everyone,

I am working on implementing a blog comment system using the query below to display comments. My question is what is the best way to do a row count for the id column with this query or do I need to do a second db query to accomplish this? The purpose of this is to echo out the number of comments for that post, before displaying them.

Any help is appreciated.

Thanks in advance,

kaiman


<?php
// get url variables
$post_id = mysql_real_escape_string($_GET['id']);

include ("../../scripts/includes/nl2p.inc.php");

// connects to server and selects database
include ("../../scripts/includes/dbconnect.inc.php");

// table name
$tbl_name3="blog_comments";

// select info from comments database
$result3 = mysql_query ("SELECT count(*) FROM $table_name3 WHERE id='$post_id' ORDER BY id DESC LIMIT 1") 
or trigger_error("A mysql error has occurred!");
if (mysql_num_rows($result3) > 0 ) {
while($row = mysql_fetch_array($result3)) {
extract($row);

// display number of comments


// display date
$row_date = strtotime($row['date']);
putenv("TZ=America/Denver");
echo "<p class=\post\">On ".date('F, jS, Y', $row_date)." ";

// display commenter name
if($row['url'] == "") { 
echo $row['name']." wrote:</p>\n"; 
} 
     else { 
echo "<a href=\"".$row['url']."\" target=\"_blank\">".$row['name']."</a> wrote:</p>\n"; 
} 

// display content
$comments = $row['comment'];
echo nl2p($comments);
}
}
else {
echo "<p class=\"large_spacer\">No Comments</p>";
}
?>

I have 3 set of pages. The first html page only contains a search textbox and a submit button which then call up the second page search.php which populate html table with search result. The third page is update.php which allows to update the records populated by search.php.

The problem is that it when it update the records it automatically updates all rows in the table making all records look the same. I do not know where I have made a mistake please help. When I use phpadmin to update the records no problem as I can specifically input the cf_id number directly.


Search.php code
Code: [Select]
  <?php


  if(isset($_POST['submit'])){ 
  if(isset($_GET['go'])){ 
  if(preg_match("^/[A-Za-z]+/", $_POST['name'])){ 
   $name=$_POST['name']; 
  } 
  } 
  else{ 
  echo  "<p>Please enter a search query</p>"; 
  } 
}
//connect  to the database 


$con = mysql_connect("localhost","dbusrn","dbpwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydb", $con);
$name = $_POST['name'];
$result = mysql_query ("select * from Customer_Registration where Firstname like '%$name%' or lastname like '%$name%' ");



$row = mysql_fetch_row($result);
$cf_uid = $row[0];
$Firstname = $row[6];
$lastname = $row[7];
$Address = $row[8];
$Postcode = $row[9];
$Phone = $row[10];
$Email = $row[11];
$Customer_Type = $row[12];
$Computer_type = $row[13];
$Computer_maker = $row[14];
$Model = $row[15];
$OS = $row[16];
$Appointment_date = $row[17];
$Problem = $row[18];
$Solution = $row[19];
$Comment = $row[20];
?>
       

 <form action="updatecus.php" method="post">

<table width="100%" border="2" cellspacing="0" cellpadding="8">

<tr><td width="45%" class="FormText">Customer ID:</td>
<td width="55%"><?php echo $row[0];?></td></tr>
<tr><td width="45%" class="FormText">First name:</td>
<td width="55%"><input  name="Firstname" type="text" value="<?php echo $Firstname;?>"?> </td></tr>


<tr><td width="45%" class="FormText">Last name:</td>
<td width="55%"><input  name="lastname" type="text" value="<?php echo $lastname;?>"?> </td></tr>



<tr><td width="45%" class="FormText">Address:</td>
<td width="55%"><input  name="Address" type="text" value="<?php echo $Address; ?>"?> </td></tr>

 <tr><td width="45%" class="FormText">Postcode:</td>
<td width="55%"><input  name="Postcode" type="text" value="<?php echo $Postcode; ?>"?> </td></tr>


 <tr><td width="45%" class="FormText">Phone:</td>
<td width="55%"><input  name="Phone" type="text" value="<?php echo $Phone; ?>"?> </td></tr>

 <tr><td width="45%" class="FormText">Email:</td>
<td width="55%"><input  name="Email" type="text" value="<?php echo $Email; ?>"?> </td></tr>

 <tr><td width="45%" class="FormText">Customer:</td>
<td width="55%"><input  name="Customer_Type" type="text" value="<?php echo $Customer_Type; ?>"?> </td></tr>

 <tr><td width="45%" class="FormText">Computer :</td>
<td width="55%"><input  name="Computer_type" type="text" value="<?php echo $Computer_type; ?>"?> </td></tr>

 <tr><td width="45%" class="FormText">Manufactural:</td>
<td width="55%"><input  name="Computer_maker" type="text" value="<?php echo $Computer_maker; ?>"?> </td></tr>

 <tr><td width="45%" class="FormText">Model:</td>
<td width="55%"><input  name="Model" type="text" value="<?php echo $Model; ?>"?> </td></tr>

 <tr><td width="45%" class="FormText">Operating System:</td>
<td width="55%"><input  name="OS" type="text" value="<?php echo $OS; ?>"?> </td></tr>

 <tr><td width="45%" class="FormText">Appointment:</td>
<td width="55%"><input  name="Appointment_date" type="text" value="<?php echo $Appointment_date; ?>"?> </td></tr>

  <tr><td width="45%" class="FormText">Problem:</td>
<td width="55%"><textarea name="Problem" rows="10" cols="20" ><?php echo $Problem; ?></textarea></td></tr>

 <tr><td width="45%" class="FormText">Solution:</td>
<td width="55%"><textarea name="Solution" rows="10" cols="20" ><?php echo $Solution; ?></textarea></td></tr>

 <tr><td width="45%" class="FormText">Comment:</td>
<td width="55%"><textarea name="Comment" rows="10" cols="20" ><?php echo $Comment; ?></textarea></td></tr>

 
 
<td width="55%"><input name="submit"  value="submit"  type="submit" <br> <input type="Submit" value="Cancel"</br></td>


        </form>
        <?php

 
 
?>

update.php code

Code: [Select]
<?php
//connect  to the database 


$con = mysql_connect("localhost","dbusrn","dbpwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydb", $con);




//if (isset($_POST['submit']))  {
$cf_id  = $_POST['cf_id'];
$Firstname = $_POST['Firstname'];
$lastname = $_POST['lastname'];
$Address = $_POST['Address'];
$Postcode = $_POST['Postcode'];
$Phone = $_POST['Phone'];
$Email = $_POST['Email'];
$Customer_Type = $_POST['Customer_Type'];
$Computer_type = $_POST['Computer_type'];
$Computer_maker = $_POST['Computer_maker'];
$Model = $_POST['Model'];
$OS = $_POST['OS'];
$Appointment_date = $_POST['Appointment_date'];
$Problem = $_POST['Problem'];
$Solution = $_POST['Solution'];
$Comment = $_POST['Comment'];



if (isset($_POST['submit']))  {
  

$query ="UPDATE Customer_Registration SET  Firstname='$Firstname',lastname='$lastname',Address='$Address',Postcode='$Postcode',Phone='$Phone',Email='$Email',Customer_Type='$Customer_Type',Computer_type='$Computer_type',Computer_maker='$Computer_maker',Model='$Model',OS='$OS',
Appointment_date='$Appointment_date',Problem='$Problem',Solution='$Solution',Comment='$Comment' WHERE cf_id=cf_id"or die (mysql_error());

echo $query;

mysql_query($query) or die(mysql_error());
//mysql_close($con);
 
echo "<p>Congrats Record Updated</p>";
}

?>
Thank you in advance for your help

I have a query that pulls 1 field with 20 rows of data.  I need to assign each row to a different variable so that I can then display them in different locations on a page.
I cannot make each row of data a different field because of other constraints.
My data is very well normalized.
I am using mysqli so something like the old mysql_result would be lovely!
How can this be done without hitting my database 20 times?
Thanks for the help.

i am printing the dates from the last 30 days in ascending order and then comparing them in my query to print the results. How can I can print the below array in descending order without affecting my query?
Code: [Select]
   
  $thirtydaysago = time() - (30 * 24 * 60 * 60);
 
  $oneday=24 * 60 * 60;
  for($i=0;$i<31;$i++)
  {
     $d[$i]= $thirtydaysago + ($i*$oneday);
echo date('Y-m-d',$d[$i])."<br>";
  }
  for($i=0;$i<31;$i++)
  {
     $postsql=mysql_query("SELECT DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d') AS FmtDate, COUNT(postid) AS PostCnt FROM post " .
              "WHERE dateline < '" . $d[$i+1] . "' AND dateline >= '" . $d[$i] . "' GROUP BY DATE_FORMAT(FROM_UNIXTIME(dateline), '%Y-%m-%d') DESC") or die(mysql_error());
   

   if($postsql_rows=mysql_fetch_assoc($postsql))
   {
    $data_date[$i]["date"]=$postsql_rows['FmtDate'];
$data_postcount[$k][$i]["postcount"]=$postsql_rows['PostCnt'];
   }
 }
 $k++;
 }

My code:

Code: [Select]
$rank =  implode(',', $ibforums->input['rank']);
echo $rank;
For that form, it will show:

'2,0,0'


Problem is, I need to update each row with each individual value, and is this possible with using  1 query?

Hello folks,   In trying to improve the user experience for my first WebApp I have decided to create two new tables - one a master file to contain a list of all stores, and the second a master file to contain a list of all products that are normally purchased - and I would like to use the values from these two tables as lookup values in dropdown listboxes, to which the user can also add new entries.   As it turns out, I'm stuck on the very first objective i.e. to lookup/pull-in the distinct values from the master tables.   The problem I'm having is that the query seems to return no rows at all...in spite of the fact that there a records in the table, and the exact same query (when run within the MySQL environment) returns all the rows correctly.   Is there something wrong with my code, or how can I debug to see whether or not the query is being executed?   Objective # 2, which is to allow new values to be entered into the dropdown listbox, and then inserted into the respective table is certainly waaay beyond my beginner skills, and I'll most certainly need to some help with that as well..so if I can get some code/directions in that regard it will be most appreciated.   Thank you.

<?php
   $sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";

   $statement = $conn->prepare($sql);
   $statement->execute();

   $count = $statement->rowCount();                                    
   echo $count;

   // fetch ALL results pertaining to the query
   $result = $statement->fetchAll();

   echo "<select name=\"store_name\">";
   echo '<option value="">Please select...</option>';
   foreach($result as $row)
       {
          echo "<option value='" . $row['store_name'] . "'></option>";
       }
       echo "</select>";
?>