Jump to content

SHOW COLUMNS with prepared in php


BrainPill
 Share

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
Link to comment
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
Link to comment
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? 

 

 

Link to comment
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.

Link to comment
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 

 

 

 

Link to comment
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
        
        ?>

 

Link to comment
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
Link to comment
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.

Link to comment
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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...