Avatar of Richard Korts
Richard Korts
Flag for United States of America asked on

How to check existence of column in MySQL table using php

I want to check a MySQL table in php to see if a column with a specific name exists in that table.


For example, a table might contain columns like "col1-abc", "col2-abc" and col2-xyz" but NOT "col1-xyz".


What is php code that will do this?

Thank you


PHPMySQL Server

Avatar of undefined
Last Comment
Chris Stanyon

8/22/2022 - Mon
ste5an

You just need to query the information schema:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTable'
    AND COLUMN_NAME = 'yourColumn';

Open in new window

Richard Korts

ASKER
I don't understand in php.

I have several databases in my server account and an information_schema I connect to one of my databases, I don't see how this can work. I need to be connected to a SPECIFIC database.

Can someone show php code that will do this with database "xyz"?

Thank you 
Chris Stanyon

Hey Richard,

How's things ?

Couple of ways of doing this, so I'll give you a general overview. Your MySQl server contains a database called INFORMATION_SCHEMA which contains a lot of information about all the databases, tables, columns etc on that server. The idea that ste5an alluded to was to query that database to see if you column exists - effectively, the query would return a record if the column existed.

I'm gonna assume PDO here:

$pdo = new PDO( //connect to your DB here );

$sqlStr = <<<EOT
    SELECT COUNT(1)
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE
        TABLE_SCHEMA='xyz' AND
        TABLE_NAME='yourTable' AND
        COLUMN_NAME='col1-xyz'
EOT;

$result = $pdo->query($sqlStr);
$columnExists = (bool)$result->fetchColumn();

Open in new window

Let's break that down. We're making a connection your DB using PDO. We're then running a query against the COLUMNS table (this contains all the info about all the columns in all the tables across all the databases) which will count the number of records that match the criteria - it checks for a named Column (COLUMN_NAME), within a named Table (TABLE_NAME), within a named Database (TABLE_SCHEMA).

Because we're using COUNT, the results of running that query will simply be 0 or 1 (no records or 1 record - can't have more than one with the same name !!).

We then cast the 0 or 1 to a boolean to give you a true / false value
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Richard Korts

ASKER
Chris,

Looks good, I'll try shortly.

I'm doing OK, was about to contact you a week ago, you are one of my two "absolutely can solve anything" guys. The other one did it, or enough that I got the rest.

Richard
Richard Korts

ASKER
I don't understand what $pdo is.
I connect to the data base using an include that has the db name, password, etc.

The database name is "lakoshva_dev"

I don't want to put password on open EE.

Please tell me what to assign to $pdo.

Thank you,

Richard
Chris Stanyon

Hey Richard,

$pdo is just the variable that I used to store the connection - if you look in your include file, you'll see what you should use.

If you're using PDO, then it will look something like

$someVariable = new PDO('mysql:host=localhost;dbname=lakoshva_dev;charset=utf8mb4', $user, $pass);

Open in new window

Once you've got the connection stored in a variable,, then you use that to execute commands against your DB ($pdo->query() etc.)

If you're using MySQLi instead of PDO, then it will look slightly different, depending on whether you're using Procedural or Object notation (preferred!)

// Procedural
$myDb = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');

// Object (OOP)
$conn = new mysqli('localhost', 'my_user', 'my_password', 'my_db');

Open in new window

If you are using MySQLi, then you'll need to adapt my code above as it was written specifically for PDO - a much better library in my opinion
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Richard Korts

ASKER
Not quite there.

I have this:

<?php
// chect if table exists
include "db_connect_nb.php";

$sqlStr = <<<EOT
    SELECT COUNT(1)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
        TABLE_SCHEMA='lakoshva_dev' AND
        TABLE_NAME='control_panel_schematics' AND
        COLUMN_NAME='TC-ABV-230'
EOT;

$result = $link->query($sqlStr);
$columnExists = (bool)$result->fetchColumn();
if ($columnExists) {
   echo "column there";
} else {
   echo "column NOT there:|";
}
exit;
?>  

I get: Fatal error: Call to undefined method mysqli_result::fetchColumn() in /home/lakoshva/public_html/dev/chk_column_there.php on line 15 
ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Richard Korts

ASKER
OK, Chris, thanks a lot, works perfectly.

Richard Korts

ASKER
Chris,

I incorporated the code into my real program, like this:

<?php
   include "db_connect_nb.php";
   //echo "entered summary<br>";
   //echo "session[vid] = " . $_SESSION['vid'] . "<br>";
   function fmt_price_whole($x) {

      //echo "x = " . $x . "<br>";

      if ($x == 0) {

         $_SESSION['fac_quote'] = true;

         return "Factory Quote";

      }

      $r = "$" . number_format($x,0);

      return $r;

   }
   function chk_voltage_docs($x) {
      $colname = "TC-ABC-" . $x;
      $sqlStr = <<<EOT
    SELECT COUNT(1)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
        TABLE_SCHEMA='lakoshva_dev' AND
        TABLE_NAME='control_panel_schematics' AND
        COLUMN_NAME='<? print $colname; ?>'
EOT;
   $result = $link->query($sqlStr);
   $columnExists = (bool)$result->fetch_row()[0];
   if ($columnExists) {
      return true;
   } else {
      return false;
   }
}  

It gives me this error:

