SHOW COLUMNS with prepared in php

[BrainPill]

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 February 19, 2018 by BrainPill

[iwato]

$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 February 19, 2018 by iwato

[iwato]

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

$mysqli_obj = $conn;

[BrainPill]

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? 

 

 

[iwato]

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.

[BrainPill]

 

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 

 

 

 

[dsonesuk]

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
        
        ?>

 

[dsonesuk]

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 February 21, 2018 by dsonesuk

[BrainPill]

Using the 2nd option was a useful solution.

 

Thanks for helping me out dsonesuk.

 

[BrainPill]

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.

[iwato]

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