mysql SELECT a whole column or cycle through all IDs
Question by Jordashiro
I need to select a whole column.
So my question is how do i get a whole column ?
$query = "SELECT * ";
$query .= "FROM employees ";
$query .= "WHERE id=*";
$query .= "ORDER BY id ASC ";
I tried id=* but no luck …
My goal is to cycle through all IDs but some may be missing so i figured i put them in a numeric or associative array and use foreach. If there is a better way , please do share.
EDIT:
function get_all_ids()
{
global $connection;
$query = "SELECT * ";
$query .= "FROM employees ";
$query_result = mysql_query ( $query , $connection );
confirm_query($query_result);
$query_result_array = mysql_fetch_assoc($query_result);
return $query_result_array;
}
i use this to print the array
$all_id = get_all_ids();
// preparing the table;
echo "<pre>";
print_r($table);
print_r($all_id);
echo "</pre>";
and this is the array
Array
(
[id] => 1
[department_id] => 1
[name] => jordan
[EGN] => 9108121544
[email] => testEmail
[address] => testAddress
[country] => testCounty
)
Answer by Linus Kleen
If there’s more than one row in your result set, you need to keep fetching until all results are retrieved:
$q = mysql_query('SELECT * FROM `table`');
while (($row = mysql_fetch_assoc($q)) != FALSE)
{
// Do something with *one* result
}
mysql_free_result($q);
If you’d like to retrieve all ids in a single fetch, you could do:
$q = mysql_query('SELECT GROUP_CONCAT(`id`) AS `id_list` FROM `table`');
$row = mysql_fetch_assoc($q);
mysql_free_result($q);
$list_of_ids = explode(',', $row['id_list']);
WARNING: GROUP_CONCAT() usually has a result limit of 1024 bytes; meaning your results will be truncated for large tables. You could either resort to the first solution, or increase group_concat_max_len for the current connection.
Answer by Starx
If you want to select a single column. Then do not use “*”, give the name of the columns name separated by comma and quoted with “`” (tick) for safety.
$query = "SELECT `id` "; //if you only want to get ids from the table
$query .= "FROM employees ";
$query .= "WHERE id=*";
$query .= "ORDER BY id ASC ";
