Page 1 of 1

Help with mySQL

Posted: Thu Oct 26, 2006 8:38 am
by akimm
There are no errors specifically, however, I tried to insert a value into a DB, then display those values inserted. I have messed with a few tutorials online, which mostly contributes to the code I have thus far, mind you this is my first stab at mySQL so its very likely I made a common mistake, I just don't recognize it yet. Thanks for the help in advance.

Code: Select all

<?php
$user="#####";
$password="#####";
$host = "mysql.akimm.com";
if(mysql_connect($host,$user, $password)) {
echo "You connected, try some querying";
}	else {
echo "sorry brother brown, your screwed";
}
$database = mysql_select_db("akimm0co")
        or die(MySQL_Error());
if($database) {
	echo "<br>" . "Good job";
	} else {
		echo"<br>" . "its f'ed still";
}

?>
<form action="<?php $_SERVER['PHP_SELF'];?>" method="POST">
<b> Your name </b>
<input type="text" name="person">
<br>
<b> Your email </b>
<input type="text" name="mail">
<input type="submit" value="submit this form" name="submit">
<?php $insert = ("INSERT INTO contacts (first_name, last_name, email)
VALUES ('Glenn', 'Quagmire', 'dailylifez@yahoo.com')");

$display = "SELECT first_name, email FROM contacts";
$username = $_POST['person'];
$email = $_POST['mail'];

if(isSet($_POST)) {
	$insert && $display;
}
?>
</form>

Posted: Thu Oct 26, 2006 8:46 am
by volka
<?php $_SERVER['PHP_SELF'];?>
echo forgotten.
$database = mysql_select_db("akimm0co")
or die(MySQL_Error());
if($database) {
echo "<br>" . "Good job";
} else {
echo"<br>" . "its f'ed still";
}
if $database evaluated to FALSE the or die() statement is executed. If the script continues $database can't be false, no need to check that again.
$insert = ("INSERT INTO contacts (first_name, last_name, email)
VALUES ('Glenn', 'Quagmire', 'dailylifez@yahoo.com')");
No need for surounding parenthesis, just $var="xyz";
first_name, last_name, email? Your form only asks for name and email.
The rest of the script I do not understand (and I doubt you do ;)). Try to explain it.

Posted: Thu Oct 26, 2006 8:53 am
by akimm

Code: Select all

<form action="<?php $_SERVER['PHP_SELF'];?>" method="POST"> 
<b> Your name </b> 
<input type="text" name="person"> 
<br> 
<b> Your email </b> 
<input type="text" name="mail"> 
<input type="submit" value="submit this form" name="submit"> 
<?php 
#naturally insert values into table contacts
$insert = ("INSERT INTO contacts (first_name, last_name, email) 
VALUES ('Glenn', 'Quagmire', 'dailylifez@yahoo.com')"); 
#Display was meant to select 2 columns of my DB and then output them, I think you mentioned I need an echo statement,which makes perfect sense. 
$display = "SELECT first_name, email FROM contacts"; 
$username = $_POST['person']; 
$email = $_POST['mail']; 
##here I attempted to use the $_POST array.  When I wrote this code, I didn't have the INSERT, then the INSERT was(no pun intended) inserted, it had pre-existing values, so I just kept them, so as not to mess up the way the original programmer intended that to be added into the DB
if(isSet($_POST)) { 
        $insert;
echo $display; 
#i just tested echo, for display that doesn't work either 
} 
?> 
</form>

Posted: Thu Oct 26, 2006 9:16 am
by volka
The aray $_POST is always set, therefore isset($_POST) always return true, no need to check that.
What's
$insert;
supposed to do?
echo $display;
That will print the string currently stored in $display and that is SELECT first_name, email FROM contacts. sql statements are just strings to php, they have no special meanings for it. You have to send your statements to the sql server. see http://php.net/mysql_query.

The way you've placed your mysql operations suggest that you might not have grasped how php is working. There's no need to place the insert-code "within" the form element after the input elements. Neither will the browser execute the php code (it's been done server-side before the browser waits for the user input) nor will php wait for the user input and then (by magic) grab the values from the client.

try

Code: Select all

<?php
error_reporting(E_ALL);
ini_set('display_errors', true);

$user="#####";
$password="#####";
$host = "mysql.akimm.com";

$mysql = mysql_connect($host,$user, $password) or die(mysql_error());
mysql_select_db("akimm0co", $mysql) or die(mysql_error());

if ( isset($_POST['firstname'], $_POST['lastname'], $_POST['mailaddress']) ) {
	/*
		you might want to do more checkings here
		apperently valid mail address
		string length, whatever
		
		important: secure parameters against sql injections
		 strings => mysql_real_escape_string
	*/
	$fname = mysql_real_escape_string($_POST['firstname'], $mysql);
	$lname = mysql_real_escape_string($_POST['lastname'], $mysql);
	$mail = mysql_real_escape_string($_POST['mailaddress'], $mysql);
	
	$query = "INSERT INTO
			contacts
			(first_name, last_name, email)
		VALUES
			('$fname', '$lname', '$mail')";
			
	$result = mysql_query($query, $mysql) or die(mysql_error());
	echo '<pre>executed: ', htmlentities($query), '</pre>';
}
?>
<form action="<?php $_SERVER['PHP_SELF'];?>" method="POST">
	<table>
		<tr>
			<th>Your first name</th>
			<td><input type="text" name="firstname" />
		</tr>
		<tr>
			<th>Your last name</th>
			<td><input type="text" name="lastname" />
		</tr>
		<tr>
			<th>Your email address</th>
			<td><input type="text" name="mailaddress" />
		</tr>
		<tr>
			<td colspan="2"><input type="submit" /></td>
		</tr>
	</table>
</form>
<?php
$query = 'SELECT first_name, email FROM contacts';
$result = mysql_query($query, $mysql) or die(mysql_error());

echo '<table border="1"><tr><th>first name</th><th>email address</th></tr>', "\n";
while ( $row=mysql_fetch_array($result, MYSQL_ASSOC) ) {
	echo '<tr><td>', htmlentities($row['first_name']), '</td><td>', htmlentities($row['email']), "</td></tr>\n";
}
echo "<table>\n";
?>
(untested)

Posted: Thu Oct 26, 2006 12:25 pm
by RobertGonzalez
Volka mentioned at one point that you are not echoing out the $_SERVER['PHP_SELF'] var. You still haven't gotten that in there.

Posted: Thu Oct 26, 2006 1:04 pm
by akimm
Thank you for the clarification.

So anytime I insert into DB via SQL I need to escape special chars with mysql_real_escape_string, html with htmlentities() and probably slashes with stripslashes()????

Thanks again, I have a better understanding of the logisitcs of these codes, than I had prior to your help!

Posted: Thu Oct 26, 2006 1:05 pm
by volka
akimm wrote:So anytime I insert into DB via SQL I need to escape special chars with mysql_real_escape_string, html with htmlentities() and probably slashes with stripslashes()????
8O Where did you get that impression?

Posted: Thu Oct 26, 2006 1:23 pm
by akimm
hehe, I guess the wrong place?

I just saw you used it, seeing as you know a lot or so it would seem with your displayed skill. I took what you did as common practices, not variances depending on the sittuation, but now I understand

*I think*

Thanks!

Posted: Thu Oct 26, 2006 1:30 pm
by volka
data->mysql => mysql_real_escape_string()
data->html client => htmlentities()

Sometimes htmlentities() is applied to the data that is stored in the database for performance reasons.
$param = mysql_real_escape_string(htmlentities($yadda));
This way htmlentities() has to run only once and you can display the data without further calls to that function.
But I avoid that if possible. I think it's more important to focus on what is needed when. mysql doesn't care about html entities. It only needs those characters escaped that mysql_real_escape_string handles. So we call mysql_real_escape_string() and don't care about htmlentitites() as well. And another way to look at it: What if you do not want to send te data to a html client?

Posted: Thu Oct 26, 2006 7:01 pm
by akimm
Thanks Volka, I do appreciate the insight you've provided.