gunthertoody
-
Posts
20 -
Joined
-
Last visited
Never
Posts posted by gunthertoody
-
-
I must be missing something because i just get a blank screen. Here the entire code:
$result = mysql_query("SELECT DATE_FORMAT(`date`, '%Y %m'), COUNT(*) FROM customers_history WHERE status IN ('1', '2', '4') GROUP BY DATE_FORMAT(`date`, '%Y %m') ORDER BY DATE_FORMAT(`date`, '%Y %m') DESC LIMIT 12") or die(mysql_error()); $bg = '#ffffff'; //For Initial Row Color while($row = mysql_fetch_array($result)){ $bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors echo "<tr bgcolor='{$bg}'>"; echo "<td><span class='rowWidth1'>"; echo $row['COUNT(*)']; echo "</span></td>"; echo "<td><span class='rowWidth1'>"; echo $row['status']; echo "</span></td></tr>"; -
Thank for the help Keith. I tried to echo it out, but am not sure I'm doing it right as the screen is just blank...
echo "<tr bgcolor='{$bg}'>"; echo "<td><span class='rowWidth1'>"; echo $row['DATE_FORMAT(date)']; echo "</span></td>"; echo "<td><span class='rowWidth1'>"; echo $row['status']; echo "</span></td></tr>"; -
I'm up for the easiest and clearest way to get it done, but I'm a complete novice and am not sure how that would work? If you could spell it out it would be greatly appreciated.
David
-
Sorry, but I should clarify as I didn't made it clear enough.
TABLE NAME: customers_history
COLUMN NAME: status
COLUMN NAME: date
I need to count the number of times "1" or "2" or "4" appear in the "status" column for a given month.
IF I HAVE:
date status
2011-02-25 4
2011-02-14 1
2011-02-03 0
2011-02-01 2
2011-01-21 1
2011-01-12 4
OUTPUT SHOULD BE:
Feb. 2011 3
Jan. 2011 2
(Note the zero isn't counted)
-
it's my output. I haven't done the other two columns yet.
-
MONTH ADDED CANCELLED TOTAL
2009-05-01 105
2009-04-01 76
2009-03-02 90
2009-02-03 109
2009-01-01 168
2008-12-03 106
2008-11-03 100
2008-10-01 112
2008-09-01 127
2008-08-01 162
2008-07-01 131
2008-06-23 83
-
I think i'm getting closer. Below is the revised code. My monthly columns aren't adding correctly and the first month is not the latest. It's starting with May 2009 for some reason.
//Create Variable for Cumulative Monthly Stats echo '<table class="newTable"><tr><th>MONTH</th><th>ADDED</th><th>CANCELLED</th><th>TOTAL</th></tr>'; $result = mysql_query("SELECT date, COUNT(status) FROM customers_history WHERE status ='1' OR status = '2' OR status = '4' GROUP BY MONTH(date) ORDER BY date DESC LIMIT 25") or die(mysql_error()); // Print out result for Cumulative Monthly Stats $bg = '#ffffff'; //For Initial Row Color while($row = mysql_fetch_array($result)){ $bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors echo "<tr bgcolor='{$bg}'>"; echo "<td><span class='rowWidth1'>"; echo $row['date']; echo "</span></td>"; echo "<td><span class='rowWidth1'>"; echo $row['COUNT(status)']; echo "</span></td></tr>"; } echo '</table>'; echo '<br />'; -
I'm a novice and am trying to get monthly totals for a specific column.
TABLE NAME: customers_history
COLUMN NAME: status
If a value in the "status" column equals "1" "2" or "4" I need to count it for that month. Your help is greatly appreciated.
$result = mysql_query("SELECT status, MONTH(date) FROM customers_history WHERE status ='1' OR status = '2' OR status = '4' GROUP BY MONTH(date) ORDER BY date DESC LIMIT 12") or die(mysql_error()); // Print out result $bg = '#ffffff'; //For Initial Row Color while($row = mysql_fetch_array($result)){ $bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors echo "<tr bgcolor='{$bg}'>"; echo "<td><span class='rowWidth1'>"; echo $row['date']; } echo '</span></td></tr>'; echo '</table>'; echo '<br />'; -
Thanks. I'm learning bit by bit and I like the structure you put it in.
Regarding the functionality. It works except "customers_history.customers_id" won't populate into the browser. It goes straight from "ID=" to "&action".
I tend to like to keep some sort of structure, so I would tend to do something like:
echo "<tr bgcolor='{$bg}'>"; echo "<td>"; echo "<a href='https://www.example.com/admin/customers.php?page=1&cID={$row['customers_history.customers_id']}&action=edit'>{$row['customers_firstname']} {$row['customers_lastname']}</a>"; echo "</td>"; echo "<td>" . date('M. d, Y', strtotime($row['date'])) . "</td>"; echo "<td>"; -
Thanks, but I'm a novice and completely lost as to the proper syntax for a variable inside an href. What is the proper syntax?
echo '<tr bgcolor="' . $bg . '"><td><a href="https://www.example.com/admin/customers.php?page=1&cID=<?=$row['customers_history.customers_id'];?>&action=edit">' . $row['customers_firstname'] . " " . $row['customers_lastname'] . '</a></td><td>' . date("M. d, Y", strtotime($row['date'])) . '</td><td>'; -
-
Okay, I think I got most of the syntax right, but how do I pull the variable (customers_history.customers_id) from the SELECT statement and make it populate in the link below?
$query = "SELECT customers_history.customers_id, customers_history.date, customers_history.status, customers_history.plan_id, customers.customers_firstname, customers.customers_lastname FROM customers_history LEFT JOIN customers ON customers_history.customers_id = customers.customers_id WHERE customers_history.status ='1' OR customers_history.status = '4' ORDER BY date DESC LIMIT 10"; echo '<tr bgcolor="' . $bg . '"><td><a href="https://www.example/admin/customers.php?page=1&cID=<?=[b]$customers_history.customers_id[/b]>&action=edit">' . $row['customers_firstname'] . " " . $row['customers_lastname'] . '</a></td><td>' . date("M. d, Y", strtotime($row['date'])) . '</td><td>'; -
Okay. I got the link to work, but only with a static ID. What is the proper syntax for a variable in the bold text? Do I have to qualify it somehow? The variable from the select statement is "customers_history.customers_id".
$bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors echo '<tr bgcolor="' . $bg . '"><td><a href="https://www.phpfreaks.com/admin/customers.php?page=1&cID=[b]['customers_history.customers_id'][/b]&action=edit">' . $row['customers_firstname'] . " " . $row['customers_lastname'] . '</a></td><td>' . date("M. d, Y", strtotime($row['date'])) . '</td><td>'; -
I have to put a link that can be clicked around the first name and last name variables in the echo statement below. I also need to add a variable from the Select query to the link such as: 'www.phpfreaks.com/admin/customers.php?page=1&cID="VARIABLE HERE" &action=edit'
I've tried a bunch of things and none seem to work.
while($row = mysql_fetch_array($result)){
echo '<tr><td>' . $row['customers_firstname'] . " " . $row['customers_lastname'] . '</td><td>' .
date("M. d, Y", strtotime($row['date'])) . '</td><td>' . $row['plan_id'] . '</td></tr>';
David
-
THANK SO MUCH CYBER, that did the trick.
So I guess the rule is if I start a conditional statement resulting in another echo, I must close out the previous echo statement with a semi-colon?
David
-
Ken,
Your code works great, unfortunately, I still need help. What I didn't mention, for the sake of brevity, was that I have 3 more plan_id's and two don't match up. id5 = 6 and id6 =5. That is why I thought that if elseif was the best way to go. I know there's got to be a way, but I'm probably just overlooking proper syntax.
David
-
Thanks but it's still not working :'(
Second looks appreciated.
The "plan_id" column currently holds either 1, 2 or 3 as values. I just want to change the value from a number to text.
Desired results:
when plan_id = 1 echo "1 DVD"
when plan_id = 2 echo "2 DVDs"
when plan_id = 3 echo "3 DVDs"
$bg = '#ffffff'; //For Initial Row Color
while($row = mysql_fetch_array($result)){
$bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors
echo '<tr bgcolor="' . $bg . '"><td>' . $row['customers_firstname'] . " " . $row['customers_lastname'] . '</td><td>' . date("M. d, Y", strtotime($row['date'])) . '</td><td>' . if ($row['plan_id'] == 1) {
echo '1 DVD';
}
elseif ($row['plan_id'] == 2) {
echo '2 DVDs';
}
else {
echo '3 DVDs';
}
. '</td></tr>';}
echo '</table>';
echo '<br />';
-
Thanks, but I'll clarify. The "plan_id" column currently holds either 1, 2 or 3 as values. I just want to change the value from a number to text.
Desired results:
when plan_id = 1 echo "1 DVD"
when plan_id = 2 echo "2 DVDs"
when plan_id = 3 echo "3 DVDs"
-
I am a novice and had this code below working fine until I added the If / elseif to "plan_id". Prior, the column would just list out the numbers from the database, but I want the numbers to echo out the text. Any help is appreciated.
while($row = mysql_fetch_array($result)){
$bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors
echo '<tr bgcolor="' . $bg . '"><td>' . $row['customers_firstname'] . " " . $row['customers_lastname'] . '</td><td>' . date("M. d, Y", strtotime($row['date'])) . '</td><td>' . if ($row['plan_id'] == 1) {
echo "1 DVD";
}
elseif ($row['plan_id'] == 2) {
echo "2 DVDs";
}
else {
echo "3 DVDs";
}
. '</td></tr>';}
echo '</table>';
echo '<br />';
MONTHLY TOTALS
in MySQL Help
Posted
I used it and I got one solid string: 2011 02402011 01592010 12522010 11432010 10432010 09472010 08382010 07292010 06412010 05442010 04422010 0364
restate:
TABLE NAME: customers_history
COLUMN NAME: status
COLUMN NAME: date
I need to count the number of times "1" or "2" or "4" appear in the "status" column for a given month.
IF I HAVE:
date status
2011-02-25 4
2011-02-14 1
2011-02-03 0
2011-02-01 2
2011-01-21 1
2011-01-12 4
OUTPUT SHOULD BE:
Feb. 2011 3
Jan. 2011 2
Thank you for your patience.
David