How to keep one variable in memory

[pheng56]

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,

 

[SaRiD]

Hi

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

$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:

$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

http://www.apexinternet.co.nz/blog/author/nigel-wells/

 

[pheng56]

Hi Nigel,

Thank you, it works fine.