DW2004/php/mysql Calling last 3 entries

[o_LOBO_o]

Hi,
Im very new at this foum and with PHP/MYSQL.

Im trying to call the last 3+ entries from my DB with this code.

But I can't make this work I can see only the last entry.
Thank you for any help.
_Lobo_

ps:I make use of Dreamweaver 2004 training from the source but if someone
know other good books / forums will be great!


<?php
mysql_select_db($database_conn_newland, $conn_newland);
//$query_News = "SELECT journalID, journal_entry FROM tbl_journal ORDER BY
journalID DESC";
$query_News = "SELECT journalID, journal_entry FROM tbl_journal ORDER BY
journalID DESC LIMIT 3";

$News = mysql_query($query_News, $conn_newland) or die(mysql_error());
$row_News = mysql_fetch_assoc($News);
$totalRows_News = mysql_num_rows($News);

mysql_select_db($database_conn_newland, $conn_newland);
//$query_News1 = "SELECT ID, deutch FROM tbl_news ORDER BY ID DESC";
$query_News1 = "SELECT ID, deutch FROM tbl_news ORDER BY ID DESC LIMIT 3";
$News1 = mysql_query($query_News1, $conn_newland) or die(mysql_error());
$row_News1 = mysql_fetch_assoc($News1);
$totalRows_News1 = mysql_num_rows($News1);

mysql_select_db($database_conn_newland, $conn_newland);
//$query_Recordset1 = "SELECT * FROM tbl_news ORDER BY ID ASC";
$query_Recordset1 = "SELECT * FROM tbl_news ORDER BY ID ASC LIMIT 3";
$Recordset1 = mysql_query($query_Recordset1, $conn_newland) or
die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>

<td><?php echo $row_Recordset1['deutch']; ?></td>

[Joe Makowiec]

On 12 May 2005 in macromedia.dreamweaver.appdev, o_LOBO_o wrote:

> Im very new at this foum and with PHP/MYSQL.
>
> Im trying to call the last 3+ entries from my DB with this code.
>
> But I can't make this work I can see only the last entry.

It looks like you don't have a repeat region:

<?php do { ?>
<td><?php echo $row_Recordset1['deutch']; ?></td>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

Add a repeat region: highlight the row with the entry you want repeated,
go to the 'Server Behaviors' tab, click the + button and select 'Repeat
Region'.

--
Joe Makowiec
[url]http://makowiec.net/[/url]
Email: [url]http://makowiec.net/email.php[/url]

[o_LOBO_o]

YES! it was that, Thank you very much you save me :)

Can oyu please recomend me a book/website where I can learn more of DW2004
becouse I have the trining from the source but It seems that is noth enogh.

_Lobo_

[o_LOBO_o]

please tell me something, if I want to show not the 3 last entries but all the
las entries ? For example I have on my DB the DATE 000-00-00 of the entry so I
will loto show all the last entries from the last day. How I can achive that ?

TNX on advance

[David Powers]

o_LOBO_o wrote:

> Can oyu please recomend me a book/website where I can learn more of DW2004
> becouse I have the trining from the source but It seems that is noth enogh.

If it's the PHP aspect of working with MX 2004 that you are interested
in, you might want to take a look at the book I co-authored:

[url]http://computerbookshelf.com/phpdw.php[/url]

It's been well reviewed, and seems quite popular with readers.

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[David Powers]

o_LOBO_o wrote:

> please tell me something, if I want to show not the 3 last entries but all the
> las entries ? For example I have on my DB the DATE 000-00-00 of the entry so I
> will loto show all the last entries from the last day. How I can achive that ?

Assuming your date column is called "theDate", change the SQL like this:

"SELECT journalID, journal_entry FROM tbl_journal
WHERE theDate = MAX(theDate)
ORDER BY journalID DESC"

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[o_LOBO_o]

*Thank you for the name of the book I need to learn how to work with PHP/MYSQL
on Dreamweaver 2004 I hope have lots of pictures :)

*I made the changes that you seggest and I get this error:
'Unknown column 'Date' in 'where clause' The name of the column where my Date
is I called "Date"



NOW:
$query_LastNews ="SELECT journalID, journal_entry FROM tbl_journal WHERE Date
= MAX(Date)ORDER BY journalID DESC";

BEFORE
//$query_LastNews = "SELECT `Date`, ID, deutch FROM tbl_news ORDER BY `Date`
DESC";

[David Powers]

o_LOBO_o wrote:

> *Thank you for the name of the book I need to learn how to work with PHP/MYSQL
> on Dreamweaver 2004 I hope have lots of pictures :)

The book contains a lot of screenshots showing you how to do things in
Dreamweaver with PHP.

> *I made the changes that you seggest and I get this error:
> 'Unknown column 'Date' in 'where clause' The name of the column where my Date
> is I called "Date"

If you look at your original SQL, you'll see there are backticks (`)
around the name Date. This is because you have used a MySQL reserved
word as the name of the column. You need to put backticks around Date in
the new SQL.

> NOW:
> $query_LastNews ="SELECT journalID, journal_entry FROM tbl_journal WHERE Date
> = MAX(Date)ORDER BY journalID DESC";

This should be

$query_LastNews ="SELECT journalID, journal_entry FROM tbl_journal WHERE
`Date` = MAX(`Date`)ORDER BY journalID DESC";

The backtick is *different* from a single quote. On my PC keyboard it's
immediately above the TAB key. On my Mac keyboard, it's to the left of Z.

> BEFORE
> //$query_LastNews = "SELECT `Date`, ID, deutch FROM tbl_news ORDER BY `Date`
> DESC";

