[SOLVED] mysql statements

[andrew_biggart]

what kind of statement would i use to do the following/

 

i would like to select the latest say recipe added to the database so how would i tell it to select the last recipe_id ?

 

and also is there a way that i can count the number of users i have in my database and display it as a number?

thanks

[premiso]

$sql = "SELECT * FROM recipes ORDER BY recipe_id DESC"

 

$sql = "SELECT count(userid) as userscnt FROM members";

[Mark Baker]

i would like to select the latest say recipe added to the database so how would i tell it to select the last recipe_id ?

SELECT *
  FROM recipes
ORDER BY recipe_id DESC
LIMIT 1

 

and also is there a way that i can count the number of users i have in my database and display it as a number?

SELECT COUNT(*) AS userCount
  FROM users

[allworknoplay]

For your recipe table, it's probably a good idea to have a timestamp of when it was submitted.

 

this way you can show dates for when people submited or modified recipes.

 

You can order by date, instead of just the last recipe and give you more control over how you want to show recipes....

[andrew_biggart]

is this right then?

 

	<br />
<img alt="Newest recipe" src="../Header_images/newestrecipe.gif" /><br />
<br />
<?php

include("config_recipes.php");

// Retrieve data from database 
$sql="SELECT * FROM User_recipesT ORDER BY Recipe_id LIMIT 1 ";
$result=mysql_query($sql);

// Start looping rows in mysql database.
while($rows=mysql_fetch_array($result)){
?>
<table>
<tr><td class="newmain">
<table>
<tr><td><h1 class="new_up">Uploaded by :</h1></td><td><h1 class="new_sub_user"><a href="profile.php?username=<? echo $rows['Recipe_username']; ?>"><? echo $rows['Recipe_username']; ?></a></h1></td></tr>
</table>
<table>
<tr><td><h1 class="new_sub"><? echo $rows['Recipe_subject']; ?></h1></td></tr>
</table>
<? echo $rows['Recipe_content']; ?>
</td></tr>
</table>
<br />
<?
// close while loop 
}

// close connection 
mysql_close();
?>

[andrew_biggart]

yea i have a timestamp on it hwo would i do the select statement then? cheers

[premiso]

First part, no.

 

$sql="SELECT * FROM User_recipesT ORDER BY Recipe_id DESC LIMIT 1 ";

 

Notice the DESC, which Mark and I added in our post.

 

With a timestamp it would be similar (I cannot remember if the desc is needed for timestamps or not. You will have to test this.)

$sql="SELECT * FROM User_recipesT ORDER BY Recipe_date DESC LIMIT 1 ";

 

Just change the order by column.

[andrew_biggart]

Cheers everyone thats worked