I have an array of ID's that correlate to a unique column in a MySQL table, is there a way to get the ID's from the array that don't appear in the database? I know I could do it by selecting the entire database and doing comparisons with PHP, but the table could get pretty big, so this doesn't seem like a very good idea to me.
Do it in two stages:
$ids = implode(',', $your_array);
SELECT id FROM thetable WHERE id IN ($ids);
$found_ids = array();
while($row = fetch($result)) {
$found_ids[] = $row['id'];
}
$missing = array_diff($your_array, $found_ids);
Basically: use your array of ids to select any matching records from the DB. Any ids in the array which don't have matching records will obviously not be returned. Take that result set, stuff it into another array. Then do a diff between the two arrays. The missing values will pop out as they'll only be in the original array, not the "found" one.
Yes you can. Define your column(ID) as UNIQUE constraint. And for checking whether it is in database or not : add ignore keyword in insert query.
I have similar question.
You can find your solution here :
Hope this might help..
You can do the following sql:
<?php
$ids = implode(',', $YOUR_ARRAY_OF_IDS);
$sql = "SELECT * FROM table_name WHERE ID NOT IN(".$ids.") ORDER by ID ASC;"
If you don't like to run sql in PHP then you just do the following:
<?php
echo $ids = implode(',', $YOUR_ARRAY_OF_IDS);
run this code and you will get the imploded string. And then you can do the SQL in MySQL or phpMyAdmin.
Hope this helps you.
You can use something like
SELECT * FROM your_table WHERE ID NOT IN( your_ids )
I have this tested with about 2K array of IDs on oracle and performance is still usable, but I must admit it is not the best solution (Be aware in oracle and other DBs, there is also a constraint to 1K items in IN() condition).
Or you can make temporary table from array of IDs and make JOIN with your table which is a little cleaner.
I know what you mean. I misread your problem too and was going to suggest something along the lines above. I think you are looking for an efficient way to identify the IDs in your array that are not in your table. There isn't really one query way of doing it. Although I don't do a lot of mysql in my PHP, my work with oracle leans towards looking at preparing a query, using binding variables and looping through your array.
Have you had a look at the prepare method in the Mysqli extension in PHP?
Preparing a query along the lines of "select count(1) from IDTABLE where ID=?" you could use code similar to the example in the PHP manual for the function/method:
http://www.php.net/manual/en/mysqli.prepare.php
It looks promising to me.
The code would be something like this:
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s
", mysqli_connect_error());
exit();
}
$ID = 0;
/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT count(*) FROM IDTABLE ID=?")) {
/* bind parameters for markers */
$stmt->bind_param("i", $ID);
$not_in=array();
$in=array();
foreach ($my_ids as $ID) {
/* execute query */
$stmt->execute();
/* bind result variables */
$stmt->bind_result($matches);
/* fetch value */
$stmt->fetch();
if ($matches == 0) {
$not_in[]=$ID;
} else {
$in[]=$ID;
}
}
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Not sure if you can move the $stmt->bid_result() up beside the bind_param - before the loop.
Any Help?
select (t.id + 1) as id from table_name t where not exists (
select * from table_name t2 where t2.id = (t.id + 1)
)
Assuming your IDs are numeric. Thank you!