How do I find if a column exists or not using PHP + mysqli database

Question

How do I find if a column exists or not using PHP + mysqli database.

I've been trying these and also comment part..but nothing seems to be working.

$column1="address";
$chkcol="SELECT * FROM `table_name` LIMIT 1";
/*  $chktable="SELECT * FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = `db_name` AND TABLE_NAME = `table_name` AND COLUMN_NAME = `$col_name`";*/
    $mycol = mysqli_fetch_array($chkcol); 
/*  echo $val=mysqli_query($conn,$chktable);
    echo $val; */
    if(isset($mycol['$column1']))
        {
            $message="Adress column already exists.";
            echo $message;
        }

    else
        {
            echo "Column doesn't exist";
         }

If there's any shortcode or simple code to make it work. Then let me know. This isn't MySQL, it's MySQLi(Procedural), since the earlier answers I found were MySQL (Obj Oriented) does't work.

querying information_schema is the right approach, dont no why you abandoned it — , Jan 18 '18 at 20:40
You haven't executed the query before trying to fetch results — Don't Panic, Jan 18 '18 at 20:40
In the commented information_schema query, you're using an undefined variable `$col_name` — Don't Panic, Jan 18 '18 at 20:42
As you are using `mysqli` perhaps take a look at `mysqli_fetch_fields/fetch_fields` - can easily be used to determine if a column exists. http://php.net/manual/en/mysqli-result.fetch-fields.php — Professor Abronsius, Jan 18 '18 at 20:47
Possible duplicate of [Check if column exist in Mysql table via php](https://stackoverflow.com/questions/23513479/check-if-column-exist-in-mysql-table-via-php) — Matt S, Jan 18 '18 at 20:51
@Don'tPanic `/* $chkcol="SELECT $currentdate FROM information_schema.columns WHERE table_name = 'teacher_attendance2'"; $val=mysqli_query($conn,$chkcol); $lol=mysqli_num_rows($chkcol); */` I commented the info.schema method because the mysqli_num_rows() is giving an error `mysqli_num_rows() expects parameter 1 to be mysqli_result` — Kirtimaan Chhabra, Jan 19 '18 at 07:28

sujaypatil · Answer 1 · 2018-01-19T06:42:51.460

Assuming you have already created the DB/Table with all the fields and successfully established connection with the MySQL database you're using (by creating a mysqli object or the procedural way), which might look something like this -

$connection = new mysqli('HOST_NAME', 'USERNAME', 'PASSWORD', 'DATABASE_NAME');

Write down your SQL query this way and store it in the variable of your choice. This way -

$query = 'SHOW COLUMNS FROM table_name';

Issue the query to the DB in the following way and capture the result -

$result = $connection->query($query);

You can then use the fetch_all function with a MYSQLI_ASSOC constant to retrieve all the column names. This way -

$values = $result->fetch_all(MYSQLI_ASSOC);
$column_names = array();    // $column_names = [];

if(!empty($values)){
    $column_names = array_keys($values[0]);
}

Now you know the column name you're looking for, which is stored as

$column1 = "address";

You can now check the $column_names array if your column exists -

if (in_array($coulmn1, $column_names)) {
    echo "Yes! " . $column1 . " is present in the table!";
}

This is a slightly longer solution. Probably not what you were looking for, but hope this helps.

Unfortunately not working for me. Instead of returning my table's columns, `echo json_encode($column_names)` returns `["Field","Type","Null","Key","Default","Extra"]`. These are the column names generated from a `SHOW COLUMNS` command ( http://www.mysqltutorial.org/mysql-show-columns/ ). I need all the entries in the `Field` column. — velkoon, Jan 21 '19 at 03:59

How do I find if a column exists or not using PHP + mysqli database

1 Answers1