sorting table data with JavaScript when data is stored in MySQL DB

[webguync]

I have an AJAX based script which sorts a column when you click on the table header. This works great when it's a static table. Example can be found here: http://www.inspired-evolution.com/AJAX_Table_Sort.php

 

When I am extracting the data from a MySQL DB however the sort script no longer works. Is it not possible to sort on a MySQL results table ?

 

let me know if I need to explain further.

[BlueSkyIS]

use the ORDER BY clause

 

SELECT * FROM some_table ORDER BY last_name, first_name

[webguync]

thanks, I wish to be able to sort by clicking on the table header though. For instance if a table column is cities a user can click the header to sort alphabetically descending.

[mkoga]

thanks, I wish to be able to sort by clicking on the table header though. For instance if a table column is cities a user can click the header to sort alphabetically descending.

 

are you trying to do the sorting client side or with php?

[AdRock]

There is something like that at www.codewalkers.com where you can click on the heading and sort

[webguync]

are you trying to do the sorting client side or with php?

 

I was trying to do it client side, but since the data is stored in MySQL, I guess it needs to be done w/ PHP. This is what I am trying to figure out.

[AdRock]

Have a look here

 

http://www.codewalkers.com/c/a/Database-Articles/Sorting-Database-Results-with-PHP/1/

[webguync]

thanks, I am trying that script out but getting an error on line 37. Need help resolving the error.

that is I believe this line

 

foreach ($row as $heading=>$column) {

 

the error is "

Parse error: syntax error, unexpected '=', expecting ')' in /home/content/n/c/u/ncupsetbids/html/Quick_Test.php on line 37"

 

Here is the full code.

<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL);
/* set the allowed order by columns */
$default_sort = 'last_name';
$allowed_order = array ('joindate', 'last_name','first_name');

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise,
* set it to what was passed in. */
if (!isset ($_GET['order']) ||
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}

/* connect to db */
mysql_connect ('localhost','user','pass');
mysql_select_db ('test');

/* construct and run our query */
$query = "SELECT * FROM sometable ORDER BY $order";
$result = mysql_query ($query);

/* make sure data was retrieved */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
    /* check if the heading is in our allowed_order
     * array. If it is, hyperlink it so that we can
     * order by this column */
    echo "<TD><b>";
    if (in_array ($heading, $allowed_order)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
    } else {
        echo $heading;
    }
    echo "</b></TD>\n";
}
echo "</TR>\n";

/* reset the $result set back to the first row and
* display the data */
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";
?>

[webguync]

anyone know why this error is being produced? I cannot figure it out :-(

[mkoga]

not sure if it's just the posting, but line 37 should be:

 

foreach ($row as $heading => $column) {

[redarrow]

<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL);
/* set the allowed order by columns */
$default_sort = 'last_name';
$allowed_order = array ('joindate', 'last_name','first_name');

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise,
* set it to what was passed in. */
if (!isset ($_GET['order']) ||
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}

/* connect to db */
mysql_connect ('localhost','user','pass');
mysql_select_db ('test');

/* construct and run our query */
$query = "SELECT * FROM sometable ORDER BY $order";
$result = mysql_query ($query);

/* make sure data was retrieved */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
    /* check if the heading is in our allowed_order
     * array. If it is, hyperlink it so that we can
     * order by this column */
    echo "<TD><b>";
    if (in_array ($heading, $allowed_order)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
    } else {
        echo $heading;
    }
    echo "</b></TD>\n";
}
echo "</TR>\n";

/* reset the $result set back to the first row and
* display the data */
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";
?>

[webguync]

thanks for the help so far.

 

I am now getting this when I display the page

 

"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /hsphere/local/home/bruceg/inspired-evolution.com/Quick_Test.php on line 30 No data to display!"

 

I believe that I have all of the info set up with the DB, Table Name and fields.

 

<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL);
/* set the allowed order by columns */
$default_sort = 'last_name';
$allowed_order = array ('joindate', 'last_name','first_name');

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise,
* set it to what was passed in. */
if (!isset ($_GET['order']) ||
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}

//check for validity of user
$db_name="bruceg_skillset";
$table_name ="sort_test";
$connection = @mysql_connect("localhost", "username", "pw")
or die (mysql_error());
$db = @mysql_select_db($db_name, $connection) or die (mysql_error());

/* construct and run our query */
$query = "SELECT * FROM sometable ORDER BY $order";
$result = mysql_query ($query);

/* make sure data was retrieved */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
    /* check if the heading is in our allowed_order
     * array. If it is, hyperlink it so that we can
     * order by this column */
    echo "<TD><b>";
    if (in_array ($heading, $allowed_order)) {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
    } else {
        echo $heading;
    }
    echo "</b></TD>\n";
}
echo "</TR>\n";

/* reset the $result set back to the first row and
* display the data */
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TR>\n";
    foreach ($row as $column) {
        echo "<TD>$column</TD>\n";
    }
    echo "</TR>\n";
}
echo "</TABLE>\n";
?>

[webguync]

also I don't see where the variable order is being set

 

SELECT * FROM sometable ORDER BY $order

 

does order need to be a column in your MySQL table?

[webguync]

I didn't have the right table name in My SQL statement. I changed that and I no longer get the error but...

 

for some reason the < and > is not being rendered as HTML brackets, thusly the HTML isn't being rendered.

 

See here and view source http://www.inspired-evolution.com/Quick_Test.php

 

any ideas why?

[webguync]

trying to get this resolved and working, any thoughts or ideas on why this is occurring?

[wmguk]

have you tried replacing them with < > and just trying that?

[webguync]

yea, that worked, I am still curious why the character entities weren't being rendered correctly. The script is working too so thanks for all the assistance on that.