Mysql sort multiple queries

[joshkraemer]

Hi-

I have a table called registration and a table called apppost. When I run the following code below, both queries print and match fine on the same page (like I want it to).

However, when I do a sort (i.e. - http://www.example.com/folder/phpfile.php?sort=status) I get a warning that the mysql_fetch_array($result2) supplied argument is not a valid MySQL result resource. The data from the first query ($result) still displays correctly, but the data from the second query ($result2) doesn't display at all.

I've tried everything from the UNION statement to simple JOIN, but I can't get the two queries to sort together.

Any help would be appreciated. Thanks!

$sort = $_GET['sort'];
if ($sort == ""){
$sort = "ID desc";}

if ($inputname == "")
{
$sql = "SELECT * FROM registration ORDER BY $sort LIMIT $offset, $rowsPerPage"; }
else {
$sql = "SELECT * FROM registration WHERE $inputname LIKE '$inputvalue' ORDER BY $sort LIMIT $offset, $rowsPerPage";
}

$result = mysql_query($sql, $db);

while ($info = mysql_fetch_array($result)) {

$date = $info['date'];
$first_name = $info['first_name'];
$last_name = $info['last_name'];
$status = $info['status'];
$ID = $info['ID'];
$cpstaff = $info['cpstaff'];
$decision= $info['decision'];
}

$sql2 = "SELECT postdate FROM apppost WHERE postid = $ID order by $sort";
$result2 = mysql_query($sql2, $db);
if ($info2 = mysql_fetch_array($result2)) {
$lastcontact = $info2['postdate']; }

[edited by: eelixduppy at 9:40 pm (utc) on Aug. 29, 2007]
[edit reason] example.com [/edit]

[RonPK]

Welcome to WebmasterWorld, josh.

The warning indicates that the query failed. Try using mysql_error() in order to retrieve the query error:

$result2 = mysql_query($sql2, $db) or die(mysql_error());

[joshkraemer]

How do I get the columns (fields) from the first query (table) to be recognized in the second query? (that's what the error was telling me I need to do).

[borntobeweb]

Yes you can only sort on fields that appear in the select statement... You can do everything in one SQL statement:

if ($inputname == "")
$whereclause = "";
else
$whereclause = WHERE $inputname LIKE '$inputvalue';
$sql = "SELECT registration.*, apppost.postdate FROM registration
LEFT JOIN apppost ON apppost.postid = registration.id
$whereclause
ORDER BY $sort LIMIT $offset, $rowsPerPage";

$result = mysql_query($sql, $db);
while ($info = mysql_fetch_array($result)) {
...
$lastcontact = $info['postdate'];
...
}

BTW, make sure you validate all values you use from $_GET, $_POST, etc. Otherwise the first hacker to come along will break into your database by making a query like http://www.example.com/folder/phpfile.php?sort=status;+injected+statement

Search for "sql injection" in these forums, on the net, or read PHP's SQL Injection Manual page [php.net]