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

22/12/2007, 08h01

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

22/12/2007, 08h41

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

22/12/2007, 10h31

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>

22/12/2007, 11h11

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

22/12/2007, 11h27

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

Glyn

22/12/2007, 11h36

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

22/12/2007, 08h01	#1
glyn@amethystmailing.co.uk Messages: n/a Hébergeur:	2nd Newbie question - results page wont load with order by in query 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

22/12/2007, 08h41	#2
J.O. Aho Messages: n/a Hébergeur:	Re: 2nd Newbie question - results page wont load with order by inquery 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

22/12/2007, 10h31	#3
glyn@amethystmailing.co.uk Messages: n/a Hébergeur:	Re: 2nd Newbie question - results page wont load with order by inquery 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>

22/12/2007, 11h11	#4
J.O. Aho Messages: n/a Hébergeur:	Re: 2nd Newbie question - results page wont load with order by inquery 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

22/12/2007, 11h27	#5
glyn@amethystmailing.co.uk Messages: n/a Hébergeur:	Re: 2nd Newbie question - results page wont load with order by inquery Fantastic, thank you so much for the advice and re writting the code, all working now. Glyn

22/12/2007, 11h36	#6
J.O. Aho Messages: n/a Hébergeur:	Re: 2nd Newbie question - results page wont load with order by inquery 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

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email