...

View Full Version : Ordering and Arranging from Mysql database



Sloth_Boy
09-06-2005, 10:13 AM
hi all,

I've been looking at a few tutorials on the net seeing if i can learn php and mysql. Most the forums seem to tell em the same thing, none of them icnlude any tips on what i feel like doing (the tutorials arent very practical).

Anyway, Here is what i already know, i know how to connect to the databse using php and im pretty good with queries like "SELECT * FROM column1 ORDER BY column2 desc LIMIT 6".etc ., im faimilair with mysql_query and all and i know html very well.

But, here's what i want to do. I want to make a news website where all articles are stored in a database (and say there in 'table1'). I want to get that data and display it on my page in a specific order.

So, liek this:
-----------------------------------------------------------------
'heading'
'date' | 'poster'

'body' -----------------------------------------
-----------------------------------------------
-----------------------------------------------

'footer'
-----------------------------------------------------------------

then another article

-----------------------------------------------------------------
'heading'
'date' | 'poster'

'body' -----------------------------------------
-----------------------------------------------
-----------------------------------------------

'footer'
-----------------------------------------------------------------

I want to have 6 of the latest articles on the homepage set out like above.

So, at the top i want the latest article (article with the hghest id), underneath i want the 2nd latest article and so on.

Now, how do i do that basically

I've already design my site and stuff (www.siliconjunkie.com.au (http://www.siliconjunkie.com.au)

So, please, any help and i will much appreciate it. Also, if you can, please tell me what each part means, so if you go while ($query = (whatever)) please tell me what that part does if you can.


Again, you dont know how much i need to know how to do this. Im kinda in a rut and stuff.

So, any help at all will be excellent

:)

Cheers!

NancyJ
09-06-2005, 10:25 AM
This should do it, hope the comments are clear


$query = "SELECT * FROM table1 ORDER BY ID desc LIMIT 6"; //sql query
$result = mysql_query($query); // result is the resource number for the result of the query

while($row = mysql_fetch_array($result)){
//mysql_fetch_array fetches a row of results from the query as an array and
//then increments the internal pointer, so this line basically says, while there
//are still rows remaining, fetch one and assign it to the array $row
extract($row); //extracts row into variables with the same name as their array
//keys, when pulling from a db, this will be column names or aliases used in the query
echo "<h1>$heading</h1>
$date | $poster <br />
<p>$body</p>
$footer";
//displays the data on the screen with html formating
}

NancyJ
09-06-2005, 11:29 AM
::nevermind, its fixed now::

Sloth_Boy
09-06-2005, 11:39 AM
when i put that, i get the headings of all the rows in my database, i really want to know, how to tell php to display say the a certain heading.

