I have an array of integers, how do I use each one in a mysql query (in php)?
As with nearly all "How do I do SQL from within PHP" questions - You really should use prepared statements. It's not that hard:
$ids = array(2, 4, 6, 8);
// prepare an SQL statement with a single parameter placeholder
$sql = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id = ?";
$stmt = $mysqli->prepare($sql);
// bind a different value to the placeholder with each execution
for ($i = 0; $i < count($ids); $i++)
{
$stmt->bind_param("i", $ids[$i]);
$stmt->execute();
echo "Updated record ID: $id\n";
}
// done
$stmt->close();
Alternatively, you can do it like this:
$ids = array(2, 4, 6, 8);
// prepare an SQL statement with multiple parameter placeholders
$params = implode(",", array_fill(0, count($ids), "?"));
$sql = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id IN ($params)";
$stmt = $mysqli->prepare($sql);
// dynamic call of mysqli_stmt::bind_param hard-coded eqivalent
$types = str_repeat("i", count($ids)); // "iiii"
$args = array_merge(array($types), $ids); // ["iiii", 2, 4, 6, 8]
call_user_func_array(array($stmt, 'bind_param'), ref($args)); // $stmt->bind_param("iiii", 2, 4, 6, 8)
// execute the query for all input values in one step
$stmt->execute();
// done
$stmt->close();
echo "Updated record IDs: " . implode("," $ids) ."\n";
// ----------------------------------------------------------------------------------
// helper function to turn an array of values into an array of value references
// necessary because mysqli_stmt::bind_param needs value refereces for no good reason
function ref($arr) {
$refs = array();
foreach ($arr as $key => $val) $refs[$key] = &$arr[$key];
return $refs;
}
Add more parameter placeholders for other fields as you need them.
Which one to pick?
The first variant works with a variable number of records iteratively, hitting the database multiple times. This is most useful for UPDATE and INSERT operations.
The second variant works with a variable number of records too, but it hits the database only once. This is much more efficient than the iterative approach, obviously you can only do the same thing to all affected records. This is most useful for SELECT and DELETE operations, or when you want to UPDATE multiple records with the same data.
Why prepared statements?
- Prepared statements are a lot safer because they make SQL injection attacks impossible. This is the primary reason to use prepared statements, even if it is more work to write them. A sensible habit to get into is: Always use prepared statements, even if you think it's "not really necessary." Neglect will come and bite you (or your customers).
- Re-using the same prepared statement multiple times with different parameter values is more efficient than sending multiple full SQL strings to the database, because the database only needs to compile the statement once and can re-use it as well.
- Only parameter values are sent to the database on
execute(), so less data needs to go over the wire when used repeatedly.
In longer loops the execution time difference between using a prepared statement and sending plain SQL will become noticeable.
MySQL query in PHP with an array in the condition
You cannot pass the array to the query. You can use IN to pass different, comma separated values to the query.
So first you have to take each array entry and concatenate it with commas:
//initialize the list
$countries = "";
foreach($country as $a){
//add each country wrapping it in single quotes
$countries .= "'".$a."',";
}
//remove the last comma that is not necessary
rtrim($countries,",");
//build the query
$sql = "SELECT * FROM table1 WHERE table1.country IN ($countries)";
//run the query
$result = mysqli_query($con,$sql);
Note that building $countries I have put single quotes around each element. The reason is that I am passing strings to the database. This would not be necessary in case of integers
Insert array of numbers into a single column , MySQL, PHP
Your issue is that the output of json_encode is something like "[5, 8, 1 ... , 2]" which is not a valid integer, so you get a value of 0 inserted. What you need to do is generate 500 inserts, which you can do using implode to generate the separate values:
$query = "INSERT INTO points(value) VALUES (" . implode('),(', $data) . ")";
Get results from mysql query into an integer array?
You run through the result and build the array.
If you really need the result to be integers, just pass it through intval. A quick snippet follows.
$query_sample = mysql_query("
SELECT left FROM sampleTable WHERE right = 1
UNION
SELECT right FROM sampleTable WHERE left = 1
");
$result = array();
while($r = mysql_fetch_array($query_sample, MYSQL_NUM)){
$result[] = intval($r[0]);
}
var_dump($result);
Edit: Sorry. I messed up with things. Just corrected it.
I basically want to be able to ask the
question: "Is $x in $results_array"
Do it using MySQL! It is optimized for doing such things fast. Especially if you have set the indexes right.
How to use php array with sql IN operator?
Since you have plain integers, you can simply join them with commas:
$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(',', $arr) . ")";
If working with with strings, particularly untrusted input:
$sql = "SELECT * FROM table WHERE comp_id IN ('"
. implode("','", array_map('mysql_real_escape_string', $arr))
. "')";
Note this does not cope with values such as NULL (will be saved as empty string), and will add quotes blindly around numeric values, which does not work if using strict mysql mode.
mysql_real_escape_string is the function from the original mysql driver extension, if using a more recent driver like mysqli, use mysqli_real_escape_string instead.
However, if you just want to work with untrusted numbers, you can use intval or floatval to sanitise the input:
$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(",", array_map('intval', $arr)) . ")";
Mysql where id is in array
$string="1,2,3,4,5";
$array=array_map('intval', explode(',', $string));
$array = implode("','",$array);
$query=mysqli_query($conn, "SELECT name FROM users WHERE id IN ('".$array."')");
NB: the syntax is:
SELECT * FROM table WHERE column IN('value1','value2','value3')
MySQL PHP - SELECT WHERE id = array()?
Use IN.
$sql = 'SELECT *
FROM `table`
WHERE `id` IN (' . implode(',', array_map('intval', $array)) . ')';