How To Pull Mysql Tbl Column Names Into Array And Display On Page Only Those Column Names Not On The Excluded List ?

phpsane · November 19, 2018

My Php Buddies,

I have mysql tbl columns these:

id:
date_&_time:
account_activation_code:
account_activation_status:
id_video_verification_status:
id_verification_video_file_url:
username:
password:
primary_domain:
primary_website_email:
sponsor_username:

Now, I want to display their row data by excluded a few columns.

Want to exclude these columns:

date_&_time

account_activation_code

account_activation_status

id_verification_video_file_url

password

So, the User's (eg. your's) homepage inside his account should display labels like these where labels match the column names but the underscores are removed and each words' first chars CAPITALISED:

Id: 1
Id Video Verification Status: 1
Username: requinix
Primary_domain: requinix.majesty
Primary Website Email: requeen@requinix.majesty
Sponsor Username: phpsane
Recruits Number: 2

For your convenience only PART 1 works. Need help on Part 2

My attempted code:

	PART 1
	<?php 
	// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
	// Query to get columns from table
$query = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'members' AND TABLE_NAME = 'users'");
	while($row = $query->fetch_assoc()){
    $result[] = $row;
}
	// Array of all column names
$columnArr = array_column($result, 'COLUMN_NAME');
	foreach ($columnArr as $value) { 
echo "<b>$value</b>: ";?><br><?php 
} 
	?>
	 
	PART 2
	<?php 
	//Display User Account Details 
echo "<h3>User: <a href=\"user.php?user=$user\">$user</a> Details</h3>";?><br> 
<?php 
$excluded_columns = array("date_&_time","account_activation_code","account_activation_status","id_verification_video_file_url","password"); 
foreach ($excluded_columns as $value2) 
{    
    echo "Excluded Column: <b>$value2</b><br>";
}
foreach ($columnArr as $value) 
{ 
    
    if($value != "$value2")
    { 
        $label = str_replace("_"," ","$value"); 
        $label = ucwords("$label"); 
        //echo "<b>$label</b>: "; echo "$_SESSION[$value]";?><br><?php 
        echo "<b>$label</b>: "; echo "${$value}";?><br><?php 
    }
} 
?>

PROBLEM: Columns from the excluded list still get displayed.

Edited November 19, 2018 by phpsane

requinix · November 19, 2018

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'members' AND TABLE_NAME = 'users'

Don't do that. Basically never do that. You shouldn't have to query information_schema for anything remotely resembling normal usage of your database.

Write the query the way it should be, listing the columns you want it to return.

phpsane · November 19, 2018

51 minutes ago, requinix said:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'members' AND TABLE_NAME = 'users'
Don't do that. Basically never do that. You shouldn't have to query information_schema for anything remotely resembling normal usage of your database.

Write the query the way it should be, listing the columns you want it to return.

Ooops! I copied it from the manual:

https://www.codexworld.com/how-to/get-column-names-from-table-in-mysql-php/

Before found this:

https://stackoverflow.com/questions/1526688/get-table-column-names-in-mysql

