2nd Newbie question - results page wont load with order by in query

#1 (**permalink**) 02-28-2008, 10:31 AM

Hi thanks to Captain Paralytic I have made a great start to my project
of converting an access system to php and mysql. I now have a query
that functions works and displays the correct results.

This query runs within a PHP results page and displays the results on
screen fine. However as soon as I add an order by statement to the end
of the query, I get a default windows page saying the page cant load.
If I comment the order out the page loads fine again?

The query runs fine in the query browser with the order statement in
place, really confused by this one.

Thanks, Learning fast.
Glyn

#2 (**permalink**) 02-28-2008, 10:31 AM

glyn@amethystmailing.co.uk wrote:

> This query runs within a PHP results page and displays the results on
> screen fine. However as soon as I add an order by statement to the end
> of the query, I get a default windows page saying the page cant load.
> If I comment the order out the page loads fine again?

It's not easy to say without seeing the query in question and the php code around.

--

//Aho

#3 (**permalink**) 02-28-2008, 10:31 AM

HI the code is as below appologies if its a bit messy its my first
attempt at this.

<html>

<head>
<title></title>
</head>

<body>

<?php

//connect to mysql
mysql_connect("localhost","root","*******")
or die("Could not connect! Error: " . mysql_error());
print "connected sucsesfully </br>";
mysql_select_db("working")
or die("Could not select $db! Error: ".mysql_error());
print "db selected </br>";
//setup query
$result = mysql_query("select tbldocketref.jobno, tbljob_info.`job
description`, clients.Clientname, tbldocketref.group,
tbldocketref.docketno, date_format(tbldocketref.`mail date`, '%d/%m/
%Y') `Mail date`,
tblmsservice.name MS_Service, tblmailpieceformat.format Pack_Format,
tbldocketref.`qty mailsort`, tbldocketref.`qty std tarrif`,
tbldocketref.`qty os`, tbldocketref.`item weight`
from tbldocketref
left join tbljob_info on tbldocketref.JobNo = tbljob_info.jobno
left join clients on tbldocketref.client = clients.clientid
left join tblmailpieceformat on tbldocketref.`mail piece format` =
tblmailpieceformat.`mailpiece id`
left join tblmsservice on tbldocketref.`ms service if app` =
tblmsservice.serviceid
where clients.clientname = \"" . $_POST['client'] . "\"")

//the next statement is the one that dosnt work
//order by `mail date`

or die("Querry Error! Error: " . mysql_error());

//parse results
print " query ran </br>";
// Return the results
$num_results = mysql_num_rows($result);
print "No of recs " . $num_results;

// Test for empty result set
// If there is data, do the header (column names) row
if ($line = mysql_fetch_array($result,MYSQL_ASSOC)) {
print "<table border=1 cellpadding=3>\n<tr>";
// Parse the column names
foreach($line as $key => $value) {
print "<td><b>$key</b></td>";
}
print "</tr>\n";
// Reset data pointer
mysql_data_seek($result,0);
// Step through the data rows
while ($line = mysql_fetch_row($result)) {
print "<tr>";
// Print each value in its own cell
foreach($line as $key => $value) {
print "<td>$line[$key] </td>";
}
print "</tr>\n";
}
print "</table>\n";
} else {
// If mysql_fetch_array fails, report empty set
print "Empty result set.<p>";
}

?>
</body>

</html>

#4 (**permalink**) 02-28-2008, 10:31 AM

glyn@amethystmailing.co.uk wrote:
> HI the code is as below appologies if its a bit messy its my first
> attempt at this.
> //setup query
> $result = mysql_query("select tbldocketref.jobno, tbljob_info.`job

I always recommend you to save the query to a variable, this gives the great
advantage that you can print out the query and see if it's set right.

$query="SELECT tbldocketref.jobno, tbljob_info.`job description`,
clients.Clientname, tbldocketref.group, tbldocketref.docketno,
DATE_FORMAT(tbldocketref.`mail date`, '%d/%m/ %Y') AS `Mail date`,
tblmsservice.name AS MS_Service, tblmailpieceformat.format AS Pack_Format,
tbldocketref.`qty mailsort`, tbldocketref.`qty std tarrif`,
tbldocketref.`qty os`, tbldocketref.`item weight`
FROM tbldocketref
LEFT JOIN tbljob_info on (tbldocketref.JobNo = tbljob_info.jobno)
LEFT JOIN clients on (tbldocketref.client = clients.clientid)
LEFT JOIN tblmailpieceformat on (tbldocketref.`mail piece format` =
tblmailpieceformat.`mailpiece id`)
LEFT JOIN tblmsservice on (tbldocketref.`ms service if app` =
tblmsservice.serviceid)
WHERE clients.clientname = '{$_POST['client']}' ORDER BY `Mail date`";

> //the next statement is the one that dosnt work
> //order by `mail date`
$result = mysql_query($query)
or die("Querry Error! Error: ".mysql_error()." Query: ".$query);

The error message will be more useful and now you can copy paste the query
that the PHP was trying to use to the mysql client.

I ALWAYS use the same case on the characters, so I would write `mail date` as
`Mail date`.

Using single quotes around values will make it easier for you to see where to
add ORDER BY. I think it's best to use capital characters for SQL functions,
this way it make it easier for me to read the query.

--

//Aho

#5 (**permalink**) 02-28-2008, 10:31 AM

Fantastic, thank you so much for the advice and re writting the code,
all working now.

Glyn

#6 (**permalink**) 02-28-2008, 10:31 AM

glyn@amethystmailing.co.uk wrote:
> Fantastic, thank you so much for the advice and re writting the code,
> all working now.

It could be good to use addslashes() when you use a that is sent to the page,
this lesses the risk with SQL injections

http://www.php.net/manual/en/function.addslashes.php

$indata=addslashes($_POST['client']);
$query="SELECT tbldocketref.jobno, tbljob_info.`job description`,
clients.Clientname, tbldocketref.group, tbldocketref.docketno,
DATE_FORMAT(tbldocketref.`mail date`, '%d/%m/ %Y') AS `Mail date`,
tblmsservice.name AS MS_Service, tblmailpieceformat.format AS Pack_Format,
tbldocketref.`qty mailsort`, tbldocketref.`qty std tarrif`,
tbldocketref.`qty os`, tbldocketref.`item weight`
FROM tbldocketref
LEFT JOIN tbljob_info on (tbldocketref.JobNo = tbljob_info.jobno)
LEFT JOIN clients on (tbldocketref.client = clients.clientid)
LEFT JOIN tblmailpieceformat on (tbldocketref.`mail piece format` =
tblmailpieceformat.`mailpiece id`)
LEFT JOIN tblmsservice on (tbldocketref.`ms service if app` =
tblmsservice.serviceid)
WHERE clients.clientname = '$indata' ORDER BY `Mail date`";

--

//Aho

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode