Check if column exist in MySQL table via PHP

Question

I created mysql tables and there I have put some columns.

Now I want to check that a certain column exists in the database via php.

Like this:

if (column exist){
echo "Column in table is available."
}
else{
echo "Column doesnt exist.";
}

Is it possible to do this?

A lot of thanks for your time :)

score 1 · Answer 1 · answered Mar 18 '19 at 07:26

Suppose you want to find the customer who has placed at least one sales order, you can use the EXISTS operator as follows:

SELECT customerNumber,
         customerName
FROM customers
WHERE EXISTS
    (SELECT 1
    FROM orders
    WHERE orders.customernumber = customers.customernumber);

For each row in the customers table, the query checks the customerNumber in the orders table.

If the customerNumber, which appears in the customers table, exists in the orders table, the subquery returns the first matching row. As the result, the EXISTS operator returns true and stops scanning the orders table. Otherwise, the subquery returns no row and the EXISTS operator returns false.

To get the customer who has not placed any sales orders, you use the NOT EXISTS operator as the following statement:

SELECT customerNumber,
         customerName
FROM customers
WHERE NOT EXISTS
    (SELECT 1
    FROM orders
    WHERE orders.customernumber = customers.customernumber);

score 0 · Answer 2 · answered Oct 07 '19 at 17:10

0

This is simple but it works for me:

1) Select all from the table you want.

$qry = "SELECT * FROM table";

2) Bring the result and verify if the field exists.

if( $result = $mysqli->query($qry)  &&  isset($result['field_you_want']) ){
    //Exists
}else{
    //Doesn't exists
}

answered Oct 07 '19 at 17:10

Giovax68

25
4

This gives the error "Cannot use object of type mysqli_result as array". You need to use a fetch method on it or something like that. Also, you could add LIMIT 1 to your query in case there's a lot of data. – Bloopy Jan 03 '21 at 02:11

score -1 · Answer 3 · answered May 07 '14 at 09:12

-1

You can use mysql_list_fields and mysql_num_fields to get columns of a table

$fields = mysql_list_fields('database_name', 'table_name');
$columns = mysql_num_fields($fields);

answered May 07 '14 at 09:12

sshet

1,152
1
6
15

looks like these methods are depreciated in current PHP versions – atwellpub Apr 09 '16 at 19:25

score -1 · Answer 4 · answered May 07 '14 at 09:27

If your database is MySQL then, you can use mysql_field_name() and mysql_fetch_field() function in php.

$res = mysql_query('select * from customer', $link);

echo mysql_field_name($res, 0); // print cust_id
echo mysql_field_name($res, 1); // print cust_name 
echo mysql_field_name($res, 2); // print cust_age

anthony · Answer 5 · 2021-01-28T13:39:04.890

-1

Create a function

function CheckIfColumnExist($string) {
global $sqlConnect;
if (empty($string)) {
    return false;
}
$query = mysqli_query($sqlConnect, "SELECT $string FROM `table_name`");
if (mysqli_num_rows($query) > 0)
{
$fetched_data = mysqli_fetch_assoc($query);
return true;
}
}

in PHP

$string = $_POST['string'];
if(CheckIfColumnExist($string) == false){
echo "column doesn't exist!."; 
}else{
echo "column is existing!.";
}

edited Jan 28 '21 at 13:39

answered Jan 28 '21 at 12:25

anthony

1
1

2

Please add some explanation to your answer such that others can learn from it – Nico Haase Jan 28 '21 at 12:42

score -2 · Answer 6 · edited May 23 '17 at 11:47

In PHP:

$fields = mysql_list_fields('database_name', 'table_name');
$columns = mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {$field_array[] = mysql_field_name($fields, $i);}

if (!in_array('price', $field_array))
{
$result = mysql_query('ALTER TABLE table_name ADD price VARCHAR(10)');
}

This should also help you:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘TEST’ AND COLUMN_NAME = ‘TEST_DATE’)
BEGIN
  ALTER TABLE TEST ADD TEST_DATE DATETIME
END

Or you can do:

Show columns from table like 'string';

Check out this question : How can I check if mysql table column even exists?

Checking whether a column exists usually should not contain `ALTER TABLE` — Nico Haase, Jan 28 '21 at 12:44

score -2 · Answer 7 · answered Feb 23 '15 at 11:07

-2

Run this query in php and check if number of rows > 0 :- DESC tablename '%columns_name%'

answered Feb 23 '15 at 11:07

Vandana

37
3

Please add some explanation to your answer such that others can learn from it – Nico Haase Jan 28 '21 at 12:43

Check if column exist in MySQL table via PHP

7 Answers7

Linked