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

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

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

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

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