Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to keep one variable in memory

Status
Not open for further replies.

pheng56

IS-IT--Management
Feb 24, 2013
7
CA
Hi all,

I would like to keep one variable in memory for
my next sql query.

mysql_connect('localhost', 'root', '')
or die("Mysql connection failed");
mysql_select_db("blog")
or die("Can't find database 'blog'");


$sql = "select * from user,article where article.userID=user.ID order by article.ID desc limit 5";
$result = mysql_query($sql);
if (!$result)
die("SQL error: ".$sql." ERROR: ".mysql_error());

while ($row = mysql_fetch_assoc($result)) {
printf("<hr><h3><a href=\"article.php?id=%d\">%s</a></h3><b>author %s, date %s</b><p>%s</p>",$row['ID'],
$row['title'], $row['username'],$row['timestamp'], $row['content']);

}

my next query to get id number id=%d , how to have it ?

$sql = "select a.ID,c.content from article as a inner join comment as c on a.ID = c.articleID

group by a.ID,c.content order by a.ID desc limit 5 where a.ID = ???? ";
$result = mysql_query($sql);
if (!$result)
die("SQL error: ".$sql." ERROR: ".mysql_error());

while ($row = mysql_fetch_assoc($result)) {
//printf($row['ID'],$row['content']);

printf("<hr>%s<p>%s</p>",$row['ID'], $row['content']);
}

please help me, I really appreciated.

Thank you,
 
Hi

Simple answer is to use $_REQUEST['id']:

Code:
$sql = "select a.ID,c.content from article as a inner join comment as c on a.ID = c.articleID

 group by a.ID,c.content order by a.ID desc limit 5 where a.ID = " . $_REQUEST['id'];

However, there are few things that need to be tweaked from a syntax POV and from a security POV.

With SQL statements the "WHERE" needs to come before the "ORDER BY" and in your case it also comes before the "GROUP BY". I would also recommend using uppercase for your SQL syntax to make it easier to read and more distinguishable from your tables/fields.

Finally, for security reasons (and to stop PHP notices), you need to check that "id" is what you're expecting otherwise you will open up your database to SQL injections.

So, based on the above, you could try the following:

Code:
$id = 0;
// Make sure "id" is set and use intval() to makes sure the value is only an integer
if(isset($_REQUEST['id'])) $id = intval($_REQUEST['id']);
// Construct your SQL statement now
$sql = 'SELECT a.ID, c.content FROM article AS a INNER JOIN comment AS c ON a.ID = c.articleID WHERE a.ID = "' . $id . '" GROUP BY a.ID, c.content ORDER BY a.ID DESC LIMIT 5';

Without knowing your database structure you probably don't need the "GROUP BY" in the above SQL either.

Give it a whirl.

Regards
Nigel
 
Hi Nigel,

Thank you, it works fine.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top