how to perform sql command on html page with user input field, and show result on the same page

Question

i write a command, or i fill up parameter value from user input field. click the button, send this command to php and send resultant value back to html to display. for example. on html page :

select ___ from ____, 

two available input field i fill up with "tablenameone" and "valueone". then, result will be printed on html text field on the same page.

what i do know is those value can be sent(perhaps) as in such format

$('input[name="talbename"]') 
$('input[name="value"]')
example.com?tablename=tablenameone&value=valueone

and from php side i use

 $sql="SELECT '$_GET['value']' FROM '$_GET['tablename']';

what i dont know is that....how exactly should i perform this in a click function? its for sure using ajax. but how can i produce example.com?tablename=tablenameone&value=valueone and where should i put $('input[name="value"]')

thanks in advance :D

Answer 1

You must not use direct input in your queries as you will be open to SQL injection attacks.

$sql="SELECT '$_GET['value']' FROM '$_GET['tablename']';

Instead, use the following:

$column = $_GET['value'];
$table = $_GET['tablename'];
$sql = sprintf("SELECT %s FROM %s;",
             mysql_real_escape_string($column),
             mysql_real_escape_string($table));

Although you are still exposing too much "inside information" by giving people a page that tells them all of your table and column names!

Anyway, here is a complete example;

<form method="post" action="">
    <fieldset>
        <legend>Select Data</legend>
        <p><label>Table<br>
        <select name="table">
            <option value="tblStudents">Students</option>
        </select></label></p>
        <p><label>Table<br>
        <select name="column">
            <option value="firstname">First Name</option>
            <option value="lastname">Last Name</option>
        </select></label></p>
        <p><input type="submit" name="submit" value="submit">
    </fieldset>
</form>
<?php
$connection = mysql_connect("servername:3306", "user", "password") or die ('Error connecting to mysql');

mysql_select_db("databasename");  

$column = mysql_real_escape_string($_POST['column']);
$table =  mysql_real_escape_string($_POST['table']);
$sql = sprintf("SELECT %s FROM %s;",
        $column,
        $table);

$result = mysql_query($sql) or die(mysql_error());

echo '<ul>';
while($row = mysql_fetch_array($result)) { 
    echo '<li>' . $row[$column] . '</li>';
}
echo '</ul>';

mysql_close($connection); 
?>

Answer 2

Seeming as though noone has actually answered the question (although they are all good points, I will assume there is a reason for you doing this), I will answer:

$('form[name=formname]').submit(function(e){
    e.preventDefault;
    var tablename = $('input[name="tablename"]').val();
    var value = $('input[name="value"]').val();
    $.get("example.php?tablename="+tablename+"&value="+value, function(data){
         $('body div').text(data);
    })
});

PHP:

$sql=mysql_query("SELECT '$_GET['value']' FROM '$_GET['tablename']'")or die(mysql_error());
$sqlOutput = mysql_fetch_array($sql);
echo "<pre>";
print_r($sqlOutput);
echo "</pre>";

Obviously replace formname with your form name, body div with the name of the element you want the output to go in and all other identifiers replaced where seen fit. Then change the output in the PHP to suit your needs.

Again, do bear in mind the posts regarding SQLi, because you have yourself a very serious problem there.

Answer 3

You really want to make sure you are not open to SQL injection.

You could use mysql prepared statements

or

use the php function mysql_real_escape_string($_GET['value'])

Read this thread: How can I prevent SQL injection in PHP?

I'm not sure what you mean by the click function.