Order by exists

[KingOfHeart]

http://openzelda.thegaminguniverse.com/file.php?name=&type=1&order=7&dir=0&page=4

 

I have a field named "screen". I would like to use order by to see if this field exists or not. It currently works alright by organizing the screen shot link's alphabetically. I'd rather have all screenshots just return a 1 and use a second orderby to list it by files that have been updated recently.

So is this possible?

[Adam]

You're not explaining your problem clearly. "ORDER BY" simply orders the results of a query by a particular column(s), it cannot be used to determine if a column 'exists' or not; even though you should know if a column doesn't exist.

[KingOfHeart]

I mean if the column is left blank. If so return a 0, if not return a 1.

 

(1)Last Update: January 20th, 2009  Screenshot: http://openzelda.thegaminguniverse.com/wiki/images/f/f4/Dark_side_triforce.PNG

(2)Last Update: January 21st, 2009  Screenshot: http://openzelda.thegaminguniverse.com/wiki/images/f/f1/Antlion_battle.PNG

(3)LastUpdate: January 16th, 2010  Screenshot:http://openzelda.thegaminguniverse.com/wiki/images/e/e9/AIScreen2.PNG

 

 

Now alphabetically this is how my screenshots are listed. I'd rather have all three just return a value of 1, then order it by Last Update. So I need it to order it like 2,1,3 instead.

 

[Adam]

How is your table structured? I don't see why you aren't just ordering it by the 'last update' column? May also help if you post your query..

[KingOfHeart]

http://openzelda.thegaminguniverse.com/file.php?name=&type=1&order=7&dir=0&page=1

 

One of the options they can choose is to order it by screenshots. So I want all screenshots to return a value of 1 so I can order all those files by last updated.

 

function SearchFiles()
{
  global $filename,$fileid,$filetype,$filedir,$fileby,$official;
  $type = ($filetype != '10' ? $filetype : 0);	
  $order = array("lastupdate","id","name","username","downloads","rating","points","screen","Rand()");
  $direction = array("Desc","ASC");	
  $official_ids = array(1,89,115,6,90,131,132);

$search = "SELECT * FROM Downloads ";
  
if($fileid > 0)
	$search .= "Where id = '$fileid'";
else
  {	
     if(strlen($filename) > 0)
	 {			 
			$search .= "Where name LIKE '%$filename%' ";
        if($type > 0)
           $search .= "AND type = '$type' ";
        else	
           $search .= "AND type > '0' ";				
     }
     else if($type != 0)	
  			$search .= "Where type = '$type' ";
	 else	
        $search .= "Where type > '0' ";	
  }				

$search .= "Order By $order[$fileby] ";
  if($fileby != 6)		
	$search .= $direction[$filedir];
  if($fileby != 5 & $fileby != 6)	
	$search	.= ",points DESC";	
  if($fileby == 
  $search .= " Limit 5";
  		
$sql = mysql_query($search);

  $n = 0;
while($rows = mysql_fetch_array($sql))
{
    if($official == true && !in_array($rows['id'],$official_ids))
			continue;				
	$out['name'][] = ($rows['name'] == null ? "No Name" : RemoveHTML($rows['name'],true));
	$out['id'][] = $rows['id'];
	$out['rating'][] = $rows['rating'];
	$sqldate = "select date_format(lastupdate,'%m-%d-%y') FROM Downloads Where id = '{$rows['id']}'";
	$dresult= mysql_query($sqldate);
	$drows = mysql_fetch_array($dresult);
	$out['date'][] = formatdate($drows[0]);
	$data_desc = RemoveHTML($rows['description'],true);
	$data_desc = bbcode2html($data_desc);
	$out['description'][] = $data_desc;

	$out['downloads'][] = $rows['downloads'];
    $out['points'][] = $rows['points'];		
	$out['username'][] = RemoveHTML($rows['username']);
	$out['userid'][] = GetGUID(RemoveHTML($rows['username']));
	$out['screen'][] = RemoveHTML($rows['screen']);
	$out['type'][] = $rows['type'];
    $n++;		
}	
return $out;
}

 

The $order field are the fields in the mysql table except for Rand().

$official_ids are the some official files which can be any type.

http://openzelda.thegaminguniverse.com/file.php?type=10 < Just go here to see what I mean.

 

[KingOfHeart]

Well just found out the null function won't work because even a blank screen seems to return some type of value.

$search = "SELECT * FROM Downloads Where screen IS NOT NULL"; < listed every file.

 

So I need any field screen that has any data in it to all return the same value.

[Adam]

How is your table structured?

[KingOfHeart]

Why do I need to post that?

Can't just give examples of a strlen if statement instead?

[Adam]

Why do I need to post that?

I was asking for the structure as it makes things quicker / easier. Tend to end up going round in circles asking questions trying to understand how the data is structured.

 

As you've tried Where screen IS NOT NULL, there must be a value within the screen column; NULL does not equal an empty string.  I'm guessing there could be a default value assigned to the filed, or during your 'insert' process you're passing an empty string.

 

 

Can't just give examples of a strlen if statement instead?

You can't use char_length() within the 'where' condition of your statement.

 

[fenway]

You can't use char_length() within the 'where' condition of your statement.

That's not correct...

[KingOfHeart]

I still want it to list the files that have no screen. I just want to order it properly.

 

Is there any way to use some type of if statement in the search. I've searched around and saw something about "As" or something.

As far as I can tell, I update the screen field even if it's blank. So that's probably why it's not Null.

 

[attachment deleted by admin]

[fenway]

That's blank, not null.

[KingOfHeart]

ok, I understand that part now. Now can you help me with my strlen problem?

[KingOfHeart]

NVM, I think I got it.

 

SELECT * FROM Downloads Where type > '0' Order By if(LENGTH(screen) > 0,1,0) Desc,lastupdate DESC,points DESC

 

http://openzelda.thegaminguniverse.com/file.php?name=&type=0&order=7&dir=0&page=1

 

It looks like it first checks to see if it has a screenshot, and then it orders it by the lastest files.