So, if i want it to display the heading of the 2nd row (which would be the second latest entry seeing we're desc 'id' with mysql) what do i do

NancyJ
09-06-2005, 12:15 PM
that should display each of the 6 entries one after another... to jump to a specific entry from the result resource you can jump to it using mysql_data_seek($result, $rownum)

Sloth_Boy
09-06-2005, 12:21 PM
You've been a really big help nancy, thanks heaps ay, really appreciate your efforts!!! - everytihng you t9old me is great, now i need to add style to it, thats why i've included my php file below so anyone can have a go.

Here's a the file im working on, see if you can achieve it for me

indexbackup.phps (http://www.siliconjunkie.com.au/indexbackup.phps) - in this file, i want the database data to go inside the div 'item#' tags, you'll see anyway (you'll see, i've made 6 of them, you may delete the other 5, they're there only so you can see basically what i want)

(change the file extension to just .php instead of .phps after you download it)

A big really big thanks to anyone that does this for me??? extra bigger thanks if you put in //comments so i learn.

and basically, so i can see how to use css styles around php

(the reason i want someone to do this for me is so i can see how someone that knows php would do it...

Cheers! - Thanks again nancy

NancyJ
09-06-2005, 12:34 PM
<?php require_once('Connections/connection1.php'); ?>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>SiliconJunkie.com.au - PC News, Reviews, Web Design and PC Forums</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
body {
background-color: #666666;
background-image: url();
background-repeat: repeat-y;
background-position: center;
margin-top: 0px;
margin-bottom: 0px;
}
-->
</style>
<link href="siliconjunkiestyles.css" rel="stylesheet" type="text/css">
<script language="JavaScript" type="text/JavaScript">
<!--



function MM_preloadImages() { //v3.0
var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}
//-->
</script>
<style type="text/css">
<!--
a:link {
color: #895E3D;
}
a:visited {
color: #895E3D;
}
a:hover {
color: #B17A52;
}
a:active {
color: #B17A52;
}
-->
</style></head>

<body>
<div align="center"></div>
<table width="796" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
<!--DWLayoutTable-->
<tr>
<td height="148" colspan="3" valign="top"><img src="/images/layout/sjbanner.jpg" width="800" height="148" border="0" usemap="#Map" class="a"></td>
</tr>
<tr class="nav1">
<td height="31" colspan="3" valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
</tr>
<tr>
<td width="177" height="312" valign="top" class="leftside"><!--DWLayoutEmptyCell-->&nbsp;</td>
<td width="603" valign="top" class="mainbody"><div id="1" class="innerbody">
<?php
mysql_select_db($database_connection1, $connection1);
$query = "SELECT * FROM table1 ORDER BY ID desc LIMIT 6"; //sql query
$result = mysql_query($query); // result is the resource number for the result of the query
$i=0;
while($row = mysql_fetch_array($result)){
//mysql_fetch_array fetches a row of results from the query as an array and
//then increments the internal pointer, so this line basically says, while there
//are still rows remaining, fetch one and assign it to the array $row
extract($row);
$i++;
echo "<div class=\"itemcontainer\" id=\"item$i\">
<div id=\"heading$i\" class=\"articleheading\">
<p>$heading<br>
<span class=\"date\">$date </span><span class=\"poster\">| $poster</span> </p>
<p class=\"articlebody\">$body</p>
</div>
</div>
&nbsp;"} ?>
</div></td>
<td width="20" rowspan="2" valign="top" class="rightside"><img src="/images/layout/rightside1.gif" width="20" height="18"></td>
</tr>
<tr>
<td height="29" valign="top"><img src="/images/layout/leftside1.gif" width="177" height="29"></td>
<td valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
</tr>
<tr>
<td height="160" colspan="3" valign="top"><img src="/images/layout/bottom1.gif" width="800" height="160"></td>
</tr>
</table>
<map name="Map">
<area shape="rect" coords="327,100,361,122" href="#">
<area shape="rect" coords="367,100,423,122" href="#">
<area shape="rect" coords="429,100,471,122" href="#">
<area shape="rect" coords="477,100,571,122" href="#">
<area shape="rect" coords="576,100,632,122" href="#">
<area shape="rect" coords="637,100,734,122" href="#">
<area shape="rect" coords="739,100,789,122" href="#">
</map>
</body>
</html>


you will have to change the variable names to the column headings from your db (I wasnt sure what they were)

Sloth_Boy
09-06-2005, 12:46 PM
thanks heaps ay nancy... you dont know how much i appreciate this.

(you almost mastered the php script, you forgot to put a ; at the end of the "" on the last line... lol)

I'll be learning from this one for a while now, thanks alot

NancyJ
09-06-2005, 12:49 PM
thanks heaps ay nancy... you dont know how much i appreciate this.

(you almost mastered the php script, you forgot to put a ; at the end of the "" on the last line... lol)

I'll be learning from this one for a while now, thanks alot
Good spot :D Its a good sign that you spotted the mistake... many wouldnt and would just complain the script didnt work ;)

I hope all my comments are understandable, if there anything you're stuck on let me know

Sloth_Boy
09-06-2005, 03:00 PM
will do ay, thanks again...

I've found alot of the tutorials out there aren't real discriptive... or are confusing... and they all seem to tell you how to do the same thing.

If anyone wants to write a tutorial out there, please make it as disriptive as possible, write it as if the person viewing it has never even heard of php or mysql. Once i learn php and mysql, ill probably write some tutorials.


Alot of people dont relise how much they can help a person by just giving a few minutes of there time, like nancy.

I now know so much more..., actually, better than writing a tutorial would be to help people out in forums like these, since you can ask questions and get answers directly from your question.

Anyway, when i get tired (which i am now) i tend to go on alot... lol,

anyway, thanks for the 200th time ay nancy, really apreciate the help

Cheers! :thumbsup:

Sloth_Boy
09-07-2005, 08:33 AM
Ok, heres another question...

How do i make it display say only the first 100 words of the 'body' of the article. I know its possible, but have no idea where to start.

Cheers!

NancyJ
09-07-2005, 08:41 AM
split the body on spaces then display 0-99 of the array.


$words = explode(" ", $body);
for($i=0; $<100; $i++){
echo "$body ";
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum