MYSQL Query Help

Jus · 27th July 2003, 11:57 AM

I have two Sections in my database, One for news, one for columns

I want a query to do the following :

Pull all of the latest articles (both news and columns) out of the database apart from the first news article.

The problem I have at the moment, is I'm using :

@$outcome=mysql_query("SELECT * FROM articles ORDER BY adate DESC,atime DESC LIMIT 1,9");
if ($sectionID) @$outcome=mysql_query("SELECT * FROM articles WHERE sectionID='$sectionID' AND adate<='$date' ORDER BY adate DESC, atime DESC LIMIT $articlenumber");

The trouble with that is, it pulls the latest articles, apart from the first one whether it is news or not, what I want is, for it to pull the latest articles out excluding the latest news article

For your info, each article has a column called sectionID, where the value is either 1 (News) or 2 (Column)

Can anyone help?

piper · 27th July 2003, 12:56 PM

I'm a bit confused...

How many tables in your database are we talking about?

Can you describe each table that you want to query?

From what I can see, your only selection criteria is the sectionID and adate. Without knowing what these represent it's difficult to fully understand what it is you're attempting.

Jus · 27th July 2003, 01:20 PM

Section ID is either 1 or 2 with :

1 = News Stories
2 = Column Article

adate is article date (IE, what time it was published)

They are located in a table called articles, if you want the full sourcecode of the page :

PHP Code:

 
<table width="100%" border="0" cellspacing="0" cellpadding="0">

<tr>

<td>

<style fprolloverstyle>A:hover {color: #CF0000}

</style>

 
<?

$tempdir="admin/";

require("admin/connect.php");

$date=date("Y-m-d");

$time=date("H:i:s");

$pathimages=substr($pathimages,3,999);

$articlenumber=9; // Set to number of articles you need to display

@$outcome=mysql_query("SELECT * FROM articles ORDER BY adate DESC,atime DESC LIMIT 1,9");

if ($sectionID) @$outcome=mysql_query("SELECT * FROM articles WHERE sectionID='$sectionID' AND adate<='$date' ORDER BY adate DESC, atime DESC LIMIT $articlenumber");

require("admin/failure.php");

while ($access=mysql_fetch_array($outcome))

    {

    $articleID=$access["ID"];

    $title=$access["title"];

    $beginning=$access["beginning"];

    $authorID=$access["authorID"];

    $year=strval(substr($access["adate"],0,4));

    $month=strval(substr($access["adate"],5,2));

    $day=strval(substr($access["adate"],8,2));

    $adate="$day.$month.$year";

    $atime=strval(substr($access["atime"],0,5));

    $imagesetID=$access["imagesetID"];

    $outcome2=$outcome;

@$outcome=mysql_query("SELECT fullname FROM users WHERE ID='$authorID'");

require("admin/failure.php");

$access=mysql_fetch_array($outcome);

$author=$access["fullname"];

unset($file1); unset($thumb1);

if ($imagesetID)

{

    @$outcome=mysql_query("SELECT * FROM images WHERE ID='$imagesetID'");

    require("admin/failure.php");

    $access=mysql_fetch_array($outcome);

    $file1=$access["file1"];

    $thumb1=substr($file1,0,-4)."a".substr($file1,-4,4);

}

@$outcome=mysql_query("SELECT views FROM stats WHERE articleID='$articleID'");

require("admin/failure.php");

$access=mysql_fetch_array($outcome);

$views=$access["views"];

$outcome=$outcome2;

echo '</td></tr><tr><td><p align="justify"><font face="Verdana, Helvetica, sans-serif"><span style="font-size: 8pt">';

echo '<B><a href="http://www.bluesweb.co.uk/news/showarticle.php?articleID=',$articleID,'" target="_top">';

 
echo $title;

echo '</B></a>';

echo '</font><br>';

 
 
if ($file1) echo '<a href="showarticle.php?articleID=',$articleID,'" target="_top"><img src="',$pathimages,$thumb1,'" border="0" align="left"></a>';

echo '<font face="Verdana, Helvetica, sans-serif"><span style="font-size: 8pt">',$beginning,'<P></font>';

}

?><font face="Verdana, Helvetica, sans-serif"><span style="font-size: 8pt"><B><a href="http://www.bluesweb.co.uk/news/archives.php" target="_top">More Stories</a></B> | 

<a href="http://www.bluesweb.co.uk/news/search.php" target="_top"><b>News Search</b></a></td>

</tr>

</table>

</body>

</html>

Output :
http://www.bluesweb.co.uk/news/showarchive.php

piper · 27th July 2003, 02:21 PM

Quote:

Pull all of the latest articles (both news and columns) out of the database apart from the first news article.
.
.
.
The trouble with that is, it pulls the latest articles, apart from the first one whether it is news or not, what I want is, for it to pull the latest articles out excluding the latest news article

How about using just the one select query to load an array:

select * from articles order by adate desc, atime desc

Then just loop through the array from first element to the last.

If the element number is 1 (or if it's the first time in the array loop) then perform an additional check on the sectionID; if sectionID == 1 then do nothing, else display the article.