How do I get a SQL script to collect all the IDs from a field and put them in an array?
3 Answers
If we assume that the ID column is just called id, then try the following code (DB connection code ommitted for clarity):
$ids_array = array();
$result = mysql_query("SELECT id FROM table_name");
while($row = mysql_fetch_array($result))
{
$ids_array[] = $row['id'];
}
If you want to sort the IDs ascending or descending, simply add ORDER BY id ASC or ORDER BY id DESC respectively to the end of the MySQL query
What the code above does is iterate through every row returned by the query. $row is an array of the current row, which just contains id, because that's all we're selecting from the database (SELECT id FROM ...). Using $ids_array[] will add a new element to the end of the array you're storing your IDs in ($ids_array, and will have the value of $row['id'] (the ID from the current row in the query) put into it.
After the while loop, use print_r($ids_array); to see what it looks like.
Comments
You have two options in PHP. The recommended one is using PDO library. You can use PDO:FETCH_COLUMN fetch mode.
// connect via PDO
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_EMULATE_PREPARES => false
]);
$stmt = $pdo->prepare('SELECT id FROM test1');
$stmt->execute();
$ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
An alternative is to use mysqli. Try to avoid mysqli if you can. It is more complicated and it is easier to make a mistake. It doesn't have the same fetch mode, so you need to iterate over the result and append a single value to an array manually.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'username', 'password', 'dbname');
$mysqli->set_charset('utf8mb4'); // always set the charset
$stmt = $mysqli->prepare('SELECT id FROM test1');
$stmt->execute();
$result = $stmt->get_result();
$ids = [];
foreach ($result as $row) {
$ids[] = $row['id'];
}
Both approaches will give you a one-dimensional array of ids.
Comments
If you want to get all of the IDs from a sqlQuery into an array, you would do something like this:
$query=mysql_query("SELECT * FROM table WHERE...");
while($row=mysql_fetch_assoc($query)){
$array[]=$row['id'];
}
name[]?