40ozOE Posted July 9, 2008 Share Posted July 9, 2008 I've been using Dreamweaver CS3 to produce PHP code, so I'm a little stumped a what may be an obvious question. I'm trying to display a particular position no matter how high the primary key goes. For example, I always want to display the entry in the 3rd spot (from the top). This code gives me the function I'm looking for: "SELECT row FROM table WHERE rowID = 103 - 3" From this code I get a result of row=100, which is the 3rd spot from the top. Entries are always being added to the database, so the primary key (rowID) will always be increasing. What is the code to say, "Find the maximum value of the primary key in the table"? Quote Link to comment Share on other sites More sharing options...
l0ve2hat3 Posted July 9, 2008 Share Posted July 9, 2008 SELECT $row FROM $table ORDER BY row DESC LIMIT 3 Quote Link to comment Share on other sites More sharing options...
l0ve2hat3 Posted July 9, 2008 Share Posted July 9, 2008 oops thats wrong. i misunderstood. Quote Link to comment Share on other sites More sharing options...
l0ve2hat3 Posted July 9, 2008 Share Posted July 9, 2008 SELECT MAX(`rowID`-3) FROM `$table` Quote Link to comment Share on other sites More sharing options...
40ozOE Posted July 9, 2008 Author Share Posted July 9, 2008 SELECT MAX(`rowID`-3) FROM `$table` Yes. Outstanding! That's a different structure than I was working with - how do I add other columns to the recordset? I added some (columnA, columnB) with SELECT and FROM, but Dreamweaver kicked back an error that says a GROUP BY command is necessary. Is that possible in this format? Quote Link to comment Share on other sites More sharing options...
l0ve2hat3 Posted July 9, 2008 Share Posted July 9, 2008 SELECT * FROM `$table` WHERE `rowID`=( SELECT MAX(`rowID`) FROM `$table`)-3 Quote Link to comment Share on other sites More sharing options...
40ozOE Posted July 9, 2008 Author Share Posted July 9, 2008 SELECT * FROM `$table` WHERE `rowID`=( SELECT MAX(`rowID`) FROM `$table`)-3 That's strange... Each half of the equations works, but not combined. SELECT * FROM `$table` WHERE `rowID`= x produces a result and ( SELECT MAX(`rowID`-3) FROM `$table`) produces a result (I did have to move the -3 inside the parentheses to get it to work), but the two of them together in one equation results in a syntax error. Quote Link to comment Share on other sites More sharing options...
l0ve2hat3 Posted July 9, 2008 Share Posted July 9, 2008 hrmmm strange... i tested the code in phpmyadmin and got results... maybe someone else knows more??? anyone?? Quote Link to comment Share on other sites More sharing options...
l0ve2hat3 Posted July 9, 2008 Share Posted July 9, 2008 post some of your code Quote Link to comment Share on other sites More sharing options...
40ozOE Posted July 9, 2008 Author Share Posted July 9, 2008 post some of your code Here's the code from a new, blank page. Your suggested code is highlighted and tagged with "102" as an arbitrary entry. Adding the second half of the code produces one result, but combining the two results in "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 'SELECT MAX(news.newsID) FROM news' at line 1" All the code was generated automatically by Dreamweaver CS3. <?php require_once('../Connections/xxxxxxxxxxxxxxx.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; } } mysql_select_db($xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx); $query_Recordset1 = "SELECT * FROM news WHERE news.newsID=102"; $Recordset1 = mysql_query($query_Recordset1, $xxxxxxxxxxxx) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <p><?php echo $row_Recordset1['newsID']; ?> - <?php echo $row_Recordset1['newsDATE']; ?> - <?php echo $row_Recordset1['newsTITLE']; ?></p> <p><?php echo $row_Recordset1['newsAUTHOR']; ?> - <?php echo $row_Recordset1['newsCAPTION']; ?></p> <p><img src="<?php echo $row_Recordset1['newsPHOTO']; ?>" /></p> </body> </html> <?php mysql_free_result($Recordset1); ?> Quote Link to comment Share on other sites More sharing options...
l0ve2hat3 Posted July 9, 2008 Share Posted July 9, 2008 $query_Recordset1 = "SELECT * FROM `news` WHERE `newsID`= (SELECT MAX(`newsID`-3) FROM `news`)"; Quote Link to comment Share on other sites More sharing options...
40ozOE Posted July 9, 2008 Author Share Posted July 9, 2008 $query_Recordset1 = "SELECT * FROM `news` WHERE `newsID`= (SELECT MAX(`newsID`-3) FROM `news`)"; No, that's still producing a syntax error. Hmm. The above example with the "102" entry was to show a successful page, that's why only half of the equation was in there - Dreamweaver won't even complete the recordset with the full parameters. Like I said, each half works, but not the two combined. Quote Link to comment Share on other sites More sharing options...
l0ve2hat3 Posted July 10, 2008 Share Posted July 10, 2008 well then you can always use them seperatly then $res=mysql_query("SELECT MAX(`newsID`-3) FROM `news`") or die(mysql_error()); $third_to_last=mysql_result($res,0); $query_Recordset1 = "SELECT * FROM `news` WHERE `newsID`='$third_to_last' "; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.