CODEWITHSUNDEEP
phpmysqlvariablessyntax
Dylan Buth
Dylan Buth

Reputation: 1666

Can't get spaces in my SQL statement

I have two variables in my WHERE statement. I cant seem to separate them with a space so i end up getting a syntax error. Thanks for the help.

(I am using codeigniter)

btw i have tried setting a $space variable, and putting spaces before the and, after setting both variables, and in the sql.

ERROR

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source_adusers.ad_account="Wolfs, Marc" GROUP BY rollout_systems.eam_user LIMIT ' at line 2

SELECT *, COUNT(rollout_systems.EAM_USER) as systems FROM rollout_systems LEFT JOIN source_adusers ON rollout_systems.EAM_User = source_adusers.ad_account WHERE rollout_systems.scope_ID = 3AND source_adusers.ad_account="Wolfs, Marc" GROUP BY rollout_systems.eam_user LIMIT 0,50

Line Number: 330

PHP

 if ($this->session->userdata('scopeId') != NULL) {
        $where1 = 'WHERE rollout_systems.scope_ID = '. $this->session->userdata('scopeId') . '';
    } else {
        redirect('/headquarters/home');;
    }

    if ($search) {
        $where2 = ' AND rollout_systems.sys_name ="'.$search.'"';
    } else {
        $where2 = '';
    }
 $query = $this->db->query('SELECT * FROM rollout_systems  LEFT JOIN   source_adusers
        ON rollout_systems.eam_user = source_adusers.ad_account '. $where1 .''. $where2 .' GROUP BY rollout_systems.sys_name LIMIT '.$limit.',50');

Upvotes: 1

Views: 745

Answers (4)

user1191247
user1191247

Reputation: 12998

Just add a space between the two vars - '. $where1 .' '. $where2 .'

As pointed out by others you really should be escaping your user input using mysql_real_escape_string() or intval() if you are expecting an integer value. If you are using PDO or mysqli use prepared statements.

If $this->db is a PDO instance you could use -

$params = array();

if ($this->session->userdata('scopeId') != NULL) {
    $where = 'WHERE rollout_systems.scope_ID = ?';
    $params[] = $this->session->userdata('scopeId');
} else {
    redirect('/headquarters/home');;
}

if ($search) {
    $where .= ' AND rollout_systems.sys_name = ?';
    $params[] = $search;
}

$sql = "SELECT * FROM rollout_systems
    LEFT JOIN source_adusers ON rollout_systems.eam_user = source_adusers.ad_account
    $where
    GROUP BY rollout_systems.sys_name
    LIMIT ?, 50";
$params[] = $limit;

$query = $this->db->prepare($sql);
$query->execute($params);

Upvotes: 1

Pete
Pete

Reputation: 1299

You also have the option to use the PHP syntax 

$sql = " blah blah {$my_var} {$my_other_var} ";

Upvotes: 0

Yuriy
Yuriy

Reputation: 1984

$where = array();

if ($this->session->userdata('scopeId') != NULL) {
    // better to escape your parameter here unless you trust it totally
    // judging by its name, it's user-provided data, so I wouldn't trust it
    $where[] = 'rollout_systems.scope_ID = '. $this->session->userdata('scopeId');
} else {
    redirect('/headquarters/home');
    // you may need to exit here after redirection, depends on your implementation
}

if ($search) {
    // once again don't forget to escape $search in real application
    $where[] = "rollout_systems.sys_name = '" . $search . "'";
}

$query = $this->db->query("
    SELECT
      *
    FROM
      `rollout_systems`
      LEFT JOIN
      `source_adusers`
      ON rollout_systems.eam_user = source_adusers.ad_account
    WHERE
      " . implode (' AND ', $where) . "
    GROUP BY
      rollout_systems.sys_name
    LIMIT " . $limit /* escape it! */ . ",50"
);

Upvotes: 0

user158017
user158017

Reputation: 2991

what if you keep the spaces and the AND in the $query, instead of building them into your where variables? Then your $where 2 just needs to work without affecting the query - thus 0=0.

 if ($this->session->userdata('scopeId') != NULL) {
        $where1 = 'WHERE rollout_systems.scope_ID = '. $this->session->userdata('scopeId') . '';
    } else {
        redirect('/headquarters/home');;
    }

    if ($search) {
        $where2 = 'rollout_systems.sys_name ="'.$search.'"';
    } else {
        $where2 = '0=0';
    }
 $query = $this->db->query('SELECT * FROM rollout_systems  LEFT JOIN   source_adusers
        ON rollout_systems.eam_user = source_adusers.ad_account '. $where1 .' and '. $where2 .' GROUP BY rollout_systems.sys_name LIMIT '.$limit.',50');

Upvotes: 2

Related Questions