Fatal error: Call to a member function query() on null in /home/lakoshva/public_html/dev/summary.php on line 32 

Line 32 is:  $result = $link->query($sqlStr);

db_connect_nb includes the database connection returning $link.

Any ideas?

Do I have to include db_connect_nb in the function?

Thanks,

Richard
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Chris Stanyon

Hmmm,

If you're getting that error, then your $link variable is null. This is down to either your include failing, or the connection within the include failing. In your code, you should really change 'include' to 'require_once'. This is more explicit and acts as a definitive command instead of a lazy suggestion - a failed include generates a warning whereas as failed require generates an error - it makes a difference to your code flow.

<?php
   requite_once "db_connect_nb.php";

Open in new window

Now your code will either fail on the require ( the file can't be found) or it will fail on the connection - in which case you need to check that code specifically. Either way, you'll know exactly where you code fails.
Richard Korts

ASKER
OK, I'll try that but the rest of this program requires the database connection in MANY other places & they all work.
Richard
Richard Korts

ASKER
Chris,

I haven tried the require_once yet. The following is the little test program I wrote to test your method with my data:

<?php
// check if table exists
include "db_connect_nb.php";

$sqlStr = <<<EOT
    SELECT COUNT(1)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
        TABLE_SCHEMA='lakoshva_dev' AND
        TABLE_NAME='control_panel_schematics' AND
        COLUMN_NAME='TC-ABV-460V'
EOT;

$result = $link->query($sqlStr);
$columnExists = (bool)$result->fetch_row()[0];
if ($columnExists) {
   echo "column there";
} else {
   echo "column NOT there:";
}
exit;
?>  

This works perfectly, notice the include at the top.

Richard
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Stanyon

Hmmm,

Tricky one. You shouldn't need to 'include' within the function, as you include earlier which should push the $link variable into the Global Scope (available everywhere). I'm going to assume you're not using any namespacing here and you're not writing any OOP code (using classes / objects etc).

I'm only seeing part of your code so it's a little tricky figuring out what's happening. The code we're seeing only declares the functions - you're obviously calling those functions from somewhere else, so I can't know the scope of the call. Maybe post up the more code showing where you call the functions from.

It is possible that running include instead of include_once / require_once causes a weird side effect, because each time you include, you create a new connection. That's why we use the *_once functions - it only get's included once, and therefore you only ever have one connection.
Richard Korts

ASKER
Hi Chris,

I changed the include to "require_once".

Here is the code where the function is called. Only in this one place:

// control panel schematics
$headtag =  $_SESSION['voltage'] . "V";
if (chk_voltage_docs($headtag)) {
//if (headtag == "460V"){
   if (substr($model,0,4) == "eTCX" || substr($model,0,4) == "eTBX") {
etc.

Same as before:

Fatal error: Call to a member function query() on null in /home/lakoshva/public_html/dev/summary.php on line 32 
Chris Stanyon

Still struggling to see what's going on here. The only reason you'd get that error is if the $link is null, which indicates a failure to connect to the the DB.  Can you paste up the full code for your summary.php page so I cans ee th efull context of it.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Richard Korts

ASKER
Hi Chris,

Full code attached. If you need to see db_connect_nb.php, I can send it to you privately as it has the database passwords in it.

FYI, other code in summary.php accesses the database & that part is working fine.

I only made the column lookup thing a function because that seemed like what it should be. The same code in-line without a function is in the little test program I used to validate your methodology. Since it is only accessed, I could just put the code inline there.

Thank you,

Richard

summary.php
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Richard Korts

ASKER
I kind of thought that would be the case.

Yeah, on the 2nd thing, I sometimes forget where I'm at, thanks for catching that. That's obvious.

I'll try changes shortly, customer ran into the fatal error, I'm reverting out to let him test other things for a while.

Thank you, I knew you would find it. Boy am I ever glad I discovered you on EE
ste5an

Just some comments:

I would guess after a first glance at it, around 40 up to 60 percent of the code is redundant, created by copy and paste. Use methods and loops instead.

Tidy up your markup. There is also a remarkable amount of inline style tags used instead of CSS (padding, font, colors) in a separate file.

You have JavaScript decoupled from the PHP code. I strongly recommend to move it into separate files.

You have only around ten lines JavaScript using JQuery, I would consider VanillaJS instead.

Also there are lots of different type of string literals in the code, which should be clearly loaded from the database (captions) or a configuration file (paths).

And it seems that you should seriously consider splitting the markup into components (partial views). The alternative would be a uniform summary, but then it should be possible to load all the data from the data base and you should be able to avoid lots of the PHP code flow.

Furthermore, there are redundant round-trips to your database. A lot of queries are only dependent on the $model. Looks like this could be loaded in one round-trip.

My personal, but strong opinion:

Checking in such a use-case columns for existence is a major design flaw.

From the database perspective, schemata are static or at least stable during life cycles.
Changes must be handled be the deployment management.

Your original question in conjunction with that code is a strong indicator that you store (variant) data in the schema instead of a table. This means there is a server data model flaw.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Richard Korts

ASKER
Thanks, Chris.

After correcting about 5 other of my typos, it WORKS!!
Chris Stanyon

Nice one Richard. Always a good feeling to get a resolution.

Good luck with the rest of your project