For this query, is necessary to use mysql_real_escape_string?
Any improvement or the query is fine ?
$consulta = $_REQUEST["term"]."%";
($sql = $db->prepare('select location from location_job where location like ?'));
$sql->bind_param('s', $consulta);
$sql->execute();
$sql->bind_result($location);
$data = array();
while ($sql->fetch()) {
$data[] = array('label' => $location);
}
The query speed is important in this case.
Managed to figure it out, i will detail my answer below for anyone whos interested in future.
Original Code
$query="select SQL_CALC_FOUND_ROWS id,title,location,salary,employer from jobs where region=38 limit 0,3";
if($stmt = $connection->prepare($query)) {
$stmt->execute()or die($connection->error); //execute query
$stmt->bind_result($id,$title,$location,$salary,$employer,$image);
while($stmt->fetch()){
$jobs[$x]['id']=$id;
$jobs[$x]['title']=$title;
$jobs[$x]['location']=$location;
$jobs[$x]['salary']=$salary;
$jobs[$x]['employer']=$employer;
$jobs[$x]['image']=$image;
$x++;
}
$stmt->close();//close statement
}
Updated Code
$query="select SQL_CALC_FOUND_ROWS id,title,location,salary,employer from jobs where region=38 limit 0,3";
if($stmt = $connection->prepare($query)) {
$stmt->execute()or die($connection->error); //execute query
$stmt->bind_result($id,$title,$location,$salary,$employer,$image);
while($stmt->fetch()){
$jobs[$x]['id']=$id;
$jobs[$x]['title']=$title;
$jobs[$x]['location']=$location;
$jobs[$x]['salary']=$salary;
$jobs[$x]['employer']=$employer;
$jobs[$x]['image']=$image;
$x++;
}
//get total number of rows.
$query="SELECT FOUND_ROWS()";
$stmt = $connection->prepare($query);
$stmt->execute();
$stmt->bind_result($num);
while($stmt->fetch()){
$count=$num;
}
$stmt->close();//close statement
}
Probably could do it better another way but couldn't seem to find any good examples anywhere online and this works!
The trick is to construct an array that contains the parameters that you want to bind, then with the help of call_user_func_array, you can pass this array to bind_param.
See http://www.php.net/manual/en/function.call-user-func-array.php for details on call_user_func_array.
Your code can be something like:
$para_type="";
/* $para is the array that later passed into bind_param */
$para=array($para_type);
$query = 'UPDATE tickets SET ';
IF(count($data) != 0) {
/* Looping all values */
foreach($data as $k=>$d) {
$query .= '`'.$d['field'].'` = ? ,';
$para_type .=$d['type'];
$para[] = &$data[$k]['value'];
}
/* removing last comma */
$query[(strlen($query)-2)] = '';
/* adding where */
$query .= ' WHERE `ticket_id` = ?';
$para_type .= 'i';
$para[]=&$ticket_id;
call_user_func_array(array($stmt, 'bind_param'), $para);
return true;
}
Notice the & in front of all parameters, it is required by bind_param.
Another way which I think is better is to use PDO. It takes named parameter and can do incremental bind.
You could change your query as follows:
UPDATE members SET
username = IFNULL(?, username),
email = IFNULL(?, email) -- and so on for all fields
WHERE...
It could also be more efficient to check the value of your parameters first, and build the query dynamically, including only fields for which you have a non-null value to update with.
You already have the code in
if(mysql_stmt_execute(stmt) != 0) {
printf("Unable to create new session: Could not execute statementn");
return NULL;
}
If that fails, you didn't insert any rows. The docs contain a full example
You can also use mysql_stmt_affected_rows() after a successful mysql_stmt_execute() to find out how many rows were insterted/updated/deleted.
No, prepared queries (when used properly) will ensure data is properly escaped for safe querying. You are kind of using them properly, just need change one little thing. Because you are using the '?' placeholder, it is better to pass params through the execute method.
$sql->execute(array($consulta));Just be careful if you're outputting that to your page, database sanitization does not mean it will be safe for display within HTML, so run htmlspecialchars() on it as well.