You mean, I should use Prep Stmts ? Like this:

	$query = "SELECT COLUMN_NAME FROM users WHERE TABLE_SCHEMA = ?" AND TABLE_NAME = ?; 
	$stmt = mysqli_prepare($conn,$query); 
 mysqli_stmt_bind_param($stmt,'ss',$db_name','tbl_name'); 
mysqli_stmt_execute($stmt); 
$result = mysqli_stmt_bind_result($stmt,$db_id,$db_recruits_number,$db_sponsor_username,$db_account_activation_status,$db_id_video_verification_status,$db_id_verification_video_file_url,$db_username,$db_password,$db_primary_domain,$db_website_email,$db_registering_country,$registering_ip,$registering_browser,$registering_os,$registering_isp,$db_age_range); 
    mysqli_stmt_fetch($stmt_1); 
    mysqli_stmt_close($stmt_1); 
    //Free Result_1 Set 
    mysqli_stmt_free_result($stmt_1);

You know what ? my "mysqli_stmt_bind_result" is wrong up there because I literally wrote the cols names. We were supposed to get the script to do that. And so, how to integrate my old code's relevant parts here to complete the script ? I need your help here!

Btw, don't even know what this SCHEMA is.

I have a feeling this isn't right and so how to mend it ?

	 mysqli_stmt_bind_param($stmt,'ss',$db_name','tbl_name');

My conn.php looks like this:

	<?php 
	//Connect to Mysql Database in this input format: "Server", "MySql User", "Mysql User Password", "MySql Database Name". 
$conn = mysqli_connect("localhost", "root", "", "test_tbl"); 
	if (!$conn) 
{ 
    //Error Message to show user in technical/development mode to see errors.
    die("Database Error : " . mysqli_error($conn)); 
    
    //Error Message to show User in Layman's mode to see errors.
    die("Database error."); 
    exit(); 
} 
	?>

Edited November 19, 2018 by phpsane

requinix · November 19, 2018

SELECT id, id_video_verification_status, username, primary_domain, primary_website_email, sponsor_username FROM users...

phpsane · November 19, 2018

1 minute ago, requinix said:


SELECT id, id_video_verification_status, username, primary_domain, primary_website_email, sponsor_username FROM users...

You mean I should literally write the cols names and not get the script to grab them ? Why ? What is the risk and how to avoid it ?

Anyway, let us say for learning purpose I want to go ahead with it. Now, how would you code it ? May I see a sample ? I need my previous post answered too!

Barand · November 19, 2018

if (!$conn) 
{ 
    //Error Message to show user in technical/development mode to see errors.
    die("Database Error : " . mysqli_error($conn)); 
    
    //Error Message to show User in Layman's mode to see errors.
    die("Database error."); 
    exit(); 
}

Like you, your code can only die() once. You are attempting it three times (note exit() is the same as die() )

requinix · November 19, 2018

Just now, phpsane said:

You mean I should literally write the cols names and not get the script to grab them ? Why ?

Because that is the correct way to do it.

Just now, phpsane said:

What is the risk and how to avoid it ?

The risk with using information_schema is because you don't know what it is and you're going to abuse it.

You avoid the risk by not using it.

Just now, phpsane said:

Anyway, let us say for learning purpose I want to go ahead with it. Now, how would you code it ?

I would not.

Just now, phpsane said:

May I see a sample ?

Not from me.

phpsane · November 19, 2018

9 minutes ago, Barand said:


if (!$conn) 
{ 
    //Error Message to show user in technical/development mode to see errors.
    die("Database Error : " . mysqli_error($conn)); 
    
    //Error Message to show User in Layman's mode to see errors.
    die("Database error."); 
    exit(); 
}

Like you, your code can only die() once. You are attempting it three times (note exit() is the same as die() )

Man! Some guy wrote that file for me like that. Yes, I do know die() and exit() are same. I just overlooked his code. Thanks for bringing it to my attention!

Switching it to this:

if (!$conn) 
{ 
    //Error Message to show user in technical/development mode to see errors.
    "Database Error : " . mysqli_error($conn)); 
    
    //Error Message to show User in Layman's mode to see errors.
    "Database error."); 
    exit(); 
}

Edited November 19, 2018 by phpsane

phpsane · November 19, 2018

3 minutes ago, requinix said:

Because that is the correct way to do it.

The risk with using information_schema is because you don't know what it is and you're going to abuse it.

You avoid the risk by not using it.

I would not.

Not from me.

Mmm. I get your point. But, since you know the risk then I wanna see a sample from you to see how you'd avoid the abuse. Or, tell me atleast what to google for to find the right code.

@barand: Can you suggest some code sample or keywords to google ?

requinix · November 19, 2018

2 minutes ago, phpsane said:

I wanna see a sample from you to see how you'd avoid the abuse

I would avoid the abuse by NOT USING IT AT ALL.

Barand · November 19, 2018

9 minutes ago, phpsane said:

@barand: Can you suggest some code sample or keywords to google ?

I fail to see the point of creating a list of the columns you don't want then writing a shed load of code to stop them displaying.

It's far easier and more efficient to do it the correct way (as @requinix told you) and specify the columns you do want in the SELECT list.

mac_gyver · November 20, 2018

1 hour ago, Barand said:
if (!$conn) 
{ 
    //Error Message to show user in technical/development mode to see errors.
    die("Database Error : " . mysqli_error($conn)); 
    
    //Error Message to show User in Layman's mode to see errors.
    die("Database error."); 
    exit(); 
} 
Like you, your code can only die() once. You are attempting it three times (note exit() is the same as die() )

it's even worse than that. you cannot use mysqli_error to report connection errors, since there's no connection to supply as a parameter.

also, the OP's main code is using $conn->connect_error, which only works if the connection is using OOP notation, which it isn't.

i'm pretty sure the OP has been shown/given the line of code needed to use exceptions for errors with the mysqli extension, since he has included it at various times in his code., and which would eliminate ALL this error handling logic from his code.

i'm wondering if the OP is ready yet to switch to the much simpler and more consistent php PDO extension, that only has ONE possible calling syntax, that will cut the amount of code in half.

ginerjm · November 20, 2018

Whatever made you consider using the information schema table to do some query? Why why why?

If the problem is trying to get a readable column heading for each column that you are querying for, why not just create an array that uses the actual column name as the key and your desired column heading value as the array's value? Then:

	echo "tr';
	$first=true;
	while($row = $qry_results->fetch())
	{
	   if ($first)
	   {
	     foreach ($row as $k=>$v)
	     {
	        echo "<th>" . $column_names["$k"] . "</th>";
	     }
	     echo "</tr>";
	    $first = false;
	  }
	// continue with output of actual query data here
	//  end of while loop
	}

This is un-tested but it gives you the jist of my idea.

Barand · November 20, 2018

21 minutes ago, ginerjm said:

$column_names["$k"]

Why the quotes?

(He has a bad habit of putting every single string variable inside quotes too )

Alternatively, you can use column aliases to get the desired headings EG

