Mysql, Iterate Through Column Names

mysql, iterate through column names

I think you want something like this:

DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  ORDER BY ordinal_position;


select FOUND_ROWS() into num_rows;

SET i = 1;
the_loop: LOOP

   IF i > num_rows THEN
        CLOSE col_names;
        LEAVE the_loop;
    END IF;


    FETCH col_names 
    INTO col_name;     

     //do whatever else you need to do with the col name

    SET i = i + 1;  
END LOOP the_loop;

MySQL Iterate over column names

You want to use UNION to combine a number of your count queries, as follows:

(SELECT 'Column1' AS `COLUMN_NAME`, COUNT(Column1) AS Count FROM my_table)
UNION ALL
(SELECT 'Column2' AS `COLUMN_NAME`, COUNT(Column2) AS Count FROM my_table)
-- ...
ORDER BY Count DESC

If you need to build such a query dynamically (e.g. because you do not know the column names in advance¹) then you can do so in your language of choice, using the result of your query on the information schema.

If your language of choice happens to be MySQL, then you can use its SQL syntax for prepared statements:

SELECT CONCAT(
         GROUP_CONCAT('(
           SELECT ', QUOTE(COLUMN_NAME), ' AS `COLUMN_NAME`,
                  COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '` AS Count
           FROM   my_table
         )'
         SEPARATOR ' UNION ALL '
       ), '
       ORDER BY Count DESC
       ')
INTO   @sql
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'my_table';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

^{1. Having dynamic schema of this sort is generally indicative of poor design.}

iterate through column names while updating table in mysql /multiple file uploads

Here's a starting point. You'll need to make some changes as question doesn't contain enough information.

$query = 'UPDATE property set ';
if(empty($_FILES['fileUpload']['name']) || !is_array($_FILES['fileUpload']['name'])) {
    exit('Invalid Update');
} else {
    foreach($_FILES['fileUpload']['name'] as $key => $value){
        $query .= ' img' . $key . ' = ?, ';
    }
}
$query = rtrim($query, ', ');
$query .= ' WHERE ... = ?'; <--- fix this (Don't replace the ?, that is placeholder for value that the column should equal)
$insert = $conn->prepare($query);
$params = array_merge($_FILES['fileUpload']['name'], array('WHERE IDENTIFER')); <--- fix this
$stmt->bind_param(str_repeat('s', count($_FILES['fileUpload']['name']) . 's(WHERE DATA TYPE HERE)', ...$params); <-- fix this
$stmt->execute();

Other notes:

This database design is going to cause issues later. See:

https://en.wikipedia.org/wiki/First_normal_form

You should do more than checking an extension to verify file integrity/security:

Full Secure Image Upload Script

(If not viewing full answer navigate to instead of just relying on the Content-type header for bit about file extension)

For more info on prepared statements with MySQLi see https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php. If just starting out you might consider PDO. It is usable across multiple database systems, and requires less code to create/execute prepared statements. Considerably less when retrieving data, and execution process is a lot clearer than MySQLi's.

PHP MySQL Get column names While iterating through all records

You can use foreach loop

$query = mysql_query("SELECT * FROM mytable");

while ($row = mysql_fetch_assoc($query)) {
     foreach($row as $key => $value) {
         print "$key = $value <br />";
     }
}

Loop through every column name and value in MySql reader?

What i would do is just loop through all the FieldCount this allows you to get the index for each returning row and then just add them to the dictionary using GetName and GetValue

protected bool TryLoadPlayerData(PlayerConnection playerConnection, string sso)
{
    try
    {
        SetPlayer(playerConnection);

        using (var databaseConnection = Hariak.HariakServer.Database.NewDatabaseConnection)
        {
            databaseConnection.SetQuery("SELECT * FROM `users` WHERE `auth_ticket` = @sso LIMIT 1;");
            databaseConnection.AppendParameter("sso", sso);


            using (MySqlDataReader reader = databaseConnection.ExecuteReader())
            {
                Dictionary<string, string> dictionary = new Dictionary<string, string>();
                for (int i = 0; i < reader.FieldCount; i++)
                    {
                        dictionary.Add(reader.GetName(i), reader.GetValue(i));
                    }
            }
        }

        Logger.Info("Loaded PlayerData for " + SelectColumn("username"));
        return true;
    }
    catch (Exception exception)
    {
        Logger.Error(exception, "Failed to load PlayerData for player.");
        return false;
    }
}

MYSQL: How could I iterate over table columns using a concatenated string as column name?

A normalised design might look something like this

loop through column names and insert values

Your insert statement is wrong. You can't use a variable sColName directly as a column name. You should generate the INSERT query string by concatenating parts of strings.

This is wrong:

INSERT INTO lptable (sColName) VALUE ( CONCAT('loop + ',X));

And You have to use VALUES but not VALUE.

And you also need prepared statement to execute dynamically composed SQL statements.

You would need following changes in your procedure.

BEGIN  
   ...

    DECLARE table_fields_str     VARCHAR( 255 );
    DECLARE values_str           VARCHAR( 255 );
    DECLARE _temp_sql_insert_str VARCHAR( 255 );

   SET table_fields_str = CONCAT( 'INSERT INTO lptable (', sColName, ') ' );
   SET values_str       =  CONCAT( ' VALUES( CONCAT( ''loop '', ', X, ' ) )' );

   SELECT CONCAT( table_fields, values_str ) INTO @_temp_sql_insert_str;

   PREPARE stmt FROM @_temp_sql_insert_str;
   EXECUTE stmt;
END;

Using Cursors to iterate through columns and rows assigning each to variables

Hope this sorts out your problem,

delimiter $$
create procedure test()
BEGIN

DECLARE done INT DEFAULT 0;
DECLARE i INT DEFAULT FALSE;
DECLARE num_rows INT DEFAULT FALSE;
DECLARE col_name VARCHAR(2000) DEFAULT FALSE;

DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_results';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  

set @info= null, @col_value= null;

OPEN col_names;


the_loop: LOOP

    FETCH col_names INTO col_name;
    IF done = 1 THEN
        LEAVE the_loop;
    END IF;


    SET @info = concat('SELECT ',col_name,' into @col_value FROM tbl_results LIMIT 1');
    prepare sqlstmt from @info;
    execute sqlstmt;
    deallocate prepare sqlstmt;


    SELECT xml_tag(col_name,@col_value); 

END LOOP the_loop;

CLOSE col_names;
set done = 0;

END$$

SQL get column names in a loop for aggregate function

You can create the query using dynamic SQL in a stored procedure. You get the column names from the INFORMATION_SCHEMA.COLUMNS table.

SET @cols = (
    SELECT GROUP_CONCAT('COUNT(`', column_name, '`) AS `', column_name, '`'))
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'table');
PREPARE @stmt FROM CONCAT('SELECT ', @cols, ' FROM table');
EXECUTE @stmt;

Mysql, Iterate Through Column Names