Sort MYSQL TABLES using PHP

Question

I have a table that sorts the name by ASC order but when i click the button it doesn't work. I tried doing the same with 2 buttons and checked some of the codes available but it doesn't work at all. Any help?

PHP Code:

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "myfeeds";

    $conn = mysql_connect($servername, $username, $password, $dbname);
    if (!$conn) {
        die("Connection failed");
    }

    $db = mysql_select_db("myfeeds", $conn);
    if (!$db) {
        die("Can't select database");
    }

    if (isset($_POST['asc'])) {
        $result = mysql_query("SELECT * FROM websites ORDER BY name ASC");
    } else {
        $result = mysql_query("SELECT * FROM websites ORDER BY name DESC");

    }

    if (!$result) {
        die("Failed to show queries from table");
    }


    $num = mysql_numrows($result);
    mysql_close();
    ?>

Here's the button:

SORT BY:  
            <form method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
                <button type="submit" id="asc" name="asc">ASC</button>
            </form>

Table:

                    <table cellpadding="0">
                    <tr>
                        <th>ID</th>
                        <th>Name</th>
                        <th>URL</th>
                        <th>Description</th>
                        <th>Logo</th>
                    </tr>

                    <?php
                    $i = 0;

                    while ($i < $num) {
                        $f5 = mysql_result($result, $i, "id");
                        $f1 = mysql_result($result, $i, "name");
                        $f2 = mysql_result($result, $i, "url");
                        $f3 = mysql_result($result, $i, "description");
                        $f4 = mysql_result($result, $i, "image");
                        ?>
                        <tr>
                            <td><?php echo $f5; ?></td>
                            <td><?php echo $f1; ?></td>
                            <td><?php echo $f2; ?></td>
                            <td><?php echo $f3; ?></td>
                            <td><?php echo "<img src='$f4'>"; ?></td>
                        </tr>
                        <?php
                        $i++;
                    }
                    ?>
                </table>

Sidenote: mysql_numrows is invalid. That should read as mysql_num_rows. Plus, where are you echoing your results? It's not in what you posted. You need to echo those. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 23, 2014 at 4:27
Add error reporting to the top of your file(s) right after your opening <?php tag error_reporting(E_ALL); ini_set('display_errors', 1); see if it yields anything. Also or die(mysql_error()) to mysql_query(). Doing die("Message X") doesn't help. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 23, 2014 at 4:29
@CEP <button type="submit" id="asc" name="asc">ASC</button> is valid. Had the OP used <button type="button" id="asc" name="asc">ASC</button> then it would not have worked. OP's error is either elsewhere and is not showing it, or if it's the exact code that is using, isn't doing anything with the query. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 23, 2014 at 4:34
@Fred-ii- Noted, thanks :) That is new to me. And yes, I think OP is not not doing anything with the result. — pradeepcep
– pradeepcep, Commented Nov 23, 2014 at 4:37

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

According to the manual, the fourth parameter of mysql_connect should be a new link of connection, not the database name.

new_link

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. >The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters. In SQL safe mode, this parameter is ignored.

I suggest use mysqli_* instead, since mysql is deprecated.

And of course, don't forget to fetch the rows after the query.

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myfeeds";

$conn = mysqli_connect($servername, $username, $password, $dbname);

$order = isset($_POST['asc']) ? 'ASC' : 'DESC';
$sql = "SELECT * FROM websites ORDER BY name $order";
$query = mysqli_query($conn, $sql);

$num = $query->num_rows;
if($num > 0) {
    while($row = mysqli_fetch_assoc($query)) {
        echo $row['name'] . '<br/>';
    }
}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 23, 2014 at 4:45

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Funk Forty Niner Over a year ago

"According to the manual, the fourth parameter of mysql_connect should be a new link of connection, not the database name." - Well I feel like a real "duh" lol +1 ;) just goes to show how much about mysql_ I know. I'm a "mysqli/PDO" guy.

Kevin Over a year ago

@Fred-ii- yeah i thought i was seeing mysqli at first, but it was mysql_connect, and having that $dbname in there was a bit off.

Funk Forty Niner Over a year ago

OP using die("Connection failed") doesn't help neither. die(mysql_error()) is better.

Kevin Over a year ago

@Fred-ii- surprisingly yes of course after fixing the num rows

Kamilah Over a year ago

@Ghost Thank you for the advice. I'm new to mysql and php. I'm just referring everything from our lecture notes.

|

jmn · Accepted Answer · 2014-11-24 08:14:11Z

try this.. working 100% =)

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "myfeeds";

    $conn = mysql_connect($servername, $username, $password, $dbname);
    if (!$conn) {
        die("Connection failed");
    }

    $db = mysql_select_db("myfeeds", $conn);
    if (!$db) {
        die("Can't select database");
    }

    if (isset($_GET['asc']))
        $result = mysql_query("SELECT * FROM websites ORDER BY name ASC");
    else
        $result = mysql_query("SELECT * FROM websites ORDER BY name DESC");


    if (!$result)
        die("Failed to show queries from table");

    if (mysql_num_rows($result) > 0) {
        // output data of each row
        while($row = mysql_fetch_assoc($result)) {
            echo "name: " . $row["name"]. "<br>";
        }
    } else {
        echo "0 results";
    }

    $num = mysql_numrows($result);
    mysql_close();

?>

<form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    <button type="submit" id="asc" name="asc" value="asc">ASC</button>
    <button type="submit" id="asc" name="desc" value="desc">DESC</button>
</form>

it is better to use PDO this is how..

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "myfeeds";


    try
    {
        $conn = new PDO("mysql:host=".$servername.";dbname=".$dbname, $username, $password);
        $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e)
    {
        die($e->getMessage());
    }


    try
    {
        if (isset($_GET['asc']))
            $result = $conn->prepare("SELECT * FROM websites ORDER BY name ASC");
        else
            $result = $conn->prepare("SELECT * FROM websites ORDER BY name DESC");

        $result->execute();

        if($result->rowCount())
        {
            while($r = $result->fetch(PDO::FETCH_OBJ))
            {
                echo 'Name:' . $r->name . '<br/>';
            }
        }
        else echo 'no record found!';

    }
    catch (PDOException $e)
    {
        die($e->getMessage());
    }

?>

Viewing datas from database using mysql

<table cellpadding="0">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>URL</th>
        <th>Description</th>
        <th>Logo</th>
    </tr>

    <?php

    while ($row = mysql_fetch_assoc($result)) {
        ?>
        <tr>
            <td><?php echo $row["id"]; ?></td>
            <td><?php echo $row["name"]; ?></td>
            <td><?php echo $row["url"]; ?></td>
            <td><?php echo $row["description"]; ?></td>
            <td><?php echo "<img src='".$row["image"]."'>"; ?></td>
        </tr>
        <?php

    }
    ?>
</table>

@jmn I tried your codes and it works but I don't how to implement it using my table output.
i already edited the code and added how to view information from database

Collectives™ on Stack Overflow

Sort MYSQL TABLES using PHP

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related