Show ten latest and then show all

[Bman900]

I basically have a table in my database called 'question' with columns called 'name' 'question' 'email'

 

Than I have a table in my PHP page:

 <table width="500" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>Question</td>
    <td>Name</td>
    <td>Email</td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
</table>

 

I want to show the last 10 question in my database along with email and names. Here is the code I have so far which just spits it out:

 

$result = mysql_query("SELECT * FROM question")
or die(mysql_error());
while($row = mysql_fetch_assoc( $result ))
{
    echo "Name: ".$row['name'];
    echo " Question: ".$row['question'];
    echo " Email: ".$row['email'];
} 

 

Than I want to make another page that just spit every row out in a table like the first one but that automatically expands as more entries are added.

 

Where do I start?

 

 

[Maq]

First, let PHP create the table for you in the while loop after you extract your data.

 

I want to show the last 10 question in my database along with email and names. Here is the code I have so far which just spits it out:

 

Something like?

 

SELECT * FROM question ORDER BY [date or id] DESC LIMIT 10

 

Than I want to make another page that just spit every row out in a table like the first one but that automatically expands as more entries are added.

 

Where do I start?

 

Not sure exactly what you mean.  Maybe Pagination?

 

[Bman900]

Alright two questions how do I make PHP create my table and then how would I flip the order if I do

 

 SELECT * FROM question ORDER BY [id] DESC LIMIT 10 

 

I don't want it to do 1-10 but rather 10-1.

[neogemima]

Okay, so it sounds like you are pretty new to php, I would start by looking up basic queries to your database from the web using php in the reference section of the site.  That will give you basic instructions on connecting to your database etc. 

 

What you need to do is query your database to display a certain number of rows.  What I would do in your situation is create two different php pages which query your database for ten rows, then for all of the rows in your table. 

 

I will give you an example of this in php code, but again, you really should read up on the php reference guide to learn what this code means.

 

<?php
//begin recent news doc

@ $db = mysqli_connect('xxxxxxxxxxx', 'xxxxxxxxx', 'xxxxxxxxxx', 'xxxxxxxxxx');

if (mysqli_connect_errno($db)) {
	echo 'Error: Could not connect to database.  Please try again later.';
}

mysqli_select_db($db, 'xxxxxxxxxxxxx');
$result = mysqli_query($db, "SELECT question, email, name FROM Mytable");
$num_rows = mysqli_num_rows($result);

$i = $num_rows;


	$sql = "SELECT question, email, name FROM Mytable ORDER BY id DESC LIMIT 10";
	$queryresult = mysqli_query($db, $sql);


	if ($num_rows > 0) {

		while ($rowresult = mysqli_fetch_array($queryresult, MYSQLI_ASSOC)){
		$id = $rowresult['id'];

		echo "this is where you insert your code for the table.  This code will run over and over again ten times, so for each element you must include your table tags exactly how the browser will see this text.";
		echo "<br>";
	}
	}

?>

 

I will not teach you php over this forum, but this should get you started on what your php of the results page will look like.  You will also need html in here to encapsulate your table and page etc.  Good luck...

[Maq]

how would I flip the order if I do

 

Sub-queries like this aren't supported with earlier versions of MySQL but this should give you an idea.

 

SELECT * FROM question WHERE id IN(SELECT id FROM question ORDER BY id DESC LIMIT 10) ORDER BY id ASC

 

 

Alright two questions how do I make PHP create my table

 

*Not tested

 

This should give you an idea of how to create a dynamic table with the results from the query.

 

You simply add the rows/columns that are dependent on the query inside the while loop:

 

 </pre>
<table width="500" border="0" cellspacing="0" cellpadding="0">
  
    Question //these should be table headers, correct?
    Name
    Email
  
while($row = mysql_fetch_assoc( $result ))
{
    echo "";
    echo "$row['name']";
    echo "$row['question']";
    echo "$row['email']";
    echo "";
} 
?>
  
</

[Bman900]

Okay, so it sounds like you are pretty new to php, I would start by looking up basic queries to your database from the web using php in the reference section of the site.  That will give you basic instructions on connecting to your database etc. 

 

What you need to do is query your database to display a certain number of rows.  What I would do in your situation is create two different php pages which query your database for ten rows, then for all of the rows in your table. 

 

 

 

Thats exactly what I want to do. 

 

Also Maq I see what you are doing but once I run it gives me an error like:

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /homepages/44/d272374679/htdocs/draft/admin/report.php on line 102

 

Don't you have to like put a \ infront of <td> to escape the php for that second?

[Bman900]

Alright I solved it by making the code look like this:

echo "<tr>";
    echo "<td>".$row['name']."</td>";
    echo "<td>".$row['question']."</td>";
    echo "<td>".$row['email']."</td>";
    echo "</tr>";

 

But I get this: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

 

I am running MySQL 5.0

[neogemima]

Hmmm.  I haven't run into that with a query similar to the one I posted above.  What does your query look like right now that is giving you this error?  Post the code.

[Bman900]

SELECT * FROM question WHERE id IN(SELECT id FROM question ORDER BY id DESC LIMIT 10) ORDER BY id ASC

 

But then I changed it to:

 

SELECT * FROM question ORDER BY [id] DESC LIMIT 10

 

which then worked.

[Ken2k7]

What do the brackets around id do?

[Maq]

What do the brackets around id do?

 

I think he took my syntax literally when I gave him this example:

 

Something like?

 

SELECT * FROM question ORDER BY [date or id] DESC LIMIT 10