Ordering results from MySQL table based on content

Norman_Graham

I have several texts about historical events stored in a MySQL table. Each text also has a title field. I want to display the texts on a web page, but not just in any old order - it's about history. The titles of the texts determine in which order the texts should be displayed. The usual way of fetching info from a table is like this or similar:

<?php
    mysql_connect("localhost", "root", "password") or
        die("Could not connect: " . mysql_error());
    mysql_select_db("mydb");

$result = mysql_query("SELECT title, content FROM mytable");

while ($row = mysql_fetch_array($result)) {

       echo "$row["title"] . "<br />" . $row["content"]";

}

mysql_free_result($result);
?>

How can I instruct PHP to display the results according to a particular title order, i.e. title 1 and its text, then title 2 and its text? Can I use some sort of array and then specify title[1], title[2] and so on in the echo call? What would the syntax for this be?The texts are in the table in random order because I add them as I learn more information about aspects of the historical events in question. Putting them into the table in the order in which I wish them to be displayed would be easy enough, but seems to defeat the purpose of having a database.

Thank you very much for any help you can offer.

Norman

sONOCOOLO

Perhaps the follwoing does the job:

<?php
    	mysql_connect("localhost", "root", "password") or die("Could not connect: " . mysql_error());
    	mysql_select_db("mydb");

$direction = "ASC"  // ASC for ascending, DESC for descending
	$result = mysql_query("SELECT title, content FROM mytable ORDER BY title $direction");
	while ($row = mysql_fetch_array($result)) {
	echo "$row["title"] . "<br />" . $row["content"]";
}
	mysql_free_result($result);
?>

Norman_Graham

Unfortunately that won't help. I'll explain: I insert the texts into the database whenever I get enough information on a subject to write a little text about it. The texts are about history, so a text on events in the 17th century should appear on the webpage before a text about events in the 20th century, but I may have inserted the text about the 20th century several weeks before I inserted the text about the 17th century. On the webpage, therefore, the text that is in row 7, for example, may actually be the first text on the page, while the text in row 1 should really appear last in order for the sequence of texts to make sense.
How can I stipulate the order in which the rows should be displayed in the webpage? So that I can say (this is just pseudocode, but you see what I mean):

if ($title=="The 17th century") 
{
$title=$title1;
}

elseif ($title=="The 18th century")
{
$title=$title2;
}

//and so on and so forth

$title_order=array($title1, $title2 ...); //etc., etc.

while ($row = mysql_fetch_array($result)) {

for ($i=0; $i<n; $i++) {

       echo "$row[$title_order[$i]]" . "<br />" . "$row["content"]";

}
}

So I'm trying to pull out all the data in a table and put it in order of historical sequence. I actually tried a script very similar to the one I've shown here and all the texts and titles just disappeared from the HTML page - no error messages, just nothing.

Thanks for any help you can give

Norman

diego25

Well, you'll need a field in the database that tells it what century is each text from, otherwise how is the database supposed to know?

After adding that field, you use the code that sONOCOOLO posted:

SELECT title, content FROM mytable ORDER BY theNewCenturyField

Diego

Norman_Graham

Thanks for that Diego. I realised that adding a new field to the table was the blindingly obvious solution last night. I guess I just wanted to find a clever sorting solution in PHP and thus improve my rudimentary knowledge of PHP.

... now for one of those little PHP messages ...

if ($custom=="celebrate Christmas")
{
$greeting="Merry Christmas and a Happy New Year!";
}

elseif ($custom=="no Christmas")
{
$greeting="All the best for 2003!";
}

Norman