• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need help related to my codeigniter wallpaper script

#11
(01-12-2018, 05:02 AM)InsiteFX Wrote: GROUP BY should be the last statement in database queries.
It's the last statement. Take note of the brackets.
Reply

#12
(01-12-2018, 06:02 AM)jreklund Wrote:
(01-12-2018, 05:02 AM)InsiteFX Wrote: GROUP BY should be the last statement in database queries.
It's the last statement. Take note of the brackets.

Thanks guys for your support, but didn't work.

I added

PHP Code:
public function color_sql($filter)
{
 
  $this->db->select('*, sum(count) AS color_sum');
 
  $this->db->from('wallpapers_colors');
 
  $this->db->where('saturation BETWEEN ' . (($filter['saturation'][0]) / 100) . ' AND ' . (($filter['saturation'][1]) / 100));
 
  $this->db->where('lightness BETWEEN ' . (($filter['lightness'][0]) / 100) . ' AND ' . (($filter['lightness'][1]) / 100));
 
  $this->db->where('hue BETWEEN ' . ($filter['hue'][0]) . ' AND ' . ($filter['hue'][1]));
 
  $this->db->group_by('wallpaper_seq_id');
 
  return $this->sql();
var_dump($filter);
die();



and returns I guess the same error:

Code:
A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BETWEEN0 AND 0.4 AND `lightness` BETWEEN 0.55 AND 0.95 AND `hue` BETWEEN0 AND 20' at line 4

SELECT COUNT(*) AS `numrows` FROM ((SELECT *, sum(count) AS color_sum FROM `WS_wallpapers_colors` WHERE `saturation` BETWEEN0 AND 0.4 AND `lightness` BETWEEN 0.55 AND 0.95 AND `hue` BETWEEN0 AND 20 GROUP BY `wallpaper_seq_id`) wc) JOIN `WS_wallpapers` `w` ON `w`.`wallpaper_seq_id` = `wc`.`wallpaper_seq_id` LEFT JOIN (SELECT `t2`.*, GROUP_CONCAT(c.category_slug order by c1.lvl desc SEPARATOR '/') AS cFullSlug, GROUP_CONCAT(c.category_name order by c1.lvl desc SEPARATOR '|') AS cFullName FROM `WS_closures` `c1` LEFT JOIN `WS_category` `c` ON `c`.`category_seq_id` = `c1`.`ancestor` LEFT JOIN `WS_category` `t2` ON `t2`.`category_seq_id` = `c1`.`descendant` GROUP BY `c1`.`descendant`) as c ON `c`.`category_seq_id` = `w`.`category_seq_id` WHERE `w`.`wallpaper_status` = 1 AND `w`.`wallpaper_approved` = 1

Filename: core/WS_Model.php

Line Number: 76


But, also wonder why the 1st color that not returns the error, why not return the wallpaper? (Why is coding so hard? Kidding! Smile )
Reply

#13
It's suppose to be before everything else, so that you dump what $filter holds. Now it's already returning the sql command.
PHP Code:
public function color_sql($filter)
{
   
var_dump($filter);
   die();
   
$this->db->select('*, sum(count) AS color_sum');
   
$this->db->from('wallpapers_colors');
   
$this->db->where('saturation BETWEEN ' . (($filter['saturation'][0]) / 100) . ' AND ' . (($filter['saturation'][1]) / 100));
   
$this->db->where('lightness BETWEEN ' . (($filter['lightness'][0]) / 100) . ' AND ' . (($filter['lightness'][1]) / 100));
   
$this->db->where('hue BETWEEN ' . ($filter['hue'][0]) . ' AND ' . ($filter['hue'][1]));
   
$this->db->group_by('wallpaper_seq_id');
   return 
$this->sql();

Reply

#14
Yes, now returns this:

array(4) { ["saturation"]=> array(2) { [0]=> int(0) [1]=> int(40) } ["lightness"]=> array(2) { [0]=> int(0) [1]=> int(40) } ["hue"]=> array(2) { [0]=> int(0) [1]=> int(20) } ["focus"]=> array(3) { ["H"]=> int(0) ["S"]=> int(0) ["L"]=> int(0) } }

You can test it, because I didn't remove it yet.
Reply

#15
Try this one

PHP Code:
public function color_sql($filter)
{
   
$this->db->select('*, sum(count) AS color_sum');
   
$this->db->from('wallpapers_colors');
   
$this->db->where('saturation BETWEEN ' . (float)(($filter['saturation'][0]) / 100) . ' AND ' . (float)(($filter['saturation'][1]) / 100), NULLFALSE);
   
$this->db->where('lightness BETWEEN ' . (float)(($filter['lightness'][0]) / 100) . ' AND ' . (float)(($filter['lightness'][1]) / 100), NULLFALSE);
   
$this->db->where('hue BETWEEN ' .(float)($filter['hue'][0]) . ' AND ' . (float)($filter['hue'][1]), NULLFALSE);
   
$this->db->group_by('wallpaper_seq_id');
   return 
$this->sql();

Reply

#16
@jreklund

Thank you!!!!

It works now.

But, can you explain me a little detailed how it works exactly, because I don't really understand how is thought.

Some colors from spectrum don't return any wallpaper. And some return only one (depends on how much it is in) which is correct and some returns both which is also correct.



But why some colors that are listed don't return any wallpaper (why to be there if don't match any wallpaper)?
Reply

#17
Everything looks good and well from these CI functions:
where -> _wh('qb_where')

But when you are compiling it to pure SQL and validating against SQL injections:
get_compiled_select (same as your sql I guess) -> _compile_select -> _compile_wh('qb_where')

It separate everything and later on glues everything together. And in the process trims white space due to the fact 0 = empty.

system\database\DB_query_builder.php
LINE 2433: $conditions[$ci] = $matches[1].$this->protect_identifiers(trim($matches[2]))
LINE 2434: .' '.trim($matches[3]).$matches[4].$matches[5];
LINE 2437: $this->{$qb_key}[$i] = implode('', $conditions);

So I disabled the sql injection protection and forced all $filter values to be float numbers.
___________________________________

I can't answer why some colors works but some dosen't.
- You need to look on how these colors get generated.
- How they are stored in the database.
- How the select query works.
Reply

#18
OK, thank you for your time!

Regards,
Stan
Reply

#19
Hello again!

I need your help again.

1. First code: (this one is loaded in html too, of course without the php parts)


PHP Code:
            <div class="col-sm-6">
                <
div class="form-group">
                    <
label for="license">License:*</label>
 
                   <?php echo form_dropdown('license_type', array(
                        
'1' => 'Only as Personal Wallpaper',
                        
'2' => 'Public Domain CC0',
                        
'3' => 'Creative Commons BY 4.0',
                        
'4' => 'Creative Commons BY-SA 4.0',
                        
'5' => 'Creative Commons BY-NC-SA 4.0',
                        
'6' => 'Creative Commons BY-NC 4.0'
                    
), ($wallpaper ? array($wallpaper->license) : array('2')), 'class="form-control input-sm"'); ?>
                </div> 


2. Second code:

PHP Code:
       if ($this->form_validation->run() && empty($data['error']) && $data['image']) {

 
           if (empty($data['error'])) {
 
                    // here are few lines of code related to path where the image will be saved and date...
            
$insert_data = array(
 
                   'wallpaper_name' => $clean_name,
         
   'license' => in_array($this->input->post('license_type')) ? $this->input->post('license') : '2',
 
               );
                
        }
        } 

Here in the 2nd code, at the "license" line I think it is the part that it writes in database the number that corresponds to selected license. But, it doesn't matter what license I choose, because always choose the one from the number 2. (i seen that '2' from the end, and i remove it that '2', the ':' ... all kind of trials... but when i remove it writes in database 0 or null. Is )



3. Third code:

PHP Code:
       if ($this->form_validation->run() && empty($data['error']) && $data['image']) {
 
          // no code because the image is already here, as it writes below $update_data array
 
           if (empty($data['error'])) {
 
               $update_data = array(
 
                   'wallpaper_name' => $this->input->post('image_name'),
         
   'license' => in_array($this->input->post('license_type')) ? $this->input->post('license') : '2',
 
               );
            
        }
    } 

And in the 3rd code, if I update the wallpaper and choose another license don't change it.

Can someone tell me what is wrong? (I think it is related to that "2" from the end, but I don't know how to tell it to write in database the new number that corresponds for the license set in the form_dropdown, because i choose another, in the form looks ok, but when i refresh the page or check the database is the same number set at first upload)

Thank you!

PS: Or maybe a simple way to write in database the number that I choose and which corresponds to the correct license.
Reply

#20
You are opening a dropdown named 'license_type':
PHP Code:
form_dropdown('license_type',array()); 

And later on you are trying to fetch a 'license' that's supposed to be 'license_type'. Also you need a haystack in your in_array.
in_array validates your post (license_type) and try to match it against a haystack. haystack = all possible options.
http://php.net/manual/en/function.in-array.php
PHP Code:
'license' => in_array($this->input->post('license_type')) ? $this->input->post('license') : '2'

PHP Code:
$haystack = (1,2,3,4,5,6); // Don't hardcode this if you have an array already
'license' => in_array($this->input->post('license_type'),$haystack) ? $this->input->post('license_type') : '2'
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2018 MyBB Group.