Insert Into query

Page 1 of 1

3 Replies - 801 Views - Last Post: 12 March 2009 - 03:29 PM Rate Topic: -----

#1 Linz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-November 08

Insert Into query

Post icon  Posted 12 March 2009 - 09:38 AM

Hi, I am having trouble getting my insert into query to work. It doesn't do anything when I type in the insert sql code into the query. I might be missing something?
here is my code for the query_proc sheet


<?/* connect to DB */
function connect_to_db()
{
	if(!mysql_connect("localhost","ise456","153103"))
	{
		echo "<h3>Cannot connect to the database</h3>";
		echo "<h3>Check Login Info</h3>";
		die();
	}
	mysql_select_db("ISE456");
}


/*	Writes out a table from a single result query. */
function make_table($query_result)
{
   $row = mysql_fetch_array($query_result);
   // Make header
   $table = "<table><tr>";
   for($i = 0; $i < mysql_num_fields($query_result); ++$i)
   {
		$table .= "<th BGCOLOR='00CCFF'>". mysql_field_name($query_result,$i). "</th>";
   }
   $table .= "</tr>\n<tr BGCOLOR='009966'>";
   // Make first row
   for($i = 0; $i < mysql_num_fields($query_result); ++$i)
   {
		$table .= "<td>" . $row[mysql_field_name($query_result,$i)]. "</td>";
   }
   $table .= "</tr>\n";
   //Fill rest of the table
   $row_color = "009966";
   while( $row=mysql_fetch_array($query_result))
   {
		($row_color == "009966")? $row_color = "#9900FF" : $row_color = "009966";
		$table .= "<tr BGCOLOR='". $row_color ."'>";
		for($i = 0; $i < mysql_num_fields($query_result); ++$i)
		{
			   $table .= "<td>" . $row[mysql_field_name($query_result,$i)]. "</td>";
		}
		$table .= "</tr>\n";
   }
   $table .= "</tr></table>";
	 return $table;
 }


// make new queries
   $sql="INSERT INTO table (NUM, NAME, STATUS, CITY) VALUES ('$_POST[NUM]','$_POST[NAME]','$_POST[STATUS]','$_POST[CITY]')"; 
	
	if (!mysql_query($sql))
	{
	die();
	}
	echo "Successful Insert!";

?>


here is the code for the queries sheet:

<html>
<body>
<h3> PHP Query Processor </h3>
<form name="input" action="queries.php" method="get">
   <textarea name="query" rows="10" cols="20">Type Query Here</textarea><br>
   <input type="submit" value="Run Query">
</form>
<hr>
<?
   // Load query and remove escape slashes
   $query = stripslashes($_GET["query"]);
   // Check for a null query
   ($query == "" || $query == "Type Query Here" ) ? die() : "";
   include("query_proc.php");
   connect_to_db();
   $result= mysql_query($query)
		  or die("Query ".$query." falied--> error message: " . mysql_error());
   echo "<Table><tr><td name='querytable' valign='top' width=150 bgcolor='33FF99'><FONT COLOR='9966FF'>"
	   . $query . "</color></td><td>" . make_table($result) . "</td></table>";
?>
</body>
</html>


Is This A Good Question/Topic? 0
  • +

Replies To: Insert Into query

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5676
  • View blog
  • Posts: 22,537
  • Joined: 23-August 08

Re: Insert Into query

Posted 12 March 2009 - 10:15 AM 

$sql="INSERT INTO table (NUM, NAME, STATUS, CITY) VALUES ('$_POST[NUM]','$_POST[NAME]','$_POST[STATUS]','$_POST[CITY]')";


Are NUM, NAME, STATUS, and CITY the actual column names? If so, try using backticks, as there may be a reserved word in there. Also, the indexes of arrays are strings, so you might want to take that into account:

$sql="INSERT INTO table (`NUM`, `NAME`, `STATUS`, `CITY`) VALUES ('$_POST["NUM"]','$_POST["NAME"]','$_POST["STATUS"]','$_POST["CITY"]')";


Also, look up SQL injection. Using input direct from users is fraught with danger.
Was This Post Helpful? 0
  • +
  • -

#3 Linz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 10-November 08

Re: Insert Into query

Posted 12 March 2009 - 03:21 PM

Okay so I tried this 


<?/* connect to DB */
function connect_to_db()
{
	if(!mysql_connect("localhost","ise456","153103"))
	{
		echo "<h3>Cannot connect to the database</h3>";
		echo "<h3>Check Login Info</h3>";
		die();
	}
	mysql_select_db("ISE456");
}


/*	Writes out a table from a single result query. */
function make_table($query_result)
{
   $row = mysql_fetch_array($query_result);
   // Make header
   $table = "<table><tr>";
   for($i = 0; $i < mysql_num_fields($query_result); ++$i)
   {
		$table .= "<th BGCOLOR='00CCFF'>". mysql_field_name($query_result,$i). "</th>";
   }
   $table .= "</tr>\n<tr BGCOLOR='009966'>";
   // Make first row
   for($i = 0; $i < mysql_num_fields($query_result); ++$i)
   {
		$table .= "<td>" . $row[mysql_field_name($query_result,$i)]. "</td>";
   }
   $table .= "</tr>\n";
   //Fill rest of the table
   $row_color = "009966";
   while( $row=mysql_fetch_array($query_result))
   {
		($row_color == "009966")? $row_color = "#9900FF" : $row_color = "009966";
		$table .= "<tr BGCOLOR='". $row_color ."'>";
		for($i = 0; $i < mysql_num_fields($query_result); ++$i)
		{
			   $table .= "<td>" . $row[mysql_field_name($query_result,$i)]. "</td>";
		}
		$table .= "</tr>\n";
   }
   $table .= "</tr></table>";
	 return $table;
 }


// make new queries
	 $sql="INSERT INTO $table (`NUM`, `NAME`, `STATUS`, `CITY`) VALUES ('$_POST[NUM]','$_POST[NAME]','$_POST[STATUS]','$_POST[CITY]')"; 
	
	if (!mysql_query($sql))
	{
	die();
	}
	echo "Successful Insert!";



?>


and when I run it, it still does not do anything and will not even let me write a sql select statement.
Am i putting this Insert Into statement in the right order? I tried putting it about the return $table but it came back with errors.
Also, do I need to change $sql to $query_result?
I put the backticks in and it did not change anything.


// make new queries
	 $sql="INSERT INTO $table (`NUM`, `NAME`, `STATUS`, `CITY`) VALUES ('$_POST[NUM]','$_POST[NAME]','$_POST[STATUS]','$_POST[CITY]')"; 
	
	if (!mysql_query($sql))
	{
	die();
	}
	echo "Successful Insert!";

Was This Post Helpful? 0
  • +
  • -

#4 Valek  Icon User is offline

  • The Real Skynet
  • member icon

Reputation: 514
  • View blog
  • Posts: 1,604
  • Joined: 08-November 08

Re: Insert Into query

Posted 12 March 2009 - 03:29 PM

Your array indexes still aren't quoted out to tell PHP they're strings. Use this:

$sql="INSERT INTO $table (`NUM`, `NAME`, `STATUS`, `CITY`) VALUES ('$_POST['NUM']','$_POST['NAME']','$_POST['STATUS']','$_POST['CITY']')";


Also, as JackOfAllTrades has stated, you will want to validate and clean user input. Simply inserting user-input data raw leaves you wide open to SQL injection attacks. Consider mysql_real_escape_string() and htmlentities().

This post has been edited by Valek: 12 March 2009 - 03:29 PM

Was This Post Helpful? 0
  • +
  • -
Page 1 of 1