Jump to content
BrainPill

SHOW COLUMNS with prepared in php

Recommended Posts

Hi

I would like to know how to fetch all columns from a table. Not the data in the table, but only the names of the columns.

I made a script. I tested the query first in the console giving me a good result in the section Field .

Can someone please explain how to use this in PHP ?

code example:

<?php 

	// test set up for fetching column names

	$servername = "localhost";
	$username = "name";
	$password = "pass123";
	$dbname = "test_database"; //  
	  

	$conn = new mysqli($servername, $username, $password, $dbname);

	if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	}
		
		if ($stmt = $conn->prepare("SHOW COLUMNS	
				FROM test_table ")) {
				
		 
		$stmt->execute();
		
			$row = $stmt->fetch();
		    var_dump($stmt);
		    var_dump($row);
			foreach ($row as $value) {
			var_dump($value);
				
					
			}
		
		}
	$stmt->close();
			$conn->close();
	


?>

 

EDIT:

I solved the script in the following way.

<?php  

		$stmt = $conn->prepare("SHOW COLUMNS	
			FROM test_table");
	 	$stmt->execute();
	
		$res = $stmt->get_result();
		var_dump($res);
	
    	foreach ($res as $val){
		$col = $val['Field'];
		var_dump($col);
		
                          	}

?>

 

You have to remove the if part and the {} and replace it with the code above.

 

 

 

Edited by BrainPill

Share this post


Link to post
Share on other sites
$db_name = "...";
$tbl_name = "...";
$sql = "SHOW FULL COLUMNS IN " . $tbl_name . " IN " . $db_name;
$result_obj = $mysqli_obj->query($sql);
while ($row = $result_obj->fetch_assoc()) {
    foreach ($row as $key => $value) {
        echo "<span style='font-weight:bold;'>" . $key . "</span>: <span style='color:red;'>" . $value . '</span><br />';
    }
    echo '<hr>';
}

Roddy

Edited by iwato

Share this post


Link to post
Share on other sites

Why are you using prepared statements to show table fields?  It seems excessive.  By the way, in your case

$mysqli_obj = $conn;

Share this post


Link to post
Share on other sites

This recommendation you give is mysqli and not OOP with prepared statements.

Would that be safe? 

I am not that experienced with php and injection but from reading online my conclusion is that OOP with prepared statements and preferably placeholders was the safest way of programming. 

But, then again, in which cases is it - regarding security - obliged? 

 

 

Share this post


Link to post
Share on other sites

Security is necessary when others have access to your database.  PHP is only visible to you.  A form, on the other hand, is visible to everyone, and the data passed to your database via the form can be entered by anyone.

Roddy

If what i write has been of help, please do not forget to say thanks.
Hover over the heart and click on the trophy.

Share this post


Link to post
Share on other sites

 

Its the same case as above.

I have to arrays.

But one array has the wrong output. Its like this:

Quote

........\ArrayTests\test-array-forumpost.php:16:

array (size=1)

  0 => string 'cola5a8d9f9f47c' (length=15)

........\ArrayTests\test-array-forumpost.php:16:

array (size=1)

  0 => string 'colb5a8d9f9f47c' (length=15)

........\ArrayTests\test-array-forumpost.php:16:

array (size=1)

  0 => string 'colc5a8d9f9f47c' (length=15)

........\ArrayTests\test-array-forumpost.php:16:

array (size=1)

  0 => string 'cold5a8d9f9f47c' (length=15)

 

It is all assigned to key value 0.

But it should be an array with:

1=> value

2=> next value

3=> next value 

etc.

 

this is the script I use for it:


$pr_ar  = array( 0 => 'cola' ,  1 => 'colb' , 2 => 'colc' , 3 => 'cold' ) ;
	
         $unique = uniqid(); 
		  $suf = substr($unique, 0, 11);  
		 	
	foreach ($pr_ar as $pre) {
	
	// var_dump($pre);
	 
			$new_col = $pre.$suf;
			//var_dump($new_col);	
 $cna = array($new_col);
	 var_dump($cna);
	 
	}

 

  how to assign the values to different keys? 

Please help 

 

 

 

Share this post


Link to post
Share on other sites

You are recreating the array and resetting it to take a single value each loop

should be

$pr_ar = array(0 => 'cola', 1 => 'colb', 2 => 'colc', 3 => 'cold');

        $unique = uniqid();
        $suf = substr($unique, 0, 11);
        $cna = []; // set up loop
        foreach ($pr_ar as $pre) {

            // var_dump($pre);

            $new_col = $pre . $suf;
            //var_dump($new_col);
            $cna[] = $new_col; // add new value to array
        }
        var_dump($cna); // show final result
        
        ?>

 

Share this post


Link to post
Share on other sites

OR use array $pr_ar  $key in foreach to apply index key ref to the newly created array item each loop

        $pr_ar = array(0 => 'cola', 1 => 'colb', 2 => 'colc', 3 => 'cold');

        $unique = uniqid();
        $suf = substr($unique, 0, 11);

        foreach ($pr_ar as $key => $pre) {

            $new_col = $pre . $suf;
            $cna = [];
            $cna[$key] = $new_col;
            var_dump($cna);
        }
        ?>

It will give the correct result from your previous post, but why create another array? just gather the string value on each loop, and use to rewrite column name, if this is to used to change column names? as indicated by another topic.

Edited by dsonesuk
  • Like 1

Share this post


Link to post
Share on other sites
18 hours ago, iwato said:

Security is necessary when others have access to your database.  PHP is only visible to you.  A form, on the other hand, is visible to everyone, and the data passed to your database via the form can be entered by anyone.

Roddy

If what i write has been of help, please do not forget to say thanks.
Hover over the heart and click on the trophy.

Hi Roddy.

I think your method of querying is (almost) obsolete and I prefer the methods I described.

Share this post


Link to post
Share on other sites
4 hours ago, BrainPill said:

I think your method of querying is (almost) obsolete and I prefer the methods I described.

Prepared statements are more difficult to implement.  

Match the method with the task.  Prepared statements are only required when security is an issue.

Roddy

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...