Asked  7 Months ago    Answers:  5   Viewed   32 times

I know it's such a basic thing, but a Google search hasn't shown me how to re-sort the rows after clicking the th links.

I've got this:

<table border="1">
  <tr>
    <th>Type:</th>
    <th>Description:</th>
    <th>Recorded Date:</th>
    <th>Added Date:</th>
  </tr>

<?php 
while($row = mysql_fetch_array($result)){
    ?>
    <tr>
        <td><?php echo $row['type'] ?></td>
        <td><?php echo $row['description'] ?></td>
        <td><?php echo $row['recorded_date'] ?></td>
        <td><?php echo $row['added_date'] ?></td>
    </tr>
    <br /> 


  <?php 
}
mysql_close();
?>
</table>

I need to be able to click type and sort alphabetically, and click on either Recorded Date or Added Date and sort by date. I see that I need to have the MySQL queries do this, but do I set them up as conditionals with a href tags?

 Answers

41

The easiest way to do this would be to put a link on your column headers, pointing to the same page. In the query string, put a variable so that you know what they clicked on, and then use ORDER BY in your SQL query to perform the ordering.

The HTML would look like this:

<th><a href="mypage.php?sort=type">Type:</a></th>
<th><a href="mypage.php?sort=desc">Description:</a></th>
<th><a href="mypage.php?sort=recorded">Recorded Date:</a></th>
<th><a href="mypage.php?sort=added">Added Date:</a></th>

And in the php code, do something like this:

<?php

$sql = "SELECT * FROM MyTable";

if ($_GET['sort'] == 'type')
{
    $sql .= " ORDER BY type";
}
elseif ($_GET['sort'] == 'desc')
{
    $sql .= " ORDER BY Description";
}
elseif ($_GET['sort'] == 'recorded')
{
    $sql .= " ORDER BY DateRecorded";
}
elseif($_GET['sort'] == 'added')
{
    $sql .= " ORDER BY DateAdded";
}

$>

Notice that you shouldn't take the $_GET value directly and append it to your query. As some user could got to MyPage.php?sort=; DELETE FROM MyTable;

Wednesday, March 31, 2021
 
Nickool
answered 7 Months ago
83

After calling mysqli_stmt_store_result(), the MySQL driver will not permit you to operate on a result set until all rows are fetched or the result set is freed and the statement closed. So a subsequent call to mysqli_stmt_get_result() will return false, and probably result in an error like

Commands out of sync; you can't run this command now

which you may check with echo mysqli_error($dbc);

Transferring the statement's result set with mysqli_stmt_get_result() will give you access to its num_rows property, so you actually don't need to use mysqli_stmt_store_result(). Instead just rely on mysqli_stmt_get_result() before checking the number of rows returned:

if (mysqli_stmt_prepare($stmt_sel, $prep_sel)) {
     mysqli_stmt_bind_param($stmt_sel, 's', $form_email);
     mysqli_stmt_execute($stmt_sel);

     // Transfer the result set here:
     $result = mysqli_stmt_get_result($stmt_sel);

     // Then check rows returned on the $result obj
     // using mysqli_num_rows(), not mysqli_stmt_num_rows()
     if (mysqli_num_rows($result) == 1) {
       while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
          // Check your password, etc....
       }
     }
     else {
        // More than 1, do whatever you need to handle this
     }

     // Close it
     mysqli_stmt_close($stmt_sel);
}
Wednesday, March 31, 2021
 
max_
answered 7 Months ago
59

Follow this order

  1. Prepare Query $stmt->prepare()
  2. Bind Param if you use placeholders $stmt->bind_param("ss", $username, $password);
  3. Execute
  4. Bind Results to variables $stmt->bind_result($username, $password)
  5. The data is now ready to be picked up with $stmt->fetch()
  6. Close prepared statement $stmt->close

I think it isn't clear to you that $stmt->bind_result() already sets up variables. In your case you are likely to only fetch 1 row from the table, so $stmt->fetch() is enough. If you have multiple rows you need a loop which you use to use the data.

example:

$a = array();
while ($stmt->fetch()) {
    $a = array('username' => $username, 'password' => $password);
}

regarding the user and password match: you now have

$_POST['username'];
$_POST['password'];
$username;
$password;

so you have have to make an if statement that checks $_POST['username'] == $username && $_POST['password'] == $password. If this is true, you can set your loggedin flag.

To keep a user logged in just set a $_SESSION variable. You could set $_SESSION['logged_in'] = true;, this gives you the possibility to know the user is already authenticated if this variable is set. If user wants to end his session, just unset the variable (and destroy session).

also one more thing I noticed:

if($stmt->fetch() == true) { } // equals: if ($stmt->fetch()) { }
Wednesday, March 31, 2021
 
Xavio
answered 7 Months ago
71

In Postgres you can use a writeable CTE to update both tables in a single statement.

Assuming this table setup:

create table a (rid integer primary key, ride text, qunta integer);
create table b (kid integer primary key, rid integer references a, date date);

The CTE would be:

with new_a as (
  update a 
    set rid = 110
  where rid = 1
)
update b 
  set rid = 110 
where rid = 1;

As (non-deferrable) foreign keys are evaluated on statement level and both the primary and foreign key are changed in the same statement, this works.

SQLFiddle: http://sqlfiddle.com/#!15/db6d1/1

Saturday, August 14, 2021
 
ritch
answered 3 Months ago
73

There are 2 ways to sort. Ascending order and Descending order. You have not mentioned the order. So I am providing you both answers with 2 variations:

ASCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.product_id ASC, table2.volgnr ASC;

DESCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.product_id DESC, table2.volgnr DESC;

If you want to tell MySQL to first sort FIRST by volgnr and then by product_id:

ASCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.volgnr ASC, table2.product_id ASC;

DESCENDING ORDER:

SELECT DISTINCT table1.*
FROM table1
INNER JOIN table2 ON table1.product_id = table2.product_id
GROUP BY table1.product_id
ORDER BY table2.volgnr DESC, table2.product_id DESC;

Hope that helps.

Edit 1:

I have now edited the query so that it does not give you duplicates in results. Try it out and let me know how that goes.

Edit 2: Added Group By clause. Try this out.

Saturday, October 2, 2021
 
Malloc
answered 4 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share