Experts Exchange connects you with the people and services you need so you can get back to work.

MySQL Query

I have set up a html form:
<FORM ACTION="http:// -----------" METHOD="post">
<INPUT TYPE='text" NAME="quest" SIZE="40" MAXLENGTH="100" VALUE="" />
etc.
and a php file that contains
$query = $_POST['quest'];

The idea is that an SQL Query is typed in as text and then that is passed to the php file which executes the query. It works sometimes, but doesn't work most of the time --- evidently that $_POST doesn't capture the query just as typed. (The php program works fine if I type the query in.

This is my first question to Experts-Exchange.

QRFOAAsked: 2010-08-25

Who is Participating?

Solutions

Advertise Here

LVL 2

arch-itectConnect With a Mentor Commented: 2010-08-26

You have to add :

foreach($_POST as $name => $value){
if(get_magic_quotes_gpc()==1){
$_POST[$name] = stripslashes($value);
}else{
}
}

at the beginning of the php code, so finally :

(this file has to be named index.php because the post method posts to itself, or change the form action to the file)

<html>
<head>
      <title>Premiere Products</title>
</head>
<body>
  <h1>Arnold-Jackson-Silacci-Nixon Genealogy</h1>
  <form action="index.php" method="post">
    <p>
      DB Server : <input type="text" name="host" value="localhost"></input><br/>
      Database Name: <input type="text" name="database" value="mysql"></input><br/>
      DB User : <input type="text" name="user" value="root"></input><br/>
      DB Password : <input type="text" name="password" value=""></input><br/>
      Query:
      <textarea NAME="quest" ROWS=4 COLS=40></textarea><BR />
    </p>
    <input TYPE="submit" NAME="submit" VALUE="Go!"/>
  </form>

<?php
  foreach($_POST as $name => $value){
   if(get_magic_quotes_gpc()==1){
    $_POST[$name] = stripslashes($value);
   }else{
   }
  }

  $linkID = 0;                      // Result of mysql_connect() 

  function connect() { 
    $host = $_POST['host'];           // Hostname of our MySQL server 
    $database = $_POST['database'];         // Logical database name on that server 
    $user = $_POST['user'];           // Database user 
    $password = $_POST['password'];   // Database user's password 
    
    echo "Host : $host <br/>";
    echo "Database : $database <br/>";
    echo "Username : $user <br/>";
    echo "Password : $password <br/>";
  
    $lID = mysql_connect($host, $user, $password); 
    if (!$lID) { 
      echo("connect failed"); 
    } 
    $selectResult = mysql_select_db($database, $lID); 
    if(!$selectResult) { 
        $errno = mysql_errno($lID); 
        $error = mysql_error($lID); 
        echo("cannot select database <I>".$database."</I>"); 
    } 
    return $lID;
  } 

  $query = $_POST['quest'];
  
  if (strlen($query) > 0) {
    $linkID = connect(); 
    echo "linkID : $linkID<br/>";

   //$query = mysql_real_escape_string($query, $linkID);
  
    echo "Query : $query <br/>";


    $result = mysql_query($query, $linkID);

    echo "<table border='2' cellspacing='0' cellpadding='6'>";

    if (mysql_num_rows($result)>0) { 
      echo "<tr>";
      //loop thru the field names to print the correct headers 
      $i = 0; 
      while ($i < mysql_num_fields($result)) { 
        echo "<th>". mysql_field_name($result, $i) . "</th>"; 
        $i++; 
      } 
      echo "</tr>"; 
    }

    if($result) {
      while($row = mysql_fetch_array($result,MYSQL_BOTH)) {
        echo "<tr>";
        //loop thru the fields
        $i = 0;
        while ($i < mysql_num_fields($result)) {
          echo "<td>".$row[$i]."</td>";
          $i++;
        }
        echo "</tr>";
      }
    }
   }
   
?>

</body>
</html>

Open in new window

LVL 2

arch-itectCommented: 2010-08-25

Why don't you rather use a

<textarea name="quest"></textarea>

this could be very problematic :

SIZE="40" MAXLENGTH="100"

LVL 61

HainKurtSr. System AnalystCommented: 2010-08-25

please post samples of query that works and dont work...
also please post the original (what you enter) and what you get from php (using echo statement, from the page source)

LVL 61

HainKurtSr. System AnalystCommented: 2010-08-25

please post sample values after modifying your input value like arch suggested

<INPUT TYPE="text" NAME="quest" SIZE="40" MAXLENGTH="100" VALUE="" />
-->
<textarea name="quest" cols=40 rows=4></textarea>

LVL 1

dazza98Commented: 2010-08-25

MySQL queries have characters which you sometimes need to escape.

try using $query = mysql_escape_string($_POST['quest']);

QRFOAAuthor Commented: 2010-08-25

Here is the HTML page:
<html>
<head>
<title>AJSN</title>
</head>
<body>
<FORM ACTION="http://www.korski.net/AJSN2.php" METHOD="post">
<p>
Query:
<TEXTAREA NAME="quest" ROWS=4 COLS=40></TEXTAREA><BR>
</p>
<input TYPE="submit" NAME="submit" VALUE="Go!"/>
</form>
</body>
</html>

Here is the important part of the php file:
$query = mysql_escape_string($_POST['quest']);

echo $query;

$result = mysql_query($query);

echo "<html><head><title>Premiere Products</title></head><body>";
echo "<h1>Arnold-Jackson-Silacci-Nixon Genealogy</h1>";
echo "<table border='2' cellspacing='0' cellpadding='6'>";

if (mysql_num_rows($result)>0)
{
echo "<tr>";
//loop thru the field names to print the correct headers
$i = 0;
while ($i < mysql_num_fields($result))
{
echo "<th>". mysql_field_name($result, $i) . "</th>";
$i++;
}
echo "</tr>";
}

if($result)
{
while($row = mysql_fetch_array($result,MYSQL_BOTH))
{

echo "<tr>";
//loop thru the fields
$i = 0;
while ($i < mysql_num_fields($result))
{echo "<td>".$row[$i]."</td>";
$i++;
}
echo "</tr>";
}
}
echo "</table></body></html>";
?>

Also, I loaded 2 images --- one working and one not working
AJSN-Worked.tiff
AJSN-DoesntWork.tiff

LVL 2

arch-itectCommented: 2010-08-26

LastName is most likely a varchar so the query should be :

select * from Person where LastName = 'Arnold';

Open in new window

LVL 2

arch-itectCommented: 2010-08-26

dazza98's comment still applies.

QRFOAAuthor Commented: 2010-08-26

I've tried all of the suggestions so far, and nothing works for me. It must have something to do with arch-itect's comment, but I had tried the quotes many times --- both with and without dazza98's comments.
ALL of the fields are CHAR fields (including the ID field), so I don't understand why it works with the ID field (which is the primary key) but will not work with any of the other fields.

LVL 61

HainKurtSr. System AnalystCommented: 2010-08-26

select * from Person where id=0000002

may work and interpreted as

select * from Person where id=2

but

select * from Person where LastName=Arnold

does not work, you should use

select * from Person where LastName='Arnold'

QRFOAAuthor Commented: 2010-08-26

I've tried it --- doesn't work for me.

LVL 61

HainKurtSr. System AnalystCommented: 2010-08-26

try

select * from Person where LastName=''Arnold''

or

select * from Person where LastName="Arnold"

(copy paste these queries and run, one is with double ', the other is single ")

LVL 61

HainKurtSr. System AnalystCommented: 2010-08-26

when you enter

select * from Person where LastName='Arnold'

you said it does not work, but what the you get on page (screen you posted @ 33527822)

QRFOAAuthor Commented: 2010-08-26

Here is the window when using quotes around Arnold.
DoesntWork2.tiff

LVL 2

arch-itectCommented: 2010-08-26

Here you go:

<html>
<head>
      <title>Premiere Products</title>
</head>
<body>
  <h1>Arnold-Jackson-Silacci-Nixon Genealogy</h1>
  <form action="index.php" method="post">
    <p>
      DB Server : <input type="text" name="host" value="localhost"></input><br/>
      Database Name: <input type="text" name="database" value="mysql"></input><br/>
      DB User : <input type="text" name="user" value="root"></input><br/>
      DB Password : <input type="text" name="password"></input><br/>
      Query:
      <textarea NAME="quest" ROWS=4 COLS=40></textarea><BR />
    </p>
    <input TYPE="submit" NAME="submit" VALUE="Go!"/>
  </form>

<?php
  $linkID = 0;                      // Result of mysql_connect() 

  function connect() { 
    $host = $_POST['host'];           // Hostname of our MySQL server 
    $database = $_POST['database'];         // Logical database name on that server 
    $user = $_POST['user'];           // Database user 
    $password = $_POST['password'];   // Database user's password 
    
    echo "Host : $host <br/>";
    echo "Database : $database <br/>";
    echo "Username : $user <br/>";
    echo "Password : $password <br/>";
  
    $lID = mysql_connect($host, $user, $password); 
    if (!$lID) { 
      echo("connect failed"); 
    } 
    $selectResult = mysql_select_db($database, $lID); 
    if(!$selectResult) { 
        $errno = mysql_errno($lID); 
        $error = mysql_error($lID); 
        echo("cannot select database <I>".$database."</I>"); 
    } 
    return $lID;
  } 

  //Here is the important part of the php file:
  $query = mysql_escape_string($_POST['quest']);

  if (strlen($query) > 0) {
    $linkID = connect(); 
    echo "linkID : $linkID<br/>";
    echo "Query : $query <br/>";


    $result = mysql_query($query, $linkID);

    echo "<table border='2' cellspacing='0' cellpadding='6'>";

    if (mysql_num_rows($result)>0) { 
      echo "<tr>";
      //loop thru the field names to print the correct headers 
      $i = 0; 
      while ($i < mysql_num_fields($result)) { 
        echo "<th>". mysql_field_name($result, $i) . "</th>"; 
        $i++; 
      } 
      echo "</tr>"; 
    }

    if($result) {
      while($row = mysql_fetch_array($result,MYSQL_BOTH)) {
        echo "<tr>";
        //loop thru the fields
        $i = 0;
        while ($i < mysql_num_fields($result)) {
          echo "<td>".$row[$i]."</td>";
          $i++;
        }
        echo "</tr>";
      }
    }
   }
   
?>

</body>
</html>

Open in new window

LVL 2

arch-itectCommented: 2010-08-26

Just change the database name to your database's name; and username and password.

LVL 61

HainKurtSr. System AnalystCommented: 2010-08-26

arch-itect, what is your suggestion to fix the problem(?)

where is \\\ coming from?
comment out this line

$query = mysql_escape_string($_POST['quest']);

dont use mysql_escape_string

LVL 2

arch-itectCommented: 2010-08-26

Sorry, didn't test with quotes.

The textarea is called "quest" so:

<html>
<head>
      <title>Premiere Products</title>
</head>
<body>
  <h1>Arnold-Jackson-Silacci-Nixon Genealogy</h1>
  <form action="index.php" method="post">
    <p>
      DB Server : <input type="text" name="host" value="localhost"></input><br/>
      Database Name: <input type="text" name="database" value="mysql"></input><br/>
      DB User : <input type="text" name="user" value="root"></input><br/>
      DB Password : <input type="text" name="password"></input><br/>
      Query:
      <textarea NAME="quest" ROWS=4 COLS=40></textarea><BR />
    </p>
    <input TYPE="submit" NAME="submit" VALUE="Go!"/>
  </form>

<?php
  $linkID = 0;                      // Result of mysql_connect() 

  function connect() { 
    $host = $_POST['host'];           // Hostname of our MySQL server 
    $database = $_POST['database'];         // Logical database name on that server 
    $user = $_POST['user'];           // Database user 
    $password = $_POST['password'];   // Database user's password 
    
    echo "Host : $host <br/>";
    echo "Database : $database <br/>";
    echo "Username : $user <br/>";
    echo "Password : $password <br/>";
  
    $lID = mysql_connect($host, $user, $password); 
    if (!$lID) { 
      echo("connect failed"); 
    } 
    $selectResult = mysql_select_db($database, $lID); 
    if(!$selectResult) { 
        $errno = mysql_errno($lID); 
        $error = mysql_error($lID); 
        echo("cannot select database <I>".$database."</I>"); 
    } 
    return $lID;
  } 

  //Here is the important part of the php file:
  $query = $_POST['quest'];

  if (strlen($query) > 0) {
    $linkID = connect(); 
    echo "linkID : $linkID<br/>";
    echo "Query : $query <br/>";


    $result = mysql_query($query, $linkID);

    echo "<table border='2' cellspacing='0' cellpadding='6'>";

    if (mysql_num_rows($result)>0) { 
      echo "<tr>";
      //loop thru the field names to print the correct headers 
      $i = 0; 
      while ($i < mysql_num_fields($result)) { 
        echo "<th>". mysql_field_name($result, $i) . "</th>"; 
        $i++; 
      } 
      echo "</tr>"; 
    }

    if($result) {
      while($row = mysql_fetch_array($result,MYSQL_BOTH)) {
        echo "<tr>";
        //loop thru the fields
        $i = 0;
        while ($i < mysql_num_fields($result)) {
          echo "<td>".$row[$i]."</td>";
          $i++;
        }
        echo "</tr>";
      }
    }
   }
   
?>

</body>
</html>

Open in new window

QRFOAAuthor Commented: 2010-08-26

Thanks

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Already a member? Login.

All Courses

From novice to tech pro — start learning today.

By:

Ken Mayer

Course of the Month6 days, 5 hours left to enroll

Certification
Monitoring and Operating a Private Cloud with System Center 2012 R2

Premium

Premium members can enroll in this course at no extra cost.
Learn More

273 lessons
By:

Habib Z

Practical Implementation of DMVPN between Offices

Premium

Premium members can enroll in this course at no extra cost.
Learn More

5 lessons
By:

Habib Z

Practical Implementation of How to Filter routes from the Routers

Premium

Premium members can enroll in this course at no extra cost.
Learn More

5 lessons
By:

Thomas Zucker-Scharff

How to Prepare for and Navigate a Ransomware Attack

Premium

Premium members can enroll in this course at no extra cost.
Learn More

36 lessons
By:

Ben Brumm

Introduction to Oracle SQL

Premium

Premium members can enroll in this course at no extra cost.
Learn More

67 lessons
By:

Kumar D

Microsoft SQL Server and Database Concepts

Premium

Premium members can enroll in this course at no extra cost.
Learn More

23 lessons
By:

Habib Z

Learn how to use and implement OSPF in various network designs

Premium

Premium members can enroll in this course at no extra cost.
Learn More

10 lessons
By:

Paul Kent

Programming for You: Introduction to C++

Premium

Premium members can enroll in this course at no extra cost.
Learn More

17 lessons
By:

Kyle Santos

Introduction to GIMP

Premium

Premium members can enroll in this course at no extra cost.
Learn More

3 lessons
By:

Rodney Barnhardt

Migrating and Managing Backup Exec 2014

Premium

Premium members can enroll in this course at no extra cost.
Learn More

3 lessons