3 Replies - 450 Views - Last Post: 03 December 2012 - 04:06 PM Rate Topic: -----

#1 Static Hazard  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 25-November 09

Getting values from a MySQL database

Posted 03 December 2012 - 03:17 PM

Hey everyone,

I have a PHP/HTML file that connects to my MySQL database. The HTML part just makes the text area, but the PHP part does all the work. When a user enters a SQL command, it sends it to the database, and returns the value on the screen, just below the text area. It works great for checking and viewing data, such as SELECT * FROM blah and DESCRIBE blah, etc.

However, if the user wants to query the database, I have problems returning the result. Any SQL keywords such as WHERE, AND, GROUP BY, HAVING, ORDER BY, etc don't return anything. I can see that the query was successful because I have a PHP line that counts the rows affected, so I always see how many rows should be returned, but the data doesn't show up.

I think it would be a bit tedious to code in PHP what to do for every single possible SQL keyword. Is there some way I can just grab the returned table and put it on the screen?

Is This A Good Question/Topic? 0
  • +

Replies To: Getting values from a MySQL database

#2 laytonsdad  Icon User is offline

  • Cheese and Sprinkles
  • member icon

Reputation: 451
  • View blog
  • Posts: 1,948
  • Joined: 30-April 10

Re: Getting values from a MySQL database

Posted 03 December 2012 - 03:50 PM

Need to see the code to help.
Was This Post Helpful? 0
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: Getting values from a MySQL database

Posted 03 December 2012 - 03:51 PM

Hey.

Are you talking about just printing the result set into a HTML table? For that you could simply do:
echo "<table>";
while ($row = $result->fetch()) {
    echo "<tr>";
    foreach ($row as $field) {
        echo "<td>" . htmlentities($field, ENT_QUOTES, "UTF-8") . "</td>";
    }
    echo "</tr>";
}
echo "</table>";



If not, please elaborate. Show us your code.
Was This Post Helpful? 1
  • +
  • -

#4 Static Hazard  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 25-November 09

Re: Getting values from a MySQL database

Posted 03 December 2012 - 04:06 PM

Here is my code. As you can see I have instructions to handle each keyword SELECT, DESCRIBE, FROM, and DROP I think.

I would like to avoid doing this for every single keyword. That would take forever. Right now my interface is just being used to view tables, not query them, but that is my next step.

<?php
//DB connection local;

define("HOST","XXXXXXXX");
define("USER","XXXXXXXX");
define("PASSWORD","XXXXXXXX");
define("DATABASE_NAME","XXXXXXXXX");




//opens connection to MySQL server
$dbc = mysql_pconnect(HOST, USER, PASSWORD);

if(!$dbc){
   die('Not connected :' . mysql_error());
   }

//select database
$db_selected = mysql_select_db(DATABASE_NAME, $dbc);
if(!$db_selected){
	die("Not connected : " . mysql_error());
  }
// post the sql
if(isset($_POST['message_body']))
{
 $sql = $_POST['message_body'];
 if (preg_match('/^DROP/i' , trim($sql)))
	$message = "ERROR: MySQL DROP statements are not permitted.";
 else // this is not a drop query;
 {
	$query = mysql_query($sql);
	if($query == true)
	{
		if(preg_match('/^create/i' , trim($sql)))
			$message = " Table Created ";
		else if(preg_match('/^update/i' , trim($sql)))
			$message = " Table Updated - updated rows: ". mysql_affected_rows();
		else if(preg_match('/^delete/i' , trim($sql)))
			$message = " Deleted Rows : ". mysql_affected_rows();
		else if(preg_match('/^insert/i' , trim($sql)))
			$message = " Inserted Rows : ". mysql_affected_rows();
		else if(preg_match('/^select|^describe/i' , trim($sql)))
		{
			$message = " selected Rows : ". mysql_num_rows($query); // selected rows 
			$strpos = strpos(strtolower($sql),'from');  // position of "FROM" in the query
			$sub = substr($sql,$strpos+4);           // sub string after the from and forth;
			$exp = explode(" ",trim($sub));					   // $exp[0]  == table name;	
			$query1 = mysql_query("DESCRIBE {$exp[0]}");
			$arr = array();
			while($result = @mysql_fetch_object($query1))
			{
				if( strpos($sql , '*')  || strpos($sql,$result->Field))
					$arr[] = $result->Field;
			}
			
			$html = "<table border='0'><tr>\n";
			for($i=0;$i<count($arr);$i++)
				$html .= "<td><u>{$arr[$i]}</u></td>\n";
				
			$html .='</tr>';
			
			while($result1 = mysql_fetch_object($query))
			{
				$html .='<tr>';
				foreach($arr as $value)
					$html .= "<td>{$result1->$value}</td>";
				$html .= '</tr>';
			}
		
			
			
			if(preg_match('/^describe/i' , trim($sql)))
			{
				$html = "<table><tr>
							<td>Field</td>
							<td>Type</td>
							<td>Null</td>
							<td>Default</td>
							<td>Extra<td></tr>\n";
				$query2 = mysql_query($sql);
				while($result2 = mysql_fetch_object($query2))
				{
					$html .='<tr>';
					$html .= "<td>{$result2->Field}</td>";
					$html .= "<td>{$result2->Type}</td>";
					$html .= "<td>{$result2->Null}</td>";
					$html .= "<td>{$result2->Default}</td>";
					$html .= "<td>{$result2->Extra}</td>";
					$html .= "</tr>\n";	
				
				}
							
			}
			
			$html .= "</table>\n";
			
		} else if(preg_match('/^show/i' , trim($sql)))
		{
			$html = "";
			$DB = "Tables_in_".DATABASE_NAME;
			while($result = mysql_fetch_object($query))
				$html .= $result->$DB."<BR>";
					 
		}
	}else
		$message= "You have an error in your SQL query (" . mysql_error(). " ) " ;
	
		
 }
}
?><html>
<head>
<title>Database Query Form by Static Hazard</title>
</head>
<body>
<p>message from server:<span style='color:red'> <?php echo (isset($message))?$message:" no messages yet! ";?> </span></p> 
<form action="" method="POST">
      <h3>SQL Entry Form by Static Hazard</h3>
      <p></p>

      Enter your SQL statement here:
      <p></p>

      <textarea rows="10" cols="40" name="message_body"><?php echo (isset($sql))?$sql:"";?></textarea>
      <p></p>
      <input type="submit" name="submit" value="Submit" />
</form>
<p>Result:</p>
<?php echo(isset($html))?$html:"";?>
</body>
</html>


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1