StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POZend_Db_Select order by random, compatible in mssql / mysql
text
Body
copied!<p>Alright here's the situation, I have an application written in the Zend_Framework, that is compatible with both MySQL and MSSQL as the backend. Now, ZF is pretty good at solving a lot of the SQL discrepancies/differences between the two languages, but I still have yet to figure this one out. </p> <p>The objective is to select 1 random record from the table, which is an extremely simple statement. </p> <p>Here's a select statement for example:</p> <pre><code>$sql = $db->select() ->from("table") ->order("rand()") ->limit(1); </code></pre> <p>This works perfectly for the MySQL database tables, because the sql for MySQL is as follows:</p> <pre><code>SELECT `table`.* FROM `table` ORDER BY rand() ASC </code></pre> <p>Now MSSQL on the other hand, uses the <strong>newid()</strong> function to do randomizing. </p> <p>Is there some sort of helper I can pass into the <strong>order()</strong> function in order to make it realize that it has to use the proper ordering? I searched the documentation and on the zfforums, found a few tips, but nothing solid. </p> <p>One of the things I did find was:</p> <p><a href="http://www.zfforums.com/zend-framework-components-13/databases-20/order-random-not-working-566.html" rel="nofollow noreferrer">ORDER BY RANDOM() not working</a> - ZFForums.com</p> <p>They are using the following:</p> <pre><code>$res = $db->fetchAll( 'SELECT * FROM table ORDER BY :random', array('random' => new Zend_Db_Expr('RANDOM()') ); </code></pre> <p>It works... but I am not looking to build my select statement by typing it out and doing a replace on the string, I am trying to keep it in the same Zend_Db_Select object. I also have tried passing in the <code>Zend_Db_Expr('RANDOM()')</code> into the <code>->order()</code> on the statement, and it fails. He also posts a theoretical solution to finding the answer, but I am not looking to rewrite the function this is within, modifying the $db->fetch() call. </p> <p>Any ideas?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload