Jump to content

select the table, then column in SQL by PHP

Sigmahokies

By Sigmahokies
in PHP

 Share

Recommended Posts

Sigmahokies Newbie

Sigmahokies

Posted
Posted

Hi everyone, I'm trying to create to select the name of table, then pick column in PHP, I succeed pick table, then put name table in variable, then put this variable to end of SQL syntax, then pick column name, then submit, but it went error by broke and violate of SQL syntax. Hey, It's not about SQL injection, this database is very useless. it's just practice.

Can you help why it won't work?

[php]

$sqltable = "SHOW TABLES";

$querytable = $GaryDB->query($sqltable);

while($row3 = $querytable->fetch()) {
    $table .= "<option value='$row3[0]'>" . $row3[0] . "</option>";
}
    if(isset($_POST['table'])) {
        $table2 = $_POST['table'];


        $sql = "SHOW COLUMNS FROM $table2 WHERE Field NOT IN ('id')";

        $query = $GaryDB->query($sql);

        while ($row = $query->fetch()) {
            $list .= "<option value='$row[0]'>" . $row[0] . "</option>";
        }
}
        if(isset
        ($_POST['col'])){
        $col = $_POST['col'];

        $selsql = "SELECT $col FROM $table2";

        $colquery = $GaryDB->query($selsql);

        while ($row2 = $colquery->fetch()) {
            $list2 .= "<tr><td>" . $row2[0] . "</td></tr>";
        }
        } else {
            $list2 = "No column on table";
        }
?>
<html>
<head>
<title>Gary Taylor's demonstration PHP Page</title>
</head>
<body>
<p>Welcome to Gary Taylor's demonstration in PHP page.</p>
<br />
<form action="demonstration.php" method="post">
    <table>
        <tr><td><?php echo $selsql ?></td></tr>
        <tr><td>Please select table: <select name="table">
                    <?php echo $table ?>
                </select></td><td><input type="submit" name="submit2" value="selected"></td></tr>
    </table>

</form>
<form action="demonstration.php" method="post">
<table>
    <tr><td colspan="2">Sample of SELECT in column's name in table.</td></tr>
    <tr><td>SELECT <select name="col">
                    <?php echo $list ?>
            </select> FROM <?php echo $table2 ?></td><td><input type="submit" name="submit" value="selected"></td></tr>
</table>
</form>
    <table>
        <?php echo $list2 ?>
    </table>
</body>
</html>

[/php]

Thank you in advance times.

Link to comment
Share on other sites
Sigmahokies Newbie

Sigmahokies

Posted
  • Author
Posted

Niche,

I'm using PDO, not mysqli. I just set up the connection, it works perfectly, but I'm struggle with correct SQL syntax. like this:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

Link to comment
Share on other sites
niche Devoted Member

niche

Posted
Posted (edited)

Which query query is blowing up?

BTW, I use try/catch to do my PDO error reporting. That link was the quickest w3 link I could find as a error handling example.

Edited by niche
Link to comment
Share on other sites
Sigmahokies Newbie

Sigmahokies

Posted
  • Author
Posted (edited)

I think I know where it goes. I think second post didn't take from table name because it just took one name from second select, I added array into two

Like this: <select name="col[]"> then $_POST['col'][0] && $_POST['col'][1]; is that incorrect?

I mean, php put column name in, but it didn't put table name in SQL syntax

Edited by Sigmahokies
Link to comment
Share on other sites
niche Devoted Member

niche

Posted
Posted

If it works, it's correct.

If it doesn't, please post the relevant actual HTML that's produced, emphasis on relevant. 

Link to comment
Share on other sites
Sigmahokies Newbie

Sigmahokies

Posted
  • Author
Posted (edited)

Here you go

<?php

include("connection.php");

if(isset($mysqli_error)) {
    echo $mysqli_error;
}

$sqltable = "SHOW TABLES";

$querytable = $GaryDB->query($sqltable);

while($row3 = $querytable->fetch()) {
    $table .= "<option value='$row3[0]'>" . $row3[0] . "</option>";
}
    if(isset($_POST['table'])) {
        $table2 = $_POST['table'];


        $sql = "SHOW COLUMNS FROM $table2 WHERE Field NOT IN ('id')";

        $query = $GaryDB->query($sql);

        while ($row = $query->fetch()) {
            $list .= "<option value='$row[0]'>" . $row[0] . "</option>";
        }
    }
        if (isset
        ($_POST['col'])) {
            $col = $_POST['col'][0];
            $table3 = $_POST['col'][1];

            $selsql = "SELECT $col FROM $table3";

            $colquery = $GaryDB->query($selsql);

            while ($row2 = $colquery->fetch()) {
                $list2 .= "<tr><td>" . $row2[0] . "</td></tr>";
            }

        } else {
            $list2 = "No column on table";
        }

?>
<html>
<head>
<title>Gary Taylor's demonstration PHP Page</title>
</head>
<body>
<p>Welcome to Gary Taylor's demonstration in PHP page.</p>
<br />
<form action="demonstration.php" method="post">
    <table>
        <tr><td><?php echo $selsql ?></td></tr>
        <tr><td>Please select table: <select name="table">
                    <?php echo $table ?>
                </select></td><td><input type="submit" value="selected"></td></tr>
    </table>

</form>
<form action="demonstration.php" method="post">
<table>
    <tr><td colspan="2">Sample of SELECT in column's name in table.</td></tr>
    <tr><td>SELECT <select name="col[]">
                    <?php echo $list ?>
            </select> FROM <?php echo $table2 ?></td><td><input type="submit" value="selected"></td></tr>
</table>
</form>
    <table>
        <?php echo $list2 ?>
    </table>
</body>
</html>

Edited by Sigmahokies
Link to comment
Share on other sites
niche Devoted Member

niche

Posted
Posted (edited)

That's the code. If it's not working, show the the least amount of html, that shows the actual available inputs, that go into the sql.

EDIT:

Or, the resulting $_POST array.

 

Edited by niche
Link to comment
Share on other sites
Sigmahokies Newbie

Sigmahokies

Posted
  • Author
Posted

I was right, html shows table name didn't be post. how can I post table name? I used array to take two name to post, but it didn't work. Like this:

select name="col[]",

then,

$col = $_POST['col][0];

$table3 = $_POST['col'][1];

but it didn't post from col 1

Link to comment
Share on other sites
niche Devoted Member

niche

Posted
Posted (edited)

I suppose you do it with  

$sqltable = "SHOW TABLES";

I've never used it, but a quick google says it should work.

Then, all you need to do is get the results into your flow.

Voila!

EDIT: Let me know if you need help with that.

Edited by niche
Link to comment
Share on other sites
Sigmahokies Newbie

Sigmahokies

Posted
  • Author
Posted

I did that, show tables is work. it's what not work is name in html did not take two array to post, to send to SQL syntax. It took one name from, not two. 

my echo shows :

 

Welcome to Gary Taylor's demonstration in PHP page.

 

SELECT FirstName FROM
Please select table:                      Memberscityuser_details                
Sample of SELECT in column's name in table.
SELECT                                   FROM

SELECT FirstName FROM

Look at "Select FirstName FROM" that's where table name is missing

Link to comment
Share on other sites
niche Devoted Member

niche

Posted
Posted

This produced a list of my tables: 

		try {	
			$sql = '
				SHOW TABLES
			';
			$stmt0 = $db0->prepare($sql);
		} catch (PDOException $e) {
			echo $e->getMessage() . '<br>';			
		}

		try {	
			$stmt0->execute();
		} catch (PDOException $e) {
			echo $e->getMessage() . '<br>';
		}		
		
		$tables =  $stmt0->fetchall(PDO::FETCH_ASSOC);
		var_dump($tables);

Get $tables into your flow.

Link to comment
Share on other sites
niche Devoted Member

niche

Posted
Posted (edited)

I think this is the problem: 

  $sql = "SHOW COLUMNS FROM $table2 WHERE Field NOT IN ('id')";

EDIT:

This is the solution: 

$sql = "SHOW COLUMNS FROM ' . $table2 . ' WHERE Field NOT IN ('id')";

Remember, $sql is a string!

Edited by niche
Link to comment
Share on other sites
Sigmahokies Newbie

Sigmahokies

Posted
  • Author
Posted

I said show tables work fine. my point is inside html, name in select didn't take TWO names. I set up array to take two. Like this:

in HTML - <select name="col[]">

in PHP - $col = $_POST['col'][0];

               $table3 = $_POST['col'][1];

so, it didn't take name col to second array to post, get it?

 

Link to comment
Share on other sites
Sigmahokies Newbie

Sigmahokies

Posted
  • Author
Posted

In HTML

<table>
    <tr><td colspan="2">Sample of SELECT in column's name in table.</td></tr>
    <tr><td>SELECT <select name="col[]">
                    <?php echo $list ?>
            </select> FROM <?php echo $table2 ?></td><td><input type="submit" value="selected"></td></tr>
</table>

in PHP

if (isset
        ($_POST['col'])) {
            $col = $_POST['col'][0];
            $table3 = $_POST['col'][1];

            $selsql = "SELECT $col FROM $table3";

            /*$colquery = $GaryDB->query($selsql);

            while ($row2 = $colquery->fetch()) {
                $list2 .= "<tr><td>" . $row2[0] . "</td></tr>";
            }*/

output: SELECT Name FROM (blank)

Link to comment
Share on other sites
niche Devoted Member

niche

Posted
Posted (edited)

Then something going wrong in the if.

EDIT:

What's $_POST look like?

Edited by niche
Link to comment
Share on other sites
dsonesuk w3schools Guru

dsonesuk

Posted
Posted (edited)

If its not a multiple select it should be

<select name="col[]">

<option value=" tablename, columnname"> blah </option>

explode the values from post using ',' as the delimiter into a variable  as array, then use $postarraytest[0] and $postarray[1];

Its not like a checkbox where you have multiple values sent depending on individual checkboxes checked, you are sending multiple values on a single selection.

Edited by dsonesuk
Link to comment
Share on other sites
Sigmahokies Newbie

Sigmahokies

Posted
  • Author
Posted

Hi dsonesuk,

I tried that, it won't work. I used session to save first call name from html to post, so i can use session string in second block in php to insert string in SQL syntax. I know it's not properly to do that, do you know how to save string for second block in php?

It's about user pick table, then pick column in table that user picked. Like this:

User pick name of table, then move this string word to SQL syntax, then user pick name of column, then move this strong to SQL syntax. Like - SELECT (picked name of column by user) FROM (picked name of table by user). 

Plus, how can you post as code form in this thread? I mean, screen of gray and courier font on display

Thank you

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
Go to topic listing
×
×
  • Create New...