$sql = "SELECT fname as `First Name`
             , lname as `Last Name`
             , timestampdiff(YEAR, dob, curdate()) as Age
        FROM employee
        ORDER BY age     
        ";
        
$res = $db->query($sql);
if ($row = $res->fetch()) {
    echo "<table border='1' style='border-collapse: collapse'>\n" ;
    echo "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";    // get headings from row array keys
    do {
        echo "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
    } while ($row = $res->fetch());
    echo "</table>\n";
}  

+------------+-----------+------+
| First Name | Last Name | Age  |
+------------+-----------+------+
| Jane       | Mansfield |   27 |
| Peter      | Smith     |   33 |
| Mary       | Baker     |   38 |
| Paul       | Hartley   |   44 |
+------------+-----------+------+

requinix · November 20, 2018

1 hour ago, Barand said:

Why the quotes?

I believe it happens when people have learned that array keys are strings and that strings need quotes, so therefore array keys need quotes.

phpsane · November 20, 2018

16 hours ago, mac_gyver said:

it's even worse than that. you cannot use mysqli_error to report connection errors, since there's no connection to supply as a parameter.

also, the OP's main code is using $conn->connect_error, which only works if the connection is using OOP notation, which it isn't.

i'm pretty sure the OP has been shown/given the line of code needed to use exceptions for errors with the mysqli extension, since he has included it at various times in his code., and which would eliminate ALL this error handling logic from his code.

i'm wondering if the OP is ready yet to switch to the much simpler and more consistent php PDO extension, that only has ONE possible calling syntax, that will cut the amount of code in half.

Hello Mac Guyver!

Long time since I heard from you.

My original code I acquired from one of tutorial links mentioned above. It was in oop. My membership script (which I am building for 21mnths now) is mysqli procedural.

Other programmers in many other forums (especially Benanamen) been suggesting for nearly 2yrs now to switch to pdo. Back then, I did not know much like what is pdo and mysqli, etc. Most tutorials were on mysqli and started on that and did not want to quit halfway. Been making dates with suggesters for over a yr now when I am gonna start on pdo but could not really finish learning mysqli properly. Actually, I been struggling to build a pagination with prep stmt and so this is the delay.

Anyway, since I now got Requinix, Barand and you to help me get going to finish it then I can proudly say I will probably start on pdo next wk. It all depends on how fast you folks complete the script. So, I will open a thread to convert non-prep stmt pagination code to prep stmt pagination and you fine folks can show me sample lines where I went wrong so I can learn quickly and get over mysqli and jump onto pdo next wk. Don't want to see another Christmas Eve, Christmas, New Yrs Eve and New Yrs Day pass with me still fumbling with mysqli. Ok ?

Cheers!

phpsane · November 20, 2018

3 hours ago, ginerjm said:
Whatever made you consider using the information schema table to do some query? Why why why?

If the problem is trying to get a readable column heading for each column that you are querying for, why not just create an array that uses the actual column name as the key and your desired column heading value as the array's value? Then:
	echo "tr';
	$first=true;
	while($row = $qry_results->fetch())
	{
	   if ($first)
	   {
	     foreach ($row as $k=>$v)
	     {
	        echo "<th>" . $column_names["$k"] . "</th>";
	     }
	     echo "</tr>";
	    $first = false;
	  }
	// continue with output of actual query data here
	//  end of while loop
	}
	
This is un-tested but it gives you the jist of my idea.

I just tired writing 20 or so column names and so wanted the php script to do the name writing. Also, was curious to learn how to do it.

Thanks for the code sample!

Barand · November 20, 2018

Cut out the middlemen. Go here

Barand · November 20, 2018

5 minutes ago, phpsane said:

I just tired writing 20 or so column names

Get a decent IDE.

For example, mine (PhpEd) lets me drag all the fields names from my db structure into my code.

phpsane · November 20, 2018

21 minutes ago, Barand said:

Get a decent IDE.

For example, mine (PhpEd) lets me drag all the fields names from my db structure into my code.

I use NotePad++. I thought most programmers do. But now I suspect otherwise. According to you which majority use ?

Barand · November 21, 2018

Going back to your original question (if you really want to do the extra work out of curiosity) here is a safer way using a SHOW COLUMNS query, which gives output like this

mysql> show columns from notes;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| sender_id    | int(11)      | YES  |     | NULL    |                |
| recipient_id | int(11)      | YES  |     | NULL    |                |
| message      | varchar(500) | YES  |     | NULL    |                |
| time_sent    | datetime     | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

The column you are interested in is the "Field" column. So you can

$db = pdoConnect('test');

$exclude = ['id', 'time_sent'];           // columns to be excluded from the query

$result = $db->query("SHOW COLUMNS FROM notes");
$data = $result->fetchAll();
$fields = array_column($data, 'Field');
$wanted = array_diff($fields, $exclude);

$sql = 'SELECT ' . join(', ', $wanted) . ' FROM notes';

echo $sql;              //--> SELECT sender_id, recipient_id, message FROM notes

Sign In

How To Pull Mysql Tbl Column Names Into Array And Display On Page Only Those Column Names Not On The Excluded List ?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information