[SOLVED] Wierd Error when using ORDER BY

[i-fm]

I'm using the following bit of code

 

SELECT articles.*, partners.image FROM articles LEFT JOIN partners ON articles.source = partners.name WHERE type = 'Feature' AND pdate < CURDATE() ORDER BY pdate DESC LIMIT $offset, $entries_per_page

 

And i get this error, which i have never seen before

 

Can't create/write to file '/var/tmp/#sql_16abc_0.MYI' (Errcode: 13)

SELECT articles.*, partners.image FROM articles LEFT JOIN partners ON articles.source = partners.name WHERE type = 'Feature' AND pdate < CURDATE() ORDER BY pdate DESC LIMIT 0, 10

 

But if i remove

 

ORDER BY pdate

 

It Works,  but i don't want to remove because i need it.

 

Can anyone help?

 

cheers

Dave

[johng]

Is pdate in the "articles" table?

[i-fm]

yes pdate is in articles...

 

ORDER BY did work before i added

 

articles.*, partners.image FROM articles LEFT JOIN partners ON articles.source = partners.name

 

When it used to be just

SELECT * FROM articles WHERE type = 'Feature' AND pdate < CURDATE() ORDER BY pdate DESC LIMIT $offset, $entries_per_page

[i-fm]

This is the entire page code if it helps.

 

----

 

<table width="100%" border="0" cellspacing="0" cellpadding="0">

<tr>

<td class="title"><?php

//display ttitle

print(stripslashes($pagedata['name']));

 

?></td>

</tr>

</table><br><?php

 

$result = mysql_query("SELECT COUNT(*) AS total_entries FROM articles WHERE type = 'Feature'") or die(mysql_error()); $row = mysql_fetch_row($result); $total_entries = $row[0];

 

$entries_per_page = 10;

 

if(isset($_GET['page_number'])) { $page_number = $_GET['page_number']; } else { $page_number = 1; }

 

$total_pages = ceil($total_entries / $entries_per_page);

 

$offset = ($page_number - 1) * $entries_per_page;

 

 

 

//create table query object

 

$data = $db->select_list_b("SELECT articles.*, partners.image FROM articles LEFT JOIN partners ON articles.source = partners.name WHERE type = 'Feature' AND pdate < CURDATE() LIMIT $offset, $entries_per_page");

 

while ($data = $db->fetch_array()){ 

 

?><table width="500" border="0" cellspacing="2" cellpadding="2">

<tr>

<td bgcolor="#eeeedd" width="100"><img src="images/partners/<?php print($data['image']); ?>"></td>

<td valign="top" bgcolor="#eeeedd"><span class="para"><a href="index.php?p=articlef&story=<?php print($data['art_id']); ?>"><?php print($data['headline']); ?></a></span><br>

<span class="parasmall"><?php print($data['synopsis']); ?></span><br>

<i><span class="parasmall">Published - <?php print date("D jS F Y",strtotime($data['pdate'])); ?></span><br>

</i><br>

</td>

</tr>

</table><span class="para"><a href="index.php?p=partners#<?php print($data['name']); ?>"><?php print($data['name']); ?></a><?php } ?><br>

<br>Page

<?php

for($i = 1; $i <= $total_pages; $i++) {

if($i == $page_number) { print "$i "; }

else { 

print " <a href='index.php?p=features&page_number=$i'>$i</a> "; } }

?></span>

[i-fm]

this seems to of stumped a few people, myself included.

[Vikas Jayna]

Well, I'm not stumped! Here's why:-

 

Mysql makes use of temporary files while doing a sort or a group by operation and these temporary files are created at a location specified by the tmpdir variable in mysql. Check the output for the "show variables" query and u should get "/var/tmp" in this variable. Now why mysql is unable to create the temporary table will have to be explored. There could be many reasons:-

 

1) mysql may not have write permissions on this directory.

2) probably this directory does not exist.

3) problem of disk space?

 

Go through the following link to know more: http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html

[i-fm]

Vikas

 

Thank you so much.... 

 

that was simple in the end...  just went into the server and changed the permissions and all working.