error when the link is clicked for detail page

Report · Oct 16, 2012

Hello I am creating pages using MySql and PHP with dream weaver. I created my master record set and detail page. the master record set works b but when you click the link to bring up the details page I get this error

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name = 'Brandon' LIMIT 0, 10' at line 1"

Any help with guidance on trouble shooting error would be greatly appreciated.

David

Report · Oct 17, 2012

Please include the entire code for your page.

Report · Oct 18, 2012

=====================================================Master page=============================================================

<?php require_once('Connections/Onantional.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_Recordset1 = 10;

$pageNum_Recordset1 = 0;

if (isset($_GET['pageNum_Recordset1'])) {

$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];

}

$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

mysql_select_db($database_Onantional, $Onantional);

$query_Recordset1 = "SELECT * FROM Roster";

$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);

$Recordset1 = mysql_query($query_limit_Recordset1, $Onantional) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {

$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];

} else {

$all_Recordset1 = mysql_query($query_Recordset1);

$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);

}

$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

$queryString_Recordset1 = "";

if (!empty($_SERVER['QUERY_STRING'])) {

$params = explode("&", $_SERVER['QUERY_STRING']);

$newParams = array();

foreach ($params as $param) {

if (stristr($param, "pageNum_Recordset1") == false &&

stristr($param, "totalRows_Recordset1") == false) {

array_push($newParams, $param);

}

if (count($newParams) != 0) {

$queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));

}

$queryString_Recordset1 = sprintf("&totalRows_Recordset1=%d%s", $totalRows_Recordset1, $queryString_Recordset1);

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

</head>

<body>

<tr>

<td>First name</td>

<td>Conference</td>

<td>email</td>

</tr>

<?php do { ?>

<tr>

</tr>

<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

</table>

<br />

<tr>

<td><?php if ($pageNum_Recordset1 > 0) { // Show if not first page ?>

<a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, 0, $queryString_Recordset1); ?>">First</a>

<?php } // Show if not first page ?>

</td>

<td><?php if ($pageNum_Recordset1 > 0) { // Show if not first page ?>

<a href="<?php printf("%s?pageNum_Recordset1=%d%s", $currentPage, max(0, $pageNum_Recordset1 - 1), $queryString_Recordset1); ?>">Previous</a>

<?php } // Show if not first page ?>

</td>

<?php } // Show if not last page ?>

</td>

<?php } // Show if not last page ?>

</td>

</tr>

</table>

Records <?php echo ($startRow_Recordset1 + 1) ?> to <?php echo min($startRow_Recordset1 + $maxRows_Recordset1, $totalRows_Recordset1) ?> of <?php echo $totalRows_Recordset1 ?>

</body>

</html>

<?php

mysql_free_result($Recordset1);

?>

=====================================================end master ========================================================

========================================================details page=========================================================

<?php require_once('Connections/Onantional.php'); ?><?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

$maxRows_DetailRS1 = 10;

$pageNum_DetailRS1 = 0;

if (isset($_GET['pageNum_DetailRS1'])) {

$pageNum_DetailRS1 = $_GET['pageNum_DetailRS1'];

}

$startRow_DetailRS1 = $pageNum_DetailRS1 * $maxRows_DetailRS1;

$colname_DetailRS1 = "-1";

if (isset($_GET['recordID'])) {

$colname_DetailRS1 = $_GET['recordID'];

}

mysql_select_db($database_Onantional, $Onantional);

$query_DetailRS1 = sprintf("SELECT * FROM Roster WHERE First name = %s", GetSQLValueString($colname_DetailRS1, "text"));

$query_limit_DetailRS1 = sprintf("%s LIMIT %d, %d", $query_DetailRS1, $startRow_DetailRS1, $maxRows_DetailRS1);

$DetailRS1 = mysql_query($query_limit_DetailRS1, $Onantional) or die(mysql_error());

$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);

if (isset($_GET['totalRows_DetailRS1'])) {

$totalRows_DetailRS1 = $_GET['totalRows_DetailRS1'];

} else {

$all_DetailRS1 = mysql_query($query_DetailRS1);

$totalRows_DetailRS1 = mysql_num_rows($all_DetailRS1);

}

$totalPages_DetailRS1 = ceil($totalRows_DetailRS1/$maxRows_DetailRS1)-1;

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

</head>

<body>

<tr>

<td>First name</td>

</tr>

<tr>

</tr>

<tr>

<td>Conference</td>

</tr>

<tr>

<td>email</td>

</tr>

</table>

</body>

</html><?php

mysql_free_result($DetailRS1);

?>

Report · Oct 18, 2012

>SELECT * FROM Roster WHERE First name = %s

In order to use columns that contains spaces in the name, you must quote them in your code, which is why you are getting the error.

Although MySQL may allow spaces in column names, it's a very bad practice. Many DBMS simply don't allow this.

Do yourself a big favor and rename those columns. You can do FirstName, firstName, first_name, fname, etc. Just be consistent with your naming conventions.

Report · Oct 18, 2012

Thanks! I will make changes and post the results!