The example I gave was based on your earlier query. If this is the query
you want to use, amend it like this:

$query_LastNews = "SELECT MAX(`Date`), ID, deutch FROM tbl_news ORDER BY
`Date` DESC";

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[o_LOBO_o]

Ok I was playing with that query and I get this (still with erros: <b>Invalid
use of group function</b> )

$query_LastNews ="SELECT `Date`, ID, deutch FROM tbl_news WHERE `Date` =
MAX(`Date`)ORDER BY ID DESC";
//$query_LastNews = "SELECT `Date`, ID, deutch FROM tbl_news ORDER BY `Date`
DESC";

[David Powers]

o_LOBO_o wrote:

> Ok I was playing with that query and I get this (still with erros: <b>Invalid
> use of group function</b> )
>
> $query_LastNews ="SELECT `Date`, ID, deutch FROM tbl_news WHERE `Date` =
> MAX(`Date`)ORDER BY ID DESC";
> //$query_LastNews = "SELECT `Date`, ID, deutch FROM tbl_news ORDER BY `Date`
> DESC";

Hmm, sorry about that. I've done some further testing myself. There is a
very easy way to do this *if* your version of MySQL is 4.1 or higher
(both on your own machine, and at your hosting company).

$query_LastNews ="SELECT `Date`, ID, deutch FROM tbl_news WHERE `Date` =
(SELECT MAX(`Date`) FROM tbl_news) ORDER BY ID DESC";

Unfortunately, if MySQL 4.1 isn't available, you'll need to run two queries:

$query_getLatestDate = "SELECT MAX(`Date`) FROM tbl_news";
$latestDate = mysql_query($query_getLatestDate, $conn_newland) or
die(mysql_error());
$row_LatestDate = mysql_fetch_assoc($latestDate);
$theDate = $row_LatestDate['Date'];

$query_LastNews ="SELECT `Date`, ID, deutch FROM tbl_news WHERE `Date` =
'$theDate' ORDER BY ID DESC";

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[o_LOBO_o]

It seems to work, Thank you!

But tell me if there is an easy way to do this with DWMX2004 tools/behaviors?
something like this:

It looks like you don't have a repeat region:

<?php do { ?>
<td><?php echo $row_Recordset1['deutch']; ?></td>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

Add a repeat region: highlight the row with the entry you want repeated,
go to the 'Server Behaviors' tab, click the + button and select 'Repeat
Region'.

Thank you and advance for all your help.

[David Powers]

o_LOBO_o wrote:

> It seems to work, Thank you!

Good. Progress at last!

> But tell me if there is an easy way to do this with DWMX2004 tools/behaviors?

I've just done it with a database of my own. This is what I did.

1. Create a recordset to extract the date. I discovered that I needed to
create an alias for the MAX(`Date`), like this:

"SELECT MAX(`Date`) as theDate FROM execsummary"

I could then refer to the latest date as:
$theDate = $row_rst_LatestDate['theDate'];

2. Create a recordset for the articles, and use $theDate as the value in
the WHERE clause.

3. Create a repeat region as normal.

The entire script looks like this (don't forget, it's using my column
names, not yours):

mysql_select_db($database_oxwebj, $oxwebj);
$query_rst_LatestDate = "SELECT MAX(esItemDate) as theDate FROM
execsummary";
$rst_LatestDate = mysql_query($query_rst_LatestDate, $oxwebj) or
die(mysql_error());
$row_rst_LatestDate = mysql_fetch_assoc($rst_LatestDate);
$theDate = $row_rst_LatestDate['theDate'];
$totalRows_rst_LatestDate = mysql_num_rows($rst_LatestDate);

mysql_select_db($database_oxwebj, $oxwebj);
$query_rst_LatestItems = "SELECT execsummary.esItemDate,
execsummary.esItemTitleEng FROM execsummary WHERE execsummary.esItemDate
= '$theDate'";
$rst_LatestItems = mysql_query($query_rst_LatestItems, $oxwebj) or
die(mysql_error());
$row_rst_LatestItems = mysql_fetch_assoc($rst_LatestItems);
$totalRows_rst_LatestItems = mysql_num_rows($rst_LatestItems);

<?php do { ?>
<tr>
<td><?php echo $row_rst_LatestItems['esItemDate']; ?></td>
<td><?php echo $row_rst_LatestItems['esItemTitleEng']; ?></td>
</tr>
<?php } while ($row_rst_LatestItems =
mysql_fetch_assoc($rst_LatestItems)); ?>

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[o_LOBO_o]

It seems like there is non a esay way to do this with DW2004. Before Joe show
me how to do something with DW2004:
__________________________________________________
"Add a repeat region: highlight the row with the entry you want repeated,
go to the 'Server Behaviors' tab, click the + button and select 'Repeat
Region'."

RESULT:
<?php do { ?>
<td><?php echo $row_Recordset1['deutch']; ?></td>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
__________________________________________________

But Maybe what you show me is only possible by hand coding ?

[David Powers]

o_LOBO_o wrote:

> It seems like there is non a esay way to do this with DW2004. Before Joe show
> me how to do something with DW2004:
> __________________________________________________
> "Add a repeat region: highlight the row with the entry you want repeated,
> go to the 'Server Behaviors' tab, click the + button and select 'Repeat
> Region'."

That's exactly what you do.

> But Maybe what you show me is only possible by hand coding ?

The only part that needs hand coding is getting the value of the latest
date from the first recordset, and inserting it in the SQL for the
second recordset.

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]