User-Defined Query to Display Table of Results

[javinladish]

First off, let me say that this will not be a public use site.  We want to create a site that allows us to run queries in our database; this way we don't need to log into the MySQL server when ever we want to run a query.

 

Basically, I am trying to create something as simple as a form with a text-input and a submit button.  In this text-input, we will write a query (such as SELECT * FROM xxxx).  When we hit submit, we would like to have the MySQL table printed out in a large textbox below.

 

Our database has multiple tables, so something that works across the entire database seamlessly is key.

 

Has anyone ever made anything like this?  Is there another way to go about this?  We really just want an easy way to run queries on the go.

 

Thanks in advance to anyone who replies!

[Psycho]

Seriously? What is the difficulty you face. If you've ever done any queries this should be a piece of cake:

 

//Get query submitted from form
$query = $_POST['user_query'];
//Run query
$result = mysql_query($query);

//Output results
if(!$result)
{
    echo "There was an error running the query: " . mysql_error();
}
elseif(!mysql_num_rows($result))
{
    echo "No results returned";
}
else
{
    $header = false;
    echo "<table border='1'>\n";
    while($row = mysql_fetch_assoc())
    {
        if(!$header)
        {
            echo "<tr>\n";
            foreach($row as $header => $value)
            {
                echo "<th>{$header}</th>\n";
            }
            echo "</tr>\n";
            $header = true;
        }
        echo "<tr>\n";
        foreach($row as $value)
        {
            echo "<th>{$value}</th>\n";
        }
        echo "</tr>\n";
    }
    echo "</table>\n";
}

[javinladish]

I think we are on the right track, but I am still getting some syntax errors.

The code to my form is as follows:

 

<form id="form1" name="form1" method="post" action="">
  <p>
    <label for="user_query">Query:</label>
    <input type="text" name="user_query" id="user_query" />
  </p>
  <p>
    <input type="submit" name="submit" id="submit" value="Submit" />
  </p>
</form>

 

Am I still going about this wrong?

And yes, I am fairly new to php/MySQL.

[xyph]

Seems fine.

[javinladish]

This is the syntax error that is being reported.

 

Notice: Undefined index: user_query in /var/www/test.php on line 11

 

The line of code this error is referring to is:

 

$query = $_POST['user_query'];

[javinladish]

Nevermind about that last error.  I think it might just because nothing is submitted when you first load the page.  However, when I run a query on a table that I know has information in it, I get this error:

 

Warning: mysql_fetch_assoc() expects at least 1 parameter, 0 given in /var/www/test.php on line 27

[xyph]

That's a notice. It's pretty much saying the key doesn't exist. To avoid this, use isset

 

<?php 

if( isset($_POST['user_query']) ) {
$query = $_POST['user_query'];
} else {
$query = '';
}

?>

 

 

Using the ternary operator http://php.net/manual/en/language.operators.comparison.php

 

<?php 

$query = isset($_POST['user_query']) ? $_POST['user_query'] : '';

?>