Solved

MySQL Query

Posted on 2010-08-25
19
214 Views
Last Modified: 2013-12-13
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.
0
Comment
Question by:QRFOA
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 2

Expert Comment

by:arch-itect
ID: 33527278
Why don't you rather use a

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

this could be very problematic :

SIZE="40"  MAXLENGTH="100"
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33527286
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)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33527353
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>
0
 
LVL 1

Expert Comment

by:dazza98
ID: 33527625
MySQL queries have characters which you sometimes need to escape.

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

Author Comment

by:QRFOA
ID: 33527822
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
0
 
LVL 2

Expert Comment

by:arch-itect
ID: 33528830
LastName is most likely a varchar so the query should be :
select * from Person where LastName = 'Arnold';

Open in new window

0
 
LVL 2

Expert Comment

by:arch-itect
ID: 33528956
dazza98's comment still applies.
0
 

Author Comment

by:QRFOA
ID: 33531878
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.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33531965
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'
 
0
Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:QRFOA
ID: 33532347
I've tried it --- doesn't work for me.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33532435
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 ")
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33532475
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)
0
 

Author Comment

by:QRFOA
ID: 33532561
Here is the window when using quotes around Arnold.
DoesntWork2.tiff
0
 
LVL 2

Expert Comment

by:arch-itect
ID: 33533125
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

0
 
LVL 2

Expert Comment

by:arch-itect
ID: 33533150
Just change the database name to your database's name; and username and password.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33533239
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
0
 
LVL 2

Expert Comment

by:arch-itect
ID: 33533399
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

0
 
LVL 2

Accepted Solution

by:
arch-itect earned 500 total points
ID: 33533681
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

0
 

Author Closing Comment

by:QRFOA
ID: 33534806
Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now