set sql variable before main query

set sql variable before main query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: set sql variable before main query (/showthread.php?tid=72766)

set sql variable before main query - ash-f - 02-08-2019

Is it possible to set sql variable before other queries?(not $this->db->query())

I'd like to do something like this.

PHP Code:
$this->db->query("SET @min_id := (SELECT min_id FROM shop_parameter)");
$this->db->select('item_id');
$this->db->from('products');
$this->db->where('(item_id > @min_id',NULL,FALSE);
$this->db->where(array('item_stat >'=>10));
$query=$this->db->get();
foreach($query->result() as $r){
   $products[$r->item_id]=$r; }
} 

RE: set sql variable before main query - php_rocs - 02-08-2019

@ash-f,

Yes, it is possible to set sql variable before other queries. The catch is...not the way you have it in your example. There are a couple of ways that you could do it but you would have to use $this->db->query(). With one of those ways, you could actually do it in one query but again it is using the $this->db-query();

RE: set sql variable before main query - InsiteFX - 02-09-2019

PHP Code:
$this->db->where('(item_id > @min_id',NULL,FALSE); 

Are you missing a parentheses / bracket here?

RE: set sql variable before main query - ash-f - 02-12-2019

(02-09-2019, 05:16 AM)InsiteFX Wrote:
PHP Code:
$this->db->where('(item_id > @min_id',NULL,FALSE);

Are you missing a parentheses / bracket here?

That is a typo created when I copy&pasted from the original code.
In the original code, I used (item_id > @min_id AND item_id < @max_id)

RE: set sql variable before main query - ash-f - 02-12-2019

(02-08-2019, 05:46 PM)php_rocs Wrote: @ash-f,

Yes, it is possible to set sql variable before other queries. The catch is...not the way you have it in your example. There are a couple of ways that you could do it but you would have to use $this->db->query(). With one of those ways, you could actually do it in one query but again it is using the $this->db-query();

Thanks.
I saw something similar and they are using transaction with multiple $this->db-query().
So is that the standard way?

RE: set sql variable before main query - ash-f - 02-12-2019

Ok, I've used two successive $this->db->query()

Code:
$randomitem=array();

$valquery = "SELECT @rid := FLOOR(1+RAND()*item_id) FROM products ORDER BY item_id DESC LIMIT 1";

$this->db->query($valquery);

$mainquery  = "SELECT item_id FROM products WHERE item_id < @rid AND item_stat > 10 ORDER BY item_id DESC LIMIT 2";

$query = $this->db->query($mainquery);

foreach($query->result() as $r){ $randomitem[]=$r->item_id; }

RE: set sql variable before main query - php_rocs - 02-13-2019

@ash-f,

Documentation for future reference (of query results)... https://codeigniter.com/user_guide/database/results.html#generating-query-results

RE: set sql variable before main query - ash-f - 02-14-2019

(02-13-2019, 09:27 PM)php_rocs Wrote: @ash-f,

Documentation for future reference (of query results)... https://codeigniter.com/user_guide/database/results.html#generating-query-results

Thank you