yourbrain
-
Posts
14 -
Joined
-
Last visited
Never
Posts posted by yourbrain
-
-
Nothing was damaged, thank God.
Anyway, here's more info that might be of use for anyone who can help out...
Two tables: [b]np_articles[/b] and [b]np_categories[/b]
[b]np_articles[/b] has many columns, the most important for this topic are [b]article_id[/b] and [b]article_category_id[/b]
[b]np_categories[/b] only has three columns: [b]category_id[/b], [b]category_name[/b], and [b]category_description[/b].
On my current page, I have a list of my categories. I use the following query: [i]$query_rsCategories = "SELECT * FROM np_categories ORDER BY category_name ASC";[/i]
I have had no problems with this query and have used it often. It lives at the very top of my document.
To display a list of the categories, I use something similar to this,
[code]<p>Total Categories: <?php echo $totalRows_rsCategories ?></p>
<?php do { ?>
<p><?php echo $row_rsCategories['category_name']; ?></p>
<?php } while ($row_rsCategories = mysql_fetch_assoc($rsCategories)); ?>
[/code]
I tried adding this query: [i]$query_rsCountArticles = "SELECT COUNT(np_articles.article_id) AS articlecount, np_categories.category_name FROM np_articles LEFT JOIN np_categories ON np_articles.article_category_id = np_categories.category_id GROUP BY np_categories.category_name";[/i] and adding this line: [code]<?php echo $row_rsCategories['articlecount'];?>[/code] in brackets after my category_name bit (shown above), but no luck.
*sigh*. Help me. anyone.
-
[!--quoteo(post=357946:date=Mar 24 2006, 06:33 PM:name=redbullmarky)--][div class=\'quotetop\']QUOTE(redbullmarky @ Mar 24 2006, 06:33 PM) [snapback]357946[/snapback][/div][div class=\'quotemain\'][!--quotec--]
what errors/results are you getting now?
[/quote]
Nothing shows up in my brackets, I get: Category Name (). As if I didn't do anything.
And my mac battery died on me, so I have yet to check what happened to my file... -
:( This isn't working for me. And it's supposed to be so simple. Argh. Tell me, is there a working example I can see somewhere. Or, can I give you any more info that'll help lead to the problem (other than my non-functional mind)?
-
[!--quoteo(post=357915:date=Mar 24 2006, 04:03 PM:name=redbullmarky)--][div class=\'quotetop\']QUOTE(redbullmarky @ Mar 24 2006, 04:03 PM) [snapback]357915[/snapback][/div][div class=\'quotemain\'][!--quotec--]
it's not $totalRows_rsCountArticles you need. all that does is return the number of records the SQL query found.
[code]
select count(articles.id) as articlecount, categories.cat_name from articles left join categories on articles.article_category_id = categories.id
[/code]
then refer to the article count as $row_rsCategories['articlecount'], not $totalRows_rsCountArticles
[/quote]
I get this error when I try to implent that:
Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause -
I tried this:
[code]SELECT COUNT(*) FROM np_articles INNER JOIN np_categories ON article_category_id=category_id WHERE article_category_id = 3[/code]
as my query. And after my category name I have this:
[code]<p><?php echo $row_rsCategories['category_name']; ?> (<?php echo $totalRows_rsCountArticles ?>)</p>[/code]
But, in preview mode, I still get [b](1)[/b] next to each category name....
Any clue as to why that happened? -
[!--quoteo(post=356719:date=Mar 20 2006, 10:09 PM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ Mar 20 2006, 10:09 PM) [snapback]356719[/snapback][/div][div class=\'quotemain\'][!--quotec--]
SELECT COUNT(colname) AS cntCol FROM tablexyz WHERE cat = 'x';
[/quote]
A few questions please...
What do I put instead of [b]colname[/b]?
And from which table do I select?
My articles table has a column called [b]article_category_id[/b] which carries the Id of the category. The category table only has three columns (id, name, and description).
Do I do an INNER JOIN?
Sorry, new at this... I'm trying different this now as I wait for your reply... but I am not getting good results. So far, this is telling me that each category has 1 article in it... not correct.
-
An update: my limited knowledge of php/mysql has not led me far. I can now enter the date I want as a text only, without the use of a handy drop down menu :(
[img src=\"http://your.brain.googlepages.com/insert_date.gif\" border=\"0\" alt=\"IPB Image\" /]
However, I do not want to give up on this. My computer time is limited now (midterms) but I can spare some time to read any articles or tutorials and try some stuff. Anyway, I might even consider autogenerated code (so if there's any dreamweaver extension out there... lead me to it).
Waiting for help. Who wants to type the date, people. Selecting from a drop menu is nicer and safer (codewise).
-
I have a database with a table for my [b]articles[/b] and another for my [b]categories[/b].
I want to be able to display the [u]number of articles in each category[/u]. For example:[list][*]Tutorials (5)[*]Updates (11)[*]Links (7)[*]Downloads (3)[/list]
How can I do this? I've tried several ways, but none have worked in my application.
Thanks in advance. -
I can't remember where I got the whole $dbDateTime stuff from, I had already built my application and it entered date automatically, but I wanted to have control over the date... so I searched for PHP code, and this one worked well.
I'll try $_POST and your other recommendation and let you know how it goes.
Thank you for replying. -
My page comes out blank when I preview it.
-
I'm starting to feel really stupid. I tried that, but I still get the incorrect date [1970-01-01].
Here's the code for my new_article.php page:
[code]
<?php require_once('Connections/NewsPress.php'); ?>
<?php
mysql_select_db($database_NewsPress, $NewsPress);
$query_rsArticleDates = "SELECT date_format(article_date, '%Y-%c-%e') as ArticleDate FROM np_articles";
$rsArticleDates = mysql_query($query_rsArticleDates, $NewsPress) or die(mysql_error());
$row_rsArticleDates = mysql_fetch_assoc($rsArticleDates);
$totalRows_rsArticleDates = mysql_num_rows($rsArticleDates);
?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO np_articles (article_title, article_category_id, article_description, article_text, article_author, article_date) VALUES (%s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['article_title'], "text"),
GetSQLValueString($_POST['article_category_id'], "int"),
GetSQLValueString($_POST['article_description'], "text"),
GetSQLValueString($_POST['article_text'], "text"),
GetSQLValueString($_POST['article_author'], "text"),
GetSQLValueString($_POST['article_date'], "date"));
mysql_select_db($database_NewsPress, $NewsPress);
$Result1 = mysql_query($insertSQL, $NewsPress) or die(mysql_error());
$insertGoTo = "manage_articles.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
mysql_select_db($database_NewsPress, $NewsPress);
$query_rsMenu = "SELECT * FROM np_categories";
$rsMenu = mysql_query($query_rsMenu, $NewsPress) or die(mysql_error());
$row_rsMenu = mysql_fetch_assoc($rsMenu);
$totalRows_rsMenu = mysql_num_rows($rsMenu);
# part of datetime.php include
$dbDateTime = mktime($_POST['hour'], $_POST['minute'], $_POST['second'], $_POST['month'], $_POST['day'], $_POST['year']);
$dbDateTime = date("Y-m-d H:i:s", $dbDateTime);
// Insert $dbDateTime to DATETIME column
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/default.dwt.php" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>NewsPress</title>
<!-- InstanceEndEditable -->
<!-- InstanceBeginEditable name="head" -->
<link rel="stylesheet" type="text/css" href="Templates/default.css" />
<!-- InstanceEndEditable -->
</head>
<body>
<div id="header"></div>
<div id="container">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td valign="top" id="content">
<div id="spotlight"></div>
<!-- InstanceBeginEditable name="content" -->
<h1>Post a new Article</h1>
<form method="post" name="form1" action="<?php echo $editFormAction; ?>">
<table id="graybox" align="center">
<tr valign="baseline">
<td nowrap align="right">Title:</td>
<td><input type="text" name="article_title" value="" size="32"></td>
</tr>
<tr valign="baseline">
<td nowrap align="right" valign="top">Category:</td>
<td><select name="article_category_id" id="article_category_id">
<?php
do {
?>
<option value="<?php echo $row_rsMenu['category_id']?>"><?php echo $row_rsMenu['category_name']?></option>
<?php
} while ($row_rsMenu = mysql_fetch_assoc($rsMenu));
$rows = mysql_num_rows($rsMenu);
if($rows > 0) {
mysql_data_seek($rsMenu, 0);
$row_rsMenu = mysql_fetch_assoc($rsMenu);
}
?>
</select>
</td>
</tr>
<tr valign="baseline">
<td nowrap align="right" valign="top">Description:</td>
<td><textarea name="article_description" cols="50" rows="5"></textarea></td>
</tr>
<tr valign="baseline">
<td nowrap align="right" valign="top">Article:</td>
<td><textarea name="article_text" cols="50" rows="5"></textarea></td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Author:</td>
<td><input type="text" name="article_author" value="" size="32"></td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Date:</td>
<td>
<?php include('datetime.php'); ?>
</td>
</tr>
<tr valign="baseline">
<td nowrap> </td>
<td><hr /><input type="submit" value="Insert record"></td>
</tr>
</table>
<input type="hidden" name="article_date" value="<?php echo $dbDateTime; ?>">
<input type="hidden" name="MM_insert" value="form1">
</form>
<!-- InstanceEndEditable -->
</td>
<td id="nav" valign="top">
<div id="navheader"></div>
<div id="navcontent">
<p>
<a href="index.php">Front Page</a><br />
<a href="manage_articles.php">Manage Articles</a><br />
<a href="manage_categories.php">Manage Categories</a><br />
<a href="javascript:window.close();">Logout</a>
</p>
<h1>Archive</h1>
<table id="calendar" width="75%" border="0" cellspacing="0" cellpadding="0" align="center">
<tr><td>
<?php
function build_calendar($month,$year,$day) {
/* Declaring the variables */
$daysOfWeek = array('Su','Mo','Tu','We','Th','Fr','Sa');
$firstDayOfMonth = mktime(0,0,0,$month,1,$year);
$noDays = date('t',$firstDayOfMonth);
$dateComponents = getdate($firstDayOfMonth);
$dayOfWeek = $dateComponents['wday'];
$monthName = date('F',mktime(0,0,0,$month,1,$year));
global $rsArticleDates;
global $_GET;
if (mysql_num_rows($rsArticleDates) > 0){
mysql_data_seek($rsArticleDates,0);
while($row_rsArticleDates = mysql_fetch_assoc($rsArticleDates)){
$dates[] = $row_rsArticleDates['ArticleDate'];
}
}
/* Computing the previous month. */
if($month == 1) {
$mn=12;
$yn=$year-1;
} else {
$mn=$month-1;
$yn=$year;
}
/* Computing the next month. */
if($month == 12) {
$mn2=1;
$yn2=$year+1;
} else {
$mn2=$month+1;
$yn2=$year;
}
/* Calendar header: next and previous month links */
$calendar = "<table>";
$calendar .= "<tr><td><a href=day.php?m=$mn&y=$yn&d=$day><</a></td>";
$calendar .="<td colspan=5 align=center>$monthName, $year</td>";
$calendar .="<td><a href=day.php?m=$mn2&y=$yn2&d=$day>></a></td></tr>";
$calendar .="<tr>";
/* Calendar header: Display the days of the week */
foreach($daysOfWeek as $day) {
$calendar .= "<td>$day</td>";
}
$calendar .= "</tr>";
$calendar .= "<tr>";
$currentDay = 1;
/* Fill in the beginning of the calendar body */
if ($dayOfWeek > 0) {
$calendar .= "<td colspan='$dayOfWeek'> </td>";
}
/* Generate the calendar body */
while ($currentDay <= $noDays) {
if ($dayOfWeek == 7) {
$dayOfWeek = 0;
$calendar .= "</tr><tr>";
}
$date = $year."-".$month."-".$currentDay;
if (in_array($date,$dates)) {
$calendar .= "<td><a href='day.php?m=$month&y=$year&d=$currentDay'>$currentDay</a></td>";
} else {
$calendar .= "<td>$currentDay</td>";
}
$currentDay++;
$dayOfWeek++;
}
/* Filling in the end of the calendar body */
if ($dayOfWeek != 7) {
$remainingDays = 7 - $dayOfWeek;
$calendar .= "<td colspan='$remainingDays'> </td>";
}
$calendar .= "</table>";
return $calendar;
}
if (isset($_GET['m']) && isset($_GET['y']) && isset($_GET['d'])){
$month = $_GET['m'];
$year = $_GET['y'];
$day = $_GET['d'];
} else {
$dateComponents = getdate();
$month = $dateComponents['mon'];
$year = $dateComponents['year'];
$day = $dateComponents['mday'];
}
echo build_calendar($month,$year,$day); ?>
</td></tr>
</table>
<p> </p>
</div>
<div id="navfooter"></div>
</td>
</tr>
</table>
</div>
<div id="footer"></div>
</body>
<!-- InstanceEnd --></html>
<?php
mysql_free_result($rsMenu);
mysql_free_result($rsArticleDates);
?>
[/code]
And this is the datetime.php page:
[code]
<!-- DATETIME Drop Menu -->
<!-- MONTH -->
<select name="month" ><?
for($i=2;$i<14;$i++){
$month=date("F",mktime(0,0,0,$i,0,0));
echo "<option value='".($i-1)."'>$month</option>";
}
?></select>
<!-- DAY -->
<select name="day"><?
for($i=1;$i<32;$i++)
echo "<option value='$i'>$i</option>";
?></select>,
<!-- YEAR -->
<select name="year"><?
echo date('Y');
$currYear=date("Y");
for($i=$currYear-6;$i<$currYear+5;$i++)
echo "<option value='$i'>$i</option>";
?></select>
<!-- HOUR -->
<select name="hour"><?
for($i=1;$i<25;$i++)
echo "<option value='$i'>$i</option>";
?></select>:
<!-- MINUTES -->
<select name="minute"><?
for($i=0;$i<60;$i++)
echo "<option value='$i'>$i</option>";
?></select>:
<!-- SECONDS -->
<select name="second"><?
for($i=0;$i<60;$i++)
echo "<option value='$i'>$i</option>";
?></select>
[/code]
Perhaps this will help. From what I understand, the date is being inserted but incorrectly.
I'm using Dreamweaver MX to create this, and I use CocoaMySQL to manage the database... -
Thanks for the quick replies!
[b]obsidian[/b] - No, the last three lines are placed at the top of my [b]new_post.php[/b] page, along with other php code. Is that good? Because I tried placing it after my form and at the bottom of the page, but then I get a message telling me [b]article_date[/b] needs to have a value...
[b]litebearer[/b] - I saw the example, but how can I make it so that it takes the date from the menu and puts it in the database without taking me more than 30 years back?
Oh, and [b]obsidian[/b], nice website! :) -
I am working on a PHP News manager for my website, it's going well so far - but I decided I'd prefer selecting the date of the posts than have it automatically inserted.
I want to be able to select the Month, Day, Year, Hour and Minutes from a drop menu and have that date inserted as [b]article_date[/b].
I used the following code to create the menus:
[code]
<!-- DATETIME Drop Menu -->
<!-- MONTH -->
<select name="month" ><?
for($i=2;$i<14;$i++){
$month=date("F",mktime(0,0,0,$i,0,0));
echo "<option value='".($i-1)."'>$month</option>";
}
?></select>
<!-- DAY -->
<select name="day"><?
for($i=1;$i<32;$i++)
echo "<option value='$i'>$i</option>";
?></select>,
<!-- YEAR -->
<select name="year"><?
echo date('Y');
$currYear=date("Y");
for($i=$currYear-6;$i<$currYear+5;$i++)
echo "<option value='$i'>$i</option>";
?></select>
<!-- HOUR -->
<select name="hour"><?
for($i=1;$i<25;$i++)
echo "<option value='$i'>$i</option>";
?></select>:
<!-- MINUTES -->
<select name="minute"><?
for($i=0;$i<60;$i++)
echo "<option value='$i'>$i</option>";
?></select>:
<!-- SECONDS -->
<select name="second"><?
for($i=0;$i<60;$i++)
echo "<option value='$i'>$i</option>";
?></select>
$dbDateTime = mktime($_POST['hour'], $_POST['minute'], $_POST['second'], $_POST['month'], $_POST['day'], $_POST['year']);
$dbDateTime = date("Y-m-d H:i:s", $dbDateTime);
[/code]
Now, when I post an article and select a date, the article is displayed with the following datetime: [b]1970-01-01 2:59:59[/b]. I wasn't even born then.
Anyway, I am trying to fix this but thought I'd ask, maybe someone here might have a solution. I want my custom date to be the article date.
If any of you can help, I'd really appreciate it.
Thanks in advance.
Count and display total articles in a category
in PHP Coding Help
Posted