How to check existence of column in MySQL table using php

You just need to query the information schema:

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

Select allOpen in new window

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 

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();

Select allOpen 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

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

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

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);

Select allOpen 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');

Select allOpen 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

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 

OK, Chris, thanks a lot, works perfectly.

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

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";

Select allOpen 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.

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

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

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.

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 

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.

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

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

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.

Thanks, Chris.

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

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

Good luck with the rest of your project