Jump to content


codebyren

Member Since 12 Oct 2007
Offline Last Active Private

Topics I've Started

Ordering and grouping with sticky entries at the bottom

14 May 2012 - 09:51 PM

Hi.

My MySQL server version is 5.5.8.  I've got two tables something like these simplified ones:

authors:


















author_id name
1 Joe Bloggs
2 Mary Bloggs
3 John Smith


books:
(in this example, a book will only ever have 1 author):



















































book_id author_id title price discontinued
1 3 Some Book Title 10.00 0
2 1 Another Great Book 15.00 0
3 1 Not Such A Great Book 15.00 1
4 1 Average Book 8.00 0
5 2 The Penultimate Book 15.00 1
6 3 The Last Book 27.00 0


My goal is to join these tables in a query that will:

1.) return a single row per author and
2.) the row must contain the author's cheapest book that is not discontinued and
3.) the rows must be sorted by price/author BUT
4.) if an author's ONLY book is discontinued, this row must appear last in the result set.

The catch for me is that when I include a GROUP BY `author_id` clause, any author that has ANY discontinued book whatsoever is getting pushed to the bottom of the result set.  If they have any non-discontinued books, I need the cheapest one to be included instead (and sorted appropriately).

So far I've tried simple variations of:

SELECT *, MIN(price) AS min_price FROM authors JOIN books ON authors.author_id = books.author_id GROUP BY author_id ORDER BY discontinued ASC, min_price ASC

but as I say, the Joe Bloggs author with both the cheapest book and a separate, discontinued book would be listed last (undesirably) according to my query like the above.

I suspect it has to do with how GROUP BY determines which row to include but I've spent way too much time looking into this already and would appreciate some help.

I'd be happy to generate some dummy data if anyone wants to have a play around but I get the feeling I'm just missing something simple here.

Thanks.

Site critique

02 June 2010 - 05:32 AM

Hi guys,

I'm busy finishing up (hopefully) my one-page freelancer website: http://www.codebyren.com

I'm primarily a developer (as opposed to designer) so unfortunately my previous work is generally under the hood somewhere.  I do plan to overhaul the personal sites I have showcased once I finish up some development on them though.

So yeah, it's simple but I'd appreciate any feedback anyway.

Cheers.

Displaying code in HTML

26 February 2010 - 03:58 AM

Hi,

I am trying to print out html code so that a user can just copy and paste it from my site without having to dig into the page source.  I know this can be done with htmlentities but I am having a problem preserving any indentation etc. to keep it neat.  What do developers usually use for this?  For example:

<tr>
	<!-- See how this is indented... -->
	<td>data</td>
	<td>more data</td> 
</tr>

I believe HTML Tidy will do this but the server in questions doesn't have it available so hoping there are alternative options that are also less of an overkill.

If it makes any difference, I will probably be generating the HTML with output buffering or pulling the source of a remote file using get_file_contents.

Thanks for any help!

mod_rewrite

24 December 2009 - 09:29 PM

Hi everyone,

I am trying to rewrite a url like:

https://mysite.com/p...erid=myUsername

to:

https://mysite.com/i...erid:myUsername

The ?result=encryptedStringHere&userid=myUsername in the orignal URL is appended by a payment gateway I am using to process payments so is really outside of my control.  Other than this, the URLs for my site work fine using my current .htaccess setup:

<IfModule mod_rewrite.c>
RewriteEngine On

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d

# RewriteRule for hits from Payment Gateway
# the commented out rule below doesn't work... need help
# RewriteRule ^/payments/update/([0-9]+)/?result=(.*)&userid=(.*)$ index.php?url=/payments/update/$1/result:$2/userid:$3 [PT,L]

# I'm assuming if the URL doesn't match rule above (when uncommented), this next one will be used:
RewriteRule ^(.*)$ index.php?url=$1 [PT,L]

</IfModule>

The above basically involves Anything in the URL after the domain being appended as parameters to index.php, so
http://www.site.com/controller/action
is rewritten to
http://www.site.com/...ntroller/action

This seems to break with the parameters appended by the payment gateway though (due to the additional "?" and "&" I guess).

Would appreciate any help or suggestions from someone with more regex or mod_rewrite experience...

Thanks.


[SOLVED] Problems with returning true or false in eval()'d code

30 October 2009 - 07:40 AM

Hi guys,

Would appreciate some help with a problem when running eval() on a function that should return true or false. 

From php.net:

eval() returns NULL unless return is called in the evaluated code, in which case the value passed to return is returned.


Not sure if I'm missing something here but the following code illustrates that the function returns true/false as expected when run directly. However, the same function run using eval() never returns true:

<?php

// Function to test what eval() returns...
function test() 
{
		
	$test = rand(1, 3);
	return ($test === 1) ? TRUE : FALSE;

}

$eval = "test();";
$test = eval($eval); # expect this should set $test to TRUE for 1 in 3 attempts

echo ($test) ? 'eval returned true' : 'eval returned FALSE or NULL <br />';

// Now retest the function by calling it directly, not through eval...
$test2 = test();
echo ($test2) ? 'test returned TRUE' : 'test returned FALSE or NULL <br />'; # this behaves as expected...

?>

Hopefully it's something ridiculously obvious to someone out there...

Cheers for any feedback.