April 8, 2012
PHP & MySQL : What's Wrong with Code to Add to a Database & Retrieve
Question by Ed Cox
It’s supposed to let you add a personal link to a database, and the Server is running Windows Server 2008 R2 with MySQL & Active Directory.
<?php
$dbl = mysql_connect('localhost', 'USERNAME', 'PASSWORD') or die('failed to connect to mysql');
mysql_select_db('linksdatabase') or die('failed to select database');
if(isset($_POST['linkaddress']) && isset($_POST['linkname']))
$sql = "INSERT INTO userlinks (username, linkaddress, linkname) VALUES ('%s','%s','%s')";
mysql_query ( sprintf ( $sql, mysql_real_escape_string ( $_SERVER ['AUTH_USER'] ), mysql_real_escape_string ( $_POST ['linkaddress'] ), mysql_real_escape_string ( $_POST ['linkname'] ) ) );
echo '<p>Links:</p><ul>';
$result = mysql_query ( sprintf ( "SELECT linkaddress, linkname FROM userlinks WHERE username = '%s'", mysql_real_escape_string ( $_SERVER ['AUTH_USER'] ) ) );
while($row = mysql_fetch_array($result))
echo '<li><a href="', htmlentities($row['linkaddress']), '">', htmlentities($row['linkname']), '</a></li>';
echo '</ul>';
?>
<form action="" method="post">
<fieldset>
<legend>Add a Link</legend>
Address: <input type="text" name="linkaddress" /><br />
Name: <input type="text" name="linkname" /><br />
<input type="submit" value="Add" />
</fieldset>
</form>
Answer by Baba
You are having some single quote ' issues
Replace
mysql_query('INSERT INTO userlinks (username, linkaddress, linkname) VALUES ('' . mysql_real_escape_string($_SERVER['AUTH_USER']) . '', '' . mysql_real_escape_string($_POST['linkaddress']) . '', '' . mysql_real_escape_string($_POST['linkname']) . ''');
With
$sql = "INSERT INTO userlinks (username, linkaddress, linkname) VALUES ('%s','%s','%s')";
mysql_query ( sprintf ( $sql, mysql_real_escape_string ( $_SERVER ['AUTH_USER'] ), mysql_real_escape_string ( $_POST ['linkaddress'] ), mysql_real_escape_string ( $_POST ['linkname'] ) ) );
Replace
$result = mysql_query('SELECT linkaddress, linkname FROM userlinks WHERE username = '' . mysql_real_escape_string($_SERVER['AUTH_USER']) . ''');
With
$result = mysql_query ( sprintf ( "SELECT linkaddress, linkname FROM userlinks WHERE username = '%s'", mysql_real_escape_string ( $_SERVER ['AUTH_USER'] ) ) );
This code might help you to find the error
error_reporting(E_ALL);
ini_set('display_errors','On');
$dbl = mysql_connect ( 'localhost', 'USERNAME', 'PASSWORD' ) or die ( 'failed to connect to mysql' );
mysql_select_db ( 'linksdatabase' ) or die ( 'failed to select database' );
if (count ( $_POST ) < 1) {
var_dump ( "Nothign was posted" );
} else {
var_dump ( $_POST );
}
if (isset ( $_POST ['linkaddress'] ) && isset ( $_POST ['linkname'] )) {
$sql = "INSERT INTO userlinks (username, linkaddress, linkname) VALUES ('%s','%s','%s')";
mysql_query ( sprintf ( $sql, mysql_real_escape_string ( $_SERVER ['AUTH_USER'] ), mysql_real_escape_string ( $_POST ['linkaddress'] ), mysql_real_escape_string ( $_POST ['linkname'] ) ) );
if (mysql_errno ()) {
var_dump ( "MySQL error " . mysql_errno () . ": " . mysql_error () );
} else {
var_dump ( "OK Insert" );
}
}
echo '<p>Links:</p><ul>';
$result = mysql_query ( sprintf ( "SELECT linkaddress, linkname FROM userlinks WHERE username = '%s'", mysql_real_escape_string ( $_SERVER ['AUTH_USER'] ) ) );
if (mysql_errno ()) {
var_dump ( "MySQL error " . mysql_errno () . ": " . mysql_error () );
} else {
var_dump ( "OK Select" );
}
while ( $row = mysql_fetch_array ( $result ) )
echo '<li><a href="', htmlentities ( $row ['linkaddress'] ), '">', htmlentities ( $row ['linkname'] ), '</a></li>';
echo '</ul>';
Answer by Starx
You are missing a closing bracket ) at the end of your query.
mysql_query('INSERT INTO userlinks (username, linkaddress, linkname) VALUES ('' . mysql_real_escape_string($_SERVER['AUTH_USER']) . '', '' . mysql_real_escape_string($_POST['linkaddress']) . '', '' . mysql_real_escape_string($_POST['linkname']) . '')');
